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:
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.Assumptions made before using this script:
full
, diff
or inc
- anything else will NOT create a proper backup.pbzip2
- change the script in case you dont have pbzip2
handylog_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.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).ignreg
variable is for - it has NO effect when using the MySQL binary log facility