Netexpertise

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.
 

Mysql Backup 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

Tags: backup, linux, LVM, Mysql

Comments RSS