I recently took ownership of a shiny new Freecom 400GB USB drive, and with it a renewed understanding of how important data backups are. Automating the backup of files and settings in OS X is pretty easy - I went with iBackup (mainly as it was free) and have been pleasantly surprised at the backup flexibility it gives. MySQL — something I use pretty heavily — is not something it can do. Nor can many other backup tools.

My quest to automate MySQL database backups led me to Andy Budd’s excellent post from 2004. I copied the bash script over and made a few adjustments to allow several databases to be backed up in one run.

#!/bin/bash
 
echo "Backup Script Processing"
 
# navigate to backup dir
if
	cd /Users/kester/Backups/
then
	echo "...successfully navigated to backup dir"
else
	echo "could not locate backup directory"
	exit 0
fi
 
databases=( blog cms justkez project1 work )
${databases[@]}
 
for database in ${databases[@]}
do
	if
		/usr/bin/mysqldump --user=username --password=password $database --opt --add-drop-table > $database`date +%u`.sql;
	then
		echo $database "backed up."
	else
		echo "mysqldump error"
		exit 0
	fi
done

I trimmed things down a bit - some of the output messages are gone as I’m not so bothered about them. The main addition is the array - simply tweak this line to reflect the databases you want to backup and there will be a dump file for each of them output to the destination directory.

The script is then automated through cron (thanks to the wonderful cronnix) to run nightly. I have iBackup include the target directory in its backup run, so the MySQL dump files are copied over along with everything else. Harmony.

The path to your mysqldump might be different - run a quick “locate mysqldump” to track it down.

Extending

This is a small scale job - I will never have enormous databases on my system so I need not worry about optimizing the dumps. If you are not in the same position it would be worth looking at the documentation for mysqldump. In addition, using your default MySQL account with mysqldump is not such a great idea, as it requires your password to be stored in plain text. You might consider creating a read only account specifically for this purpose.

There also seems to be a small bug; the script tries to execute the first entry in the array as a command, often rendering in an output of “Command not found”, but this doesn’t affect the script running.

Download the MySQL Backup Shell Script. And feel free to post some comments with how you do it, or any changes you could make to the script!