MySQL Open Source Backup and Recovery Alternative: Xtrabackup

MySQL database administrators are always looking for a solid backup and recovery tool that will suit all their needs. Xtrabackup, created by Percona, is the open source alternative to the commercial Innodb Hot Backup tool. This article explains a good methodology for testing and verifying Xtrabackups capabilities and precision.

More
and more I see MySQL database administrators’ looking for a solid backup tool
that will suit all their needs. There are many commercial and open source tools
out there; some of the commercial products I have used are R1Soft, Zamanda and Innodb Hot
Backup. On the open source side, I have used mysqldump,
mk-parallel-dump/load located in the MaatKit and
most recently, Xtrabackup.

Xtrabackup,
created by Percona, is
the open source alternative to the commercial Innodb Hot
Backup
tool. In this article, I will explain a good methodology for testing
and verifying Xtrabackups capabilities and precision.

Process Abstract

I will be running a script
on my test server that creates 19 tables in the bktest database, and then
continuously runs bulk inserts into the tables. The script uses the root user
with a blank password. While this script is running, I will run a backup using
Xtrabackup. After the Xtrabackup has finished I will continue to run the load
script for about 30 seconds. I will set up another mysqld server on the same
host, run a recovery from the Xtrabackup, and replicate the original instance
to the new instance. From here, all we need to do is make sure both sets of
data match!

Hardware Specifications

I will be using my test
server, a SunFire 2200, two Dual Quad-Core AMD Opteron(tm) Processor 2376, two
220GB SATA RAID 1.

Xtrabackup

For this example, I will
be using innobackupex-1.5.1.

Example Load Script

#!/usr/bin/perl -w
# This is an endless loop that will create 19 tables in the bktest database
# on the server it's running on. This script uses the root user with a blank
# password to connect.
# after the 20 tables have been created inserts will continue being executed, in bulk,
# on the server until the script is terminated
use strict;
use DBI;
use DBD::mysql;
use Data::Dumper;
my $dbh = DBI->connect("dbi:mysql:bktest;host=localhost", 'root', '', {RaiseError=>1});
if (!$dbh) {
print "Could not connect to localhost. [" . $DBI::err . "] ". $DBI::errstr . "n"; }
eval {
my $sth;
for(my $i = 1; $i < 20; $i++) {
$sth = $dbh->prepare("CREATE TABLE IF NOT EXISTS tbl_$i (a int not null auto_increment primary key, b varchar(10), key b_idx (b))");
$sth->execute();
}
while(1) {
for(my $i = 1; $i < 20; $i++) {
my $sql = "INSERT INTO tbl_$i (b) values ";
my $comma = '';
for(my $counter = 0; $counter < 1000; $counter++) {
$sql .= $comma . "('test test test test')";
$comma = ',';
}
$sth = $dbh->prepare($sql);
$sth->execute();
}
}
};
if($@) {
print "errorn";
}
exit;

The Test in Action

I have my MySQL instance
running on port 3306, I’ve created the bktest database and I am about to run
myloopscript.pl. Please note that I will be running the loop script and the
backup in two separate terminal windows.

Running the Script

Shell> time perl myloopscript.pl

Running the Backup

Shell> /usr/bin/innobackupex-1.5.1 --defaults-file=/etc/my.cnf --user=root --socket=/tmp/mysql.sock /local/backups
-- Output from Xtrabackup
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
IMPORTANT: Please check that the backup run completes successfully.
 At the end of a successful backup run innobackupex-1.5.1
 prints "completed OK!".
