If you are running your own MySQL server, setting up a good backup routine is an inescapable responsibility. If you’re not running your own server, check with your hosting provider to verify that they are indeed backing your data up. If your hosting provider does not and does not at least provide you with way to roll your own database backups, consider switching to someone that does.
A handy utility that we like use to backup databases is AutoMySQLBackup, a mature and very effective Sourceforge project which manages both the backups as well as their compression and organization. It will retain daily, weekly and monthly backups of some or all of your databases to your file system. Couple this with something to move the database backups offsite or an effective server imaging schedule, and you’ve got a pretty good mysql backup process.
To install the utility on Ubuntu, simply run the command-line sequence:
sudo apt-get install automysqlbackup
If you are running on an older version of Ubuntu or a different flavor of Linux, you can grab it from their download page at http://sourceforge.net/projects/automysqlbackup/files
The installation process will install a binary at /usr/sbin/automysqlbackup and a configuration file at /etc/default/automysqlbackup. Simply Issuing
at the command-line will attempt to run the backup process.
By default AutoMySQLBackup will use root as the login name to get access to the databases for backup. In order for it to access the data correctly, you may need to add a line like this to the file:
PASSWORD=[your root password]
If you do put your password in the configuration file, you may want issue a
sudo chmod 600 /etc/default/automysqlbackup
to only allow access to this file by the root user on the server.
AutoMySQLBackup will backup all of your databases and their tables, will compress these backups with gzip (bzip2 is also an option), and then organize them into three folders stored in /var/lib/automysqlbackup/ – daily, weekly, monthly. You can change where these folders are created by editing the BACKUPDIR configuration variable. Each of these folders will contain a folder for each database on your system.
By default, the daily folder will contain all of the last seven days. The weekly folder will grow to contain the database as it was on Sunday each of the last fifty-two weeks. Similarly, monthly will contain the end of all each of the last twelve months.
If you only want to back up certain databases, you can specify them in the DBNAMES configuration variable. Conversely, if you want to backup everything except certain databases, you can use the DBEXCLUDE configuration variable to list what to exclude.
Once you have configured it to your liking, issue a
sudo crontab -e
and add a line like this to your crontab:
0 0 * * * /usr/sbin/automysqlbackup
This will schedule your database backups to occur every night at midnight. Now go get yourself the beer of victory.