xtrabackup备份与恢复数据库

转载 2013年12月05日 13:55:28

之前我在维护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作为基础进行研究描述。www.it165.net
下面介绍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数据库

01.root@client2:/tmp# mysql -u root -p 
02.Enter password:  
03.Welcome to the MySQL monitor.  Commands end with ; or \g. 
04.Your MySQL connection id is 40 
05.Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu) 
06. 
07.Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
08. 
09.Oracle is a registered trademark of Oracle Corporation and/or its 
10.affiliates. Other names may be trademarks of their respective 
11.owners. 
12. 
13.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
14.mysql> drop database test
15.Query OK, 3 rows affected (0.13 sec) 
16.mysql> create database test
17.Query OK, 1 row affected (0.00 sec) 
18. 
19.mysql> use test
20.Database changed 
21.mysql> create table test (id int); 
22.Query OK, 0 rows affected (0.06 sec) 
23.mysql> insert into test values(1); 
24.Query OK, 1 row affected (0.04 sec) 
25. 
26.mysql> insert into test values(2); 
27.Query OK, 1 row affected (0.01 sec) 
28. 
29.mysql> insert into test values(3); 
30.Query OK, 1 row affected (0.00 sec) 
31. 
32.mysql> insert into test values(4); 
33.Query OK, 1 row affected (0.00 sec) 
34. 
35.mysql> insert into test values(5); 
36.Query OK, 1 row affected (0.01 sec) 
37. 
38.mysql&gtselect * from test
39.+------+ 
40.id   
41.+------+ 
42.|    1 | 
43.|    2 | 
44.|    3 | 
45.|    4 | 
46.|    5 | 
47.+------+ 
48.5 rows in set (0.00 sec) 
49. 
50.mysql> flush privileges; 
51.Query OK, 0 rows affected (0.00 sec) 

(2)然后备份test的整个数据库
使用下面的backup.sh脚本
 

01.root@client2:/tmp# cat backup.sh  
02.#!/bin/bash 
03.user='root'
04.passwd='123456'
05.database=test
06.my_config='/etc/mysql/my.cnf'
07.log=$database-$(date +%Y%m%d%H%M).log 
08.str=$database-$(date +%Y%m%d%H%M).tar.gz 
09.backup_dir='/tmp/data'
10.echo "Start to backup at $(date +%Y%m%d%H%M)" 
11.if [ ! -d "$backup_dir" ];then 
12.mkdir $backup_dir 
13.fi 
14.innobackupex --user=$user --password=$passwd --defaults-file=$my_config --database=$database --stream=tar $backup_dir 2>$backup_dir/$log | gzip1>$backup_dir/$str 
15.if [ $? -eq 0 ];then 
16.echo "Backup is finish! at $(date +%Y%m%d%H%M)" 
17.exit 
18.else 
19.echo "Backup is Fail! at $(date +%Y%m%d%H%M)" 
20.exit 
21.fi 

现在开始运行此脚本
 

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

然后到data里查看结果
 

