I’ve been working on migrating this site to Drupal v6.2. In order to do so, I’ve created some test/development areas to work through the transition. These areas required me to repeatedly copy my live (version 5) database over to various test and staging servers. I researched and perfected a few one-shot command line recipes to handle all of this. I figured I’d share them with the world.

In each of these examples, the multiple lines are to improve readability. All of these commands should be run on a single line.

First off, we have the basic “mysqldump” command, used to create a backup. This syntax can be found anywhere, but it’s the starting point for the rest of these recipes.

mysqldump -Ce -u[username] -p[password] -h[database server] --add-drop-tables [dbname] > [filename.sql]

A few notes: You don’t want spaces between the -u, -p and -h flags, unlike most command-line tools. The -Ce flags compress the data transfer and output extended inserts in your restore code. This speeds things up considerably, but doesn’t work with every version of mysql.

You can also back up all the databases by substituting “–all-databases” for the database name. You can also pipe this through gzip to compress it by putting “| gzip” in between [dbname] and “>”. Nice for storing regular backups, these files compress nicely.

You can restore pretty easily by just putting the whole thing back through mysql:

mysql -C -u[username] -p[password] -h[database server] [dbname] < [filename.sql]

Since you put the “–add-drop-tables” flag into the backup, any existing tables in your restore DB that you’re restoring will be dropped and re-created. This will speed things up a bit, and makes for a tidier restore. It also helps if there’s been any schema changes since your last backup.

But what if you’re using a database that, say, you’ve upgraded and there’s been new tables added to it that you’d rather not have around? The easiest option is to just drop the database and re-create it. But if you’re on a shared hosting account, you probably don’t have that kind of access. So you’ll have to drop the tables by hand.

This typically involves a query like “drop table ‘table1’; drop table ‘table2’; etc…”, but that’s pretty tedious. Even if you use a graphical or web-based mysql management client, it’s still an awful lot of checkboxes to click. Instead, you can use that handy –add-drop-tables flag to generate a script that will do that for you, dynamically. You just need to make sure you parse out all of the commands to re-create those tables. Here’s what that script looks like:

mysqldump -C u[username] -p[password] -h[database server] --add-drop-table --no-data [dbname] | grep ^DROP > [filename.sql]

(Be sure to give it a different file name than your backup script!)

Or, if you want to get a bit fancier, you can pipe this whole script to a mysql command to drop the tables in one fell swoop:

mysqldump -C u[username] -p[password] -h[database server] --add-drop-table --no-data [dbname] | grep ^DROP | mysql -C -u[username] -p[password] -h[database server] [dbname]

Once you’ve cleared out your database, you can use the same technique to move your production database to your development server in one line:

mysqldump -Ce -u[source_username] -p[source_password] -h[source_database server] --add-drop-tables [source_dbname] | mysql -C -u[destination_username] -p[destination_password] -h[destination_database server] [destination_dbname]

And, finally, put both the clear and copy commands on one line, separated by a semicolon, to execute the whole thing at once. Instant Copy!

mysqldump -C u[destination_username] -p[destination_password] -h[destination_database server] --add-drop-table --no-data [destination_dbname] | grep ^DROP | mysql -C -u[destination_username] -p[destination_password] -h[destination_database server] [destination_dbname] ; mysqldump -Ce -u[source_username] -p[source_password] -h[source_database server] --add-drop-tables [source_dbname] | mysql -C -u[destination_username] -p[destination_password] -h[destination_database server] [destination_dbname]

Admittedly, that’s a lot more than you probably want to type on a single line, so you’ll probably want to write a script. But if you’re just doing this for a day or something while you work on a site upgrade or whatever, it’ll get the job done for you for sure!