Content tagged with: script

Using awk, grep, sed, and mysqldump to script and backup your MySQL databases on the command line

Submitted by Eric on Wed, 08/25/2010 - 20:56

In this tutorial, I'll show how you can use awk, grep, and sed (my favorite command line tools) to backup and archive your MySQL databases. This can be useful to schedule a cron job, transfer your databases to another server, or any other type of scripting.

First, you'll have to get acquainted with connecting to and dumping your database on the command line. Depending on your user, credentials, and where the databases are located, your command might look something like this. Please note, there is no space between the password and the "-p" flag.

$ mysqldump -u user -pPASSWORD -h hostname database > database.sql

To simplify my example, I'm going to shorten the mysqldump command to the follow.

$ mysqldump database > database.sql

Now that we're MySQL command line pros, I'll break down each command. I'll start by showing all the databases.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases"
+---------------------+
| Database            |
+---------------------+
| customers           |
| db_pics_ericlondon  |
| db_thedrupalblog_d6 |
| drupal              |
| drupal-pics         |
| drupalmusicproject  |
| itunes              |
+---------------------+

Now, I'll "pipe" the output from the previous command into awk to show the first column data.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}'
Database
customers
db_pics_ericlondon
db_thedrupalblog_d6
drupal
drupal-pics
drupalmusicproject
itunes

And use grep to remove the first line that says "Database".

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$
customers
db_pics_ericlondon
db_thedrupalblog_d6
drupal
drupal-pics
drupalmusicproject
itunes

And use sed to build the mysqldump command. This one is kinda tricky, sorry. As you can see, I also embedded the date command in there to generate today's date in the format: YYYYMMDD.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$ | sed 's/\(.*\)/mysqldump \1 > \1.'$(date +"%Y%m%d")'.sql/'
mysqldump customers > customers.20100825.sql
mysqldump db_pics_ericlondon > db_pics_ericlondon.20100825.sql
mysqldump db_thedrupalblog_d6 > db_thedrupalblog_d6.20100825.sql
mysqldump drupal > drupal.20100825.sql
mysqldump drupal-pics > drupal-pics.20100825.sql
mysqldump drupalmusicproject > drupalmusicproject.20100825.sql
mysqldump itunes > itunes.20100825.sql

Lastly, if everything looks good, you can pipe the output back to the command line.

Eric-Londons-MacBook-Pro:backup Eric$ mysql --execute="show databases" | awk '{print $1}' | grep -iv ^Database$ | sed 's/\(.*\)/mysqldump \1 > \1.'$(date +"%Y%m%d")'.sql/' | sh

Eric-Londons-MacBook-Pro:backup Eric$ ls -1
customers.20100825.sql
db_pics_ericlondon.20100825.sql
db_thedrupalblog_d6.20100825.sql
drupal-pics.20100825.sql
drupal.20100825.sql
drupalmusicproject.20100825.sql
itunes.20100825.sql

You could even take this one step further and pipe the output through gzip to compress the dumps :)

Add new comment

Configuring a server to parse email via a PHP script

Submitted by Eric on Mon, 01/11/2010 - 00:32

In this tutorial I'll show how you can setup a server to parse email with a PHP script. This tutorial assumes that your server is configured to receive email (I wrote this using a virtual machine running postfix).

The first thing you'll need to do is configure an alias to direct email to a PHP script (instead of an email box). I added the following entry to the bottom of my /etc/aliases file and then ran the "newaliases" command to refresh my aliases database:

phpscript: "|php -q /usr/local/bin/email.php"

The above entry will pipe email sent to phpscript@MYDOMAIN to the designated PHP script.

And here's the script:

#!/usr/bin/php
<?php

// fetch data from stdin
$data = file_get_contents("php://stdin");

// extract the body
// NOTE: a properly formatted email's first empty line defines the separation between the headers and the message body
list($data, $body) = explode("\n\n", $data, 2);

// explode on new line
$data = explode("\n", $data);

// define a variable map of known headers
$patterns = array(
  'Return-Path',
  'X-Original-To',
  'Delivered-To',
  'Received',
  'To',
  'Message-Id',
  'Date',
  'From',
  'Subject',
);

// define a variable to hold parsed headers
$headers = array();

