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