01.root@client2:/tmp# cd data 
02.root@client2:/tmp/data# ll 
03.total 3272 
04.drwxr-xr-x  2 root root    4096 Mar  7 21:01 ./ 
05.drwxrwxrwt 13 root root    4096 Mar  7 21:02 ../ 
06.-rw-r--r--  1 root root    3780 Mar  7 21:02 test-201303072101.log 
07.-rw-r--r--  1 root root 3336909 Mar  7 21:02 test-201303072101.tar.gz 
08.root@client2:/tmp/data# cat test-201303072101.log  
09. 
10.InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 
11.and Percona Inc 2009-2012.  All Rights Reserved. 
12. 
13.This software is published under 
14.the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 
15. 
16.130307 21:01:39  innobackupex: Starting mysql with options:  --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 
17.130307 21:01:39  innobackupex: Connected to database with mysql child process (pid=12441) 
18.130307 21:01:45  innobackupex: Connection to database server closed 
19.IMPORTANT: Please check that the backup run completes successfully. 
20.At the end of a successful backup run innobackupex 
21.prints "completed OK!"
22. 
23.innobackupex: Using mysql  Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2 
24.innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
25. 
26.innobackupex: Created backup directory /tmp/data 
27.130307 21:01:45  innobackupex: Starting mysql with options:  --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 
28.130307 21:01:45  innobackupex: Connected to database with mysql child process (pid=12471) 
29.130307 21:01:47  innobackupex: Connection to database server closed 
30. 
31.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 
32.innobackupex: Waiting for ibbackup (pid=12478) to suspend 
33.innobackupex: Suspend file '/tmp/xtrabackup_suspended' 
34. 
35.xtrabackup: suspend-at-end is enabled. 
36.xtrabackup: uses posix_fadvise(). 
37.xtrabackup: cd to /var/lib/mysql 
38.xtrabackup: Target instance is assumed as followings. 
39.xtrabackup:   innodb_data_home_dir = ./ 
40.xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend 
41.xtrabackup:   innodb_log_group_home_dir = ./ 
42.xtrabackup:   innodb_log_files_in_group = 2
43.xtrabackup:   innodb_log_file_size = 5242880
44.130307 21:01:47 InnoDB: Using Linux native AIO 
45.xtrabackup: Stream mode. 
46.>> log scanned up to (59605543) 
47. 
48.130307 21:01:49  innobackupex: Continuing after ibbackup has suspended 
49. 
50.innobackupex: Starting to backup InnoDB tables and indexes 
51.innobackupex: from original InnoDB data directory '/var/lib/mysql' 
52.innobackupex: Backing up as tar stream 'ibdata1' 
53.130307 21:01:52  innobackupex: Starting mysql with options:  --defaults-file='/etc/mysql/my.cnf' --password=xxxxxxxx --user='root' --unbuffered -- 
54.130307 21:01:52  innobackupex: Connected to database with mysql child process (pid=12494) 
55.>> log scanned up to (59605543) 
56.130307 21:01:54  innobackupex: Starting to lock all tables... 
57.>> log scanned up to (59605543) 
58.>> log scanned up to (59605543) 
59.130307 21:02:04  innobackupex: All tables locked and flushed to disk 
60. 
61.130307 21:02:04  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI, 
62.innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in 
63.innobackupex: subdirectories of '/var/lib/mysql' 
64.innobackupex: Backing up file '/var/lib/mysql/test/test.frm' 
65.innobackupex: Backing up file '/var/lib/mysql/test/db.opt' 
66.130307 21:02:04  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files 
67. 
68.innobackupex: Resuming ibbackup 
69. 
70.xtrabackup: The latest check point (for incremental): '59605543' 
71.>> log scanned up to (59605543) 
72.xtrabackup: Transaction log of lsn (59605543) to (59605543) was copied. 
73.130307 21:02:06  innobackupex: All tables unlocked 
74.130307 21:02:06  innobackupex: Connection to database server closed 
75. 
76.innobackupex: Backup created in directory '/tmp/data' 
77.innobackupex: MySQL binlog position: filename 'mysql-bin.000022', position 107 
78.innobackupex: You must use -i (--ignore-zeros) option for extraction of the tarstream. 
79.130307 21:02:06  innobackupex: completed OK! 
可以看到备份完成了
(3)恢复数据库
先关闭mysql服务,然后再删除test数据库文件

01.root@client2:/tmp/data# service mysql stop 
02.mysql stop/waiting 
03.root@client2:/tmp/data# cd /var/lib/mysql/ 
04.root@client2:/var/lib/mysql# ll 
05.total 77860 
06.drwx------  8 mysql mysql     4096 Mar  7 20:59 ./ 
07.drwxr-xr-x 38 root  root      4096 Mar  7 19:52 ../ 
08.-rw-r--r--  1 root  root         0 Jan  5 14:22 debian-5.5.flag 
09.drwx------  2 mysql mysql     4096 Feb 11 17:39 django/ 
10.-rw-rw----  1 mysql mysql 69206016 Mar  7 21:02 ibdata1 
11.-rw-rw----  1 mysql mysql  5242880 Mar  7 21:02 ib_logfile0 
12.-rw-rw----  1 mysql mysql  5242880 Mar  7 21:01 ib_logfile1 
13.drwx------  2 mysql mysql     4096 Jan  5 22:55 monitor/ 
14.drwx------  2 mysql root      4096 Jan  5 14:22 mysql/ 
15.-rw-rw----  1 root  root         6 Jan  5 14:22 mysql_upgrade_info 
16.drwx------  2 mysql mysql     4096 Jan  5 14:22 performance_schema/ 
17.drwx------  2 mysql mysql     4096 Mar  7 21:00 test
18.drwxr-xr-x  2 mysql mysql     4096 Mar  7 19:58 xtrbackup/ 
19.root@client2:/var/lib/mysql# rm -rf test 
20.root@client2:/var/lib/mysql# ll 
21.total 77856 
22.drwx------  7 mysql mysql     4096 Mar  7 21:03 ./ 
23.drwxr-xr-x 38 root  root      4096 Mar  7 19:52 ../ 
24.-rw-r--r--  1 root  root         0 Jan  5 14:22 debian-5.5.flag 
25.drwx------  2 mysql mysql     4096 Feb 11 17:39 django/ 
26.-rw-rw----  1 mysql mysql 69206016 Mar  7 21:02 ibdata1 
27.-rw-rw----  1 mysql mysql  5242880 Mar  7 21:02 ib_logfile0 
28.-rw-rw----  1 mysql mysql  5242880 Mar  7 21:01 ib_logfile1 
29.drwx------  2 mysql mysql     4096 Jan  5 22:55 monitor/ 
30.drwx------  2 mysql root      4096 Jan  5 14:22 mysql/ 
31.-rw-rw----  1 root  root         6 Jan  5 14:22 mysql_upgrade_info 
32.drwx------  2 mysql mysql     4096 Jan  5 14:22 performance_schema/ 
33.drwxr-xr-x  2 mysql mysql     4096 Mar  7 19:58 xtrbackup/ 

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

01.root@client2:cd /tmp/data 
02.root@client2:/tmp/data# tar -izxvf test-201303072101.tar.gz -C /tmp/restore/ 
03.backup-my.cnf 
04.ibdata1 
05.xtrabackup_binlog_info 
06.test/test.frm 
07.test/db.opt 
08.xtrabackup_logfile 
09.xtrabackup_checkpoints 
10.xtrabackup_binary 

注意:这里tar解包必须使用-i参数,否则解压出来的文件只有一个backup-my.cnf
查看一下restore的内容
 

01.root@client2:/tmp/data# cd /tmp/restore/ 
02.root@client2:/tmp/restore# ll 
03.total 67616 
04.drwxr-xr-x  3 root  root      4096 Mar  7 21:03 ./ 
05.drwxrwxrwt 14 root  root      4096 Mar  7 21:03 ../ 
06.-rw-r--r--  1 root  root       260 Mar  7 21:01 backup-my.cnf 
07.-rw-rw----  1 mysql mysql 69206016 Mar  7 21:01 ibdata1 
08.drwxr-xr-x  2 root  root      4096 Mar  7 21:03 test
09.-rw-r--r--  1 root  root        13 Mar  7 21:02 xtrabackup_binary 
10.-rw-r--r--  1 root  root        23 Mar  7 21:02 xtrabackup_binlog_info 
11.-rw-r--r--  1 root  root        79 Mar  7 21:02 xtrabackup_checkpoints 
12.-rw-r--r--  1 root  root      2560 Mar  7 21:02 xtrabackup_logfile 

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

 

