If you search for “bacula mysql backup” on Google you won’t find any recipes to make a backup of your MySQL databases. Sure, you can just copy the files, but then you’ll have a big nasty surprise when you need to restore it.

So I decided to make my own script to create a copy of the MySQL database I can back up and restore with Bacula. I run it every day on the server, and it dumps every table to a separate file, overwriting the previous file only if the contents have changed. In this way, when I do an incremental or differential backup, Bacula will copy only those tables whose contents have changed. Of course, consistency among tables is lost, as every table dump is done within a separate transaction, but… well, I’m copying a MySQL database with MyISAM tables, so it’s not like there was much consistency to start with :)

And so I leave here the script in case someone else would like to replicate my setup. And if someone else can tell me if there’s a wonderfully simple way to make backups of MySQL databases with Bacula that I’ve somehow overlooked, I’m all ears :)

#!/bin/bash -e

CWD=$(pwd)
[ -n "$1" ] && CWD="$1"

sql() {
        mysql -B "$@"
}

clean() {
        sed 1d | sort -u
}

was_table_modified() {
        UNCHANGED=1
        for dbfile in /var/lib/mysql/$2/$3.*
        do
                if [ "$1" -ot "$dbfile" ]
                then
                        UNCHANGED=0
                fi
        done
        return $UNCHANGED
}

DATABASES=$(echo "SHOW DATABASES" | sql | clean)

for db in ${DATABASES}
do
        [ -d "$CWD/$db" ] || mkdir -p "$CWD/$db"

        TABLES=$(echo "SHOW TABLES" | sql $db | clean)
        for table in ${TABLES}
        do
                FILE="$CWD/$db/$table".gz
                if was_table_modified $FILE $db $table
                then
                        mysqldump --opt $db $table | gzip > "$FILE".new
                        if [ ! -f "$FILE" ] || ! cmp -s "$FILE" "$FILE".new
                        then
                                mv "$FILE".new "$FILE"
                        else
                                rm -f "$FILE".new
                        fi
                fi
        done
done