innobackupex恢复到指定的增量备份

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

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>

通过innobackupex实现对MySQL的增量备份与还原 

innobackupex的备份和恢复 

使用innobackupex备份遇到的问题和解决方法 

使用Innobackupex快速搭建(修复)MySQL主从架构 

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/aa025e429e2101f6a47e9b5362dd6a7c.html