001.root@client2:/tmp/restore# innobackupex --user=root --password=123456 --apply-log /tmp/restore/ 
002. 
003.InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 
004.and Percona Inc 2009-2012.  All Rights Reserved. 
005. 
006.This software is published under 
007.the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 
008. 
009.IMPORTANT: Please check that the apply-log run completes successfully. 
010.At the end of a successful apply-log run innobackupex 
011.prints "completed OK!"
012. 
013. 
014. 
015.130307 21:04:18  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/tmp/restore/backup-my.cnf" --prepare --target-dir=/tmp/restore 
016. 
017.xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revisionid: undefined) 
018.xtrabackup: cd to /tmp/restore 
019.xtrabackup: This target seems to be not prepared yet. 
020.xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59605543) 
021.xtrabackup: Temporary instance for recovery is set as followings. 
022.xtrabackup:   innodb_data_home_dir = ./ 
023.xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend 
024.xtrabackup:   innodb_log_group_home_dir = ./ 
025.xtrabackup:   innodb_log_files_in_group = 1
026.xtrabackup:   innodb_log_file_size = 2097152
027.130307 21:04:19 InnoDB: Using Linux native AIO 
028.xtrabackup: Starting InnoDB instance for recovery. 
029.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 
030.130307 21:04:19 InnoDB: The InnoDB memory heap is disabled 
031.130307 21:04:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins 
032.130307 21:04:19 InnoDB: Compressed tables use zlib 1.2.3 
033.130307 21:04:19 InnoDB: Using Linux native AIO 
034.130307 21:04:19 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 
035.130307 21:04:19 InnoDB: Initializing buffer pool, size = 100.0M 
036.130307 21:04:19 InnoDB: Completed initialization of buffer pool 
037.130307 21:04:19 InnoDB: highest supported file format is Barracuda. 
038.InnoDB: The log sequence number in ibdata files does not match 
039.InnoDB: the log sequence number in the ib_logfiles! 
040.130307 21:04:19  InnoDB: Database was not shut down normally! 
041.InnoDB: Starting crash recovery. 
042.InnoDB: Reading tablespace information from the .ibd files... 
043.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022 
044.130307 21:04:20  InnoDB: Waiting for the background threads to start 
045.130307 21:04:21 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59605543 
046. 
047.[notice (again)] 
048.If you use binary log and don't use any hack of group commit, 
049.the binary log position seems to be: 
050.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022 
051. 
052.xtrabackup: starting shutdown with innodb_fast_shutdown = 1
053.130307 21:04:21  InnoDB: Starting shutdown... 
054.130307 21:04:25  InnoDB: Shutdown completed; log sequence number 59606758 
055. 
056.130307 21:04:25  innobackupex: Restarting xtrabackup with command: xtrabackup_55  --defaults-file="/tmp/restore/backup-my.cnf" --prepare --target-dir=/tmp/restore 
057.for creating ib_logfile* 
058. 
059.xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revisionid: undefined) 
060.xtrabackup: cd to /tmp/restore 
061.xtrabackup: This target seems to be already prepared. 
062.xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'. 
063.xtrabackup: Temporary instance for recovery is set as followings. 
064.xtrabackup:   innodb_data_home_dir = ./ 
065.xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend 
066.xtrabackup:   innodb_log_group_home_dir = ./ 
067.xtrabackup:   innodb_log_files_in_group = 2
068.xtrabackup:   innodb_log_file_size = 5242880
069.130307 21:04:25 InnoDB: Using Linux native AIO 
070.xtrabackup: Starting InnoDB instance for recovery. 
071.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 
072.130307 21:04:25 InnoDB: The InnoDB memory heap is disabled 
073.130307 21:04:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins 
074.130307 21:04:25 InnoDB: Compressed tables use zlib 1.2.3 
075.130307 21:04:25 InnoDB: Using Linux native AIO 
076.130307 21:04:25 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 
077.130307 21:04:25 InnoDB: Initializing buffer pool, size = 100.0M 
078.130307 21:04:25 InnoDB: Completed initialization of buffer pool 
079.130307 21:04:25  InnoDB: Log file ./ib_logfile0 did not exist: new to be created 
080.InnoDB: Setting log file ./ib_logfile0 size to 5 MB 
081.InnoDB: Database physically writes the file full: wait... 
082.130307 21:04:25  InnoDB: Log file ./ib_logfile1 did not exist: new to be created 
083.InnoDB: Setting log file ./ib_logfile1 size to 5 MB 
084.InnoDB: Database physically writes the file full: wait... 
085.130307 21:04:25 InnoDB: highest supported file format is Barracuda. 
086.InnoDB: The log sequence number in ibdata files does not match 
087.InnoDB: the log sequence number in the ib_logfiles! 
088.130307 21:04:25  InnoDB: Database was not shut down normally! 
089.InnoDB: Starting crash recovery. 
090.InnoDB: Reading tablespace information from the .ibd files... 
091.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022 
092.130307 21:04:26  InnoDB: Waiting for the background threads to start 
093.130307 21:04:27 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59607052 
094. 
095.[notice (again)] 
096.If you use binary log and don't use any hack of group commit, 
097.the binary log position seems to be: 
098.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000022 
099. 
100.xtrabackup: starting shutdown with innodb_fast_shutdown = 1
101.130307 21:04:27  InnoDB: Starting shutdown... 
102.130307 21:04:31  InnoDB: Shutdown completed; log sequence number 59607052 
103.130307 21:04:31  innobackupex: completed OK! 

