MySQL backups with mysqlhotcopy

When considering a backup strategy for your mysql databases, there are 2 utilities recommended for doing the job - mysqldump and mysqlhotcopy. While mysqldump offers a broad range of possibilities for both backup and recovery, mysqlhotcopy is often a better solution. It is important to note that mysqlhotcopy will only work for tables that use MyISAM or ARCHIVE storage engines.

The reason that mysqlhotcopy is often the better solution is that it results in a synchronized and stable duplicate set of files for your database that can be restored simply by moving the files. There's no need to re-import data or re-create table structures. Additionally, when working with very large databases, mysqlhotcopy is significantly faster than mysqldump. For more information on the benefits and limitations of mysqlhotcopy, please refer to http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html

Here's a simple shell script I found that can be easily modified to suit anyone's situation:

#!/bin/sh

# Custom backup script by Ben
# Edited by Todd for vpslink wiki
# This will back up the selected databases and gzip the results. It will then
# rotate the results to keep 4 weeks' worth of backups on hand.
# Edit the three variables below to list your database and authentication information
# note that DBNAME can be a comma separated list of database names 

DBNAME=mysql
USER=root
PASS=rootpass

if [ -x /usr/bin/mysqlhotcopy ]
then

   # Create copy of database files in /var/lib/_old
   mysqlhotcopy -u $USER -p $PASS $DBNAME --allowold --quiet

   # tarball it to a temp file
   cd /var/lib/mysql
   tar -czf /usr/backup/$DBNAME.tar.gz.temp $DBNAME/

   # If the tarball above got created,
   # rotate the old backups -- keep only the last 4 weeks
   if [ -s /usr/backup/$DBNAME.tar.gz.temp ]
   then
        savelog -l -q -c 4 /usr/backup/$DBNAME.tar.gz
        mv /usr/backup/$DBNAME.tar.gz.temp /usr/backup/$DBNAME.tar.gz
   fi
fi

If you edit and save the script above on your server as /usr/local/bin/mysqlbackup then you can automate the backup process in cron. The example below would run the script every day at 4 AM if placed into the crontab.

0 4 * * * /usr/local/bin/mysqlbackup