Automating MySQL Database Backups

Great, so you’ve got your web application up and running on some server somewhere, and thousands of users are hard at work entering their valuable data into your application, and thence to a MySQL database. Now people depend on your system to keep their data safe. What are you going to do about it?

The approach I’ve taken is to run a backup script on a cron job, which dumps data from the database a few times a daily, compresses it, and emails it off to my GMail account. All those GMail gigabytes have got to be good for something, right :-)?

I started writing a simple backup shell script using mysqldump, gzip and sendmail, but ran into problems on RailsPlayground as sendmail is not supported there. RailsPlayground suggested this perl script for mysql backup (mirrored on my server). The script does pretty much what I was looking for, and also supports skipping tables (useful for ignoring things like session tables, which, for my app, are not really worth backing up).

A little more investigation revealed that the script wouldn’t work out of the box, as RailsPlayground requires SMTP auth. The backup script is not hard to enhance to support SMTP auth. It can be done as follows:

In the configuration section of the file (around line 203), add 2 new variables:

$smtp_user           = "my username"
$smtp_password       = "my password"

Around line 1200, add in the AuthUser and AuthPass:

MIME::Lite->send("$send_method", "$mailprog_or_smtp_host", Timeout=>60,
            AuthUser=>$smtp_user, AuthPass=>$smtp_password);

As an aside, this script can also do backups via FTP.

Restore of mysql dump files is pretty easy. You just use the standard client:

mysql -uMyUser -p MyDatabaseName < SqlDumpFile.sql