这里的--apply-log指明是将日志应用到数据文件上,完成之后将备份文件中的数据恢复到数据库中:
然后再查看一下当前目录内容
 

01.root@client2:/tmp/restore# ll 
02.total 79904 
03.drwxr-xr-x  3 root  root      4096 Mar  7 21:04 ./ 
04.drwxrwxrwt 14 root  root      4096 Mar  7 21:04 ../ 
05.-rw-r--r--  1 root  root       260 Mar  7 21:01 backup-my.cnf 
06.-rw-rw----  1 mysql mysql 69206016 Mar  7 21:04 ibdata1 
07.-rw-r--r--  1 root  root   5242880 Mar  7 21:04 ib_logfile0 
08.-rw-r--r--  1 root  root   5242880 Mar  7 21:04 ib_logfile1 
09.drwxr-xr-x  2 root  root      4096 Mar  7 21:03 test
10.-rw-r--r--  1 root  root        13 Mar  7 21:02 xtrabackup_binary 
11.-rw-r--r--  1 root  root        23 Mar  7 21:02 xtrabackup_binlog_info 
12.-rw-r--r--  1 root  root        36 Mar  7 21:04 xtrabackup_binlog_pos_innodb 
13.-rw-r--r--  1 root  root        79 Mar  7 21:04 xtrabackup_checkpoints 
14.-rw-r--r--  1 root  root   2097152 Mar  7 21:04 xtrabackup_logfile 
15.然后把test目录复制到/var/lib/mysql目录 
16.root@client2:/tmp/restore# rsync -avz test ib* /var/lib/mysql/ 
17.sending incremental file list 
18.test
19.test/db.opt 
20.test/test.frm 
21. 
22.sent 381 bytes  received 54 bytes  870.00 bytes/sec 
23.total size is 8621  speedup is 19.82 
24.root@client2:/tmp/restore# cd /var/lib/mysql/ 
25.root@client2:/var/lib/mysql# ll 
26.total 77860 
27.drwx------  8 mysql mysql     4096 Mar  7 21:06 ./ 
28.drwxr-xr-x 38 root  root      4096 Mar  7 19:52 ../ 
29.-rw-r--r--  1 root  root         0 Jan  5 14:22 debian-5.5.flag 
30.drwx------  2 mysql mysql     4096 Feb 11 17:39 django/ 
31.-rw-rw----  1 mysql mysql 69206016 Mar  7 21:02 ibdata1 
32.-rw-rw----  1 mysql mysql  5242880 Mar  7 21:02 ib_logfile0 
33.-rw-rw----  1 mysql mysql  5242880 Mar  7 21:01 ib_logfile1 
34.drwx------  2 mysql mysql     4096 Jan  5 22:55 monitor/ 
35.drwx------  2 mysql root      4096 Jan  5 14:22 mysql/ 
36.-rw-rw----  1 root  root         6 Jan  5 14:22 mysql_upgrade_info 
37.drwx------  2 mysql mysql     4096 Jan  5 14:22 performance_schema/ 
38.drwxr-xr-x  2 root  root      4096 Mar  7 21:03 test
39.drwxr-xr-x  2 mysql mysql     4096 Mar  7 19:58 xtrbackup/ 

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

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

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

01.root@client2:/var/lib/mysql# service  mysql start 
02.mysql start/running, process 12730 
03.root@client2:/var/lib/mysql# mysql -u root -p 
04.Enter password:  
05.Welcome to the MySQL monitor.  Commands end with ; or \g. 
06.Your MySQL connection id is 36 
07.Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu) 
08. 
09.Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
10. 
11.Oracle is a registered trademark of Oracle Corporation and/or its 
12.affiliates. Other names may be trademarks of their respective 
13.owners. 
14. 
15.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
16. 
17.mysql> use test
18.Reading table information for completion of table and column names 
19.You can turn off this feature to get a quicker startup with -A 
20. 
21.Database changed 
22.mysql&gtselect * from test
23.+------+ 
24.id   
25.+------+ 
26.|    1 | 
27.|    2 | 
28.|    3 | 
29.|    4 | 
30.|    5 | 
31.+------+ 
32.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。
innobackupexcopy-back was run. With this bug fix, innobackupexcopy-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后直接复制数据目录到数据库的位置上吧。
 
