Automating Backups of Your MySQL Databases via Minimal Mysqldump Privileges

Now that MySQL is set up and configured on OS X Server, here’s how to ensure that databases are regularly backed up — while granting to the backup script only the minimal privileges needed for mysqldump.

Why Back Up MySQL Databases Separately?

You might wonder: if you’re already backing up the contents of /Library/Server off-site anyway (“OS X Server Backups to a Remote Server”), or even just via Time Machine or cloning (“OS X Server First Things First: Backup and Recovery and Other Preliminaries”), why bother to make an additional backup of MySQL databases? The answer is that while it is, in principle, possible to restore databases simply by restoring the underlying MySQL data files, in practice it is far far easier — especially if you want to restore just one database — to restore using the SQL instructions which go into a database dump. The underlying data files are not intended to be interacted with directly, whereas the higher level SQL instructions can re-create the database either on your server or on any other. (That’s why, when moving a database-driven site from one server to another — a topic which I’ll come back to in a later article — we always use a database dump rather than trying to anything at all with the underlying files.)

MySQL Backup Automation Scripts

Several automated MySQL backup scripts are available out there. At the simple end, quite a bit can be done with just a couple of lines, like the following dropped into a shell script (hat tip to The Code Cave):

#!/bin/bash
for database in $(mysql -e "show databases"|awk -F " " '{print $1}'); do
	mysqldump $database | gzip -9 > $database.sql.gz
done

At the more sophisticated end are full rotating backup system like the well known automysqlbackup script.

You can download, install, and manually run the automysqlbackup script in its basic default configuration in probably under two minutes. Spend a few minutes configuring it to your liking, however, set it up to be triggered via launchd (see “Replacing Cron Jobs With Launchd on OS X”), and combine this with a regularly scheduled rsync to a remote server — or, at the very least, a copy to a separate disk in your server — and you can have a highly robust, automatic MySQL backup system running regularly in the background with zero intervention.

Since it includes full documentation in a README file as well as additional inline comments in the sample configuration file, configuring the backup script is straightforward, with options available for automatically compressing and encrypting your backups, changing where everything gets stored, and so forth. You can also exclude particular databases from being backed up, for example:

CONFIG_db_exclude=( 'information_schema' 'performance_schema' )

A full set of options is available for rotating the backups, although for many people the default option to keep backups going back for months is probably a bit much. You can, however, disable monthly rotation altogether while retaining daily and weekly rotation, which in my own case provides a decent balance between thoroughness and overkill.

The automatic backup script can be triggered via launchd using the method I’ve described separately (“Replacing Cron Jobs With Launchd on OS X”). Better yet, create a simple shell script which launches the mysqldump script and then rsyncs the results to a remote server, and trigger the shell script with launchd:

#!/bin/bash
/path/to/automysqlbackup /path/to/backup_settings.conf
rsync -avz -e ssh /path/to/local/backups/ user@example.com:path/on/remote/host/
exit #

Launching the backups and the rsync from a shell script enables you to easily modify the backup process independently of its launchd trigger — making it straightforward to add new backup tasks just by modifying the shell script and without issuing launchctl unload and launchctl load commands.

Minimal Mysqldump Privileges

Often, unfortunately, the setup instructions for configuring and using these types of backup systems work on the assumption that you’ll either be running them as root or are happy to store the root MySQL password in a plain text configuration file. But this is neither necessary nor particularly sensible from a security standpoint.

Instead, you can create a dedicated database user with sufficient global privileges to read and backup your databases, but without sufficient privileges to foul anything up too badly. You can use Sequel Pro‘s ‘Users’ screen to do so, granting the user only the following four privileges: SHOW DATABASES, SELECT, LOCK TABLES, RELOAD.

Alternatively, you can create the user and grant these global privileges from the MySQL command line, if you’re feeling adventurous (as in the earlier article “Installing, Configuring and Performance Tweaking MySQL for OS X Server”, just enter mysql -u root -p to interact directly with MySQL):

GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* to 'backup_user'@'localhost' IDENTIFIED BY 'backup_users_password';
FLUSH PRIVILEGES;

Now if you plug these details (‘backup_user’ and ‘backup_users_password’) into a backup script like automysqlbackup, your backup system will have only the minimal privileges necessary to get the job done, without exposing anything as powerful and potentially dangerous as your root MySQL user.

• backups
• macOS
• MySQL