MySQL: Backup and Recovery using innobackup

It is possible to perform a 'hot' (i.e. online) backup of InnoDB databases using a 3rd-party tool called innobackup from INNOBASE.

Using this tool takes a little getting used to; the following is an example hot backup and recovery:

Request an evaluation copy of the product here. You will receive the evaulation program via email, rename it to ibbackup and place it in your PATH.

Also, download the wrapper script, innobackup, cutting and pasting it into a file called innobackup; this script calls the previously acquired evaluation program, ibbackup; place this in your PATH as well.

I place the programs in the bin directory of MySQL's basedir:

# ls -lart /opt/mysql/5.1.23/bin/*backup
-rwxrwx---  1 mysql mysql   50918 Mar 16 15:59 innobackup
-rwxrwx---  1 mysql mysql 1002450 Mar 16 16:09 ibbackup

In my example, I start with a fresh MySQL install.

First I create a new database and sample table assigned to the InnoDB storage engine:

# mysql -u root --password=password -P 3307 -h localhost --protocol=tcp -p

mysql> create database innodbsmpl;
Query OK, 1 row affected (0.00 sec)

mysql> use innodbsmpl;
Database changed

mysql> create table innodbsmpl (sampleval varchar(200)) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into innodbsmpl values ('Before Backup');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

For good measure, I flush the logs via mysqladmin:

# mysqladmin -u root --password=password -P 3307 -h localhost --protocol=tcp -p flush-log

Now I will take a hot, online backup using the innobackup script:

backup_innodb.bsh

# more backup_innodb.bsh
#/bin/bash

export PATH=/opt/mysql/5.1.23/bin:$PATH;

/opt/mysql/5.1.23/bin/innobackup --no-timestamp --socket=/opt/mysql/5.1.23/socke
t/mysql-5123.sock --user root --password=password /opt/mysql/5.1.23/my.cnf /tmp/
innodbsmpl_backup > backup_innodb.log 2>&1

Run the script as mysql:

 # ./backup_innodb.bsh

Examine the resulting log file:

# more backup_innodb.log

InnoDB Backup Utility v1.4.0; Copyright 2003-2007 Innobase Oy

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 innobackup
           prints "innobackup completed OK!".

innobackup: Using mysql  Ver 14.14 Distrib 5.1.23-rc, for redhat-linux-gnu (i686) using
  EditLine wrapper
innobackup: Using mysql server version 5.1.23-rc-log
innobackup: Using InnoDB Hot Backup version 3.0.0

innobackup: Created backup directory /tmp/innodbsmpl_backup
080317 12:20:03  innobackup: Starting mysql with options: --unbuffered --password=passw
ord --user=root --socket=/opt/mysql/5.1.23/socket/mysql-5123.sock
080317 12:20:03  innobackup: Connected to database with mysql child process (pid=12220)
080317 12:20:07  innobackup: Connection to database server closed

080317 12:20:07  innobackup: Starting ibbackup with command: ibbackup --suspend-at-end
/opt/mysql/5.1.23/my.cnf /tmp/innodbsmpl_backup/backup-my.cnf
innobackup: Waiting for ibbackup (pid=12227) to suspend
innobackup: Suspend file '/tmp/innodbsmpl_backup/ibbackup_suspended'

InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy
License A11900 is granted to James Colestock (james@colestock.com)
(--apply-log works in any computer regardless of the hostname)
Licensed for use in a computer whose hostname is 'rac2.colestock.test'
Expires 2008-5-1 (year-month-day) at 00:00
See http://www.innodb.com for further information
Type ibbackup --license for detailed license terms, --help for help

Contents of /opt/mysql/5.1.23/my.cnf:
innodb_data_home_dir got value /opt/mysql/datafiles
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /opt/mysql/datafiles
innodb_log_group_home_dir got value /opt/mysql/datafiles
innodb_log_files_in_group got value 2
innodb_log_file_size got value 5242880

Contents of /tmp/innodbsmpl_backup/backup-my.cnf:
innodb_data_home_dir got value /tmp/innodbsmpl_backup
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /tmp/innodbsmpl_backup
innodb_log_group_home_dir got value /tmp/innodbsmpl_backup
innodb_log_files_in_group got value 2
innodb_log_file_size got value 5242880

ibbackup: Found checkpoint at lsn 0 48301
ibbackup: Starting log scan from lsn 0 48128
080317 12:20:07  ibbackup: Copying log...
080317 12:20:07  ibbackup: Log copied, lsn 0 48301
ibbackup: We wait 1 second before starting copying the data files...
080317 12:20:08  ibbackup: Copying /opt/mysql/datafiles/ibdata1

ibbackup: You had specified the option --suspend-at-end

