AutoMySQLBackup is a clever script that takes the pain out of setting up automated incremental daily, weekly, and monthly backups of MySQL databases. Its only dependency is the mysqldumpcommand, which comes with mysql-client. AutoMySQLBackup dumps your databases at whatever intervals you desire, and rolls them up into compressed tarballs. Then you can copy the tarballs to other media, and encrypt them if you wish.

Getting AutoMySQLBackup

Download AutoMySQLBackup from Sourceforge, and then unpack the tarball:

$ tar zxvf automysqlbackup-v3.0_rc6.tar.gz

Now you should have the automysqlbackup-v3.0_rc6 directory with six files in it: automysqlbackup, automysqlbackup.conf, CHANGELOG, install.sh, LICENSE, and README.

Next, read the README because it contains installation instructions and other useful information. It may not tell you everything you need to know, so hopefully this article will fill the gaps. Then your next step is to run install.sh, and this requires root privileges. If the installation script fails you can easily install AutoMySQLBackup manually by following these steps. These are run from the directory where you unpacked the tarball:

# chown root:root automysqlbackup
# chmod 0700 automysqlbackup
# cp automysqlbackup /usr/local/bin/
# mkdir /etc/automysqlbackup
# cp automysqlbackup.conf /etc/automysqlbackup/automysqlbackup.conf
# cd /etc/automysqlbackup
# cp automysqlbackup.conf servername.conf

Replace “servername.conf” with whatever you want to name your configuration file. Then you will have a copy of the original for reference, and your working configuration file.

Configuration Options

The configuration file is large and well-commented, and you need to change only a few things to start making and testing your backups. First you need to select a MySQL user that has, at a minimum, SELECT privileges, and then enter that user and user’s password:

# Username to access the MySQL server e.g. dbuser
CONFIG_mysql_dump_username='db_user'
# Password to access the MySQL server e.g. password
CONFIG_mysql_dump_password='sekkritpassword'

There is a complication if you are using Parallels Plesk 10.2 or newer on your server, and you want to authenticate with the admin user. It is better to use a different MySQL user, like one you have created with minimal privileges just for AutoMySQLBackup, but if that is not possible then you can’t use the plain-text admin password. Starting with version 10.2 the Plesk admin password is encrypted in /etc/psa/.psa.shadow. You can dump this password in plain text with this command if you need to recover it:

# /usr/local/psa/bin/admin --show-password

But you can’t use the plain text password in your AutoMySQLBackup configuration. Instead, use the encrypted password:

# Password to access the MySQL server e.g. password
CONFIG_mysql_dump_password=`cat /etc/psa/.psa.shadow`

Your server is localhost, and you need to create or choose a directory to store your backups in:

# Host name (or IP address) of MySQL server e.g localhost
CONFIG_mysql_dump_host='localhost'
# Backup directory location e.g /backups
CONFIG_backup_dir='/home/mysqlbackups'

You have a lot of control over which databases are backed up. If you have a lot of them, then leaving CONFIG_db_names=() empty copies all of them. Then if there are any you want to exclude, just create an exclude list. One of my servers hosts over 150 virtual Web hosts, and each one has its own MySQL database, so this is a huge timesaver for me:

# List of databases for Daily/Weekly Backup e.g. ( 'DB1' 'DB2' 'DB3' ... )
# set to (), i.e. empty, if you want to backup all databases
CONFIG_db_names=()
# List of databases for Monthly Backups.
# set to (), i.e. empty, if you want to backup all databases
CONFIG_db_month_names=()
# List of DBNAMES to EXLUCDE if DBNAMES is empty, i.e. ().
CONFIG_db_exclude=('information_schema' 'test_db' 'demo_db' )

Rotation settings are straightforward. This example performs monthly backups on the 3rd of every month, weekly backups on Saturdays, daily backups are kept for 7 days, weekly backups are kept for 14 days, and monthly backups for 30 days.

# Which day do you want monthly backups? (01 to 31)
# If the chosen day is greater than the last day of the month, it will be done
# on the last day of the month.
# Set to 0 to disable monthly backups.
CONFIG_do_monthly="03"
# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
# Set to 0 to disable weekly backups.
CONFIG_do_weekly="6"
# Set rotation of daily backups. VALUE*24hours
# If you want to keep only today's backups, you could choose 1, i.e. everything older than 24hours will be removed.
CONFIG_rotation_daily=7
# Set rotation for weekly backups. VALUE*24hours
CONFIG_rotation_weekly=14
# Set rotation for monthly backups. VALUE*24hours
CONFIG_rotation_monthly=30

There are two items that control notifications. I like to see the logfiles after every run, as this example shows:

# What would you like to be mailed to you?
# - log   : send only log file
# - files : send log file and sql files as attachments (see docs)
# - stdout : will simply output the log to the screen if run manually.
# - quiet : Only send logs if an error occurs to the MAILADDR.
CONFIG_mailcontent='log'
# Email Address to send mail to? (
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 )CONFIG_mail_address='
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 '

First Run

For the first few runs, while you’re getting familiar with AutoMySQLBackup, I recommend backing up just one or two small databases. It’s safe and can’t hurt your databases, but the larger your databases the longer it takes to try different configuration tweaks. Run it from the command line like this:

# automysqlbackup /etc/automysqlbackup/servername.conf

Then you can check your backup directory to see if it worked. It should have created all the necessary directories:

# ls /home/mysqlbackups/
daily/  
fullschema/  
latest/  
monthly/  
status/  
tmp/  
weekly/

And made entries like this, for each database you backed up, in the daily/ directory:

-rw-r--r-- 1 root root 66428 Oct 25 00:12 daily_db_name_2012-10-22_00h05m_Monday.sql.gz

AutoMySQLBackup generates a detailed log file, so there will be no mysteries about what happened if anything goes wrong.

The README offers a simple backup script:

#!/bin/sh
/usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf
chown root.root /var/backup/db* -R
find /var/backup/db* -type f -exec chmod 400 {} ;
find /var/backup/db* -type d -exec chmod 700 {} ;

You’ll need to change myserver.conf to your configuration file name, and /var/backup/ to your own backup directory. This script ensures that the backup files owner is root, and that they are read-only, which are simple precautions against accidental changes. Don’t forget to make it executable.

Scheduled Backups

Good old cron is still champ for scheduling backups. This example runs the backup script every morning at 12:05AM:

5 0 * * *  /usr/local/bin/backupscript 

Offsite Backups

Now that you have these nice database backups, what do you do with them? I use Horcrux, which we learned about last week, to encrypt and copy them to an offsite backup. So all I have to do to add my database backups to my Horcrux backup is to add this line to my Horcrux configuration:

+ /home/mysqlbackups/

Because these are compressed tarballs, you can manage them like any ordinary files and copy them to whatever backup media you want.

What about restoring from backup? AutoMySQLBackup doesn’t offer any special tools for making that any easier, so you’ll have to do it manually by first unzipping the backup file, and then using the mysql client to restore your database or databases. The README and forums are helpful, but what will help the most is knowing how to administer MySQL databases.