Backing up MySQL with daily snapshots and Bacula

I have decided that from time to time I will be putting here some scrips I find useful. As most sysadmins I am lazy and proud of it - that is exactly what makes me write more and more useful scripts. Following some slightly twisted logic if you are lazy sysadmin as well, you might find those useful (as well). Some code may and will be trivial, but still useful - that is the goal!

Today nothing fancy - just (another) database snapshot script that uses mysqldump to do the job and Bacula to get the daily backups automated.

The goal was to get files for bacula in place, so every run, Bacula can run this script using ClientRunBeforeJob (very convenient feature) and get the fresh snapshot of all databases on that server - one database per file. Then one of my friends asked me to change it to have daily snapshots in separate directories (he doesn't use bacula - poor guy) - trivia... 2 lines more + 1 changed. If you want to use it with separate backups for every day, uncomment those 3 lines and you are done - version below is one for Bacula, as I use it on some servers.

#!/bin/bash
USER=$1
PASSWORD=$2
# DATE=`date +"%Y-%m-%d"`
# mkdir -p /var/backups/mysql/${DATE}
cd /var/backups/mysql/
# cd ${DATE}
DBS=`echo "show databases;" | /usr/bin/mysql -s -u${USER} -p${PASSWORD}`
for DBNAME in $DBS; do
echo Backing up $DBNAME...
    /usr/bin/mysqldump \
        --add-drop-database --add-drop-table \
        --complete-insert --create-options \
        --single-transaction -u${USER} -p${PASSWORD} \
        ${DBNAME} > ${DBNAME}.sql
done
echo Compressing files
rm *.sql.gz 2> /dev/null
gzip -9v *.sql
echo DONE

Nothing really complicated - all you need to do is to tell it the username and password on the command line, user has to have access to all databases - usually database admin user - and yes - this is very unsecure (other users can see those parameters by using 'ps' for example), so please be aware of that risk before you use this snippet!

<OFF-TOPIC>Another question is if we want to encrypt backups... with a bit of thinking and testing you could use gpg for example to encrypt data for a particular 'user' - let it be your backup admin, so you encrypt file using his public key (to avoid passwords being passed over the network or even worse - hard-coded in the backup script), file is backed up, done. When it comes to restore, admin has to restore files from backup, decrypt using his private key, restore data to the databse. Yet another idea worth testing, even if adding encryption means longer locks on tables :-)
</OFF-TOPIC>

During it's daily run, bacula director is executing the script on the client. To make it happen, in bacula-dir.conf all you have to do is to add the following line in the job description.

ClientRunBeforeJob = "/var/backups/mysql/backup.sh <user> <password>"

This approach has several benefits:

1. Database snapshot is run always on time - just before the files are backed up.
2. Bacula starts the process and will wait for it to finish - no more problems with cron job that was started on time but didn't finish yet and we have backed up incomplete or partial files!
3. The text output from this script is included in backup report that admins get via e-mail, so admin can actually see if the job was executed before files were backed up.

Is there anything not to like? Well ok... those backups are full backups - they take space and time - that's all true. Another option would be to have once a week/month/whatever full backup and for the other days use binary logs to save space. Anyway - that is all well documented in MySQL documentation so have some read - it is not a rocket science so anyone can do it.

Nikowek has pointed out during our IRC chat, that using mysqldump with --single-transaction locks the tables. Yes - it does, but the key point is, that sending mysqldump output via pipe to gzip for compression just makes the period when tables are locked significantly longer. That's why the script above doesn't use this approach, so we take snapshot as fast as possible, remove lock from the tables, do compression later. Disk is cheap nowadays... Good spot Nikowek!

Last thing - regarding point 2 above, the worst thing to have is illusion of safety. Backup that is not complete or where restore won't work as expected (or more likely won't work at all) is not worth anything! It's just damaging and creates even greater risk - I've seen people being dismissed from their jobs for reasons like that. Remember to periodically test if the restore procedure works as planned.

Have fun and keep your data safe!