080317 12:20:09  innobackup: Continuing after ibbackup has suspended
080317 12:20:09  innobackup: Starting mysql with options: --unbuffered --password=passw
ord --user=root --socket=/opt/mysql/5.1.23/socket/mysql-5123.sock
080317 12:20:09  innobackup: Connected to database with mysql child process (pid=12228)
080317 12:20:08  ibbackup: Suspending the backup procedure to wait
ibbackup: until you delete the marker file /tmp/innodbsmpl_backup/ibbackup_suspended
080317 12:20:13  innobackup: Starting to lock all tables...
080317 12:20:29  innobackup: All tables locked and flushed to disk

080317 12:20:29  innobackup: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackup: .TRG, .TRN, and .opt files in
innobackup: subdirectories of '/opt/mysql/datafiles'
innobackup: Backing up files '/opt/mysql/datafiles/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,opt
}' (70 files)
innobackup: Backing up file '/opt/mysql/datafiles/innodbsmpl/innodbsmpl.frm'
innobackup: Backing up file '/opt/mysql/datafiles/innodbsmpl/db.opt'
innobackup: Backing up file '/opt/mysql/datafiles/test/test.frm'
innobackup: Backing up file '/opt/mysql/datafiles/test/test.MYD'
innobackup: Backing up file '/opt/mysql/datafiles/test/test.MYI'
innobackup: Backing up file '/opt/mysql/datafiles/test/db.opt'
080317 12:20:29  innobackup: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, an
d .opt files

innobackup: Resuming ibbackup

080317 12:20:30  ibbackup: Suspension ends. Continuing the backup procedure.

080317 12:20:30  ibbackup: Copying of the last data file is close to ending...
ibbackup: We still once copy the latest flushed log to ibbackup_logfile.
ibbackup: A copied database page was modified at 0 48301
ibbackup: Scanned log up to lsn 0 50133
ibbackup: Was able to parse the log up to lsn 0 50133
ibbackup: Maximum page number for a log record 51
080317 12:20:30  ibbackup: Full backup completed!
080317 12:20:34  innobackup: All tables unlocked
080317 12:20:34  innobackup: Connection to database server closed

innobackup: Backup created in directory '/tmp/innodbsmpl_backup'
innobackup: MySQL binlog position: filename 'rac2_bin.000002', position 536           -
080317 12:20:34  innobackup: innobackup completed OK!

The backup writes to the directory I specified:

# ls -lart /tmp/innodbsmpl_backup/
total 10304
-rw-r--r--   1 mysql mysql      305 Mar 17 12:20 backup-my.cnf
drwxrwxrwt  11 root  root     12288 Mar 17 12:20 ..
-rw-r-----   1 mysql mysql 10485760 Mar 17 12:20 ibdata1
-rw-r--r--   1 mysql mysql        0 Mar 17 12:20 mysql-stderr
drwxr-xr-x   2 mysql mysql     4096 Mar 17 12:20 test
drwxr-xr-x   2 mysql mysql     4096 Mar 17 12:20 mysql
drwxr-xr-x   2 mysql mysql     4096 Mar 17 12:20 innodbsmpl
-rw-r--r--   1 mysql mysql       22 Mar 17 12:20 ibbackup_binlog_info
drwxr-xr-x   5 mysql mysql     4096 Mar 17 12:20 .
-rw-r-----   1 mysql mysql     2560 Mar 17 12:20 ibbackup_logfile
-rw-r--r--   1 mysql mysql      532 Mar 17 12:20 mysql-stdout

Prepare the MySQL installation for the recovery test:

Insert a post-backup value to test rolling forward the InnoDB tablespace:

# mysql -u root -P 3307 -h localhost --protocol=tcp -p

mysql> use innodbsmpl

Database changed

mysql> insert into innodbsmpl values ('After Backup');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Flush the logs:

# /opt/mysql/5.1.23/bin/mysqladmin -u root -P 3307 --protocol=tcp -p flush-logs

Shutdown the MySQL service:

# su - root
# service mysql-5.1.23 stop
Shutting down MySQL.                                       [  OK  ]

Before we can restore the hot backup we took, we need to make it consistent via the following:

apply_innodb.bsh

# more apply_innodb.bsh
#/bin/bash

export PATH=/opt/mysql/5.1.23/bin:$PATH;

/opt/mysql/5.1.23/bin/innobackup --apply-log /tmp/innodbsmpl_backup/backup-my.cnf /tmp/
innodbsmpl_backup > apply_innodb.log 2>&1

Run the script as mysql:

 # ./apply_innodb.bsh

Examine the resulting log file:

# more apply_innodb.log
InnoDB Backup Utility v1.4.0; Copyright 2003-2007 Innobase Oy

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackup
           prints "innobackup completed OK!".

innobackup: Using InnoDB Hot Backup version 3.0.0


080317 12:32:15  innobackup: Starting ibbackup with command: ibbackup --restore /tmp/in
nodbsmpl_backup/backup-my.cnf

InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy
License A11900 is granted to James Colestock (james@colestock.com)
(--apply-log works in any computer regardless of the hostname)
Licensed for use in a computer whose hostname is 'rac2.colestock.test'
Expires 2008-5-1 (year-month-day) at 00:00
See http://www.innodb.com for further information
Type ibbackup --license for detailed license terms, --help for help

Contents of /tmp/innodbsmpl_backup/backup-my.cnf:
innodb_data_home_dir got value /tmp/innodbsmpl_backup
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /tmp/innodbsmpl_backup
innodb_log_group_home_dir got value /tmp/innodbsmpl_backup
innodb_log_files_in_group got value 2
innodb_log_file_size got value 5242880

080317 12:32:15  ibbackup: ibbackup_logfile's creation parameters:
ibbackup: start lsn 0 48128, end lsn 0 50133,
ibbackup: start checkpoint 0 48301
InnoDB: Doing recovery: scanned up to log sequence number 0 50133
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 2
2 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 5
1 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 8
0 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 Setting log file size to 0 5
242880
Setting log file size to 0 5242880
ibbackup: We were able to parse ibbackup_logfile up to
ibbackup: lsn 0 50133
ibbackup: Last MySQL binlog file position 0 536, file name ./rac2_bin.000002
ibbackup: The first data file is '/tmp/innodbsmpl_backup/ibdata1'
ibbackup: and the new created log files are at '/tmp/innodbsmpl_backup/'
080317 12:32:15  ibbackup: Full backup prepared for recovery successfully!
080317 12:32:15  innobackup: innobackup completed OK!

The aforementioned, makes the InnoDB tablespace in question consistent to change #536.

Now the backup is ready to copy back:

restore_innodb.bsh

# more restore_innodb.bsh
#/bin/bash

export PATH=/opt/mysql/5.1.23/bin:$PATH;

/opt/mysql/5.1.23/bin/innobackup --copy-back /opt/mysql/5.1.23/my.cnf /tmp/innodbsmpl_backup > restore_innodb.log 2>&1

Run the script as mysql:

 # ./restore_innodb.bsh

Examine the resulting log file:

# more restore_innodb.log

InnoDB Backup Utility v1.4.0; Copyright 2003-2007 Innobase Oy

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackup
           prints "innobackup completed OK!".

innobackup: Starting to copy MyISAM tables, indexes,
innobackup: .MRG, .TRG, .TRN, .opt, and .frm files
innobackup: in '/tmp/innodbsmpl_backup'
innobackup: back to original data directory '/opt/mysql/datafiles'
innobackup: Copying directory '/tmp/innodbsmpl_backup/mysql'
innobackup: Copying directory '/tmp/innodbsmpl_backup/innodbsmpl'
innobackup: Copying directory '/tmp/innodbsmpl_backup/test'
innobackup: Copying file '/tmp/innodbsmpl_backup/ibbackup_binlog_info'

innobackup: Starting to copy InnoDB tables and indexes
innobackup: in '/tmp/innodbsmpl_backup'
innobackup: back to original InnoDB data directory '/opt/mysql/datafiles'
innobackup: Copying file '/tmp/innodbsmpl_backup/ibdata1'

innobackup: Starting to copy InnoDB log files
innobackup: in '/tmp/innodbsmpl_backup'
innobackup: back to original InnoDB log directory '/opt/mysql/datafiles'
innobackup: Copying file '/tmp/innodbsmpl_backup/ib_logfile0'
innobackup: Copying file '/tmp/innodbsmpl_backup/ib_logfile1'
innobackup: Finished copying back files.

080317 12:40:14  innobackup: innobackup completed OK!

The backup has now been restored to the datadir.

Before starting the server, prepare the changes from the binlogs:

Obtain the starting change position from the following file:

# more /opt/mysql/datafiles/ibbackup_binlog_info
rac2_bin.000002 536

We will roll-forward starting from here. Stage binlogs changes as appropriate; for example:

Starting from the previously identified binlog and position:

# mysqlbinlog -f --start-position=536 
/opt/mysql/datafiles/rac2_bin.000002 /opt/mysql/datafiles/rac2_bin.000003 > updates2apply

At this point, all the changes to roll the databases forward are contained in the
updates2apply file we have created.

Start the MySQL service:

# su - root
# service mysql-5.1.23 start
Starting MySQL                                             [  OK  ]

Query the sample table; notice how only the changes at the time of the backup are present:

mysql> select * from innodbsmpl;
+---------------+
| sampleval     |
+---------------+
| Before Backup |
+---------------+
1 row in set (0.00 sec)

Roll forward the databases via:

# mysql --batch -u root -P 3307 --password --protocol=tcp -h localhost --force mysql < updates2apply
Enter password:
ERROR 1064 (42000) at line 17: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

You will notice there was an error, this is due to a bug that creates an invalid SQL statement at line 17. Because of bugs in the mysqlbinlog program, be sure to use the --force option whenever rolling forward your databases.

Query the sample table; notice how changes applied after the backup are now present:

mysql> select * from innodbsmpl;
+---------------+
| sampleval     |
+---------------+
| Before Backup |
| After Backup  |
+---------------+
2 rows in set (0.00 sec)

The innobackup tool gives you plenty of other options, such as starting MySQL directly from backups' directories (instead of restoring them to their original location).

Labels: MySQL