Bacula DokuWiki

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.

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

bacula-backup-mysql is a frontend to mysqlhotcopy(1). It can be used to backup MyISAM tables exclusively. This has the advantage of being faster to backup and faster to recover as you can copy back the binary files stright from backup. Disadvantages are that it can do only MyISAM backups and each backup is full backup, however Bacula is able to make internal incremental backups if the database table was not changed between last backup.

Recent versions of the tool support also creating mysqldump(1) outputs.

Having understood the approaches above, there is one thing that comes to mind: “How about a custom mysqlbackup script that lets you choose on how to backup your mysql databases or entire instances - removing the need of different backup scripts on different hosts?”

Thats why this script was developed:

#!/bin/bash

# Directory to store backups in
dst="/where/to/dump/your/mysql/content"
# In case of full backups (use them only when NOT having binary logs) you may want to ignore several databases
# a regex, passed to egrep -v, for which databases to ignore
ignreg='^snort$|^information_schema$|^performance_schema$'
# The MySQL username and password
dbuser="youruser"
dbpass="yourpassword"
# Any backups older than this will be deleted first - if they arent already removed by your proper mysql configuration
keepdays=7
# which backup level should be taken
level="$1"
# We need a timestamp# We need a timestamp
tstamp=$(date +%Y%m%d-%H%M%S)
# construct the destination including the timestamp
dumpdir="$dst/$tstamp"

if [ ! -d "$dumpdir" ]
then
   mkdir -p "$dumpdir"
fi

find "${dst}" -type d -mtime +$keepdays -exec echo rm -rf "{}" \;

case "$level" in
   "full"):
      echo "Performing full backup"
      for db in $(echo 'show databases;' | /usr/bin/mysql -s -u "$dbuser" -p"$dbpass" | /bin/egrep -v "$ignreg");
      do
         echo "Backing up ${db} to $dumpdir/$db.$level.sql.bz2"
         /usr/bin/mysqldump --opt -u "$dbuser" -p"$dbpass" "$db" | /usr/bin/pbzip2 -9 -c > "$dumpdir/$db.$level.$tstamp.sql.bz2"
      done
      ;;
   "diff"):
      echo "Performing differential backup"
      echo "Dumping changes to $dumpdir/$level.sql.bz2"
      /usr/bin/mysqldump -u "$dbuser" -p"$dbpass" --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs | /usr/bin/pbzip2 -9 > "$dumpdir/$level.$tstamp.sql.bz2"
      ;;
   "inc"):
      echo "Performing incremental backup"
      /usr/bin/mysqladmin -u "$dbuser" -p"$dbpass" flush-logs
      ;;
   *):
      echo "Not performing anything."
      ;;
esac

exit 0

Please read the following notes carefully:

• This script provides three methods of backups:
  • full - this is the same as just dumping every database fully, NOT using the MySQL binary log facility
    • When processing the databases, keep in mind that processing specific databases like “information_schema”, “performance_schema” are not supposed to be backed up, because they only contain redundant data. You dont need to back them up, actually.
  • diff - this method relies on using the MySQL binary log facility, and creates a consistent (transaction safe, not content safe) backup of the MySQL instance as well as all active bainry log files
  • inc - this method relies on using the MySQL binary log facility, and just flushes the binary logs to disk, for incremental backup after using the “diff” method

Assumptions made before using this script:

• Always use one of the parameters full, diff or inc - anything else will NOT create a proper backup.
• Backups will be compressed using pbzip2 - change the script in case you dont have pbzip2 handy
• MySQL with the MySQL binary log facility:
  • The MySQL parameters log_bin = /var/log/mysql/mysql-bin.log (that target directory is the default destination using Ubuntu) and expire_logs_days = 7 (the default is 0, which means that binary logs will never be deleted - this may arise severe issues if your backup is not running within 7 days) need to be set in your MySQL configuration.
  • Already taken backups are safe to delete after 7 days - assuming you run your backups regularly within 7 days. Having a longer delay between backups AND using the inc method will have the impact that you will NOT have a working backup, since you will be missing binary logs for restoring the most current state of the database(s).
• MySQL without the MySQL binary log facility:
  • Specific databases are not to be backed up, thats what the ignreg variable is for - it has NO effect when using the MySQL binary log facility