innobackupex恢复到指定的增量备份

43221321

                      测试验证恢复到指定的增量备份：

root@debian:/var/lib# mysql -uroot -pleyou test
mysql> show tables;
Empty set (0.00 sec)

进行一个全备：
innobackupex --defaults-file=/etc/mysql/my.cnf  --user=root --password=leyou   /home/data/backup/full

创建表：

mysql> create table t(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table t2 as select * from t;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0


基于全备的增量备份：

innobackupex  --defaults-file=/etc/mysql/my.cnf  --user=root --password=leyou --incremental /home/data/backup/incr/ --incremental-basedir=/home/data/backup/full/2017-03-07_15-58-18/ --parallel=2


模拟故障，错误drop 表 t2：

mysql> drop table t2;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t3 as select * from t;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table t4 as select * from t;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

增量备份2正常进行：
innobackupex  --defaults-file=/etc/mysql/my.cnf  --user=root --password=leyou --incremental /home/data/backup/incr/ --incremental-basedir=/home/data/backup/incr/2017-03-07_16-04-41/  --parallel=2


查看checkpoint信息：
root@debian:/home/data/backup/incr/2017-03-07_16-04-41# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 31407802020
to_lsn = 31407817345
last_lsn = 31407817345
compact = 0
root@debian:/home/data/backup/incr/2017-03-07_16-04-41#
root@debian:/home/data/backup/incr/2017-03-07_16-04-41#
root@debian:/home/data/backup/incr/2017-03-07_16-04-41#
root@debian:/home/data/backup/incr/2017-03-07_16-04-41# cd ../2017-03-07_16-06-42/
root@debian:/home/data/backup/incr/2017-03-07_16-06-42#
root@debian:/home/data/backup/incr/2017-03-07_16-06-42# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 31407817345
to_lsn = 31407833800
last_lsn = 31407833800
compact = 0




发现刚刚drop t2了，t2是个很重要的表，然后恢复，由于这个操作是在增量备份2之前操作的，因此需要恢复增量备份1。


先恢复full的日志：

innobackupex --defaults-file=/etc/mysql/my.cnf   --apply-log  --redo-only  /home/data/backup/full/2017-03-07_15-58-18/


将增量备份1应用到完全备份
innobackupex --defaults-file=/etc/mysql/my.cnf  --apply-log  /home/data/backup/full/2017-03-07_15-58-18/ --incremental-dir=/home/data/backup/incr/2017-03-07_16-04-41/



此时增量备份其实都合并到全备上了,恢复是只需要使用全备进行恢复就可以了
模拟数据故障[关闭数据库，删除数据库的数据目录，执行如下命令还原]

innobackupex --defaults-file=/etc/mysql/my.cnf  --copy-back  /home/data/backup/full/2017-03-07_15-58-18/

chown -R mysql:mysql /var/lib/mysql/


启动数据库，可以发现在增量备份2之后drop t2， create table t4 、t3 都没有了，t2已经恢复。

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
| t2             |
+----------------+
2 rows in set (0.00 sec)

mysql>
mysql>