innobackupex恢复到指定的增量备份

193次阅读

没有评论

共计 2870 个字符，预计需要花费 8 分钟才能阅读完成。

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

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# 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 |

+—————-+