xtrabackup备份、还原

原创

AmunLinux 2017-08-01 12:34:04 ©著作权

©著作权归作者所有：来自51CTO博客作者AmunLinux的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、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@localhostpercona-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-HiResperl-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@localhostcmake-2.8.6]# ./configure && gmake&& gmake install

[root@localhostcmake-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@localhostmysql-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@localhostmysql-5.5.22]# chown -R mysql:mysql/usr/local/mysql

[root@localhostmysql-5.5.22]# rm -rf /etc/my.cnf

[root@localhostmysql-5.5.22]# cp support-files/my-medium.cnf/etc/my.cnf

[root@localhostmysql-5.5.22]# /usr/local/mysql/scripts/mysql_install_db--user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

[root@localhostmysql-5.5.22]# echo "PATH=$PATH:/usr/local/mysql/bin">> /etc/profile

[root@localhostmysql-5.5.22]# . /etc/profile

[root@localhostmysql-5.5.22]# cp/usr/src/mysql-5.5.22/support-files/mysql.server /etc/rc.d/init.d/mysqld

[root@localhostmysql-5.5.22]# chmod +x /etc/rc.d/init.d/mysqld

[root@localhostmysql-5.5.22]# chkconfig --add mysqld

[root@localhostmysql-5.5.22]# vim /etc/my.cnf

27 datadir=/usr/local/mysql/data

[root@localhostmysql-5.5.22]# servicemysqld start

Starting MySQL... [确定]

[root@localhostmysql-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: Transactionlog of lsn (1595675) to (1595675) was copied.

161203 21:03:24 completedOK!

[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 MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 3

Server version: 5.5.22-logSource distribution

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

Type 'help;' or '\h' forhelp. 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,grantoption 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 MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 4

Server version: 5.5.22-logSource distribution

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

Type 'help;' or '\h' forhelp. 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,namechar(16) not null);

Query OK, 0 rows affected(0.05 sec)

mysql> insert into tb1values(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 * fromtb1;

+------+----------+

| 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 rollbacksegment(s) found. 1 redo rollback segment(s) are active.

InnoDB: 32 non-redorollback segment(s) are active.

InnoDB: 5.7.13 started;log sequence number 1595925

xtrabackup: startingshutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimizethread exiting.

InnoDB: Startingshutdown...

InnoDB: Shutdowncompleted; log sequence number 1595944

161203 21:33:32 completedOK!

[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 completedOK!

[root@localhost ~]# ll /usr/local/mysql/data/

总用量 40972

-rw-r-----. 1 root root18874368 12月 3 21:36 ibdata1

-rw-r-----. 1 rootroot 5242880 12月 3 21:36 ib_logfile0

-rw-r-----. 1 rootroot 5242880 12月 3 21:36 ib_logfile1

-rw-r-----. 1 root root12582912 12月 3 21:36 ibtmp1

drwxr-x---. 2 rootroot 4096 12月 3 21:36 mysql

drwxr-x---. 2 rootroot 4096 12月 3 21:36 performance_schema

-rw-r-----. 1 rootroot 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 MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 1

Server version: 5.5.22-logSource distribution

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

Type 'help;' or '\h' forhelp. 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.00sec)

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 modifieddata 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,namevarchar(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: Transactionlog of lsn (1603986) to (1603986) was copied.

161203 22:01:55 completedOK!

[root@localhost ~]# ls -l /opt/mysqlbackup/full/

总用量 8

drwxr-x---. 4 root root4096 12月 3 21:33 2016-12-03_21-03-06

drwxr-x---. 6 root root4096 12月 3 22:01 full_incre_20161203_22011480773711

继续往数据库增加数据，进行第一次增量备份

[root@localhost ~]# mysql -uroot -p

Enter password:

Welcome to the MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 4

Server version: 5.5.22-logSource distribution

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

Type 'help;' or '\h' forhelp. 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: Transactionlog of lsn (1605014) to (1605014) was copied.

161203 22:09:54 completedOK!

[root@localhost ~]# ls -l /opt/mysqlbackup/inc/

总用量 8

-rw-r--r--. 1 root root3041 12月 3 21:24 2016-12-03.sql

drwxr-x---. 6 root root4096 12月 3 22:09 incre_20161203_220950

注意：这里的增量备份只针对Innodb，而MyISAM仍然是完整备份

向表中再添入数据，进行第二次增量备份

[root@localhost ~]# mysql -uroot -p

Enter password:

Welcome to the MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 7

Server version: 5.5.22-logSource distribution

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

Type 'help;' or '\h' forhelp. 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: Transactionlog of lsn (1606042) to (1606042) was copied.

161203 22:18:34 completedOK!

注意:第二次增量备份--incremental--basedir指的是向上一次增量备份文件的位置

[root@localhost ~]# ls -l /opt/mysqlbackup/inc/

总用量 12

-rw-r--r--. 1 root root3041 12月 3 21:24 2016-12-03.sql

drwxr-x---. 6 root root4096 12月 3 22:09 incre_20161203_220950

drwxr-x---. 6 root root4096 12月 3 22:18 incre_20161203_221830

Xtrabackup进行增量恢复

为了验证对比，先删除两个增量备份前表里面的数据

[root@localhost ~]# mysql -uroot -p

Enter password:

Welcome to the MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 10

Server version: 5.5.22-logSource distribution

Oracle is a registered trademarkof Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

Type 'help;' or '\h' forhelp. 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"--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@localhostfull_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 MySQLmonitor. Commands end with ; or \g.

Your MySQL connection idis 3

Server version: 5.5.22-logSource distribution

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

Type 'help;' or '\h' forhelp. 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,namevarchar(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: Transactionlog of lsn (1609292) to (1609292) was copied.

161203 22:57:12 completedOK!

接下来做第一次增量备份，先进入数据库插入数据

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/

启动数据库，查看数据是否恢复