innobackupex-1.5.1: Using mysql Ver 14.14 Distrib 5.5.1-m2, for unknown-linux-gnu (x86_64) using readline 5.1
innobackupex-1.5.1: Using mysql server version Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
innobackupex-1.5.1: Created backup directory /local/backups/2010-04-20_18-29-36
100420 18:29:36 innobackupex-1.5.1: Starting mysql with options: --unbuffered --user=root --socket=/tmp/mysql.sock --
100420 18:29:36 innobackupex-1.5.1: Connected to database with mysql child process (pid=18575)
100420 18:29:40 innobackupex-1.5.1: Connection to database server closed
100420 18:29:40 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup --defaults-file="/etc/my.cnf" --backup --suspend-at-end --target-dir=/local/backups/2010-04-20_18-29-36
innobackupex-1.5.1: Waiting for ibbackup (pid=18582) to suspend
innobackupex-1.5.1: Suspend file '/local/backups/2010-04-20_18-29-36/xtrabackup_suspended'
xtrabackup Ver 1.0 Rev 113 for 5.0.84 unknown-linux-gnu (x86_64)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /local/mysql
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /local/mysql
xtrabackup: innodb_data_file_path = ibdata1:512M:autoextend
xtrabackup: innodb_log_group_home_dir = /local/mysql
xtrabackup: innodb_log_files_in_group = 3
xtrabackup: innodb_log_file_size = 52428800
xtrabackup: use O_DIRECT
>> log scanned up to (0 8333496)
Copying /local/mysql/ibdata1
to /local/backups/2010-04-20_18-29-36/ibdata1
>> log scanned up to (0 9006168)
>> log scanned up to (0 10015644)
>> log scanned up to (0 10699821)
>> log scanned up to (0 11361824)
>> log scanned up to (0 12134846)
>> log scanned up to (0 12639664)
>> log scanned up to (0 13381080)
>> log scanned up to (0 14243545)
>> log scanned up to (0 14727272)
...done
Copying ./bktest/tbl_17.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_17.ibd
...done
Copying ./bktest/tbl_5.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_5.ibd
...done
Copying ./bktest/tbl_14.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_14.ibd
...done
Copying ./bktest/tbl_9.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_9.ibd
...done
Copying ./bktest/tbl_8.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_8.ibd
...done
Copying ./bktest/tbl_18.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_18.ibd
...done
Copying ./bktest/tbl_2.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_2.ibd
...done
Copying ./bktest/tbl_7.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_7.ibd
...done
Copying ./bktest/tbl_6.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_6.ibd
...done
Copying ./bktest/tbl_19.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_19.ibd
...done
Copying ./bktest/tbl_16.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_16.ibd
...done
Copying ./bktest/tbl_13.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_13.ibd
...done
Copying ./bktest/tbl_4.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_4.ibd
...done
Copying ./bktest/tbl_11.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_11.ibd
...done
Copying ./bktest/tbl_1.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_1.ibd
...done
Copying ./bktest/tbl_10.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_10.ibd
...done
Copying ./bktest/tbl_12.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_12.ibd
...done
Copying ./bktest/tbl_15.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_15.ibd
...done
Copying ./bktest/tbl_3.ibd
to /local/backups/2010-04-20_18-29-36/bktest/tbl_3.ibd
...done
>> log scanned up to (0 15484499)
100420 18:30:40 innobackupex-1.5.1: Continuing after ibbackup has suspended
100420 18:30:40 innobackupex-1.5.1: Starting mysql with options: --unbuffered --user=root --socket=/tmp/mysql.sock --
100420 18:30:40 innobackupex-1.5.1: Connected to database with mysql child process (pid=18597)
100420 18:30:44 innobackupex-1.5.1: Starting to lock all tables...
>> log scanned up to (0 16914185)
>> log scanned up to (0 18174839)
100420 18:30:54 innobackupex-1.5.1: All tables locked and flushed to disk
100420 18:30:54 innobackupex-1.5.1: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ and .opt files in
innobackupex-1.5.1: subdirectories of '/local/mysql'
innobackupex-1.5.1: Backing up files '/local/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (68 files)
innobackupex-1.5.1: Backing up files '/local/mysql/bktest/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (20 files)
100420 18:30:55 innobackupex-1.5.1: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ and .opt files
innobackupex-1.5.1: Resuming ibbackup
xtrabackup: The latest check point (for incremental): '0:15148656'
>> log scanned up to (0 18174839)
>> log scanned up to (0 18174839)
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (0 2870677) to (0 18174839) was copied.
100420 18:30:57 innobackupex-1.5.1: All tables unlocked
100420 18:30:57 innobackupex-1.5.1: Connection to database server closed
innobackupex-1.5.1: Backup created in directory '/local/backups/2010-04-20_18-29-36'
innobackupex-1.5.1: MySQL binlog position: filename 'binary-logs.000001', position 5089343
100420 18:30:57 innobackupex-1.5.1: completed OK!
-- End Xtrabackup Output

