MySql在生产环境中是用mysqldump还是xtrabackup备份和恢复数据

如题，究竟该使用mysqldump还是xtrabackup，要说用，两个都能备份，都支持热备，但是生产环境我们要考虑的是效率，就是不管备份还是恢复，都要快，要稳定。

之前我在维护mysql数据库的时候，使用mysqldump来进行备份与恢复，在备份的时候锁住表，然后全部备份，在数据少的时候没问题，但如果数据很多，不允许锁表，同时需要恢复数据块的情况，mysqldump就不适合了，我在恢复一个4G数据文件的数据库的时候，恢复的数据是使用mysqldump的数据，恢复了3个小时还没有反应，造成的影响很严重，所以我开始寻找其他的别发软件来满足以上的需求，幸好找到了，就是使用xtrabackup来进行备份与恢复，恢复4G数据文件的数据库，仅需要14秒，同时在备份的时候不会锁表，而且支持增量备份，所以把我的比较分享给大家，希望对大家有益！

Xtrabackup 是percona公司的开源项目，用以实现类似innodb官方的热备份工具InnoDB Hot Backup的功能，能够非常快速地备份与恢复mysql数据库。 Xtrabackup中包含两个工具：

xtrabackup是用于热备份innodb, xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；

innobackupex是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。

由于innobackupex的功能更为全面和完善，所以，本文以innobackupex作为基础进行研究描述。

下面介绍xtrabackup的全部、增量的备份与恢复。

一、下载与安装

1、下载

wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-1.6.7/binary/Linux/x86_64/xtrabackup-1.6.7.tar.gz 

2、安装依赖库

如果是debian系列的话

apt-get install debhelper autotools-dev libaio-dev wget automake libtool bison libncurses-dev libz-dev cmake bzr 

如果是redhat系列的话

yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr bison libtool ncurses-devel zlib-devel 

3、解压

tar zxvf xtrabackup-1.6.7.tar.gz

4、进入目录

cd xtrabackup-1.6.7

5、复制

cd bin 
cp * /usr/bin 
然后就安装完成了，下面开始备份 

其中，

innobackupex是我们要使用的备份工具；

xtrabackup是被封装在innobackupex之中的，innobackupex运行时需要调用它；

xtrabackup_51是xtrabackup运行时需要调用的工具；

tar4ibd是以tar流的形式产生备份时用来打包的工具。

6、对某个数据库进行全部备份的命令介绍

innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --database=test --stream=tar /tmp/data/ 2>/tmp/data/err.log|gzip 1>/tmp/data/test.tar.gz 

说明：

      --database=test 单独对test数据库做备份 ，若是不添加此参数那就那就是对全库做备份

      2>/tmp/data/err.log 输出信息写入日志中

      1>/tmp/data/test.tar.gz 打包压缩存储到该文件中

 

二、对数据库的全部备份与恢复

下面开始测试xtrabackup的全部备份

（1）先进入mysql里创建一个新的test数据库

root@client2:/tmp# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 40 
Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu) 

Copyright (c) 2000, 2012, 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> drop database test; 
Query OK, 3 rows affected (0.13 sec) 
mysql> create database test; 
Query OK, 1 row affected (0.00 sec) 

mysql> use test; 
Database changed 
mysql> create table test (id int); 
Query OK, 0 rows affected (0.06 sec) 
mysql> insert into test values(1); 
Query OK, 1 row affected (0.04 sec) 

mysql> insert into test values(2); 
Query OK, 1 row affected (0.01 sec) 

mysql> insert into test values(3); 
Query OK, 1 row affected (0.00 sec) 

mysql> insert into test values(4); 
Query OK, 1 row affected (0.00 sec) 

mysql> insert into test values(5); 
Query OK, 1 row affected (0.01 sec) 

mysql> select * from test; 
+------+ 
| id | 
+------+ 
|1 | 
|2 | 
|3 | 
|4 | 
|5 | 
+------+ 
5 rows in set (0.00 sec) 

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

（2）然后备份test的整个数据库

使用下面的backup.sh脚本

root@client2:/tmp# cat backup.sh 
#!/bin/bash 
user='root' 
passwd='123456' 
database=test 
my_config='/etc/mysql/my.cnf' 
log=$database-$(date +%Y%m%d%H%M).log 
str=$database-$(date +%Y%m%d%H%M).tar.gz 
backup_dir='/tmp/data' 
echo "Start to backup at $(date +%Y%m%d%H%M)" 
if [ ! -d "$backup_dir" ];then 
mkdir $backup_dir 
fi 
innobackupex --user=$user --password=$passwd --defaults-file=$my_config --database=$database --stream=tar $backup_dir 2>$backup_dir/$log | gzip 1>$backup_dir/$str 
if [ $? -eq 0 ];then 
echo "Backup is finish! at $(date +%Y%m%d%H%M)" 
exit 0 
else 
echo "Backup is Fail! at $(date +%Y%m%d%H%M)" 
exit 1 
fi

现在开始运行此脚本

root@client2:/tmp# sh backup.sh 
Start to backup at 201303072101 
Backup is finish! at 201303072102

然后到data里查看结果

root@client2:/tmp# cd data 
root@client2:/tmp/data# ll 
total 3272 
drwxr-xr-x 2 root root4096 Mar 7 21:01 ./ 
drwxrwxrwt 13 root root4096 Mar 7 21:02 ../ 
-rw-r--r-- 1 root root3780 Mar 7 21:02 test-201303072101.log 
-rw-r--r-- 1 root root 3336909 Mar 7 21:02 test-201303072101.tar.gz 
root@client2:/tmp/data# cat test-201303072101.log 

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 
and Percona Inc 2009-2012. All Rights Reserved. 

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

130307 21:01:39 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 
130307 21:01:39 innobackupex: Connected to database with mysql child process (pid=12441) 
130307 21:01:45 innobackupex: Connection to database server closed 
IMPORTANT: Please check that the backup run completes successfully. 
At the end of a successful backup run innobackupex 
prints "completed OK!". 

innobackupex: Using mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2 
innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 

innobackupex: Created backup directory /tmp/data 
130307 21:01:45 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 
130307 21:01:45 innobackupex: Connected to database with mysql child process (pid=12471) 
130307 21:01:47 innobackupex: Connection to database server closed 

130307 21:01:47 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/mysql/my.cnf" --backup --suspend-at-end --log-stream --target-dir=/tmp 
innobackupex: Waiting for ibbackup (pid=12478) to suspend 
innobackupex: Suspend file '/tmp/xtrabackup_suspended' 

xtrabackup: suspend-at-end is enabled. 
xtrabackup: uses posix_fadvise(). 
xtrabackup: cd to /var/lib/mysql 
xtrabackup: Target instance is assumed as followings. 
xtrabackup: innodb_data_home_dir = ./ 
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend 
xtrabackup: innodb_log_group_home_dir = ./ 
xtrabackup: innodb_log_files_in_group = 2 
xtrabackup: innodb_log_file_size = 5242880 
130307 21:01:47 InnoDB: Using Linux native AIO 
xtrabackup: Stream mode. 
>> log scanned up to (59605543) 

130307 21:01:49 innobackupex: Continuing after ibbackup has suspended 

innobackupex: Starting to backup InnoDB tables and indexes 
innobackupex: from original InnoDB data directory '/var/lib/mysql' 
innobackupex: Backing up as tar stream 'ibdata1' 
130307 21:01:52 innobackupex: Starting mysql with options: --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 
130307 21:01:52 innobackupex: Connected to database with mysql child process (pid=12494) 
>> log scanned up to (59605543) 
130307 21:01:54 innobackupex: Starting to lock all tables... 
>> log scanned up to (59605543) 
>> log scanned up to (59605543) 
130307 21:02:04 innobackupex: All tables locked and flushed to disk 

130307 21:02:04 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI, 
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in 
innobackupex: subdirectories of '/var/lib/mysql' 
innobackupex: Backing up file '/var/lib/mysql/test/test.frm' 
innobackupex: Backing up file '/var/lib/mysql/test/db.opt' 
130307 21:02:04 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files 

innobackupex: Resuming ibbackup 

xtrabackup: The latest check point (for incremental): '59605543' 
>> log scanned up to (59605543) 
xtrabackup: Transaction log of lsn (59605543) to (59605543) was copied. 
130307 21:02:06 innobackupex: All tables unlocked 
130307 21:02:06 innobackupex: Connection to database server closed 

innobackupex: Backup created in directory '/tmp/data' 
innobackupex: MySQL binlog position: filename 'mysql-bin.000022', position 107 
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream. 
130307 21:02:06 innobackupex: completed OK!

可以看到备份完成了

（3）恢复数据库

先关闭mysql服务，然后再删除test数据库文件

root@client2:/tmp/data# service mysql stop 
mysql stop/waiting 
root@client2:/tmp/data# cd /var/lib/mysql/ 
root@client2:/var/lib/mysql# ll 
total 77860 
drwx------ 8 mysql mysql 4096 Mar 7 20:59 ./ 
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 
-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 
drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 
-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 
drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 
drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 
-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 
drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 
drwx------ 2 mysql mysql 4096 Mar 7 21:00 test/ 
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/ 
root@client2:/var/lib/mysql# rm -rf test 
root@client2:/var/lib/mysql# ll 
total 77856 
drwx------ 7 mysql mysql 4096 Mar 7 21:03 ./ 
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 
-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 
drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 
-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 
drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 
drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 
-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 
drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

开始恢复数据库

先把之前/tmp/data里的数据库压缩版给解压

root@client2:cd /tmp/data 
root@client2:/tmp/data# tar -izxvf test-201303072101.tar.gz -C /tmp/restore/ 
backup-my.cnf 
ibdata1 
xtrabackup_binlog_info 
test/test.frm 
test/db.opt 
xtrabackup_logfile 
xtrabackup_checkpoints 
xtrabackup_binary

注意：这里tar解包必须使用-i参数，否则解压出来的文件只有一个backup-my.cnf

查看一下restore的内容

root@client2:/tmp/data# cd /tmp/restore/ 
root@client2:/tmp/restore# ll 
total 67616 
drwxr-xr-x 3 root root 4096 Mar 7 21:03 ./ 
drwxrwxrwt 14 root root 4096 Mar 7 21:03 ../ 
-rw-r--r-- 1 root root 260 Mar 7 21:01 backup-my.cnf 
-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:01 ibdata1 
drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/ 
-rw-r--r-- 1 root root13 Mar 7 21:02 xtrabackup_binary 
-rw-r--r-- 1 root root23 Mar 7 21:02 xtrabackup_binlog_info 
-rw-r--r-- 1 root root79 Mar 7 21:02 xtrabackup_checkpoints 
-rw-r--r-- 1 root root 2560 Mar 7 21:02 xtrabackup_logfile

然后将备份文件中的日志应用到备份文件中的数据文件上

root@client2:/tmp/restore# innobackupex --user=root --password=123456 --apply-log /tmp/restore/ 

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 
and Percona Inc 2009-2012. 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 
prints "completed OK!". 

130307 21:04:18 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/tmp/restore/backup-my.cnf" --prepare --target-dir=/tmp/restore 

xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined) 
xtrabackup: cd to /tmp/restore 
xtrabackup: This target seems to be not prepared yet. 
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59605543) 
xtrabackup: Temporary instance for recovery is set as followings. 
xtrabackup: innodb_data_home_dir = ./ 
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend 
xtrabackup: innodb_log_group_home_dir = ./ 
xtrabackup: innodb_log_files_in_group = 1 
xtrabackup: innodb_log_file_size = 2097152 
130307 21:04:19 InnoDB: Using Linux native AIO 
xtrabackup: Starting InnoDB instance for recovery. 
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 
130307 21:04:19 InnoDB: The InnoDB memory heap is disabled 
130307 21:04:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins 
130307 21:04:19 InnoDB: Compressed tables use zlib 1.2.3 
130307 21:04:19 InnoDB: Using Linux native AIO 
130307 21:04:19 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 
130307 21:04:19 InnoDB: Initializing buffer pool, size = 100.0M 
130307 21:04:19 InnoDB: Completed initialization of buffer pool 
130307 21:04:19 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! 
130307 21:04:19 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 107, file name /var/log/mysql/mysql-bin.000022 
130307 21:04:20 InnoDB: Waiting for the background threads to start 
130307 21:04:21 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59605543 

[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 107, file name /var/log/mysql/mysql-bin.000022 

xtrabackup: starting shutdown with innodb_fast_shutdown = 1 
130307 21:04:21 InnoDB: Starting shutdown... 
130307 21:04:25 InnoDB: Shutdown completed; log sequence number 59606758 

130307 21:04:25 innobackupex: Restarting xtrabackup with command: xtrabackup_55 --defaults-file="/tmp/restore/backup-my.cnf" --prepare --target-dir=/tmp/restore 
for creating ib_logfile* 

xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined) 
xtrabackup: cd to /tmp/restore 
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 
xtrabackup: innodb_log_group_home_dir = ./ 
xtrabackup: innodb_log_files_in_group = 2 
xtrabackup: innodb_log_file_size = 5242880 
130307 21:04:25 InnoDB: Using Linux native AIO 
xtrabackup: Starting InnoDB instance for recovery. 
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 
130307 21:04:25 InnoDB: The InnoDB memory heap is disabled 
130307 21:04:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins 
130307 21:04:25 InnoDB: Compressed tables use zlib 1.2.3 
130307 21:04:25 InnoDB: Using Linux native AIO 
130307 21:04:25 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 
130307 21:04:25 InnoDB: Initializing buffer pool, size = 100.0M 
130307 21:04:25 InnoDB: Completed initialization of buffer pool 
130307 21:04:25 InnoDB: Log file ./ib_logfile0 did not exist: new to be created 
InnoDB: Setting log file ./ib_logfile0 size to 5 MB 
InnoDB: Database physically writes the file full: wait... 
130307 21:04:25 InnoDB: Log file ./ib_logfile1 did not exist: new to be created 
InnoDB: Setting log file ./ib_logfile1 size to 5 MB 
InnoDB: Database physically writes the file full: wait... 
130307 21:04:25 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! 
130307 21:04:25 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 107, file name /var/log/mysql/mysql-bin.000022 
130307 21:04:26 InnoDB: Waiting for the background threads to start 
130307 21:04:27 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59607052 

[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 107, file name /var/log/mysql/mysql-bin.000022 

xtrabackup: starting shutdown with innodb_fast_shutdown = 1 
130307 21:04:27 InnoDB: Starting shutdown... 
130307 21:04:31 InnoDB: Shutdown completed; log sequence number 59607052 
130307 21:04:31 innobackupex: completed OK!

这里的--apply-log指明是将日志应用到数据文件上，完成之后将备份文件中的数据恢复到数据库中：

然后再查看一下当前目录内容

root@client2:/tmp/restore# ll 
total 79904 
drwxr-xr-x 3 root root 4096 Mar 7 21:04 ./ 
drwxrwxrwt 14 root root 4096 Mar 7 21:04 ../ 
-rw-r--r-- 1 root root 260 Mar 7 21:01 backup-my.cnf 
-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:04 ibdata1 
-rw-r--r-- 1 root root 5242880 Mar 7 21:04 ib_logfile0 
-rw-r--r-- 1 root root 5242880 Mar 7 21:04 ib_logfile1 
drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/ 
-rw-r--r-- 1 root root13 Mar 7 21:02 xtrabackup_binary 
-rw-r--r-- 1 root root23 Mar 7 21:02 xtrabackup_binlog_info 
-rw-r--r-- 1 root root36 Mar 7 21:04 xtrabackup_binlog_pos_innodb 
-rw-r--r-- 1 root root79 Mar 7 21:04 xtrabackup_checkpoints 
-rw-r--r-- 1 root root 2097152 Mar 7 21:04 xtrabackup_logfile 
然后把test目录复制到/var/lib/mysql目录 
root@client2:/tmp/restore# rsync -avz test ib* /var/lib/mysql/ 
sending incremental file list 
test/ 
test/db.opt 
test/test.frm 

sent 381 bytes received 54 bytes 870.00 bytes/sec 
total size is 8621 speedup is 19.82 
root@client2:/tmp/restore# cd /var/lib/mysql/ 
root@client2:/var/lib/mysql# ll 
total 77860 
drwx------ 8 mysql mysql 4096 Mar 7 21:06 ./ 
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 
-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 
drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 
-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 
drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 
drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 
-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 
drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 
drwxr-xr-x 2 root root 4096 Mar 7 21:03 test/ 
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

然后再修改test的用户与组为mysql

root@client2:/var/lib/mysql# chown -R mysql:mysql test 
root@client2:/var/lib/mysql# ll 
total 77860 
drwx------ 8 mysql mysql 4096 Mar 7 21:06 ./ 
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 
-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 
drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 
-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1 
drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 
drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 
-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 
drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 
drwxr-xr-x 2 mysql mysql 4096 Mar 7 21:03 test/ 
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

然后启动mysql，并查看test数据库的表里内容

root@client2:/var/lib/mysql# service mysql start 
mysql start/running, process 12730 
root@client2:/var/lib/mysql# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 36 
Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu) 

Copyright (c) 2000, 2012, 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> 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> select * from test; 
+------+ 
| id | 
+------+ 
|1 | 
|2 | 
|3 | 
|4 | 
|5 | 
+------+ 
5 rows in set (0.01 sec)

可以看到数据库已经恢复完成

可能大家有个疑问，为什么我这里不像很多网上的文章里是在apply-log后，使用copy-back如果使用/usr/bin/innobackupex --copy-back命令后，会报Original data directory is not empty! at /usr/local/xtrabackup/bin/innobackupex line 538.恢复的目录必须为空。经查官网，这是xtrabackup的一个BUG。

innobackupex–copy-back was run. With this bug fix, innobackupex–copy-back operation if the destination is not empty, avoiding potential data loss or a strang combination of a restored backup and previous data. Bug Fixed: #737569 (Valentine Gostev) will now error out of the did not check that MySQL datadir was empty before

所以在apply-log后直接复制数据目录到数据库的位置上吧。

三、对数据库的增量备份与恢复

为了进行增量备份，先对数据库添加一些数据

mysql> insert into test values(11); 
Query OK, 1 row affected (0.10 sec) 

mysql> insert into test values(12); 
Query OK, 1 row affected (0.05 sec) 

mysql> insert into test values(13); 
Query OK, 1 row affected (0.00 sec) 

mysql> insert into test values(14); 
Query OK, 1 row affected (0.00 sec) 

mysql> insert into test values(15); 
Query OK, 1 row affected (0.00 sec) 

mysql> flush privileges; 
Query OK, 0 rows affected (0.01 sec) 

mysql> select * from test; 
+------+ 
| id | 
+------+ 
|1 | 
|2 | 
|3 | 
|4 | 
|5 | 
| 11 | 
| 12 | 
| 13 | 
| 14 | 
| 15 | 
+------+ 
10 rows in set (0.00 sec) 

然后进行增量的备份

root@client2:/var/lib/mysql# innobackupex --user=root --password=123456 --database=test --incremental --incremental-basedir=/tmp/restore/ /tmp/data 

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 
and Percona Inc 2009-2012. All Rights Reserved. 

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

130307 21:13:38 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered -- 
130307 21:13:38 innobackupex: Connected to database with mysql child process (pid=12864) 
130307 21:13:44 innobackupex: Connection to database server closed 
IMPORTANT: Please check that the backup run completes successfully. 
At the end of a successful backup run innobackupex 
prints "completed OK!". 

innobackupex: Using mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2 
innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 

innobackupex: Created backup directory /tmp/data/2013-03-07_21-13-44 
130307 21:13:44 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered -- 
130307 21:13:44 innobackupex: Connected to database with mysql child process (pid=12891) 
130307 21:13:46 innobackupex: Connection to database server closed 

130307 21:13:46 innobackupex: Starting ibbackup with command: xtrabackup_55 --backup --suspend-at-end --target-dir=/tmp/data/2013-03-07_21-13-44 --incremental-basedir='/tmp/restore/' 
innobackupex: Waiting for ibbackup (pid=12898) to suspend 
innobackupex: Suspend file '/tmp/data/2013-03-07_21-13-44/xtrabackup_suspended' 

xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined) 
incremental backup from 59605543 is enabled. 
xtrabackup: uses posix_fadvise(). 
xtrabackup: cd to /var/lib/mysql 
xtrabackup: Target instance is assumed as followings. 
xtrabackup: innodb_data_home_dir = ./ 
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend 
xtrabackup: innodb_log_group_home_dir = ./ 
xtrabackup: innodb_log_files_in_group = 2 
xtrabackup: innodb_log_file_size = 5242880 
130307 21:13:46 InnoDB: Using Linux native AIO 
>> log scanned up to (59606124) 
[01] Copying ./ibdata1 
to /tmp/data/2013-03-07_21-13-44/ibdata1.delta 
[01]...done 

130307 21:13:50 innobackupex: Continuing after ibbackup has suspended 
130307 21:13:50 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered -- 
130307 21:13:50 innobackupex: Connected to database with mysql child process (pid=12913) 
>> log scanned up to (59606124) 
130307 21:13:52 innobackupex: Starting to lock all tables... 
>> log scanned up to (59606124) 
>> log scanned up to (59606124) 
130307 21:14:03 innobackupex: All tables locked and flushed to disk 

130307 21:14:03 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI, 
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in 
innobackupex: subdirectories of '/var/lib/mysql' 
innobackupex: Backing up file '/var/lib/mysql/test/test.frm' 
innobackupex: Backing up file '/var/lib/mysql/test/db.opt' 
130307 21:14:03 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files 

innobackupex: Resuming ibbackup 

xtrabackup: The latest check point (for incremental): '59606124' 
>> log scanned up to (59606124) 
xtrabackup: Stopping log copying thread. 
xtrabackup: Transaction log of lsn (59606124) to (59606124) was copied. 
130307 21:14:05 innobackupex: All tables unlocked 
130307 21:14:05 innobackupex: Connection to database server closed 

innobackupex: Backup created in directory '/tmp/data/2013-03-07_21-13-44' 
innobackupex: MySQL binlog position: filename 'mysql-bin.000023', position 107 
130307 21:14:05 innobackupex: completed OK!

其中，--incremental指明是增量备份，--incremental-basedir指定上次完整备份或者增量备份文件的位置。这里的增量备份其实只针对的是InnoDB，对于MyISAM来说，还是完整备份。

在进行增量备份的恢复之前，先关闭数据库，然后删除数据库test

root@client2:/var/lib/mysql# service mysql stop 
mysql stop/waiting 
root@client2:/var/lib/mysql# rm -rf test 
root@client2:/var/lib/mysql# ll 
total 77856 
drwx------ 7 mysql mysql 4096 Mar 7 21:17 ./ 
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 
-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 
drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 
-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:17 ibdata1 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1 
drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 
drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 
-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 
drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

增量备份的恢复

root@client2:/var/lib/mysql# innobackupex -user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --apply-log /tmp/restore/ --incremental-dir=/tmp/data/2013-03-07_21-13-44/ 

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 
and Percona Inc 2009-2012. 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 
prints "completed OK!". 

130307 21:18:20 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/etc/mysql/my.cnf" --prepare --target-dir=/tmp/restore --incremental-dir=/tmp/data/2013-03-07_21-13-44/ 

xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revision id: undefined) 
incremental backup from 59605543 is enabled. 
xtrabackup: cd to /tmp/restore 
xtrabackup: This target seems to be already prepared. 
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59606124) 
xtrabackup: page size for /tmp/data/2013-03-07_21-13-44//ibdata1.delta is 16384 bytes 
Applying /tmp/data/2013-03-07_21-13-44//ibdata1.delta ... 
xtrabackup: Temporary instance for recovery is set as followings. 
xtrabackup: innodb_data_home_dir = ./ 
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend 
xtrabackup: innodb_log_group_home_dir = /tmp/data/2013-03-07_21-13-44/ 
xtrabackup: innodb_log_files_in_group = 1 
xtrabackup: innodb_log_file_size = 2097152 
130307 21:18:20 InnoDB: Using Linux native AIO 
xtrabackup: Starting InnoDB instance for recovery. 
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 
130307 21:18:20 InnoDB: The InnoDB memory heap is disabled 
130307 21:18:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins 
130307 21:18:20 InnoDB: Compressed tables use zlib 1.2.3 
130307 21:18:20 InnoDB: Using Linux native AIO 
130307 21:18:20 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 
130307 21:18:20 InnoDB: Initializing buffer pool, size = 100.0M 
130307 21:18:20 InnoDB: Completed initialization of buffer pool 
130307 21:18:20 InnoDB: highest supported file format is Barracuda. 
InnoDB: ########################################################## 
InnoDB: WARNING! 
InnoDB: The log sequence number in ibdata files is higher 
InnoDB: than the log sequence number in the ib_logfiles! Are you sure 
InnoDB: you are using the right ib_logfiles to start up the database? 
InnoDB: Log sequence number in ib_logfiles is 59606124, log 
InnoDB: sequence numbers stamped to ibdata file headers are between 
InnoDB: 59607052 and 59607052. 
InnoDB: ########################################################## 
InnoDB: The log sequence number in ibdata files does not match 
InnoDB: the log sequence number in the ib_logfiles! 
130307 21:18:20 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 107, file name /var/log/mysql/mysql-bin.000023 
130307 21:18:29 InnoDB: Waiting for the background threads to start 
130307 21:18:30 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59606124 

[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 107, file name /var/log/mysql/mysql-bin.000023 

xtrabackup: starting shutdown with innodb_fast_shutdown = 1 
130307 21:18:30 InnoDB: Starting shutdown... 
130307 21:18:34 InnoDB: Shutdown completed; log sequence number 59607339 
innobackupex: Starting to copy non-InnoDB files in '/tmp/data/2013-03-07_21-13-44/' 
innobackupex: to the full backup directory '/tmp/restore' 
innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/xtrabackup_binlog_info' to '/tmp/restore/xtrabackup_binlog_info' 
innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/test/test.frm' to '/tmp/restore/test/test.frm' 
innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/test/db.opt' to '/tmp/restore/test/db.opt' 
130307 21:18:34 innobackupex: completed OK!

然后再进入恢复的目录/tmp/data

root@client2:/var/lib/mysql# cd /tmp/data 
root@client2:/tmp/data# ll 
total 3276 
drwxr-xr-x 3 root root4096 Mar 7 21:13 ./ 
drwxrwxrwt 14 root root4096 Mar 7 21:18 ../ 
drwxr-xr-x 3 root root4096 Mar 7 21:18 2013-03-07_21-13-44/ 
-rw-r--r-- 1 root root3780 Mar 7 21:02 test-201303072101.log 
-rw-r--r-- 1 root root 3336909 Mar 7 21:02 test-201303072101.tar.gz 
root@client2:/tmp/data# cd 2013-03-07_21-13-44/ 
root@client2:/tmp/data/2013-03-07_21-13-44# ll 
total 2288 
drwxr-xr-x 3 root root4096 Mar 7 21:18 ./ 
drwxr-xr-x 3 root root4096 Mar 7 21:13 ../ 
-rw-r--r-- 1 root root 260 Mar 7 21:13 backup-my.cnf 
-rw-r--r-- 1 root root 212992 Mar 7 21:13 ibdata1.delta 
-rw-r--r-- 1 root root 18 Mar 7 21:13 ibdata1.meta 
drwxr-xr-x 2 root root4096 Mar 7 21:14 test/ 
-rw-r--r-- 1 root root 13 Mar 7 21:14 xtrabackup_binary 
-rw-r--r-- 1 root root 23 Mar 7 21:14 xtrabackup_binlog_info 
-rw-r--r-- 1 root root 84 Mar 7 21:14 xtrabackup_checkpoints 
-rw-r--r-- 1 root root 2097152 Mar 7 21:18 xtrabackup_logfile

跟全部备份一样，把test恢复到/var/lib/mysql里

root@client2:/tmp/data/2013-03-07_21-13-44# rsync -avz test ib* /var/lib/mysql/ 
sending incremental file list 
test/ 
test/db.opt 
test/test.frm 

sent 381 bytes received 54 bytes 870.00 bytes/sec 
total size is 8621 speedup is 19.82 
root@client2:/tmp/data/2013-03-07_21-13-44# cd /var/lib/mysql/ 
root@client2:/var/lib/mysql# ll 
total 77860 
drwx------ 8 mysql mysql 4096 Mar 7 21:19 ./ 
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 
-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 
drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 
-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:17 ibdata1 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1 
drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 
drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 
-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 
drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 
drwxr-xr-x 2 root root 4096 Mar 7 21:14 test/ 
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

然后修改用户与组

root@client2:/var/lib/mysql# chown -R mysql:mysql test/ 
root@client2:/var/lib/mysql# ll 
total 77860 
drwx------ 8 mysql mysql 4096 Mar 7 21:19 ./ 
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../ 
-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag 
drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/ 
-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:17 ibdata1 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:17 ib_logfile0 
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:11 ib_logfile1 
drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/ 
drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/ 
-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info 
drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/ 
drwxr-xr-x 2 mysql mysql 4096 Mar 7 21:14 test/ 
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/

然后启动msyql，并查看test数据库里test表的内容

root@client2:/var/lib/mysql# service mysql start 
mysql start/running, process 13109 
root@client2:/var/lib/mysql# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 36 
Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu) 

Copyright (c) 2000, 2012, 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> 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> select * from test; 
+------+ 
| id | 
+------+ 
|1 | 
|2 | 
|3 | 
|4 | 
|5 | 
| 11 | 
| 12 | 
| 13 | 
| 14 | 
| 15 | 
+------+ 
10 rows in set (0.00 sec)

可以看到增量备份已经恢复完成。

 下面的链接