xtrabackup进行mysql数据库备份、还原
xtrabackup简介
前面介绍mysqldump备份方式是采用逻辑备份，其最大的缺陷就是备份和恢复速度都慢，对于一个小于50G的数据库而言，这个速度还是能接受的，但如果数据库非常大，那再使用mysqldump备份就不太适合了。
这时就需要一种好用又高效的工具，xtrabackup就是其中一款，号称免费版的InnoDB HotBackup。
Xtrabackup实现是物理备份，而且是物理热备
目前主流的有两个工具可以实现物理热备：ibbackup和xtrabackup；ibbackup是商业软件，需要授权，非常昂贵。而xtrabackup功能比ibbackup还要强大，但却是开源的。因此我们这里就来介绍xtrabackup的使用。
Xtrabackup提供了两种命令行工具：
xtrabackup：专用于备份InnoDB和XtraDB引擎的数据；
innobackupex：这是一个perl脚本，在执行过程中会调用xtrabackup命令，这样用该命令即可以实现备份InnoDB，也可以备份MyISAM引擎的对象。
Xtrabackup是由percona提供的mysql数据库备份工具，特点：
(1)备份过程快速、可靠；
(2)备份过程不会打断正在执行的事务；
(3)能够基于压缩等功能节约磁盘空间和流量；
(4)自动实现备份检验；
(5)还原速度快。
官方链接地址：http://www.percona.com/software/percona-xtrabackup；可以下载源码编译安装，也可以下载适合的RPM包或使用yum进行安装或者下载二进制源码包。
xtrabackup安装
[root@localhost ~]# tar xf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz
[root@localhost ~]# cd percona-xtrabackup-2.4.4-Linux-x86_64
[root@localhost percona-xtrabackup-2.4.4-Linux-x86_64]# cp bin/* /usr/bin/
[root@localhost percona-xtrabackup-2.4.4-Linux-x86_64]# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5
[root@localhost ~]# rpm -ivh percona-toolkit-2.2.19-1.noarch.rpm
warning: percona-toolkit-2.2.19-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]
1:percona-toolkit ########################################### [100%]
源码编译数据库
[roo[root@localhost ~]# yum -y install ncurses-devel
[root@localhost ~]# tar xf cmake-2.8.6.tar.gz
[root@localhost ~]# cd cmake-2.8.6
[root@localhost cmake-2.8.6]# ./configure && gmake && gmake install
[root@localhost cmake-2.8.6]# cd
[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd -M -s /sbin/nologin mysql -g mysql
[root@localhost ~]# tar xf mysql-5.5.22.tar.gz -C /usr/src/
[root@localhost ~]# cd /usr/src/mysql-5.5.22/
[root@localhost mysql-5.5.22]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all && make && make instal
[root@localhost mysql-5.5.22]# chown -R mysql:mysql /usr/local/mysql
[root@localhost mysql-5.5.22]# rm -rf /etc/my.cnf
[root@localhost mysql-5.5.22]# cp support-files/my-medium.cnf /etc/my.cnf
[root@localhost mysql-5.5.22]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
[root@localhost mysql-5.5.22]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@localhost mysql-5.5.22]# . /etc/profile
[root@localhost mysql-5.5.22]# cp /usr/src/mysql-5.5.22/support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@localhost mysql-5.5.22]# chmod +x /etc/rc.d/init.d/mysqld
[root@localhost mysql-5.5.22]# chkconfig --add mysqld
[root@localhost mysql-5.5.22]# vim /etc/my.cnf
27 datadir=/usr/local/mysql/data
[root@localhost mysql-5.5.22]# service mysqld start
Starting MySQL... [确定]
[root@localhost mysql-5.5.22]# mysqladmin -uroot password '123456'
xtrabackup完全备份+binlog增量备份
1、完全备份
基本语法:innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP
注:--defaults-file=/etc/my.cnf 指定mysql的配置文件my.cfg，如果指定则必须是第一个参数。
/path/to/BACKUP-DIR/指定备份所存放的目标目录，备份过程会创建一个以当时备份时间命名的目录存放备份文件。
创建备份目录，full为完全备份目录，inc为增量备份目录
[root@localhost ~]# mkdir -p /opt/mysqlbackup/{full,inc}
[root@localhost ~]# innobackupex --user=root --password=123456 /opt/mysqlbackup/full/
MySQL binlog position: filename 'mysql-bin.000004', position '107'
161203 21:03:23 [00] Writing backup-my.cnf
161203 21:03:23 [00] ...done
161203 21:03:23 [00] Writing xtrabackup_info
161203 21:03:23 [00] ...done
xtrabackup: Transaction log of lsn (1595675) to (1595675) was copied.
161203 21:03:24 completed OK!
[root@localhost ~]# ls /opt/mysqlbackup/full
2016-12-03_21-03-06
[root@localhost ~]# ls /opt/mysqlbackup/full/2016-12-03_21-03-06/
backup-my.cnf mysql xtrabackup_binlog_info xtrabackup_info
ibdata1 performance_schema xtrabackup_checkpoints xtrabackup_logfile
各文件说明：
(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；
每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
(3)xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
(4)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；
(5)backup-my.cnf —— 备份命令用到的配置选项信息；
在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据
注意：相关选项说明：
其中，--user指定连接数据库的用户名，--password指定连接数据库的密码，--defaults-file指定数据库的配置文件，innobackupex要从其中获取datadir等信息；--database指定要备份的数据库，这里指定的数据库只对MyISAM表有效，对于InnoDB 数据来说都是全备（所有数据库中的InnoDB数据都进行了备份，不是只备份指定的数据库，恢复时也一样）；/opt/mysqlbackup/full是备份文件的存放位置。
注意：备份数据库的用户需要具有相应权限，如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, 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> create user 'bkpuser'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.06 sec)
mysql> revoke all privileges,grant option from 'bkpuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> grant reload,lock tables,replication client, process on *.* to 'bkpuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
2、增量备份
向mysql添加一个benet库，然后插入数据进行增量备份，对完全备份后的数据库更改进行二进制日志备份。
查看日志位置
[root@localhost ~]# cat /opt/mysqlbackup/full/2016-12-03_21-03-06/xtrabackup_binlog_info
mysql-bin.000004 107
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, 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> create database benet;
Query OK, 1 row affected (0.00 sec)
mysql> use benet
Database changed
mysql> create table tb1 (id int,name char(16) not null);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into tb1 values(1,'zhangsan');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tb1 values(2,'lisi');
Query OK, 1 row affected (0.02 sec)
mysql> select * from tb1;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
+------+----------+
2 rows in set (0.00 sec)
mysql> quit
Bye
增量备份二进制文件
[root@localhost ~]# mysqlbinlog --start-position=107 /usr/local/mysql/data/mysql-bin.000004 > /opt/mysqlbackup/inc/`date +%F`.sql
[root@localhost ~]# ls /opt/mysqlbackup/inc/
2016-12-03.sql
模拟数据库损坏
[root@localhost ~]# mv /usr/local/mysql/data/* /tmp/
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。
innobakupex命令的--apply-log选项可用于实现上述功能。如下面的命令：
[root@localhost ~]# innobackupex --apply-log /opt/mysqlbackup/full/2016-12-03_21-03-06/
.........
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.13 started; log sequence number 1595925
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1595944
161203 21:33:32 completed OK!
[root@localhost ~]# innobackupex --copy-back /opt/mysqlbackup/full/2016-12-03_21-03-06/
...........
161203 21:36:07 [01] ...done
161203 21:36:07 [01] Copying ./performance_schema/events_waits_current.frm to /usr/local/mysql/data/performance_schema/events_waits_current.frm
161203 21:36:07 [01] ...done
161203 21:36:07 completed OK!
[root@localhost ~]# ll /usr/local/mysql/data/
总用量 40972
-rw-r-----. 1 root root 18874368 12月 3 21:36 ibdata1
-rw-r-----. 1 root root 5242880 12月 3 21:36 ib_logfile0
-rw-r-----. 1 root root 5242880 12月 3 21:36 ib_logfile1
-rw-r-----. 1 root root 12582912 12月 3 21:36 ibtmp1
drwxr-x---. 2 root root 4096 12月 3 21:36 mysql
drwxr-x---. 2 root root 4096 12月 3 21:36 performance_schema
-rw-r-----. 1 root root 478 12月 3 21:36 xtrabackup_info
[root@localhost ~]# chown -R mysql:mysql /usr/local/mysql/data/
[root@localhost ~]# killall mysqld
[root@localhost ~]# service mysqld start
Starting MySQL.. [确定]
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, 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 |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)
我们可以看到在完全备份后创建的数据并没有恢复，这时就需要用增量备份来还原
为了避免产生大量的二进制日志，可以把二进制日志关掉
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /opt/mysqlbackup/inc/2016-12-03.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| benet |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use benet
Database changed
mysql> select * from tb1;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
+------+----------+
2 rows in set (0.01 sec)
xtrabackup完全备份+xtrabacup增量备份
前面我们进行增量备份时，使用的还是老方法：备份二进制日志。其实xtrabackup还支持进行增量备份。
先介绍下xtrabackup的备份原理
在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件（transaction log，事务日志）。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。
xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。这就是xtrabackup的备份过程
所以每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础。
xtraBackup基于InnoDB的crash-recovery功能。它会复制innodb的data file，由于不锁表，复制出来的数据是不一致的，在恢复的时候使用crash-recovery，使得数据恢复一致。
当InnoDB启动的时候，它会先去检查data file和transaction log，并且会做二步操作：
1.It applies committed transaction logentries to the data files
2.it performs an undo operation on anytransactions that modified data but did not commit.
所以在prepare过程中，XtraBackup使用复制到的transactions log对备份出来的innodb data file进行crash recovery。
测试环境准备
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table xx(id int,name varchar(20));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into xx values(1,'tom1');
Query OK, 1 row affected (0.02 sec)
mysql> insert into xx values(2,'tom2');
Query OK, 1 row affected (0.03 sec)
mysql> quit
Bye
执行完全备份
[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --user=root --password='123456' --port=3306 --backup --target-dir=/opt/mysqlbackup/full/full_incre_$(date +%Y%m%d_%H%M%s)
..........
161203 22:01:55 [00] Writing backup-my.cnf
161203 22:01:55 [00] ...done
161203 22:01:55 [00] Writing xtrabackup_info
161203 22:01:55 [00] ...done
xtrabackup: Transaction log of lsn (1603986) to (1603986) was copied.
161203 22:01:55 completed OK!
[root@localhost ~]# ls -l /opt/mysqlbackup/full/
总用量 8
drwxr-x---. 4 root root 4096 12月 3 21:33 2016-12-03_21-03-06
drwxr-x---. 6 root root 4096 12月 3 22:01 full_incre_20161203_22011480773711
继续往数据库增加数据，进行第一次增量备份
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, 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
Database changed
mysql> insert into xx values(3,'tom3');
Query OK, 1 row affected (0.03 sec)
mysql> quit
Bye
[root@localhost ~]# xtrabackup --dafaults-file=/etc/my.cnf --user=root --password="123456" --port=3306 --backup --target-dir=/opt/mysqlbackup/inc/incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/full/full_incre_20161203_22011480773711/
........
161203 22:09:54 [00] Writing xtrabackup_info
161203 22:09:54 [00] ...done
xtrabackup: Transaction log of lsn (1605014) to (1605014) was copied.
161203 22:09:54 completed OK!
[root@localhost ~]# ls -l /opt/mysqlbackup/inc/
总用量 8
-rw-r--r--. 1 root root 3041 12月 3 21:24 2016-12-03.sql
drwxr-x---. 6 root root 4096 12月 3 22:09 incre_20161203_220950
注意：这里的增量备份只针对Innodb，而MyISAM仍然是完整备份
向表中再添入数据，进行第二次增量备份
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, 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
Database changed
mysql> insert into xx values(4,'tom4');
Query OK, 1 row affected (0.03 sec)
mysql> quit
Bye
[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --user=root --password="123456" --port=3306 --backup --target-dir=/opt/mysqlbackup/inc/incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/inc/incre_20161203_220950/
.........
161203 22:18:34 [00] ...done
161203 22:18:34 [00] Writing xtrabackup_info
161203 22:18:34 [00] ...done
xtrabackup: Transaction log of lsn (1606042) to (1606042) was copied.
161203 22:18:34 completed OK!
注意:第二次增量备份--incremental--basedir指的是向上一次增量备份文件的位置
[root@localhost ~]# ls -l /opt/mysqlbackup/inc/
总用量 12
-rw-r--r--. 1 root root 3041 12月 3 21:24 2016-12-03.sql
drwxr-x---. 6 root root 4096 12月 3 22:09 incre_20161203_220950
drwxr-x---. 6 root root 4096 12月 3 22:18 incre_20161203_221830
Xtrabackup进行增量恢复
为了验证对比，先删除两个增量备份前表里面的数据
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, 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;
Database changed
mysql> delete from xx where id=3;
Query OK, 1 row affected (0.03 sec)
mysql> quit
Bye
[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password="123456" --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20161203_22011480773711/
[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password="123456" --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20161203_22011480773711/ --incremental-dir=/opt/mysqlbackup/inc/incre_20161203_220950/
[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password="123456" --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20161203_22011480773711/ --incremental-dir=/opt/mysqlbackup/inc/incre_20161203_221830/
[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password="123456" --target-dir=/opt/mysqlbackup/full/full_incre_20161203_22011480773711/
[root@localhost ~]# service mysqld stop
Shutting down MySQL.. [确定]
[root@localhost ~]# cd /opt/mysqlbackup/full/full_incre_20161203_22011480773711/
[root@localhost full_incre_20161203_22011480773711]# rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./ /usr/local/mysql/data/
[root@localhost ~]# chown -R mysql:mysql /usr/local/mysql/data/
[root@localhost ~]# service mysqld start
Starting MySQL... [确定]
[root@localhost ~]# mysql -uroot -p123456 -e "select * from test.xx"
+------+------+
| id | name |
+------+------+
| 1 | tom1 |
| 2 | tom2 |
| 3 | tom3 |
| 4 | tom4 |
+------+------+
innobackupex全库备份+innobackupex增量备份
测试环境准备
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, 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> create database test2;
Query OK, 1 row affected (0.01 sec)
mysql> use test2
Database changed
mysql> create table yy(id int,name varchar(20));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into yy values(1,'kim1');
Query OK, 1 row affected (0.03 sec)
mysql> insert into yy values(2,'kim2');
Query OK, 1 row affected (0.02 sec)
mysql> quit
Bye
先做完全备份
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="123456" /opt/mysqlbackup/full/fulll_incre_$(date +%Y%m%d_%H%M%S) --no-timestamp
........
161203 22:57:11 [00] Writing xtrabackup_info
161203 22:57:11 [00] ...done
xtrabackup: Transaction log of lsn (1609292) to (1609292) was copied.
161203 22:57:12 completed OK!
接下来做第一次增量备份，先进入数据库插入数据
mysql> use test2;
Database changed
mysql> insert into yy values(3,'kim3');
Query OK, 1 row affected (0.02 sec)
[root@localhost ~]# innobackupex --incremental /opt/mysqlbackup/inc/incree_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/full/fulll_incre_20161203_225708/ --user=root --password="123456" --no-timestamp
基于全备和第一次增量备份来做第二次增量备份
mysql> use test2;
Database changed
mysql> insert into yy values(4,'kim4');
Query OK, 1 row affected (0.02 sec)
[root@localhost ~]# innobackupex --incremental /opt/mysqlbackup/inc/increee_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/inc/incree_20161203_230236/ --user=root --password="123456" --no-timestamp
删除增量的数据，进行增量恢复
mysql> use test2;
Database changed
mysql> delete from yy where id=3;
Query OK, 1 row affected (0.03 sec)
[root@localhost ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/fulll_incre_20161203_225708/
[root@localhost ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/fulll_incre_20161203_225708/ --incremental-dir=/opt/mysqlbackup/inc/incree_20161203_230236/
[root@localhost ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/fulll_incre_20161203_225708/ --incremental-dir=/opt/mysqlbackup/inc/increee_20161203_230810/
[root@localhost ~]# service mysqld stop
Shutting down MySQL. [确定]
[root@localhost ~]# mkdir /tmp/mysqlbak
[root@localhost ~]# mv /usr/local/mysql/data/* /tmp/mysqlbak/
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="123456" --copy-back /opt/mysqlbackup/full/fulll_incre_20161203_225708/
启动数据库，查看数据是否恢复
QQ：1061767621
Q群：215481318
