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.

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

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

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

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

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.

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

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

In this blog entry, I'll explain how I setup a multi-site Drupal 6 installation with shared databases and single sign-on. This will enable you to store your users (and other desired tables) in a separate database, and share them across multiple sites.

Before installing Drupal I created 3 databases, added 2 mysql users, and granted permissions. Both mysql users will have access to the shared database which will contain the shared tables:

For my setup I wanted to use a single Drupal filesystem, so I created a name-based Apache virtualhost to host both domain names (ms1.erl.dev & ms2.erl.dev):

I downloaded and unpacked the Drupal installation file:

I setup 2 new sites folders in the sites folder, and copied the default.settings.php file into my first site:

At this point my databases and filesystem were ready to go so I installed Drupal for the first site (ms1.erl.dev). During installation, I entered "localhost" for the database host, drupal_ms_1 as the database, and drupal_ms_1 as the database user. I left the db_prefix setting blank (we'll change that later).

Now that Drupal was installed and the tables were created, it was time to modify the installation to enable single sign-on and shared databases.

You'll need to decide which tables to share across the sites. I decided on users, sessions, and authmap (but you could add more as desired, like roles, profile_*, etc). I used the following commands to transfer the desired tables from the drupal_ms_1 database into the shared database, remove the tables from drupal_ms_1, and then replicated drupal_ms_1 to drupal_ms_2:

Next, I modified the settings.php for my first site (sites/ms1.erl.dev/settings.php) to enable the shared database and single sign-on configuration. The $db_prefix variable is used to specify a different database using the [DATABASE].[TABLE] syntax. You'll also need to add the $cookie_domain variable to ensure the cookie set by Drupal will work across both domain names.

I then copied the ms1.erl.dev settings.php file into my second sites folder. Edit the new ms2.erl.dev/settings.php $db_url variable to point to the drupal_ms_2 database (and update the user and password as well).

After you delete your domain cookies everything should be working! To test this out, log into your first site and then open a new window/tab and go to your second site. You should be logged in as the same user. If you log out from one site, you should also be logged out from the other site.

Here's a quick code snippet I just wrote to dump and compress (gzip) all mysql databases on a server into separate files using PHP and PEAR.

It's important to realize that making changes to Drupal (configuration changes, uploading a file, etc) affect both the file system and the database. That's why I feel it's important to integrate your MySQL database with your subversion file system. For instance, if you upgrade a Drupal module, you should commit a backup of your database with the file system changes to ensure you have a definitive snap snot of your project, and can revert to a previous revision as necessary.

All of my Drupal projects are subversion integrated for proper version control. A typical subversion tree resembles:

When I checkout projects to my Linux file system, I use the following command to ensure I checkout everything parallel to the httpdocs folder. I use the "archive" folder for items (like database dumps) that I'd like to keep outside of the httpdocs, so they are not public.

Now that my file system is integrated with subversion, I can backup my MySQL database and check it into subversion before I make a critical change to the Drupal configuration, such as upgrading a module:

NOTE: if you've already checked your database into subversion and you're creating another backup, your svn commands (and output from the svn stat command) will be slightly different. A question mark represents a new file, while a capital "M" represents a modified file:

Let's say you upgrade a module (or Drupal) and get an unexpected error, you can now revert your database: