MySQL backup and recovery

Posted on , updated on by Yannick Jaquier
MySQL ,

Table of contents

Introduction

MySQL backup is most probably the weakest part of the product and the more tricky to manage. If luckily you have a disk array with snapshot capability your MySQL backup strategy can be sump up in a very simple script that is doing:

FLUSH TABLES WITH READ LOCK;

Followed by a disk array snapshot (NFS snapshot, BCV, …).

Then finally:

UNLOCK TABLES;

If your disk array does not have snapshot capability or, even worst, you are on server local disk then you must rely on mysqldump. This is quite uncomfortable situation as mysqldump has few identified bugs and because you must read lock your tables the time you export the figures to have a consistent copy of your databases. And at the end what you have is a set of SQL commands so in case your server is completely corrupted it could take a bit of time to restore it…

These two MySQL backup strategy have obviously the drawback of read locking all your tables. This is surely not an issue for MyISAM dictionary tables but it is surely one for all InnoDB tables of your application (preferred storage engine when you need to be ACID compliant). So in clear no real nice solution, Innobase company (bought by Oracle) which is developing InnoDB storage engine is proposing a non-free tool (ibbackup) to do online MySQL backup but it is only for InnoDB objects.

ibbackup is now renamed MySQL Enterprise Backup (non free product) and is now cross storage engine. We will compare it to free product from Percona call XtraBackup.

MySQL Enterprise Backup

Configuration

For testing the software can be downloaded from Oracle Edelivery web site. Testing has been done with MySQL Enterprise Backup User’s Guide (Version 3.5.2) and MySQL Enterprise 5.5.9.

[mysql@server1 backup]$ unzip V24113-01.zip
Archive:  V24113-01.zip
  inflating: meb-3-5-2-linux2-6-x86-32bit/manual.html
  inflating: meb-3-5-2-linux2-6-x86-32bit/README.txt
  inflating: meb-3-5-2-linux2-6-x86-32bit/bin/mysqlbackup
  inflating: meb-3-5-2-linux2-6-x86-32bit/bin/innobackup
  inflating: meb-3-5-2-linux2-6-x86-32bit/bin/ibbackup
[mysql@server1 backup]$ cd meb-3-5-2-linux2-6-x86-32bit/
[mysql@server1 meb-3-5-2-linux2-6-x86-32bit]$ ll
total 236
drwxr-xr-x 2 mysql dba   4096 Apr  5 14:35 bin
-rw-r--r-- 1 mysql dba 225668 Dec  8 17:59 manual.html
-rw-r--r-- 1 mysql dba    811 Dec  8 17:59 README.txt
[mysql@server1 meb-3-5-2-linux2-6-x86-32bit]$ cd bin
[mysql@server1 bin]$ ll
total 840
-rwxr-xr-x 1 mysql dba 273812 Dec  8 17:59 ibbackup
-rwxr-xr-x 1 mysql dba  72244 Dec  8 17:59 innobackup
-rwxr-xr-x 1 mysql dba 498363 Dec  8 17:59 mysqlbackup

Before going further you need to make sure your my.cnf configuration file contains at least the minimum following system variables (pay special attention to datadir and innodb_log_files_in_group):

[mysqld]
datadir = /mysql/data02 # Often not clearly specified
innodb_data_home_dir = /mysql/data02/
innodb_data_file_path = ibdata1:10M:autoextend:max:1G
innodb_log_group_home_dir = /mysql/data02/
innodb_log_file_size = 25M
innodb_log_files_in_group = 2 # Default value so more or less never specified

MySQL backup

Then you can use either ibbackup command for InnoDB backup only or mysqlbackup command if you have InnoDB and other storage engine objects to backup (knowing MySQL internal dictionary use MyISAM storage engine it is most probably the case).

[mysql@server1 bin]$ mysqlbackup --user=root --password=`cat ~mysql/.root_password` --compress --socket=/mysql/data02/mysql.sock  /mysql/software/5.5.9/conf/my.cnf /tmp
mysqlbackup: Starting mysqlbackup with following arguments:
mysqlbackup --user=root --password=password --compress --socket=/mysql/data02/mysql.sock /mysql/software/5.5.9/conf/my.cnf /tmp
mysqlbackup: The unique backup id generated for the current backup operation is 13021053216680960
 
mysqlbackup: IMPORTANT: Please check that backup run completes successfully.
            At the end of a successful 'backup' run mysqlbackup
            prints "mysqlbackup completed OK!".
 
mysqlbackup: Created backup directory '/tmp/2011-04-06_17-55-21'
mysqlbackup: Using ibbackup version 3.5.2 MySQL Enterprise Backup 3.5.2
mysqlbackup: Using MySQL client version: 5.1.53
mysqlbackup: Checking a connection to MySQL Server with parameters:
mysqlbackup: user=root, port=3316, socket=/mysql/data02/mysql.sock
mysqlbackup: Using MySQL server version: 5.5.9-enterprise-commercial-advanced-log
 
110406 17:55:21 mysqlbackup: Starting ibbackup binary with args:
 