After the backup has
finished I continued to run the myloopscript.pl for an extra 30 seconds. The
reason for this is illustrated below in the three different md5sum outputs
taken throughout the test.

Analysis

The test is complete and
we need to make sure that both the backup and the original data set are the
same. A good trick of the trade is to use md5sum. I have put together a very
simple, in-line bash script that will loop through all 19 tables in bktest and
run select * from each table. From there I pipe the output into md5sum to
gather a hash based on the output of the select.

On the original data set I
ran the following:

Shell> for x in `mysql -uroot -S /tmp/mysql.sock -e "use bktest; show tables" |grep -v Tables`; do echo $x; mysql -uroot -S /tmp/mysql.sock -e "select * from bktest.${x}" |md5sum; done
tbl_1
16207869d5fa610d17f63eb1eeb68369 -
tbl_10
16207869d5fa610d17f63eb1eeb68369 -
tbl_11
16207869d5fa610d17f63eb1eeb68369 -
tbl_12
16207869d5fa610d17f63eb1eeb68369 -
tbl_13
16207869d5fa610d17f63eb1eeb68369 -
tbl_14
16207869d5fa610d17f63eb1eeb68369 -
tbl_15
16207869d5fa610d17f63eb1eeb68369 -
tbl_16
16207869d5fa610d17f63eb1eeb68369 -
tbl_17
b3d23d1fcec610c8ffe8cf8022c4020d -
tbl_18
b3d23d1fcec610c8ffe8cf8022c4020d -
tbl_19
b3d23d1fcec610c8ffe8cf8022c4020d -
tbl_2
16207869d5fa610d17f63eb1eeb68369 -
tbl_3
16207869d5fa610d17f63eb1eeb68369 -
tbl_4
16207869d5fa610d17f63eb1eeb68369 -
tbl_5
16207869d5fa610d17f63eb1eeb68369 -
tbl_6
16207869d5fa610d17f63eb1eeb68369 -
tbl_7
16207869d5fa610d17f63eb1eeb68369 -
tbl_8
16207869d5fa610d17f63eb1eeb68369 -
tbl_9
16207869d5fa610d17f63eb1eeb68369 -

Now I will set up a
replica on the same server but port 3307 with the backup I just executed.
First, I need to copy the backup contents from the /local/backups directory
into the data directory of the new MySQL instance then apply the logs and
finally starting the instance on port 3307.

The output from this check
is expected to be different from the original given that there is more data on
the original dataset than the backup. Below are the hash values after the
backup instance has been restored.

Shell> for x in `mysql -uroot -S /tmp/mysql-3307.sock -e "use bktest; show tables" |grep -v Tables`; do echo $x; mysql -uroot -S /tmp/mysql-3307.sock -e "select * from bktest.${x}" |md5sum; done
tbl_1
dccfc9d319a4a1bbc15c8489673ede04 -
tbl_10
477f3e68b504c3464296cfa136987d8b -
tbl_11
477f3e68b504c3464296cfa136987d8b -
tbl_12
477f3e68b504c3464296cfa136987d8b -
tbl_13
477f3e68b504c3464296cfa136987d8b -
tbl_14
477f3e68b504c3464296cfa136987d8b -
tbl_15
477f3e68b504c3464296cfa136987d8b -
tbl_16
477f3e68b504c3464296cfa136987d8b -
tbl_17
477f3e68b504c3464296cfa136987d8b -
tbl_18
477f3e68b504c3464296cfa136987d8b -
tbl_19
477f3e68b504c3464296cfa136987d8b -
tbl_2
477f3e68b504c3464296cfa136987d8b -
tbl_3
477f3e68b504c3464296cfa136987d8b -
tbl_4
477f3e68b504c3464296cfa136987d8b -
tbl_5
477f3e68b504c3464296cfa136987d8b -
tbl_6
477f3e68b504c3464296cfa136987d8b -
tbl_7
477f3e68b504c3464296cfa136987d8b -
tbl_8
477f3e68b504c3464296cfa136987d8b -
tbl_9
477f3e68b504c3464296cfa136987d8b -

