How To Backup MySQL to Amazon S3

There are all sorts of ways to backup MySQL to Amazon S3 floating around the interent, but the only problem is that many of them require all sorts of things. I wanted to have my MySQL database on my WHM/cPanel server backed up automatically to Amazon S3, in the easiest way possible. Here’s what I did:

Get Amazon S3

It’s dead cheap and way easy to signup. There is a button to the right of this page. Once you have it all ready to go, hover over the “Your Web Services Account” and click the “AWS Access Identifiers” link. This is where you can get the secret code and access key needed later on.

Install Net::Amazon::S3

This you may or may not have to do, but it’s way easy with WHM. Just login, on the left menu find “Install a Perl Module”. Then enter Net::Amazon::S3 and click search. It found 3 items for me, just click on the one that says “Net::Amazon::S3”. Hard wasn’t it?

Install BackupManager

Note that I’m using the Devel 0.7.9 version because it supports S3. The commands below were via SSH logged in as root.

1. # wget http://www.backup-manager.org/download/backup-manager-0.7.9.tar.gz
2. # gzip -d backup-manager-0.7.9.tar.gz
3. # tar -xvf backup-manager-0.7.9.tar
4. # cd backup-manager-0.7.9
5. # make install
6. # cp /usr/share/backup-manager/backup-manager.conf.tpl /etc/backup-manager.conf

Configure BackupManager

There is a whole bunch of configuration options available, but I’ll only go through the options to *only* backup a MySQL database and *only* send it to Amazon S3. This should be enough to get you started.

1. # pico -w /etc/backup-manager.conf
2. Use CTRL + W to find the text below, and change accordingly. Bold text is what you want to change to your specific settings.

• export BM_ARCHIVE_METHOD=”mysql”
• export BM_MYSQL_DATABASES=”dbname1 dbname2 dbname3“
• export BM_MYSQL_ADMINLOGIN=”mysqlusername“
• export BM_MYSQL_ADMINPASS=”mysqlpassword“
• export BM_UPLOAD_METHOD=”s3″
• export BM_UPLOAD_S3_DESTINATION=”s3bucketname“
• export BM_UPLOAD_S3_ACCESS_KEY=”s3accesskey“
• export BM_UPLOAD_S3_SECRET_KEY=”s3secretkey“

1. Press CTRL + X to close and “Y” to save

Sneaky Secret

This will only work if you have *all* InnoDB tables. If you do, and you want backups to not stall your server, let’s get your hands dirty:

1. # pico /usr/share/backup-manager/backup-methods.sh
2. CTRL+W (search for –opt)
3. Replace “–opt” with “–opt –single-transaction”
4. CTRL+X to close and “Y” to save

This will turn on snapshot backups. InnoDB is able to take a snapshot of the entire database and dump it without locking the tables. This is the difference between your server database holding up the entire server and things running as if no backup is running. Some day I’ll contact the backup-manager people to have them put it in..

Run Configure BackupManager

The first time you run it, you want to do it while you are there with verbose turned on. This way if any errors happen, you’ll know right away. To run it simply type:

1. # /usr/sbin/backup-manager –verbose

Setup the Cron Job

If that runs without any errors, then the next step is to setup your cron jobs. Type the following, substituting the bold 45 for the minute you want it to run, and the bold 1 for the hour you want it to run. Remove –verbose if you don’t want to get emails with all the details.

1. # crontab -e
2. press o
3. type “45 1 * * * /usr/sbin/backup-manager –verbose“
4. press esc
5. type ZZ to save or :q! to quit if you make a mistake
6. # crontab -l

The last command should list all your cron jobs. If it looks good, your golden. Just don’t forget that the quotes are not part of the command. You are typing what is inside the quotes.

Accessing your Files with Firefox

Well this is the kicker..  Accessing the files with S3 Firefox Organizer is way easy. It’s a plugin that sits in your bottom status bar of Firefox and you can just click it anytime and see your files. You can easily use it to synchronize folders on your computer too (so you can easily download those backups anytime!).