./ibbackup --suspend-at-end --compress 1 /mysql/software/5.5.9/conf/my.cnf /tmp/2011-04-06_17-55-21/backup-my.cnf
mysqlbackup: Waiting for ibbackup process to suspend
mysqlbackup: Suspend file '/tmp/2011-04-06_17-55-21/ibbackup_suspended'
ibbackup version 3.5.2 MySQL Enterprise Backup 3.5.2
Copyright (c) 2002, 2010, Oracle and/or its affiliates.
Run 'ibbackup --help' for help and 'ibbackup --version' for version info.
 
Note: Uses posix_fadvise() for performance optimization.
 
Contents of /mysql/software/5.5.9/conf/my.cnf:
innodb_data_home_dir got value /mysql/data02/
innodb_data_file_path got value ibdata1:10M:autoextend:max:1G
datadir got value /mysql/data02
innodb_log_group_home_dir got value /mysql/data02/
innodb_log_files_in_group got value 2
innodb_log_file_size got value 26214400
 
Contents of /tmp/2011-04-06_17-55-21/backup-my.cnf:
innodb_data_home_dir got value /tmp/2011-04-06_17-55-21
innodb_data_file_path got value ibdata1:10M:autoextend:max:1G
datadir got value /tmp/2011-04-06_17-55-21
innodb_log_group_home_dir got value /tmp/2011-04-06_17-55-21
innodb_log_files_in_group got value 2
innodb_log_file_size got value 26214400
 
ibbackup: System tablespace file format is Antelope.
ibbackup: Found checkpoint at lsn 1079509188.
ibbackup: Starting log scan from lsn 1079508992.
110406 17:55:21  ibbackup: Copying log...
110406 17:55:21  ibbackup: Log copied, lsn 1079509188.
ibbackup: We wait 1 second before starting copying the data files...
110406 17:55:22  ibbackup: Copying /mysql/data02/ibdata1 (Antelope file format).
ibbackup: Progress in MB: 200 400 600
 
ibbackup: You specified the option --suspend-at-end.
110406 17:57:07  ibbackup: Suspending the backup procedure to wait
ibbackup: until you delete the marker file /tmp/2011-04-06_17-55-21/ibbackup_suspended.
 
110406 17:57:08 mysqlbackup: Continuing after ibbackup has suspended
 
110406 17:57:08 mysqlbackup: Starting to lock all the tables....
 
110406 17:57:08 mysqlbackup: All tables are locked and flushed to disk
mysqlbackup: Opening backup source directory '/mysql/data02'
 
110406 17:57:08 mysqlbackup: Starting to backup all files in subdirectories of '/mysql/data02'
mysqlbackup: Backing up the database directory 'dbt2'
mysqlbackup: Backing up the database directory 'mysql'
mysqlbackup: Backing up the database directory 'performance_schema'
mysqlbackup: Backing up the database directory 'test'
mysqlbackup: Resuming ibbackup
 
110406 17:57:08  ibbackup: Suspension ends. Continuing the backup procedure.
 
110406 17:57:08  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 1079509188.
ibbackup: Scanned log up to lsn 1079511586.
ibbackup: Was able to parse the log up to lsn 1079511586.
ibbackup: Maximum page number for a log record 47105
 
ibbackup: Compressed 760 MB of data files to 287 MB (compression 62%).
 
110406 17:57:09  ibbackup: Full backup completed!
 
110406 17:57:09 mysqlbackup: All tables unlocked
mysqlbackup: All MySQL tables were locked for 1.034 seconds
mysqlbackup: Backup created in directory '/tmp/2011-04-06_17-55-21'
mysqlbackup: MySQL binlog position: filename mysql-bin.000019, position 2638
mysqlbackup: start_lsn: 1079508992
mysqlbackup: incremental_base_lsn: 1079509188
mysqlbackup: end_lsn: 1079511586
 
110406 17:57:09 mysqlbackup: mysqlbackup completed OK!

Quite straightforward…

[mysql@server1 bin]$ ll /tmp/2011-04-06_17-55-21
total 294012
-rw-r--r-- 1 mysql dba       318 Apr  6 17:55 backup-my.cnf
drwx------ 2 mysql dba      4096 Apr  6 17:57 dbt2
-rw-r--r-- 1 mysql dba        24 Apr  6 17:57 ibbackup_binlog_info
-rw-r--r-- 1 mysql dba       155 Apr  6 17:57 ibbackup_export_variables.txt
-rw-r----- 1 mysql dba      3584 Apr  6 17:57 ibbackup_logfile
-rw-r----- 1 mysql dba 300735911 Apr  6 17:57 ibdata1.ibz
drwx------ 2 mysql dba      4096 Apr  6 17:57 mysql
drwx------ 2 mysql dba      4096 Apr  6 17:57 performance_schema
drwx------ 2 mysql dba      4096 Apr  6 17:57 test

MySQL restore

First you have to apply the log (Oracle is not entering too much in detail of this step). It will apply ibbackup_logfile log file to your InnoDB data files (ibdata*):

[mysql@server1 bin]$ mysqlbackup --apply-log --uncompress /mysql/software/5.5.9/conf/my.cnf /tmp/2011-04-06_17-55-21
mysqlbackup: Starting mysqlbackup with following arguments:
mysqlbackup --apply-log --uncompress /mysql/software/5.5.9/conf/my.cnf /tmp/2011-04-06_17-55-21
mysqlbackup: IMPORTANT: Please check that apply-log run completes successfully.
            At the end of a successful 'apply-log' run mysqlbackup
            prints "mysqlbackup completed OK!".
 
mysqlbackup: Using ibbackup version 3.5.2 MySQL Enterprise Backup 3.5.2
mysqlbackup: Starting ibbackup binary with args:
 
./ibbackup --apply-log --uncompress /tmp/2011-04-06_17-55-21/backup-my.cnf
ibbackup version 3.5.2 MySQL Enterprise Backup 3.5.2
Copyright (c) 2002, 2010, Oracle and/or its affiliates.
Run 'ibbackup --help' for help and 'ibbackup --version' for version info.
 
Note: Uses posix_fadvise() for performance optimization.
 
Contents of /tmp/2011-04-06_17-55-21/backup-my.cnf:
innodb_data_home_dir got value /tmp/2011-04-06_17-55-21
innodb_data_file_path got value ibdata1:10M:autoextend:max:1G
datadir got value /tmp/2011-04-06_17-55-21
innodb_log_group_home_dir got value /tmp/2011-04-06_17-55-21
innodb_log_files_in_group got value 2
innodb_log_file_size got value 26214400
 
ibbackup: Uncompressing data file '/tmp/2011-04-06_17-55-21/ibdata1.ibz'
 400
110406 17:59:51  ibbackup: ibbackup_logfile's creation parameters:
ibbackup: start lsn 1079508992, end lsn 1079511586,
ibbackup: start checkpoint 1079509188.
InnoDB: Doing recovery: scanned up to log sequence number 1079511586
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 22 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 51 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 80 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 26214400
Setting log file size to 0 26214400
ibbackup: We were able to parse ibbackup_logfile up to
ibbackup: lsn 1079511586.
ibbackup: Last MySQL binlog file position 0 2638, file name /mysql/data02/mysql-bin.000019
ibbackup: The first data file is '/tmp/2011-04-06_17-55-21/ibdata1'
ibbackup: and the new created log files are at '/tmp/2011-04-06_17-55-21/'
ibbackup: System tablespace file format is Antelope.
110406 18:00:03  ibbackup: Full backup prepared for recovery successfully!
 
110406 18:00:03 mysqlbackup: mysqlbackup completed OK!

Now at this step you have the capability to start a MySQL server in this backup directory or restore the files to their original location.

To start it copy your initial my.cnf parameter file and replace all initial directory references to your backup one (i.e. /mysql/data02 by /tmp/2011-04-06_17-55-21 in this example).

Then as root issue:

[root@server1 /]# cd /mysql/software/5.5.9
[root@server1 5.5.9]# /mysql/software/5.5.9/bin/mysqld_safe --defaults-file=/tmp/2011-04-06_17-55-21/my.cnf --datadir=/tmp/2011-04-06_17-55-21 --pid-file=/tmp/2011-04-06_17-55-21/server1.domain.com.pid > /dev/null 2>&1 &
[1] 23920
[root@server1 5.5.9]#  ps -ef | grep mysql/software | grep -v grep
root     23920 10816  0 18:03 pts/3    00:00:00 /bin/sh /mysql/software/5.5.9/bin/mysqld_safe --defaults-file=/tmp/2011-04-06_17-55-21/my.cnf --datadir=/tmp/2011-04-06_17-55-21 --pid-file=/tmp/2011-04-06_17-55-21/server1.domain.com.pid
mysql    24471 23920  0 18:03 pts/3    00:00:00 /mysql/software/5.5.9/bin/mysqld --defaults-file=/tmp/2011-04-06_17-55-21/my.cnf --basedir=/mysql/software/5.5.9 --datadir=/tmp/2011-04-06_17-55-21 --plugin-dir=/mysql/software/5.5.9/lib/plugin --user=mysql --log-error=/tmp/2011-04-06_17-55-21/server1.domain.com.err --pid-file=/tmp/2011-04-06_17-55-21/server1.domain.com.pid --socket=/tmp/2011-04-06_17-55-21/mysql.sock --port=3316

Then you can issue with mysql account:

[mysql@server1 bin]$ /mysql/software/5.5.9/bin/mysql --user=root --password=`cat ~mysql/.root_password` --socket=/tmp/2011-04-06_17-55-21/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.9-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
 
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbt2               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

To restore backuped files to their original location issue:

[mysql@server1 bin]$ mysqlbackup --copy-back /mysql/software/5.5.9/conf/my.cnf /tmp/2011-04-06_17-55-21/
mysqlbackup: Starting mysqlbackup with following arguments:
mysqlbackup --copy-back /mysql/software/5.5.9/conf/my.cnf /tmp/2011-04-06_17-55-21/
mysqlbackup: IMPORTANT: Please check that copy-back run completes successfully.
            At the end of a successful 'copy-back' run mysqlbackup
            prints "mysqlbackup completed OK!".
 
mysqlbackup: Starting to copy back files
mysqlbackup: in '/tmp/2011-04-06_17-55-21/' directory
mysqlbackup: back to original data directory '/mysql/data02'
mysqlbackup: Copying back directory '/tmp/2011-04-06_17-55-21//dbt2'
mysqlbackup: Copying back file '/tmp/2011-04-06_17-55-21//server1-slow.log'
mysqlbackup: Copying back file '/tmp/2011-04-06_17-55-21//server1.domain.com.err'
mysqlbackup: Copying back file '/tmp/2011-04-06_17-55-21//ibbackup_binlog_info'
mysqlbackup: Copying back file '/tmp/2011-04-06_17-55-21//my.cnf'
mysqlbackup: Copying back directory '/tmp/2011-04-06_17-55-21//mysql'
mysqlbackup: Copying back file '/tmp/2011-04-06_17-55-21//mysql-bin.000001'
mysqlbackup: Copying back file '/tmp/2011-04-06_17-55-21//mysql-bin.index'
mysqlbackup: Copying back directory '/tmp/2011-04-06_17-55-21//performance_schema'
mysqlbackup: Copying back directory '/tmp/2011-04-06_17-55-21//test'
mysqlbackup: Starting to copy back InnoDB tables and indexes
mysqlbackup: in '/tmp/2011-04-06_17-55-21/'
mysqlbackup: back to original InnoDB datadirectory '/mysql/data02/'
mysqlbackup: Copying back file '/tmp/2011-04-06_17-55-21//ibdata1'
mysqlbackup: Starting to copy back InnoDB log files
mysqlbackup: in '/tmp/2011-04-06_17-55-21/'
mysqlbackup: back to original InnoDB log directory '/mysql/data02/'
mysqlbackup: Copying back file '/tmp/2011-04-06_17-55-21//ib_logfile0'
mysqlbackup: Copying back file '/tmp/2011-04-06_17-55-21//ib_logfile1'
mysqlbackup: Finished copying backup files.
 
 
110406 18:11:48 mysqlbackup: mysqlbackup completed OK!

Remark:
The restore will create a my.cnf file in your target directory (i.e. /mysql/data02 in this example) so better delete it first to avoid confusion in default MySQL start-up script (datadir directory has preference over the –defaults-file parameter).

XtraBackup

Configuration

Release that has been stated is latest stable at the time of this article i.e. 1.4. you can download it on Percona web site or by direct access use this link:

[mysql@server1 backup]$ gunzip xtrabackup-1.4.tar.gz
[mysql@server1 backup]$ tar xvf xtrabackup-1.4.tar
xtrabackup-1.4/
xtrabackup-1.4/share/
xtrabackup-1.4/share/t/
xtrabackup-1.4/share/t/xb_basic.sh
xtrabackup-1.4/share/t/xb_stream.sh
xtrabackup-1.4/share/run.sh
xtrabackup-1.4/share/inc/
xtrabackup-1.4/share/inc/common.sh
xtrabackup-1.4/share/inc/sakila-db/
xtrabackup-1.4/share/inc/sakila-db/sakila-data.sql
xtrabackup-1.4/share/inc/sakila-db/sakila-schema.sql
xtrabackup-1.4/bin/
xtrabackup-1.4/bin/xtrabackup_51
xtrabackup-1.4/bin/xtrabackup
xtrabackup-1.4/bin/tar4ibd
xtrabackup-1.4/bin/innobackupex-1.5.1
[mysql@server1 backup]$ cd xtrabackup-1.4
[mysql@server1 xtrabackup-1.4]$ ll
total 8
drwxr-xr-x 2 mysql dba 4096 Nov 17 20:00 bin
drwxr-xr-x 4 mysql dba 4096 Nov 17 20:00 share
[mysql@server1 xtrabackup-1.4]$ cd bin
[mysql@server1 bin]$ ll
total 10888
-rwxr-xr-x 1 mysql dba   85268 Nov 17 20:00 innobackupex-1.5.1
-rwxr-xr-x 1 mysql dba  100707 Nov 17 20:00 tar4ibd
-rwxr-xr-x 1 mysql dba 5790848 Nov 17 20:00 xtrabackup
-rwxr-xr-x 1 mysql dba 5133798 Nov 17 20:00 xtrabackup_51

configuration in my.cnf parameter file is exactly the same (!!).

MySQL backup

[mysql@server1 bin]$ innobackupex-1.5.1 --user=root --password=`cat ~mysql/.root_password`  --socket=/mysql/data02/mysql.sock --defaults-file=/mysql/software/5.5.9/conf/my.cnf /tmp
 
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.
 
110405 15:23:25  innobackupex-1.5.1: Starting mysql with options:  --defaults-file="/mysql/software/5.5.9/conf/my.cnf" --password=password --user=root --socket=/mysql/data02/mysql.sock --unbuffered --
110405 15:23:25  innobackupex-1.5.1: Connected to database with mysql child process (pid=920)
innobackupex-1.5.1: fatal error: MySQL version 5.5.9-enterprise-commercial-advanced-log is not supported.
innobackupex-1.5.1: fatal error: Upgrade XtraBackup to 1.5 or more recent

So stable release is not supporting MySQL 5.5…

I have tried with 1.5 beta release but this time got:

[mysql@server1 bin]$ innobackupex-1.5.1 --user=root --password=`cat ~mysql/.root_password`  --socket=/mysql/data02/mysql.sock --defaults-file=/mysql/software/5.5.9/conf/my.cnf /tmp
 
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.
 
110405 15:27:39  innobackupex-1.5.1: Starting mysql with options:  --defaults-file="/mysql/software/5.5.9/conf/my.cnf" --password=password --user=root --socket=/mysql/data02/mysql.sock --unbuffered --
110405 15:27:39  innobackupex-1.5.1: Connected to database with mysql child process (pid=4018)
110405 15:27:45  innobackupex-1.5.1: Connection to database server closed
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.9, for linux2.6 (i686) using  EditLine wrapper
innobackupex-1.5.1: Using mysql server version Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
 
sh: xtrabackup_55: command not found
innobackupex-1.5.1: fatal error: no 'mysqld' group in MySQL options
innobackupex-1.5.1: fatal error: OR no 'datadir' option in group 'mysqld' in MySQL options

Problem of copyright ??!!

So had to start a 5.1.49sp1 Enterprise Edition of MySQL and finally used XtraBackup release 1.4:

[mysql@server1 bin]$ innobackupex-1.5.1 --user=root --password=`cat ~mysql/.root_password` --socket=/mysql/data01/mysql.sock --defaults-file=/mysql/software/5.1.49sp1/conf/my.cnf /tmp
 
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.
 
110407 10:15:24  innobackupex-1.5.1: Starting mysql with options:  --defaults-file="/mysql/software/5.1.49sp1/conf/my.cnf" --password=password --user=root --socket=/mysql/data01/mysql.sock --unbuffered --
110407 10:15:24  innobackupex-1.5.1: Connected to database with mysql child process (pid=27669)
110407 10:15:30  innobackupex-1.5.1: Connection to database server closed
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.1.49sp1, for pc-linux-gnu (i686) using readline 5.1
innobackupex-1.5.1: Using mysql server version Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
 
innobackupex-1.5.1: Created backup directory /tmp/2011-04-07_10-15-30
110407 10:15:30  innobackupex-1.5.1: Starting mysql with options:  --defaults-file="/mysql/software/5.1.49sp1/conf/my.cnf" --password=password --user=root --socket=/mysql/data01/mysql.sock --unbuffered --
110407 10:15:30  innobackupex-1.5.1: Connected to database with mysql child process (pid=27693)
110407 10:15:34  innobackupex-1.5.1: Connection to database server closed
 
110407 10:15:34  innobackupex-1.5.1: Starting ibbackup with command: xtrabackup  --defaults-file="/mysql/software/5.1.49sp1/conf/my.cnf" --backup --suspend-at-end --target-dir=/tmp/2011-04-07_10-15-30
innobackupex-1.5.1: Waiting for ibbackup (pid=27703) to suspend
innobackupex-1.5.1: Suspend file '/tmp/2011-04-07_10-15-30/xtrabackup_suspended'
 
xtrabackup  Ver 1.4 Rev undefined for 5.1.47 pc-linux-gnu (i686)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysql/data01
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = /mysql/data01/
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend:max:1G
xtrabackup:   innodb_log_group_home_dir = /mysql/data01/
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 26214400
>> log scanned up to (2854291599)
Copying /mysql/data01/ibdata1
     to /tmp/2011-04-07_10-15-30/ibdata1
>> log scanned up to (2854291599)
>> log scanned up to (2854291599)
>> log scanned up to (2854291599)
>> log scanned up to (2854291599)
>> log scanned up to (2854291599)
>> log scanned up to (2854291599)
>> log scanned up to (2854291599)
        ...done
 
110407 10:17:01  innobackupex-1.5.1: Continuing after ibbackup has suspended
110407 10:17:01  innobackupex-1.5.1: Starting mysql with options:  --defaults-file="/mysql/software/5.1.49sp1/conf/my.cnf" --password=password --user=root --socket=/mysql/data01/mysql.sock --unbuffered --
110407 10:17:01  innobackupex-1.5.1: Connected to database with mysql child process (pid=27723)
110407 10:17:05  innobackupex-1.5.1: Starting to lock all tables...
>> log scanned up to (2854291599)
>> log scanned up to (2854291599)
110407 10:17:15  innobackupex-1.5.1: All tables locked and flushed to disk
 
110407 10:17:15  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 '/mysql/data01'
innobackupex-1.5.1: Backing up file '/mysql/data01/test/sbtest.frm'
innobackupex-1.5.1: Backing up files '/mysql/data01/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (65 files)
innobackupex-1.5.1: Backing up files '/mysql/data01/dbt2/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,opt,par}' (10 files)
110407 10:17:15  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): '2854291599'
>> log scanned up to (2854291599)
>> log scanned up to (2854291599)
xtrabackup: Stopping log copying thread..
xtrabackup: Transaction log of lsn (2854291599) to (2854291599) was copied.
110407 10:17:18  innobackupex-1.5.1: All tables unlocked
110407 10:17:18  innobackupex-1.5.1: Connection to database server closed
 
innobackupex-1.5.1: Backup created in directory '/tmp/2011-04-07_10-15-30'
innobackupex-1.5.1: MySQL binlog position: filename 'mysql-bin.000025', position 106
110407 10:17:18  innobackupex-1.5.1: completed OK!
[mysql@server1 bin]$ ll /tmp/2011-04-07_10-15-30
total 779040
-rw-r--r-- 1 mysql dba       332 Apr  7 10:15 backup-my.cnf
drwxr-xr-x 2 mysql dba      4096 Apr  7 10:17 dbt2
-rw-r--r-- 1 mysql dba 796917760 Apr  7 10:16 ibdata1
drwxr-xr-x 2 mysql dba      4096 Apr  7 10:17 mysql
-rw-r--r-- 1 mysql dba         0 Apr  7 10:17 mysql-stderr
-rw-r--r-- 1 mysql dba       379 Apr  7 10:17 mysql-stdout
drwxr-xr-x 2 mysql dba      4096 Apr  7 10:17 test
-rw-r--r-- 1 mysql dba        10 Apr  7 10:17 xtrabackup_binary
-rw-r--r-- 1 mysql dba        23 Apr  7 10:17 xtrabackup_binlog_info
-rw-r--r-- 1 mysql dba        61 Apr  7 10:17 xtrabackup_checkpoints
-rw-r--r-- 1 mysql dba      2560 Apr  7 10:17 xtrabackup_logfile

Same target directory, more or less same tool !!! MySQL Enterprise Backup is also using a Percona license. Who is copying who ?

MySQL restore

Again same command, you have to apply the transaction log file named “xtrabackup_logfile” to your backuped InnoDB files:

[mysql@server1 bin]$ innobackupex-1.5.1 --user=root --password=`cat ~mysql/.root_password` --defaults-file=/mysql/software/5.1.49sp1/conf/my.cnf --apply-log /tmp/2011-04-07_10-15-30
 
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 apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex-1.5.1
           prints "completed OK!".
 
 
 
110407 11:02:25  innobackupex-1.5.1: Starting ibbackup with command: xtrabackup  --defaults-file="/mysql/software/5.1.49sp1/conf/my.cnf" --prepare --target-dir=/tmp/2011-04-07_10-15-30
 
xtrabackup  Ver 1.4 Rev undefined for 5.1.47 pc-linux-gnu (i686)
xtrabackup: cd to /tmp/2011-04-07_10-15-30
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(2854291599)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend:max:1G
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use InnoDB's own implementation
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
110407 11:02:26  InnoDB: highest supported file format is Barracuda.
110407 11:02:26 Percona XtraDB (http://www.percona.com) 1.0.8-11.2 started; log sequence number 2854291599
 
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 27316214, file name /mysql/data01/mysql-bin.000023
 
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
110407 11:02:26  InnoDB: Starting shutdown...
110407 11:02:31  InnoDB: Shutdown completed; log sequence number 2854291599
 
110407 11:02:31  innobackupex-1.5.1: Restarting xtrabackup with command: xtrabackup  --defaults-file="/mysql/software/5.1.49sp1/conf/my.cnf" --prepare --target-dir=/tmp/2011-04-07_10-15-30
for creating ib_logfile*
 
xtrabackup  Ver 1.4 Rev undefined for 5.1.47 pc-linux-gnu (i686)
xtrabackup: cd to /tmp/2011-04-07_10-15-30
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend:max:1G
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 26214400
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use InnoDB's own implementation
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
110407 11:02:31  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 25 MB
InnoDB: Database physically writes the file full: wait...
110407 11:02:34  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 25 MB
InnoDB: Database physically writes the file full: wait...
110407 11:02:36  InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
110407 11:02:36  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Last MySQL binlog file position 0 27316214, file name /mysql/data01/mysql-bin.000023
110407 11:02:36 Percona XtraDB (http://www.percona.com) 1.0.8-11.2 started; log sequence number 2854291980
 
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 27316214, file name /mysql/data01/mysql-bin.000023
 
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
110407 11:02:36  InnoDB: Starting shutdown...
110407 11:02:41  InnoDB: Shutdown completed; log sequence number 2854291980
110407 11:02:41  innobackupex-1.5.1: completed OK!

Then, same as above, create a new my.cnf parameters file, modify it to match backup directory (i.e. replace /mysql/data01 by /tmp/2011-04-07_10-15-30 in our example) and finally start MySQL as root with:

[root@server1 /]# cd /mysql/software/5.1.49sp1
[root@server1 5.1.49sp1]# /mysql/software/5.1.49sp1/bin/mysqld_safe --defaults-file=/tmp/2011-04-07_10-15-30/my.cnf --datadir=/tmp/2011-04-07_10-15-30 --pid-file=/tmp/2011-04-07_10-15-30/server1.domain.com.pid > /dev/null 2>&1 &
[1] 28081
[root@server1 5.1.49sp1]# ps -ef | grep mysql/software
root     28081 10816  0 11:08 pts/3    00:00:00 /bin/sh /mysql/software/5.1.49sp1/bin/mysqld_safe --defaults-file=/tmp/2011-04-07_10-15-30/my.cnf --datadir=/tmp/2011-04-07_10-15-30 --pid-file=/tmp/2011-04-07_10-15-30/server1.domain.com.pid
mysql    28269 28081  0 11:08 pts/3    00:00:00 /mysql/software/5.1.49sp1/bin/mysqld --defaults-file=/tmp/2011-04-07_10-15-30/my.cnf --basedir=/mysql/software/5.1.49sp1 --datadir=/tmp/2011-04-07_10-15-30 --user=mysql --log-error=/tmp/2011-04-07_10-15-30/server1.domain.com.err --pid-file=/tmp/2011-04-07_10-15-30/server1.domain.com.pid --socket=/tmp/2011-04-07_10-15-30/mysql.sock --port=3306
root     28311 10816  0 11:16 pts/3    00:00:00 grep mysql/software

Then with your MySQL account your can connect to the backup database with:

[mysql@server1 2011-04-07_10-15-30]$ /mysql/software/5.1.49sp1/bin/mysql --user=root --password=`cat ~mysql/.root_password` --socket=/tmp/2011-04-07_10-15-30/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.1.49sp1-enterprise-gpl-advanced-log MySQL Enterprise Server - Advanced Edition (GPL)
 
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
 
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dbt2               |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.02 sec)

You can also copy back the backuped files to original location:

[mysql@server1 bin]$ innobackupex-1.5.1 --user=root --password=`cat ~mysql/.root_password` --defaults-file=/mysql/software/5.1.49sp1/conf/my.cnf --copy-back /tmp/2011-04-07_10-15-30
 
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 copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex-1.5.1
           prints "completed OK!".
 
innobackupex-1.5.1: Starting to copy MyISAM tables, indexes,
innobackupex-1.5.1: .MRG, .TRG, .TRN, .ARM, .ARZ, .opt, and .frm files
innobackupex-1.5.1: in '/tmp/2011-04-07_10-15-30'
innobackupex-1.5.1: back to original data directory '/mysql/data01'
innobackupex-1.5.1: Copying file '/tmp/2011-04-07_10-15-30/mysql-bin.index'
innobackupex-1.5.1: Copying file '/tmp/2011-04-07_10-15-30/server1.domain.com.err'
innobackupex-1.5.1: Copying file '/tmp/2011-04-07_10-15-30/xtrabackup_binlog_pos_innodb'
innobackupex-1.5.1: Copying directory '/tmp/2011-04-07_10-15-30/mysql'
innobackupex-1.5.1: Copying file '/tmp/2011-04-07_10-15-30/xtrabackup_checkpoints'
innobackupex-1.5.1: Copying directory '/tmp/2011-04-07_10-15-30/dbt2'
innobackupex-1.5.1: Copying file '/tmp/2011-04-07_10-15-30/xtrabackup_binlog_info'
innobackupex-1.5.1: Copying file '/tmp/2011-04-07_10-15-30/xtrabackup_binary'
innobackupex-1.5.1: Copying directory '/tmp/2011-04-07_10-15-30/test'
innobackupex-1.5.1: Copying file '/tmp/2011-04-07_10-15-30/my.cnf'
innobackupex-1.5.1: Copying file '/tmp/2011-04-07_10-15-30/mysql-bin.000001'
 
innobackupex-1.5.1: Starting to copy InnoDB tables and indexes
innobackupex-1.5.1: in '/tmp/2011-04-07_10-15-30'
innobackupex-1.5.1: back to original InnoDB data directory '/mysql/data01/'
innobackupex-1.5.1: Copying file '/tmp/2011-04-07_10-15-30/ibdata1'
 
innobackupex-1.5.1: Starting to copy InnoDB log files
innobackupex-1.5.1: in '/tmp/2011-04-07_10-15-30'
innobackupex-1.5.1: back to original InnoDB log directory '/mysql/data01/'
innobackupex-1.5.1: Copying file '/tmp/2011-04-07_10-15-30/ib_logfile1'
innobackupex-1.5.1: Copying file '/tmp/2011-04-07_10-15-30/ib_logfile0'
innobackupex-1.5.1: Finished copying back files.
 
110407 11:24:27  innobackupex-1.5.1: completed OK!

And start MySQL with your usual script (same as for Oracle Enterprise Backup, delete the my.cnf file before doing so)…

Incremental MySQL backup and restore

As clearly stated in XtraBackup 1.4 documentation: Incremental backups are not fully supported in innobackupex yet. I will test incremental bakup with MySQL Enterprise Backup.

First, perform a full database backup with:

[mysql@server1 bin]$ mysqlbackup --user=root --password=`cat ~mysql/.root_password` --compress --socket=/mysql/data02/mysql.sock  /mysql/software/5.5.9/conf/my.cnf /tmp
.
.
.
[mysql@server1 bin]$ ll /tmp/2011-04-07_14-39-19
total 294012
-rw-r--r-- 1 mysql dba       318 Apr  7 14:39 backup-my.cnf
drwx------ 2 mysql dba      4096 Apr  7 14:41 dbt2
-rw-r--r-- 1 mysql dba        24 Apr  7 14:41 ibbackup_binlog_info
-rw-r--r-- 1 mysql dba       155 Apr  7 14:41 ibbackup_export_variables.txt
-rw-r----- 1 mysql dba      3584 Apr  7 14:41 ibbackup_logfile
-rw-r----- 1 mysql dba 300735986 Apr  7 14:41 ibdata1.ibz
drwx------ 2 mysql dba      4096 Apr  7 14:41 mysql
drwx------ 2 mysql dba      4096 Apr  7 14:41 performance_schema
drwx------ 2 mysql dba      4096 Apr  7 14:41 test

Create a small test table just before the incremental backup:

mysql> use test;
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
mysql> create table yannick1(val integer, descr varchar(30)) engine=innodb;
Query OK, 0 rows affected (0.06 sec)
 
mysql> insert into yannick1 values(1,'One');
Query OK, 1 row affected (0.01 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from test.yannick1;
+------+-------+
| val  | descr |
+------+-------+
|    1 | One   |
+------+-------+
1 row in set (0.00 sec)

Perform an incremental backup with:

[mysql@server1 bin]$ mysqlbackup --user=root --password=`cat ~mysql/.root_password` --compress --incremental --lsn 1079515092 --socket=/mysql/data02/mysql.sock  /mysql/software/5.5.9/conf/my.cnf /tmp
.
.
.
[mysql@server1 bin]$ ll /tmp/2011-04-07_15-21-02
total 92
-rw-r--r-- 1 mysql dba   318 Apr  7 15:21 backup-my.cnf
drwx------ 2 mysql dba  4096 Apr  7 15:21 dbt2
-rw-r--r-- 1 mysql dba    24 Apr  7 15:21 ibbackup_binlog_info
-rw-r--r-- 1 mysql dba   155 Apr  7 15:21 ibbackup_export_variables.txt
-rw-r--r-- 1 mysql dba     0 Apr  7 15:21 ibbackup_ibd_files
-rw-r----- 1 mysql dba  3072 Apr  7 15:21 ibbackup_logfile
-rw-r----- 1 mysql dba 57261 Apr  7 15:21 ibdata1.ibz
drwx------ 2 mysql dba  4096 Apr  7 15:21 mysql
drwx------ 2 mysql dba  4096 Apr  7 15:21 performance_schema
drwx------ 2 mysql dba  4096 Apr  7 15:21 test

Remark:
You do not have to guess the lsn but you simply read it from output of your last full backup at following line:

ibbackup: Scanned log up to lsn 1079515092.

Then process is more or less the same as a normal restore, you apply transactions that occured when doing the backup and you then apply the incremental backup to your full backup.

[mysql@server1 bin]$ mysqlbackup --apply-log --uncompress /mysql/software/5.5.9/conf/my.cnf /tmp/2011-04-07_14-39-19
.
.
.
[mysql@server1 bin]$ mysqlbackup --apply-log --incremental --uncompress /tmp/2011-04-07_15-21-02/backup-my.cnf /tmp/2011-04-07_14-39-19/backup-my.cnf
.
.
.
[mysql@server1 bin]$ mysqlbackup --copy-back /mysql/software/5.5.9/conf/my.cnf /tmp/2011-04-07_14-39-19
.
.
.

Remark:
For the incremental apply log process order of configuration file in incremental-backup-my.cnf full-backup-my.cnf.

Then you can start and connect to your MySQL database and the test table will be there…

Conclusion

XtraBackup seems to be a bit late on latest MySQL release (difficult to understand if it is linked to copyrights or to the fact that it is a free tool so development is surely not working on top priority on it) and, so far, do not support compression (even if trivial to add a gzip -9 in a shell script).

Oracle documentation is more complete than the Percona one and you can find more real life example (than can be used with XtraBackup as it is same parameter). On the counterpart Google has more example with Percona tool as it is free product…

Even if you hide the password when launching both tools it appear clearly in the output so better make sure log files of your backup jobs are not readable as most probably you will use root to perform your backup…

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

4 thoughts on “MySQL backup and recovery”

  1. Arvi says:

    Hello Yannick,

    I am using MySQL Enterprise Backup, I have taken compress full backup of mysql(innodb+myisam) in /Backup directory. So it includes few files related to MyISAM tables and ibdata1.ibz
    Before restoring database, I want to uncompress data in different directory (/restore) and keep other files in /Backup directory only. And at the time of restore, I want mysqlbackup to read Innodb Tablespace from (/restore) location and myisqm files from /Backup location.

    what changes should I male in backup.my.cnf file Please.
    ~Arvi

    • Yannick Jaquier says:

      Hello Arvi,

      For obvious cost reasons I’m not using MySQL Enterprise Backup but I don’t think such option exists. I’m afraid you need to cp or make links to progress further because unless they have added the option the source directory is one unique entry in tool options…

      Thanks, Yannick.

  2. Arvi says:

    Hell Yannick,
    you explained it very nicely.

    Could you please explain following lines:
    ibbackup: System tablespace file format is Antelope.
    ibbackup: Found checkpoint at lsn 1079509188.
    ibbackup: Starting log scan from lsn 1079508992.
    110406 17:55:21 ibbackup: Copying log…
    110406 17:55:21 ibbackup: Log copied, lsn 1079509188.
    ibbackup: We wait 1 second before starting copying the data files..

    Thanks a lot.
    Arvi

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>