Bugzappy's Blog

tarball: dbbak.tar.gz

I wrote this script as a very simple way to take any MySQL server and back up all the databases matching a certain name pattern, using a single command and no extra parameters. Simply log onto your server, run dbbak.sh and you have a new DB snapshot on your file system, and a copy somewhere on the Amazon cloud in case your server melts down.

The cool thing is that once you are set up, you don’t have to worry about naming your backups, or where to store them. Names are generated automatically based on the date and time, and the name of the server (arbitrarily set by you). The files are compressed, and saved on S3 as well.

Saving to S3 is performed by another-s3-bash, which is included in the tarball above.

update, 2009/10/04: I cleaned up the main shell script and the INSTALL instructions. In some cases the s3 client does not seem to be able to connect to AWS S3. I have not debugged that yet.

This simple technique is something I picked up from another developer who often needs to migrate loads of data from one CMS system to another. It simplifies the automatation of the migastion the process by removing the need to generate (and manage) a CSV file for each table.

Instead of aiming to create a CSV file for each table in the new database, do this:

1. Export the data from the old database. Instead of dumping the data from the old databases table by table, you may get better mileage by dumping custom-tailored SQL views consisting of multiple tables JOINed together.

2. In phpMyAdmin, you can dump all the tables and views from your database to a single Excel spreadsheet. Or generate individual CSV files and assemble them into an Excel spreadsheet.

3. If you need to escape characters in your data, the easiest way is to copy the data to a text editor, do the substituions (e.g. replace ‘ with \’) there, then copy back into the spreadsheet;

4. Create additional tabs or tables in the spreadsheets that re-organize the data into a form that is closer to the desired end result;

5. In an additional column, generate SQL insert statements using the CONCATENATE function in Excel. A very simple sample:

Sample Excel for SQL Generation

DOWNLOAD: Sample Excel for SQL Generation

6. Copy the cells that contain all your SQL insert statements, one entire column at a time, from the spreadsheet to any text editor and save. Alternatively, paste straight into the SQL input form of phpMyAdmin or your favorite SQL database client.

7. As an add-on, sometimes you’ll want to insert data into temporary tables, then post-process the data using a PHP (or RoR, Java, C#, etc.) script, to make the final inserts into the real database tables.

The idea is to break down the migration process into any number of steps that are as automated as possible, each leveraging the advantages of a specific tool. Don’t try to tackle all of it with just one tool.

Another tip: before you start adding to your new database, get to a point where you have a complete migrated data set, in a form such as Excel spreadsheets or SQL files, and which you can run again if you ever need to start again. Reproducibility is key — data migration requires a lot of attention to details, and invariably you will not get it right on first try.