Backup your MySQL databases automatically with AutoMySQLBackup

If you site relies on MySQL and stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster (manual mistake to delete some data, software errors, hardware errors, server compromise, etc.). In a previous post I have presented that MySQL provides the basic tool (mysqldump) to perform database backups. This is required because backing up a database is a little different than backing up regular files.

With mysqldump anyone can write a small shell script and running it from cron, it will achieve an automatic backup solution. There are many such scripts already available freely and also many commercial solutions also (I assume as I have not tested any really ). The script that I liked the most is AutoMySQLBackup, because it doesn’t have any real requirements (mysqldump of course is needed - in any mysql client package - and gzip or bzip2 to compress the resulting file) and has all the features I was looking for in such a script.

AutoMySQLBackup has all the features I needed: it can backup a single database, multiple databases, or all the databases on the server; each database is saved in a separate file that can be compressed (with gzip or bzip2); it will rotate the backups and not keep them filling your hard drive (as normal in the daily backup you will have only the last 7 days of backups, the weekly if enabled will have one for each week, etc.). It has also some other features (check the project homepage for full details), that I am not using myself (like email logs for example), but other peoples might find interesting.

The installation is very simple: just download the one file bash script and save it somewhere, customize it to fit your setup (only some basic changes are needed: like the MySQL user and password, backup location), make it executable and activate it in cron as needed (daily for example).

Here are the variables that I usually setup:

# Username to access the MySQL server e.g. dbuser
USERNAME=dbuser
# Username to access the MySQL server e.g. password
PASSWORD=password
# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost
# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="all"
# Backup directory location e.g /backups
BACKUPDIR="/var/backup/mysql"
# Mail setup
MAILCONTENT="quiet"

You can also run the script manually:

./automysqlbackup.sh.2.5

but probably you will want to enable it in cron and run daily. You can just dump it in the /etc/cron.daily/ folder and you should be done. .
Myself I like to place it in /opt and create a symlink to the file (to help with future updates of the script):

ln -s automysqlbackup.sh.2.5 automysqlbackup.sh

and run it from cron by placing in /etc/crontab something like:

#MySQL Daily backup
45 5   * * *   root    /opt/automysqlbackup.sh >/dev/null 2>&1

If needed, after the script is finished, you can save the backup folder where you placed the databases to a remote location (ftp, nfs, smb, or whatever) or another local medium (like tape for ex.). Saving to remote locations is not supported in the script itself so you will have to either mount the remote space locally (if it is possible… for nfs, smb, for ex.) and save directly onto it, or you will have to use another method to upload the files remotely (maybe you just have to include the backup folder used above in your regular backup script).

Are you using a different scrip to backup your databases? I would like to hear of it… Why did you like it? What features were you looking for? Or you had to write your own script because you have not found your needed features in any existing script available freely?