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.

Over the last couple of weeks I’ve been chipping away at the problem of our department having no backups whatsoever. Being a small department with few machines and a fairly small amount of data I’ve decided that systems like Bacula and Amanda are over-kill for our situation.

I’ve written a set of small scripts to handle our most pressing backup needs. Over the next few posts I’ll describe how I’ve backed things up and the scripts and tools I’ve used to do it. None of this is rocket-science but if it saves even one person, one hour of work it’ll have been worth writing down.

All the scripts in the next few posts can be found in the WeSC subversion repository.

Part 1: Mysql

Part 2: Subversion

Part 3: Rotating and Culling Backups