application_specific_backups:mysql [Bacula DokuWiki]

- MySQL
  - Using full dumps
  - Using binary logs

This is a simple script to create a snapshot of a MySQL server. It finds all databases in the server, and creates a single gzipped dump of each one using the mysqldump command, suitable for being backed up by bacula. This script should be defined as a client run before job attribute.

Note that this is only guaranteed to produce a snapshot free from table corruption - not data inconsistencies! If, for example, this script runs in between two different updates or inserts, only one of them will get recorded, and the resulting data set may appear corrupt to the application. You must ensure that this script only ever sees a consistent snapshot of data, such as by shutting down the offending applications, or ensuring that the applications are using transactions.

#!/bin/sh

# Directory to store backups in
DST=/data/spool/dbback
# A regex, passed to egrep -v, for which databases to ignore
IGNREG='^snort$'
# The MySQL username and password
DBUSER=root
DBPASS=password
# Any backups older than this will be deleted first
KEEPDAYS=7

DATE=$(date  +%Y-%m-%d)

cd /var/lib/mysql

find ${DST} -type f -mtime +${KEEPDAYS} -exec rm -f {} \;
rmdir $DST/* 2>/dev/null

mkdir -p ${DST}/${DATE}
for db in $(echo 'show databases;' | mysql -s -u ${DBUSER} -p${DBPASS} | egrep -v ${IGNREG}) ; do
        echo -n "Backing up ${db}... "
        mysqldump --opt -u ${DBUSER} -p${DBPASS} $db | gzip -c > ${DST}/${DATE}/${db}.txt.gz
        echo "Done."
done

exit 0

A more sophsicated backup script can be downloaded from http://sourceforge.net/projects/automysqlbackup/

Also note that if you are using InnoDB tables exclusively, you can safely use the LVM technique to back up your tables. InnoDB guarantees that a complete snapshot of what is on disk at any given moment will result in a consistent snapshot of the database.

Using binary logs

The above method has some disadvantages with large databases. Every time you make a backup, even only an incremental backup is scheduled, a full dump of your database is made. This produces a lot of redundant data.

MySQL has the ability to write a transaction safe binary log of all database changes. (See chapter 5 of the MySQL documentation http://dev.mysql.com/doc/refman/5.0/en/binary-log.html)

To use this, you must enable binary logging in your mysql (my.cnf) configuration.

...
log_bin = /var/lib/mysql_dump/bin/bin
...

Use the following script as your backup-before script:

#!/bin/bash

LEVEL=$1
DUMPDIR=/var/lib/mysql_dump
USER=root
PASSWORD=xxx

if [ "$LEVEL" == "Full" -o "$LEVEL" == "Differential" ]
then
  echo "Full/Differential"
  mysqldump -u $USER --password=$PASSWORD --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > $DUMPDIR/dump.$$.sql
  mv $DUMPDIR/dump.$$.sql $DUMPDIR/dump.sql
else
  echo "Incremental"
  mysqladmin -u $USER --password=$PASSWORD flush-logs
fi

With every full or differential backup, a mysql-dump is performed. Furthermore, the bin logs are deleted. Before every incremental dump, a new bin log is started.

Be careful if you have MySQL master slave replication enabled. This backup method might break it.

To restore your MySQL database, first load the dump.sql and then apply all bin logs.

For more information look at http://dev.mysql.com/doc/refman/5.0/en/recovery-from-backups.html

MySQL

Using full dumps

Using binary logs