View source for MySQL Backups

A number of [[Grid Middleware]] components use [http://dev.mysql.com/ 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 [http://dev.mysql.com/doc/refman/4.1/en/backup.html MySQL documentation] and uses the <tt>mysqldump</tt> 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 <tt>mysqlhotcpy</tt> method could be used within the Glasgow scheme, and vice versa for the <tt>mysqldump</tt> method using the RAL scheme.)

===Glasgow Method===
At [http://www.scotgrid.ac.uk/wiki Glasgow] [[User:Graeme stewart]] uses this script to trigger dumps of the database, and ensure that old dumps are deleted after a reasonable time:

<pre>
#! /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;
    }
}
</pre>

Other sites might find this a useful starting point. Note the MySQL root password is held in <tt>/root/mysql-pw</tt> 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 [http://www.scotgrid.ac.uk/wiki Glasgow] we run this script 4 times a day from <tt>/etc/cron.d/mysql-dump</tt>:

<pre>
PATH=/sbin:/bin:/usr/sbin:/usr/bin
0 */6 * * * root /usr/local/sbin/mysql-dump.pl
</pre>

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.
  #!/bin/sh
  
  LOGFILE=/var/log/mysqlhotcopy/mysqlhotcopy.log
  
  if [ ! -f /etc/mysqlhotcopy.conf ] ; then
     echo "Could not find configuration file /etc/mysqlhotcopy.conf"
     exit -1
  fi
  . /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
     else
        echo `date`": Database $DB empty - no backup is done" 2>>$LOGFILE 1>>$LOGFILE
     fi
  done
  
  unset USERNAME
  unset PASSWORD
  unset OUTPUT

with ''/etc/mysqlhotcopy.conf'' containing 

  SOURCE=/var/lib/mysql
  USERNAME=backup
  PASSWORD=AbCdEfG
  OUTPUT=/mysql-backup

There is mysqlhotcopy-cron rpm in <nowiki>http://wwwinstall.gridpp.rl.ac.uk/yum/local</nowiki> 
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 [http://www.scotgrid.ac.uk/wiki Glasgow] we use <tt>rsync</tt> over <tt>ssh</tt> to copy the SQL backups to a different machine with RAID 0 disks.

[[Category: Databases]]