As expected, the hashes
are different from that of the original dataset. We now need to make sure the
instance running on 3307 (the backup instance) is replicated and caught up to
the instance running on port 3306 (the original instance).

NOTE: please check the
"show master status" output and "show
slave status
" output at the end of the article for verification.

After looking at the
xtrabackup_binlog_info created by the backup, I can plug in the binlog and
position to a change master to statement and start the slave. From there, both
the slave (3307) and the master (3306) are identical. Now let’s run the same
script on port 3307 and compare the hashes to the original hashes on port 3306.

Shell> for x in `mysql -uroot -S /tmp/mysql-3307.sock -e "use bktest; show tables" |grep -v Tables`; do echo $x; mysql -uroot -S /tmp/mysql-3307.sock -e "select * from bktest.${x}" |md5sum; done
tbl_1
16207869d5fa610d17f63eb1eeb68369 -
tbl_10
16207869d5fa610d17f63eb1eeb68369 -
tbl_11
16207869d5fa610d17f63eb1eeb68369 -
tbl_12
16207869d5fa610d17f63eb1eeb68369 -
tbl_13
16207869d5fa610d17f63eb1eeb68369 -
tbl_14
16207869d5fa610d17f63eb1eeb68369 -
tbl_15
16207869d5fa610d17f63eb1eeb68369 -
tbl_16
16207869d5fa610d17f63eb1eeb68369 -
tbl_17
b3d23d1fcec610c8ffe8cf8022c4020d -
tbl_18
b3d23d1fcec610c8ffe8cf8022c4020d -
tbl_19
b3d23d1fcec610c8ffe8cf8022c4020d -
tbl_2
16207869d5fa610d17f63eb1eeb68369 -
tbl_3
16207869d5fa610d17f63eb1eeb68369 -
tbl_4
16207869d5fa610d17f63eb1eeb68369 -
tbl_5
16207869d5fa610d17f63eb1eeb68369 -
tbl_6
16207869d5fa610d17f63eb1eeb68369 -
tbl_7
16207869d5fa610d17f63eb1eeb68369 -
tbl_8
16207869d5fa610d17f63eb1eeb68369 -
tbl_9
16207869d5fa610d17f63eb1eeb68369 -

As you can see, or if
you’d like run your own diff, both of the hash sets are now the same.

Conclusions

This is a very simple
test, which presents a proof of concept that Xtrabackup really works; and,
there is a good way to ensure that it does work. Keep in mind that any database
administrator should run their own test(s) on their own datasets. If you did
not have a tool before to confirm that your backups were working and correct,
this article should give you some good ideas on how to move forward.

Other Tools to Consider

1.    
MaatKit

2.    
Mysqlslap

-- The Master Servers Status (3306)
(root@localhost) [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| binary-logs.000001 | 15575964 | | |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
-- The Slave Servers Status (3307)
(root@localhost) [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: binary-logs.000001
Read_Master_Log_Pos: 15575964
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 10486876
Relay_Master_Log_File: binary-logs.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 15575964
Relay_Log_Space: 10487033
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1

»


See All Articles by Columnist

Chris Schneider

Chris Schneider
Chris Schneider
Certified MySQL professional, actively involved in the MySQL community for the better end of a decade. I have designed, implemented and maintained small to large scale MySQL installations while training and mentoring teams of DBAs. This includes building architectures from the ground up and improving on those that are currently in place while emphasizing scalability, performance and ease of use. Most of the work I am involved with now is in the Web 2.0 arena. I also run a community site, EverythingMySQL.com, that promotes collaboration within the open source community, specifically, MySQL.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles