Secure, Automated MySQL Backups

Set Up a MySQL Backup User

I said we were going to do this securely, right? OK then: The first thing we will not do is use our root user (who should have a password by the way) to do the backup, because hard-coding that password is a large security risk. Instead, we will create a read-only account with access to all of our databases, and use that account when we backup our databases. To do this, simply log in to MySQL as your root user (or if you're in a CPanel, shared hosting setup, just create a read-only user who can access all your databases), and issue the following command:

%mysql> GRANT SELECT, LOCK TABLES ON *.* TO backup@'localhost' IDENTIFIED BY 'PasSW0rd'

Where you replace PasSW0rd with whatever password you want to use. As a test, run the following command:

%prompt> mysqldump -u bakcup -pPasSW0rd --all-databases --lock-tables > test.sql

Make sure the test.sql file gets created, and contains the MySQL commands necesary to restore your database. If not, the most likely problem is that you didn't properly create the new backup user. I won't cover every possible problem here; research any error messages on Google, and the MySQL Manual also isn't a bad place to look for help.

Once you've confirmed your new user is working, you can delete your test.sql file and move on.

Set Up a Remote FTP User

Set Up GnuPG

Some background

GnuPG is an extremely powerful, open-source data encryption program, and this software is what provides the real beauty of this backup approach. When we do our backups, we will immediately compress and encrypt it with GnuPG, in such a way that only we can decrypt it (and only with the proper passphrase). This makes it safe to save the data locally, to send it across the internet via regular FTP (rather than [secure] SFTP, which is not implemented by many servers, or if it is, requires you to login through your root account via SSH), and to save it on a remote server, even if it's in a shared environment where another user might have access to your files.

Most Linux users should have GnuPG installed by default. If not, it it fairly easy to install, and as mentioned in the first note above, it is freely-available for Windows as well. To see if you have it type

%prompt> gpg --version

from the command line. If you have gpg you should see something like:

gpg (GnuPG) 1.2.1
Copyright (C) 2002 Free Software Foundation, Inc.
This program comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to redistribute it
under certain conditions. See the file COPYING for details.

Home: ~/.gnupg
Supported algorithms:
Pubkey: RSA, RSA-E, RSA-S, ELG-E, DSA, ELG
Cipher: 3DES, CAST5, BLOWFISH, AES, AES192, AES256, TWOFISH
Hash: MD5, SHA1, RIPEMD160
Compress: Uncompressed, ZIP, ZLIB

and an error message otherwise.

Setting it up

I'm not going to lose focus here by teaching you every last aspect of how to use gpg if you are not familiar with it, especially because there are many quick HOWTO's available (I personally like David Aspinall's) and the man pages for gpg are actually very good. However, I'll provide enough explanation that you know what you are doing in each step and will understand at least the fundamentals behind this narrow useage of gpg.

Start by typing

%prompt> gpg --gen-key

If you have never used gpg before, it will create your 'keyrings' and config files in a folder called .gnupg in your home directory, and then exit. If so, enter the same command once again, and you should see something like

gpg (GnuPG) 1.2.1; Copyright (C) 2002 Free Software Foundation, Inc.
This program comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to redistribute it
under certain conditions. See the file COPYING for details.

Please select what kind of key you want:
   (1) DSA and ElGamal (default)
   (2) DSA (sign only)
   (5) RSA (sign only)
Your selection?

Choose option 1 and press Enter. When it asks what key size you want, hit Enter to accept the default (1024). Hit Enter again when it asks when you want your key to expire. This will create a key that never expires, which for our purposes here should be fine. It will ask you to confirm; type y and press Enter. It will then ask you for some information about your name, comment, and email. You can provide whatever information makes sense here. For example, you might answer:

Real name: John Doe
Email address: jd@mysite.com
Comment: mysite.com Admin

Make sure the information is the way you want, and then press O (for Okay) and hit Enter.

Last but not least, you need to create a pass phrase. It can have spaces, and it can be any length. It's important that your pass phrase is not too simple or easy to guess, and it is also important that you remember it — if you forget it, there is no way to figure it out again, and more importantly, there is no way to decrypt the files you have encrypted with your key. So, memorable for you, hard to guess for others — that's the name of the game. Here's an example of a strong passphrase (It might be a little short, but it's complicated enough that it's probably Okay):

~~I L!ke my key$ kept Priv@te~~

As you type, the password will not show up on the screen, so type carefully. It will ask you to repeat your password after you've entered it. After you hit Enter the second time, you'll see

We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.

This is your chance to flail at the keyboard (seriously). Type some a bunch of random stuff to help the random number generator gain entropy (disorder) so that you get a strong (random) key. If you see the message repeated,

Not enough random bytes available.  Please do some other work to give
the OS a chance to collect more entropy! (Need 130 more bytes)
fksa jWe need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.

don't worry -- just keep typing some random text. When the program has enough, it will create the key and exit. You'll see something like

public and secret key created and signed.
key marked as ultimately trusted.

pub  1024D/4B4CA59C 2006-04-15 John Doe (mysite.com Admin) 
     Key fingerprint = 621A 1583 5B09 1708 6A33  DAA4 4092 2021 4B4C C5ED
sub  1024g/1659BE41 2006-04-15

Now you've got your new key. Have a look in your .gnupg keyring folder by typing:

%prompt> ls -l ~/.gnupg

and you'll see

%prompt> gpg.conf  pubring.gpg  pubring.gpg~  random_seed  secring.gpg  trustdb.gpg

You should copy your secring.gpg file somewhere else where it will not get lost (I recommend grabbing it by SFTP [if available, otherwise regular FTP] and burning it on a CD-R), so that in case something happens to your server (eg hard disk failure), you will still have your private key.

Let's try encrypting a file. Pick some text file, myfile.txt, and dump some text into it. For this example I'll use some "Lorem ipsum" text:

Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum

Now type (replacing John's email with your own)

%prompt> gpg -e -r jd@mysite.com myfile.txt

this will create a file called myfile.txt.gpg, that will look like a real mess if you open it in a text editor:

$
u2008^T^Odô203k^K^[{ë b>÷]^Z£226×^O^DH234LYÜ~@Ï·v237q¨8ßC/²^@A×233P¶^@´H8aìí210}âC235230Ø'ö·210¯2313@nâ^Cc¤"Ae^A"^KÏ

Your file is encrypted. Let's get it back:

%prompt> gpg -o myfile_decrypted.txt -d myfile.txt.gpg

This will create a file called myfile_decrypted.txt. Enter your passphrase when it asks you for it. If you enter the wrong one it will give you a couple more chances and then quit if you cannot supply the right one. If you enter the right passowrd, it will decrypt your file for you. Open up myfile_decrypted.txt, and you'll see it's identical to myfile.txt.

Now you have everything you need set up: we're ready for the good stuff.

The Backup Script

Once you have everything set up, all you have to do is create the following script, with the variables at the top set up for your system. Most of the variables should be fairly self-explanatory. You may want to pay particular attention to the SQL_options which contains the options passed to mysqldump, so that your backups are done exactly as you want them. Personally, I back up my table structure and data separately (and only back up my structure when I change it), so that is how I have it set up by default. The other is the rotation_length variable, which controls how long to keep backups. Each time this script runs, it will delete the backup files (both locally and remotely) that are more than rotation_length days old.

You can copy the script from above, view it on its own page or download it.

After you have your script setup (I call mine mysql_backup), change the permissions to 700 (so only you can view, edit, or execute it), replacing the path shown with your own:

%prompt> chmod 700 /home/jd/mysql_backup

and then run it

%prompt> /home/jd/mysql_backup

Look in the local and remote backup directories you specified, and make sure the backup files were created.

Make the Backup Automatic

At this point, you have a script that will perform secure backups for you on-demand. The last step is to make it run automatically at set intervals so you don't have to worry about it. How often you choose to run the script depends on how much space you have to store the files, how often your database changes (there may be no point in backing up every 12 hours if your database gets new entries only once every few days), how important your data is, and how paranoid you are about losing it.

Whatever schedule you choose, you'll need to use cron to run the following command:

/home/jd/mysql_backup

or if you don't ever want any output emailed to you:

/home/jd/mysql_backup > /dev/null

Since the Internet is full of HOWTO's on cron and crontab, I'll not waste space here going over the various methods of scheduling new tasks (see the linked HOWTO if you have questions; it is thorough yet accessible).

Conclusion

Congratulations - you're done. If you've made it this far, I hope you'll agree with my statement above that it reall wasn't as bad as it looked.

As a parting thought, consider that this method of backups is not really restricted to MySQL. In fact, only one line in the script issues the mysqldump statement that produces the output file. You already know how to encrypt files using GnuPG, and compressing directories with tar is no trouble at all. You might apply the methods we used here to write a script (or extend mine) to back up important folders on your file system:

• tar/gzip your folder
• GnuPG-encrypt the .tar.gz file
• Save it locally and FTP it to your backup server
• Erase old backups
• Go grab a cold one and give yourself a pat on the back ;-)

The nice thing is, you've already set up your FTP account and GnuPG keys, so doing additional backups should be very easy. Good luck and happy backups!