Like most people I’ve got a number of MySQL servers with different databases running things like Wikis and other web-apps. MySQL ships with a handy little tool called mysqldump which can be used to dump ( as the name suggests ) a mysql DB to a text file containing SQL commands necessary to re-create it.

The first thing I like to do is to create a backup user that only has enough privileges to do the backup.

GRANT LOCK TABLES,SELECT,RELOAD,SHOW DATABASES ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'mypasswd';
FLUSH PRIVILEGES;

with this done you should be able to do something like

mysqldump -hlocalhost -ubackup -pmypasswd --databases mysql > test_backup.mysql

With that in place it was an easy task to write a script that can read a config file for the backup login info and options to pass to mysqldump. This script has a number of benefits over sticking a mysqldump command straight into cron:

1. It stores it’s settings in an external config file so you can use the same script in several settings
2. It backs up each database on the server into a separate dump file.
3. The backup options are in the config file so you can back up different servers with different tweaks. e.g locking the DB for consistancy.

the line in the config file looks like

localhost:backup:mypasswd:--opt --add-drop-table

I add a quick script to /etc/cron.daily like

mysql_backup -b /var/backups/mysql -f /etc/mysql_backup

I can now sleep a bit easier. This isn’t the only way to back up mysql, proper DBAs will know all about replication and various other tricks.

Next time: subversion repositories.