三、对数据库的增量备份与恢复
为了进行增量备份,先对数据库添加一些数据

01.mysql> insert into test values(11); 
02.Query OK, 1 row affected (0.10 sec) 
03. 
04.mysql> insert into test values(12); 
05.Query OK, 1 row affected (0.05 sec) 
06. 
07.mysql> insert into test values(13); 
08.Query OK, 1 row affected (0.00 sec) 
09. 
10.mysql> insert into test values(14); 
11.Query OK, 1 row affected (0.00 sec) 
12. 
13.mysql> insert into test values(15); 
14.Query OK, 1 row affected (0.00 sec) 
15. 
16.mysql> flush privileges; 
17.Query OK, 0 rows affected (0.01 sec) 
18. 
19.mysql&gtselect * from test
20.+------+ 
21.id   
22.+------+ 
23.|    1 | 
24.|    2 | 
25.|    3 | 
26.|    4 | 
27.|    5 | 
28.|   11 | 
29.|   12 | 
30.|   13 | 
31.|   14 | 
32.|   15 | 
33.+------+ 
34.10 rows in set (0.00 sec) 
然后进行增量的备份

01.root@client2:/var/lib/mysql# innobackupex --user=root --password=123456 --database=test --incremental --incremental-basedir=/tmp/restore/ /tmp/data 
02. 
03.InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 
04.and Percona Inc 2009-2012.  All Rights Reserved. 
05. 
06.This software is published under 
07.the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 
08. 
09.130307 21:13:38  innobackupex: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered -- 
10.130307 21:13:38  innobackupex: Connected to database with mysql child process (pid=12864) 
11.130307 21:13:44  innobackupex: Connection to database server closed 
12.IMPORTANT: Please check that the backup run completes successfully. 
13.At the end of a successful backup run innobackupex 
14.prints "completed OK!"
15. 
16.innobackupex: Using mysql  Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2 
17.innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
18. 
19.innobackupex: Created backup directory /tmp/data/2013-03-07_21-13-44 
20.130307 21:13:44  innobackupex: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered -- 
21.130307 21:13:44  innobackupex: Connected to database with mysql child process (pid=12891) 
22.130307 21:13:46  innobackupex: Connection to database server closed 
23. 
24.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/'
25.innobackupex: Waiting for ibbackup (pid=12898) to suspend 
26.innobackupex: Suspend file '/tmp/data/2013-03-07_21-13-44/xtrabackup_suspended' 
27. 
28.xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revisionid: undefined) 
29.incremental backup from 59605543 is enabled. 
30.xtrabackup: uses posix_fadvise(). 
31.xtrabackup: cd to /var/lib/mysql 
32.xtrabackup: Target instance is assumed as followings. 
33.xtrabackup:   innodb_data_home_dir = ./ 
34.xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend 
35.xtrabackup:   innodb_log_group_home_dir = ./ 
36.xtrabackup:   innodb_log_files_in_group = 2
37.xtrabackup:   innodb_log_file_size = 5242880
38.130307 21:13:46 InnoDB: Using Linux native AIO 
39.>> log scanned up to (59606124) 
40.[01] Copying ./ibdata1  
41.to /tmp/data/2013-03-07_21-13-44/ibdata1.delta 
42.[01]        ...done 
43. 
44.130307 21:13:50  innobackupex: Continuing after ibbackup has suspended 
45.130307 21:13:50  innobackupex: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered -- 
46.130307 21:13:50  innobackupex: Connected to database with mysql child process (pid=12913) 
47.>> log scanned up to (59606124) 
48.130307 21:13:52  innobackupex: Starting to lock all tables... 
49.>> log scanned up to (59606124) 
50.>> log scanned up to (59606124) 
51.130307 21:14:03  innobackupex: All tables locked and flushed to disk 
52. 
53.130307 21:14:03  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI, 
54.innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in 
55.innobackupex: subdirectories of '/var/lib/mysql' 
56.innobackupex: Backing up file '/var/lib/mysql/test/test.frm' 
57.innobackupex: Backing up file '/var/lib/mysql/test/db.opt' 
58.130307 21:14:03  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files 
59. 
60.innobackupex: Resuming ibbackup 
61. 
62.xtrabackup: The latest check point (for incremental): '59606124' 
63.>> log scanned up to (59606124) 
64.xtrabackup: Stopping log copying thread. 
65.xtrabackup: Transaction log of lsn (59606124) to (59606124) was copied. 
66.130307 21:14:05  innobackupex: All tables unlocked 
67.130307 21:14:05  innobackupex: Connection to database server closed 
68. 
69.innobackupex: Backup created in directory '/tmp/data/2013-03-07_21-13-44' 
70.innobackupex: MySQL binlog position: filename 'mysql-bin.000023', position 107 
71.130307 21:14:05  innobackupex: completed OK! 
其中,--incremental指明是增量备份,--incremental-basedir指定上次完整备份或者增量备份文件的位置。这里的增量备份其实只针对的是InnoDB,对于MyISAM来说,还是完整备份。
在进行增量备份的恢复之前,先关闭数据库,然后删除数据库test

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