// loop through data
foreach ($data as $data_line) {

  // for each line, assume a match does not exist yet
  $pattern_match_exists = false;

  // check for lines that start with white space
  // NOTE: if a line starts with a white space, it signifies a continuation of the previous header
  if ((substr($data_line,0,1)==' ' || substr($data_line,0,1)=="\t") && $last_match) {

    // append to last header
    $headers[$last_match][] = $data_line;
    continue;

  }

  // loop through patterns
  foreach ($patterns as $key => $pattern) {

    // create preg regex
    $preg_pattern = '/^' . $pattern .': (.*)$/';

    // execute preg
    preg_match($preg_pattern, $data_line, $matches);

    // check if preg matches exist
    if (count($matches)) { 

      $headers[$pattern][] = $matches[1];
      $pattern_match_exists = true;
      $last_match = $pattern;

    }

  }

  // check if a pattern did not match for this line
  if (!$pattern_match_exists) {
    $headers['UNMATCHED'][] = $data_line;
  }

}

?>

At this point in the code, the body of the message will be contained in the $body variable and the headers will be in $headers.

Here is an example of the parsed headers (using print_r()):

Array
(
    [UNMATCHED] => Array
        (
            [0] => From root@Eric-Centos.localdomain  Sun Jan 10 21:49:50 2010
        )

    [Return-Path] => Array
        (
            [0] => <root@Eric-Centos.localdomain>
        )

    [X-Original-To] => Array
        (
            [0] => phpscript
        )

    [Delivered-To] => Array
        (
            [0] => phpscript@Eric-Centos.localdomain
        )

    [Received] => Array
        (
            [0] => by Eric-Centos.localdomain (Postfix, from userid 0)
            [1] => 	id 4D03F30131; Sun, 10 Jan 2010 21:49:50 -0500 (EST)
        )

    [To] => Array
        (
            [0] => phpscript@Eric-Centos.localdomain
        )

    [Subject] => Array
        (
            [0] => This is the subject
        )

    [Message-Id] => Array
        (
            [0] => <20100111024950.4D03F30131@Eric-Centos.localdomain>
        )

    [Date] => Array
        (
            [0] => Sun, 10 Jan 2010 21:49:50 -0500 (EST)
        )

    [From] => Array
        (
            [0] => root@Eric-Centos.localdomain (root)
        )

)

Now, you have all the email headers and message body parsed. You can do whatever your heart desires with the data, like insert it into a database or even create nodes!

3 comments

Installing subversion on a Mac and handling differences in Subversion repository versions

Submitted by Eric on Tue, 09/15/2009 - 19:05

When I got my new MacBook Pro, I installed Xcode which comes with subversion (version 1.6.x):

$ which svn
/usr/bin/svn

$ /usr/bin/svn --version | head -1
svn, version 1.6.2 (r37639)

After installing Xcode I checked out some repositories to my local filesystem. Soon afterward, I realized I needed to be running an older subversion client to stay compatible with some 1.5.x repositories, so I decided to install CollabNet's OSX subversion binary (registration is required for older releases).

After downloading and installing the package (which defaults to /opt/subversion/bin/svn), I edited the /etc/profile file to override priority of my $PATH variable (since I now had 2 versions of subversion installed):

# lines added to /etc/profile:
export PATH=/opt/subversion/bin:$PATH

Now, if I ran a "which" command for svn, the appropriate svn executable is returned:

$ which svn
/opt/subversion/bin/svn

Unfortunately, the subversion projects I checked out using Xcode's subversion were inaccessible due to differences in the .svn structure:

$ cd /path/to/my/1.6.x/repo

$ svn stat
svn: This client is too old to work with working copy '.'.  You need
to get a newer Subversion client, or to downgrade this working copy.
See http://subversion.tigris.org/faq.html#working-copy-format-change
for details.

Luckily, CollabNet has a downloadable python script which allows you to switch checked out repositories to different versions. I downloaded this file and copied it into /opt/subversion/bin.

I was now able to update/downgrade my repositories using this python script:

$ svn --version | head -1
svn, version 1.5.7 (r36142)

$ cd /path/to/my/1.6.x/repo

$ change-svn-wc-format.py . 1.5
Converted WC at '.' into format 9 for Subversion 1.5

Add new comment

A command to add all the new files in your current path to subversion

Submitted by Eric on Fri, 06/20/2008 - 08:02

Here's a command to add all the new files in your current path to subversion:

svn stat | grep ^? | sed 's/?      /svn add "/' | sed 's/$/"/' | sh
svn commit -m "added all my new files"

Add new comment