Backup a MySQL Database

I had originally used a tar backup script to backup my MySQL databases, but have been shown a better method by some members of FedoraForum.org. The problem with a tar backup script is the database is running and if a file is locked, the backup may not succeed.

Thankfully MySQL provides a command for this called 'mysqlhotcopy' which can be used to make a copy of a database that is currently running. The script below uses this command to duplicate the databases into /tmp/db and then tar's the copied database into my backups directory.

#!/bin/sh

backuppath="/backup/files"
curdate=`date "+%Y-%m_%B"`

mkdir /tmp/db
nice mysqlhotcopy db1 db2 db3 /tmp/db -q
cd /tmp/db
nice tar --ignore-failed-read --same-owner -s -p -uf $backuppath/mysql_$curdate.tar .
rm -R /tmp/db/* -f
rmdir /tmp/db

exit 0

The backuppath= defines where to store the final tar backup file. curdate= contains the current date in the format YYYY-MM_Mmmm (e.g. 2005-12_December).

We then create a db directory in /tmp and run the 'mysqlhotcopy' command to duplicate our databases into this directory. You must specify each directory by name, I've used db1, db2 and db3, you would use whatever names your databases are stored as.

We then tar the duplicated data into the $backuppath directory and remove the /tmp/db directory. Remember to run the tar and mysqlhotcopy commands with nice to keep the server running smoothly.

Thanks to pparks1 and brunson for the info on how to do this.