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

Actions

Information

3 responses to “Automating MySQL Database Backups”

6 01 2008
Thibaut Barrère (19:50:05) :

Nice idea James - thanks for sharing!

2 04 2008
Lowell (00:57:26) :

I'm working with the mysql_backup.cgi script from railsplayground. The script is not sending email. I found your article and I've tried your modifications, this is the error I'm getting. Any ideas? Thanks for your help.

Lowell

Software error:
SMTP auth() command failed: No such file or directory
Need MIME::Base64 and Authen::SASL todo auth

3 04 2008
James (21:04:59) :

Hi Lowell, maybe you're on a server which doesn't have the right perl modules installed? Might be worth dropping Rails Playground support a mail.

Cheers,
James