Backup your MySQL databases automatically using Automysqlbackup

When I started managing a server, due to small database I always took manual backups, scarcely though. And because of my laziness in doing so, my fellow author, shredder12 constantly bugged me to do so. So, I started with weird entries in cron until I find this awesome script.

Automysqlbackup is a shell script that lets you take monthly, weekly and daily backup of your MySQL databases. It uses mysqldump to backup your database. With mysqldump you can set an entry in cron to backup your database as well, and there may be many script already available for that. But this script lets you backup multiple databases and compress it using gzip or bzip2. It will rotate the backup, thus not filling your harddisk and if you enable weekly backup, then it will have one for every week.

When using mysqldump for backup of MySQL, by default the master-data option will execute a FLUSH TABLES WITH READ LOCK command which disallows writes to any tables. This will keep the read lock for the entire duration of the dump to ensure that replication position won't change during the dump. This can be a problem when you are running High traffic websites with a large database. As mysqldump might take from a second to few minutes depending on the size of the database. And any user, at this time carring operations that require write to tables during the dump process are locked out. This may not be a problem with InnoDB engine, but with MyISAM (default) this is necessary.

Configuring automysqlbackup

The script is well documented. Download the script, give the +x permission and add the following line on cron

#Daily DBBackup
15 12 * * *   root    /path/to/automysqlbackup.sh >/dev/null 2>&1

Some variables that I set up in the script.

# Username to access the MySQL server e.g. dbuser
USERNAME=chia_sitex

# 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" or all
DBNAMES="db_linuxers"

# Backup directory location e.g /backups
BACKUPDIR="~/sites/db_backup"

MAILCONTENT="quiet"

# Email Address to send mail to? (user@domain.com)
MAILADDR="user@domain.com"

#Now, since I use Drupal I will use drush to set the site in maintenance mode before taking the backup. Just figure out something similar for your CMS :P

# Again this is not necessary but if you are running high traffic site this can help a log

# Command to run before backups
PREBACKUP="drush eval \"variable_set('site_offline',TRUE)\""

# Command run after backups (uncomment to use)
POSTBACKUP="drush eval \"variable_set('site_offline',FALSE)\""