Q-Spot.org

Backup MySQL/MariaDB with Bacula/Bareos

Note:
In the following text, I call the software MySQL and Bacula, but this works with MariaDB and Bareos, too.

There are many ways to backup a MySQL or MariaDB server - most of them include some kind of "mysqldump to disk and backup that directory".

This works, but it would be nice if you could just pipe the backup to your backup server? You could save the disk space and reduce the time needed.

If you ask Google, you'll find some nice examples how to do this with Bacula and the bpipe plugin. But there is a little catch: Only one dumpfile will be created and backed up. If you have a server with more than one database, and chances are that you need to restore them independently (e.g. hosting multiple client databases), you are out of luck if you're looking for an easy solution.

But there is a way (with a little bash voodoo) - all you need are named pipes, zip (tar/cpio will not work!), mysqldump and a small bash script (debian variant):

#!/bin/bash
UMASK=`umask`
umask 0007
test -d /srv/backup/ || mkdir -p /srv/backup/
umask 0077

# get the database names
DBS=`echo "SHOW DATABASES" | mysql --defaults-extra-file=/etc/mysql/debian.cnf mysql | tail -n +2 | \
     grep -v "information_schema"| grep -v "performance_schema"| grep -v "ndbinfo"`
cd /srv/backup/

# create fifo files with mysqldump processes writing to them
DUMPS=""
for db in $DBS; do
    mkfifo $db.sql
    mysqldump --defaults-extra-file=/etc/mysql/debian.cnf --ignore-table=mysql.event --single-transaction \
       -q -e -K "$db" > $db.sql &
    DUMPS="$DUMPS $db.sql"
done

# backup everything and pipe it to stdout
# don't forget the -q or you will get nasty stuff in the header of the file
zip -FI -q - $DUMPS | cat

# cleanup
for db in $DBS; do
    rm $db.sql
done

umask $UMASK

This script creates a named pipe for each database, with a mysqldump process piping the dump of the database to it. All you need to do now is to collect the data from the pipes in a zip file and send it to the backup tool. This will only work with zip because tar needs to know the size (we don't have one) before it adds the file to the archive.

The Bacula fileset can be defined like:

FileSet {.
  Name = "MySQLPipe".
  Include {
    Options {
      signature = MD5
      compression = gzip
    }
    Plugin = "bpipe:file=/MYSQL/mysql_dumps.zip:reader=sudo /etc/bareos/mysql-dump.sh:
              writer=/etc/bareos/mysql-restore.sh"
  }
}

As you can see, you need a second script to restore the backup on the client. Lets just put the backup somewhere:

#!/bin/bash
cat > /restore/mysql_dump.zip

If you look into the archive, you'll see the dumps as named pipe files with a size, but as soon as you extract them you'll have simple SQL dumps ready to restore your data.