增量备份的恢复
 

01.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/ 
02. 
03.InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy 
04.and Percona Inc 2009-2012.  All Rights Reserved. 
05. 
06.This software is published under 
07.the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. 
08. 
09.IMPORTANT: Please check that the apply-log run completes successfully. 
10.At the end of a successful apply-log run innobackupex 
11.prints "completed OK!"
12. 
13. 
14. 
15.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/ 
16. 
17.xtrabackup_55 version 1.6.7 for Percona Server 5.5.16 Linux (x86_64) (revisionid: undefined) 
18.incremental backup from 59605543 is enabled. 
19.xtrabackup: cd to /tmp/restore 
20.xtrabackup: This target seems to be already prepared. 
21.xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(59606124) 
22.xtrabackup: page size for /tmp/data/2013-03-07_21-13-44//ibdata1.delta is 16384 bytes 
23.Applying /tmp/data/2013-03-07_21-13-44//ibdata1.delta ... 
24.xtrabackup: Temporary instance for recovery is set as followings. 
25.xtrabackup:   innodb_data_home_dir = ./ 
26.xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend 
27.xtrabackup:   innodb_log_group_home_dir = /tmp/data/2013-03-07_21-13-44/ 
28.xtrabackup:   innodb_log_files_in_group = 1
29.xtrabackup:   innodb_log_file_size = 2097152
30.130307 21:18:20 InnoDB: Using Linux native AIO 
31.xtrabackup: Starting InnoDB instance for recovery. 
32.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 
33.130307 21:18:20 InnoDB: The InnoDB memory heap is disabled 
34.130307 21:18:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins 
35.130307 21:18:20 InnoDB: Compressed tables use zlib 1.2.3 
36.130307 21:18:20 InnoDB: Using Linux native AIO 
37.130307 21:18:20 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead 
38.130307 21:18:20 InnoDB: Initializing buffer pool, size = 100.0M 
39.130307 21:18:20 InnoDB: Completed initialization of buffer pool 
40.130307 21:18:20 InnoDB: highest supported file format is Barracuda. 
41.InnoDB: ########################################################## 
42.InnoDB:                          WARNING! 
43.InnoDB: The log sequence number in ibdata files is higher 
44.InnoDB: than the log sequence number in the ib_logfiles! Are you sure 
45.InnoDB: you are using the right ib_logfiles to start up the database? 
46.InnoDB: Log sequence number in ib_logfiles is 59606124, log 
47.InnoDB: sequence numbers stamped to ibdata file headers are between 
48.InnoDB: 59607052 and 59607052. 
49.InnoDB: ########################################################## 
50.InnoDB: The log sequence number in ibdata files does not match 
51.InnoDB: the log sequence number in the ib_logfiles! 
52.130307 21:18:20  InnoDB: Database was not shut down normally! 
53.InnoDB: Starting crash recovery. 
54.InnoDB: Reading tablespace information from the .ibd files... 
55.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000023 
56.130307 21:18:29  InnoDB: Waiting for the background threads to start 
57.130307 21:18:30 Percona XtraDB (http://www.percona.com) 1.1.8-20.1 started; log sequence number 59606124 
58. 
59.[notice (again)] 
60.If you use binary log and don't use any hack of group commit, 
61.the binary log position seems to be: 
62.InnoDB: Last MySQL binlog file position 0 107, file name /var/log/mysql/mysql-bin.000023 
63. 
64.xtrabackup: starting shutdown with innodb_fast_shutdown = 1
65.130307 21:18:30  InnoDB: Starting shutdown... 
66.130307 21:18:34  InnoDB: Shutdown completed; log sequence number 59607339 
67.innobackupex: Starting to copy non-InnoDB files in '/tmp/data/2013-03-07_21-13-44/' 
68.innobackupex: to the full backup directory '/tmp/restore' 
69.innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/xtrabackup_binlog_info' to '/tmp/restore/xtrabackup_binlog_info' 
70.innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/test/test.frm' to '/tmp/restore/test/test.frm' 
71.innobackupex: Copying '/tmp/data/2013-03-07_21-13-44/test/db.opt' to '/tmp/restore/test/db.opt' 
72.130307 21:18:34  innobackupex: completed OK! 

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

01.root@client2:/var/lib/mysql# cd /tmp/data 
02.root@client2:/tmp/data# ll 
03.total 3276 
04.drwxr-xr-x  3 root root    4096 Mar  7 21:13 ./ 
05.drwxrwxrwt 14 root root    4096 Mar  7 21:18 ../ 
06.drwxr-xr-x  3 root root    4096 Mar  7 21:18 2013-03-07_21-13-44/ 
07.-rw-r--r--  1 root root    3780 Mar  7 21:02 test-201303072101.log 
08.-rw-r--r--  1 root root 3336909 Mar  7 21:02 test-201303072101.tar.gz 
09.root@client2:/tmp/data# cd 2013-03-07_21-13-44/ 
10.root@client2:/tmp/data/2013-03-07_21-13-44# ll 
11.total 2288 
12.drwxr-xr-x 3 root root    4096 Mar  7 21:18 ./ 
13.drwxr-xr-x 3 root root    4096 Mar  7 21:13 ../ 
14.-rw-r--r-- 1 root root     260 Mar  7 21:13 backup-my.cnf 
15.-rw-r--r-- 1 root root  212992 Mar  7 21:13 ibdata1.delta 
16.-rw-r--r-- 1 root root      18 Mar  7 21:13 ibdata1.meta 
17.drwxr-xr-x 2 root root    4096 Mar  7 21:14 test
18.-rw-r--r-- 1 root root      13 Mar  7 21:14 xtrabackup_binary 
19.-rw-r--r-- 1 root root      23 Mar  7 21:14 xtrabackup_binlog_info 
20.-rw-r--r-- 1 root root      84 Mar  7 21:14 xtrabackup_checkpoints 
21.-rw-r--r-- 1 root root 2097152 Mar  7 21:18 xtrabackup_logfile 

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

01.root@client2:/tmp/data/2013-03-07_21-13-44# rsync -avz test ib* /var/lib/mysql/ 
02.sending incremental file list 
03.test
04.test/db.opt 
05.test/test.frm 
06. 
07.sent 381 bytes  received 54 bytes  870.00 bytes/sec 
08.total size is 8621  speedup is 19.82 
09.root@client2:/tmp/data/2013-03-07_21-13-44# cd /var/lib/mysql/ 
10.root@client2:/var/lib/mysql# ll 
11.total 77860 
12.drwx------  8 mysql mysql     4096 Mar  7 21:19 ./ 
13.drwxr-xr-x 38 root  root      4096 Mar  7 19:52 ../ 
14.-rw-r--r--  1 root  root         0 Jan  5 14:22 debian-5.5.flag 
15.drwx------  2 mysql mysql     4096 Feb 11 17:39 django/ 
16.-rw-rw----  1 mysql mysql 69206016 Mar  7 21:17 ibdata1 
17.-rw-rw----  1 mysql mysql  5242880 Mar  7 21:17 ib_logfile0 
18.-rw-rw----  1 mysql mysql  5242880 Mar  7 21:11 ib_logfile1 
19.drwx------  2 mysql mysql     4096 Jan  5 22:55 monitor/ 
20.drwx------  2 mysql root      4096 Jan  5 14:22 mysql/ 
21.-rw-rw----  1 root  root         6 Jan  5 14:22 mysql_upgrade_info 
22.drwx------  2 mysql mysql     4096 Jan  5 14:22 performance_schema/ 
23.