MySQL Backups

From GridPP Wiki
Jump to: navigation, search

A number of Grid Middleware components use MySQL to store data. For these components it is essential for sites to backup these MySQL tables to enable restoration of the service after a hardware failure.

The simplest procedure for doing this is outlined in the MySQL documentation and uses the mysqldump to dump the MySQL databases and tables as an SQL test file:

 /usr/bin/mysqldump --user=root --password=XXX --opt --all-databases | gzip -c > mysql.sql.gz

Local Dump of Databases

Two methods are given here. The Glasgow method makes a number of date stamped backups, which is more suitable for transfer to a disk based backup system (which does not itself do archiving). The RAL method creates only one backup copy, which is better for backing up to tape, where the tape rotation does the archiving. (Though the mysqlhotcpy method could be used within the Glasgow scheme, and vice versa for the mysqldump method using the RAL scheme.)

Glasgow Method

At Glasgow User:Graeme stewart uses this script to trigger dumps of the database, and ensure that old dumps are deleted after a reasonable time:

#! /usr/bin/perl -w

use POSIX;
use strict;

# Dump a mysql database, and ensure that not too many backups are kept

# Define setup
my $backup_dir="/opt/backup";
my $mysql_user="root";
my $mysql_pw_file="/root/mysql-pw";
my $keep_days=10;

my @tm = ();

# Read mysql password
open(PW, $mysql_pw_file) || die "Failed to open password file $mysql_pw_file: $!\n";
my $mysql_pw=<PW>;
chomp $mysql_pw;
close PW;

# Dump the db now
chdir $backup_dir || die "Failed to change to backup directory $backup_dir: $!\n";
@tm = localtime();
# Get date/time stamp in human friendly form "YYYY-MM-DD T HH-MM"
my $now = sprintf("%04d-%02d-%02dT%02d-%02d", $tm[5]+1900, $tm[4]+1, $tm[3], $tm[2], $tm[1]);
system "/usr/bin/mysqldump --user=$mysql_user --password=$mysql_pw --opt --all-databases --single-transaction | gzip -c > mysql-dump-$now.sql.gz";
die "Mysql failed died with exit code $?\n" if $? != 0;

# Now read the backup directory and expire old dumps
opendir(DIR, ".") || die "Failed to read directory: $!\n";
my @backup_files = readdir DIR;
closedir DIR;
my $file;
$now = time();
foreach $file (@backup_files) {
    next unless $file =~ /mysql-dump-(\d+)-(\d+)-(\d+)T(\d+)-(\d+).sql.gz/;
    # Get unix time dump was made
    my $dump_time = POSIX::mktime(0, $5, $4, $3, $2 - 1, $1 - 1900);
    if ($now - $dump_time > $keep_days * 86400) {
        unlink $file;

Other sites might find this a useful starting point. Note the MySQL root password is held in /root/mysql-pw to prevent it being read by anyone except root - but this password is dumped out of MySQL into the backup, so make sure no one but root can read these backups!

At Glasgow we run this script 4 times a day from /etc/cron.d/mysql-dump:

0 */6 * * * root /usr/local/sbin/

If your node has two (or more) disks then backup the database to other disk!

Glasgow Restore

To restore MySQL from a backup, do:

 # gunzip -c /path/to/backup/backup.sql.gz > /tmp/restore.sql
 # mysql -pPASSWORD
 mysql> source /tmp/restore.sql
 Query OK, 0 rows affected (0.00 sec)
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 Database changed
 Query OK, 0 rows affected (0.00 sec)
 Query OK, 0 rows affected (0.01 sec)
 Query OK, 0 rows affected (0.00 sec)

Lana Abadie at CERN had a minor problem restoring her DPM, due to foreign key issues. If, during a DPM restore, you get the an error message like

 ERROR 1005 (HY000): Can't create table ./dpm_db/dpn_fs.frm (errno 150).

Then try relaxing foreign key checks during the restore:

 Mysql> set Foreign_key_checks=0;
 Mysql> source <backup_file.sql>;
 Mysql> set Foreign_key_checks=1;

RAL Method

At RAL Tier1 User:Steve traylen uses the MySQL provided mysqlhotcopy script. In short the following is run on a cron.

 if [ ! -f /etc/mysqlhotcopy.conf ] ; then
    echo "Could not find configuration file /etc/mysqlhotcopy.conf"
    exit -1
 . /etc/mysqlhotcopy.conf
 DATABASES=`mysql -B -u $USERNAME --password=$PASSWORD -e 'show databases' | grep -v Database`
 for DB in $DATABASES ; do
    EMPTYDB=`mysql -B -u $USERNAME --password=$PASSWORD -e 'show tables' $DB | wc -l`
    if [ $EMPTYDB -ge 1 ] ; then
        echo `date`": Starting backup of database $DB" 2>>$LOGFILE 1>>$LOGFILE
        ( time /usr/bin/mysqlhotcopy $DB --allowold -u $USERNAME -p $PASSWORD $OUTPUT ) 2>>$LOGFILE 1>>$LOGFILE
        [ $? != 0 ] && echo "mysqlhotcopy of database $DB failed on "`hostname -f`"."
        echo `date`": Finished backup of database $DB" 2>>$LOGFILE 1>>$LOGFILE
       echo `date`": Database $DB empty - no backup is done" 2>>$LOGFILE 1>>$LOGFILE
 unset OUTPUT

with /etc/mysqlhotcopy.conf containing


There is mysqlhotcopy-cron rpm in though you will have to be inside RAL to get to it. It just creates a physcial consistant copy of the files in /mysql-backup These can then be backed up what ever way safely.

RAL Restore

After using the mysql supplied mysqlhotcopy script to recover the databases you just remove the contents of /var/lib/mysql and replace it with the contents /mysql-backup. All the mysqlbackup script does is to lock the databases one by one and make a copy of the database files on disk.

If you want to recover just one database then just remove and replace the one directory in /var/lib/mysql of your choice.

Backing Up the Dumps

Unless you dump to a different machine implicitly (say onto an nfs mount) then make arrangements to copy these dumps to your site local backup machine. In Glasgow we use rsync over ssh to copy the SQL backups to a different machine with RAID 0 disks.