Jul 26 2008
Mysql Ultra-Fast Backup with LVM
It is generally good practice to respect the two following points when backing up databases. The backup has to be:
- consistent
- fast
Consistency is easily achieved putting a read lock on all tables beforehands. However, this isn’t always applied, and WILL definately lead to a database integrity problem at some stage.
Once a lock has been set on the database, the backup has to be as quick as possible, all write instructions being held in the queue.
Mysql and LVM
Here are some usual ways to run a backup:
- mysqldump, provided within Mysql package, is fast enough for very small databases and that’s about it! It is safe to run on replication servers, or if you can afford to suspend write operations for a long time
- A simple tar of the data directory, is faster but may remain slow, especially if you run the backup over the network
- A simple tar, coupled with a volume manager that supports snapshots, like Veritas or ZFS. The most evident solution that comes to mind is Logical Volume Manager (LVM), now provided in standard with most Linux distributions. This option locks and unlocks the tables within seconds if the backup runs on a snapshot!
Other methods exist indeed but we won’t deal with them in this article.
Before You Start
All you need is have mysql data directory on a LVM partition and 10% of free space on the volume group to create the snapshot. Parameters are similar to the mysql client command line, ie same options to specify the user and password for easy usage.
The script connects to your local mysql server, puts a read lock, creates a snapshot of the LVM partition, and releases the lock. The data directory is then archived with tar and put in the destination folder of your choice, before the snapshot is destroyed.
It should also be run under the root user account. If not, provide sudo to the mount and lv commands, and make sure the user has read access to the Mysql files.
Script
#!/bin/bash user=$LOGNAME password= datadir= tmpmountpoint="/mnt" dstdir="/tmp" usage () { echo "Usage: $0 [OPTION]" echo "-d, --dest=name Destination directory. Default is /tmp" echo "-h, --help Display this help and exit." echo "-p, --password[=name] Password to use when connecting to server. If password is" echo " not given it's asked from the tty." echo "-t Temporary mount point for the snapshot. Default is /mnt." echo "-u, --user=name User for login if not current user" exit 1 } until [ -z "$1" ]; do case "$1" in -u) [ -z "$2" ] && usage user="$2" shift ;; --user=*) user=`echo $1|cut -f 2 -d '='` ;; -p*) password=`echo $1|sed -e s/"^-p"//g` ;; --password) echo -n "Enter password: " stty -echo read password stty echo ;; --password=*) password=`echo $1|cut -f 2 -d '='` ;; -d) [ -z "$2" ] && usage dstdir="$2" shift ;; --dest=*) dstdir=`echo $1|cut -f 2 -d '='` ;; -t) [ -z "$2" ] && usage tmpmountpoint="$2" shift ;; * ) usage ;; esac shift done [ -z $password ] && echo "Empty password!" && usage [ ! -d $dstdir ] && echo "$dstdir does not exist" && exit 1 # Check if temp mount point not used [ `mount | grep "$tmpmountpoint" | wc -l` -ne 0 ] && exit 1 # Get Mysql data directory datadir=`mysql -u $user -p$password -Ns -e "show global variables like 'datadir'"|cut -f 2|sed -e s/"\/$"//g` [ -z "$datadir" ] && exit 1 # Get snap name and size vg=`mount | grep $datadir | cut -d ' ' -f 1 | cut -d '/' -f 4 | cut -d '-' -f 1` lv=`mount | grep $datadir | cut -d ' ' -f 1 | cut -d '/' -f 4 | cut -d '-' -f 2` [ -z $lv ] && echo "Mysql data dir must be mounted on a LVM partition!" && exit 1 snap=$lv"snap" snapsize=$(expr `df -m $datadir | tail -1 | tr -s ' ' | cut -d ' ' -f 2` / 10)M # Backup echo "Locking databases" mysql -u$user -p$password << EOF FLUSH TABLES WITH READ LOCK; system lvcreate --snapshot -n $snap -L$snapsize /dev/$vg/$lv; UNLOCK TABLES; quit EOF echo "Databases unlocked" echo "Backing up databases" mount /dev/$vg/$snap $tmpmountpoint cd $tmpmountpoint tar cfz $dstdir/mysql.tar.gz * cd umount $tmpmountpoint lvremove -f /dev/$vg/$snap echo "Databases backed up in $dstdir" exit 0