xtrabackup增量备份与恢复测试

2019独角兽企业重金招聘Python工程师标准>>>

测试版本：xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )

xtrabackup增量备份与恢复测试

一、创建增量备份

1、创建全备

innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 /data/backup/physical/full/


2、创建新的数据库和表

1. root@[test] 11:49:19>create database test2 default charset utf8; 
2. Query OK, 1 row affected (0.00 sec)
4. root@[test] 11:49:24>use test
5. Database changed
6. root@[test] 11:49:36>create table t6 (id int,name varchar(4));
7. Query OK, 0 rows affected (0.01 sec)

3、创建增量备份1

1. innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 --incremental /data/backup/physical/inc/ --incremental-basedir=/data/backup/physical/full/2014-08-10_11-47-14/

4、修改表结构

1. root@[test] 11:49:54>alter table t5 add column (b2 int);
2. Query OK, 1 row affected (0.01 sec)
3. Records: 1  Duplicates: 0  Warnings: 0
5. root@[test] 11:53:08>alter table t6 drop column name;
6. Query OK, 0 rows affected (0.00 sec)
7. Records: 0  Duplicates: 0  Warnings: 0

5、创建增量备份2

1. innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 --incremental /data/backup/physical/inc/ --incremental-basedir=/data/backup/physical/inc/2014-08-10_11-51-50/

6、插入新数据

1. root@[test] 11:55:44>insert into t6 values (1),(2);
2. Query OK, 2 rows affected (0.00 sec)
3. Records: 2  Duplicates: 0  Warnings: 0
5. root@[test] 11:55:50>select * from t5;
6. +------+------+------+
7. | id   | b1   | b2   |
8. +------+------+------+
9. |    1 |    1 | NULL |
10. +------+------+------+
11. 1 row in set (0.00 sec)
13. root@[test] 11:56:03>insert into t5 values (2,2,2),(3,3,3);
14. Query OK, 2 rows affected (0.00 sec)
15. Records: 2  Duplicates: 0  Warnings: 0
17. root@[test] 11:56:17>select * from t5;                     
18. +------+------+------+
19. | id   | b1   | b2   |
20. +------+------+------+
21. |    1 |    1 | NULL |
22. |    2 |    2 |    2 |
23. |    3 |    3 |    3 |
24. +------+------+------+
25. 3 rows in set (0.00 sec)

7、创建增量备份3

1. innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --user=backup --password=backup@123 --incremental /data/backup/physical/inc/ --incremental-basedir=/data/backup/physical/inc/2014-08-10_11-54-06/

二、恢复测试

1、删除创建的数据库test2，删除创建的表t6，删除t5新添加的数据，需要恢复这些删除的数据，需要恢复到增量备份3

1. root@[test] 12:02:11>drop database test2; 
2. root@[test] 12:02:33>drop table t6;
3. Query OK, 0 rows affected (0.00 sec)
5. root@[test] 12:02:41>show tables;  
6. +----------------+
7. | Tables_in_test |
8. +----------------+
9. | sbtest1        |
10. | t1             |
11. | t2             |
12. | t3             |
13. | t4             |
14. | t5             |
15. +----------------+
16. 6 rows in set (0.00 sec)
18. root@[test] 12:02:45>delete from t5 where id=2 or id =3;
19. Query OK, 2 rows affected (0.00 sec)
21. root@[test] 12:03:02>select * from t5;
22. +------+------+------+
23. | id   | b1   | b2   |
24. +------+------+------+
25. |    1 |    1 | NULL |
26. +------+------+------+
27. 1 row in set (0.00 sec)

2、准备备份文件

准备全备：

1. innobackupex --apply-log -redo-only /data/backup/physical/full/2014-08-10_11-47-14/

准备增量备份1，应用到全备中：

1. innobackupex --apply-log -redo-only /data/backup/physical/full/2014-08-10_11-47-14/ --incremental-dir=/data/backup/physical/inc/2014-08-10_11-51-50/

准备增量备份2，应用到全备中：

1. innobackupex --apply-log -redo-only /data/backup/physical/full/2014-08-10_11-47-14/ --incremental-dir=/data/backup/physical/inc/2014-08-10_11-54-06/

准备增量备份3，应用到全备中，最后一个增量备份不需要使用--redo-only：

1. innobackupex --apply-log /data/backup/physical/full/2014-08-10_11-47-14/ --incremental-dir=/data/backup/physical/inc/2014-08-10_11-56-44/

然后再进行全备恢复：

1. innobackupex --apply-log /data/backup/physical/full/2014-08-10_11-47-14/

如果xtrabackup版本跟mysql版本不对应，在准备时将报错：这是使用 xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )准备mysql 5.5.38的备份时的报错信息

2014-08-10 12:10:37 7f00603a8700 InnoDB: Error: page 7561 log sequence number 2875887409

InnoDB: is in the future! Current system log sequence number 2395543093.

InnoDB: Your database may be corrupt or you may have copied the InnoDB

InnoDB: tablespace but not the InnoDB log files. See

InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

InnoDB: for more information.

3、清空数据目录的数据(最好进行冷备)，然后进行恢复，修改权限

1. rm -rf /data/mysql55/*
2. innobackupex --defaults-file=/opt/mariadb-10.0.12-linux-x86_64/my.cnf --copy-back /data/backup/physical/full/2014-08-10_11-47-14/
4. cd /data/mysql55/
5. chown mysql.mysql -R *
6. chmod 770 -R *

4、验证恢复

1. root@[(none)] 12:16:14>show databases;
2. +--------------------+
3. | Database           |
4. +--------------------+
5. | information_schema |
6. | employees          |
7. | mysql              |
8. | percona            |
9. | performance_schema |
10. | sakila             |
11. | test               |
12. | test2              |
13. +--------------------+
14. 8 rows in set (0.00 sec)
16. root@[(none)] 12:16:18>use test 
17. Database changed
18. root@[test] 12:16:50>select * from t5;
19. +------+------+------+
20. | id   | b1   | b2   |
21. +------+------+------+
22. |    1 |    1 | NULL |
23. |    2 |    2 |    2 |
24. |    3 |    3 |    3 |
25. +------+------+------+
26. 3 rows in set (0.01 sec)
28. root@[test] 12:16:59>select * from t6;
29. +------+
30. | id   |
31. +------+
32. |    1 |
33. |    2 |
34. +------+
35. 2 rows in set (0.02 sec)

如果在准备增量备份2时，少了--redo-only，是否增量备份3还能被应用到全备中，进行恢复：以下为执行情况，在应用增量备份3时报错

1. innobackupex --apply-log -redo-only /data/backup/physical/full/2014-08-10_14-25-17/
2. innobackupex --apply-log -redo-only /data/backup/physical/full/2014-08-10_14-25-17/ --incremental-dir=/data/backup/physical/inc/2014-08-10_14-30-19/
3. #在应用增量备份2时没有带--redo-only选项，这样就执行了回滚操作
4. innobackupex --apply-log /data/backup/physical/full/2014-08-10_14-25-17/ --incremental-dir=/data/backup/physical/inc/2014-08-10_14-31-21/
5. innobackupex --apply-log /data/backup/physical/full/2014-08-10_14-25-17/
7. #当继续往全备应用增量备份3时，报错，找不到对应的lsn
8. innobackupex --apply-log /data/backup/physical/full/2014-08-10_14-25-17/ --incremental-dir=/data/backup/physical/inc/2014-08-10_14-32-19/

报错信息：

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under

the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:

http://www.percona.com/xb/p

IMPORTANT: Please check that the apply-log run completes successfully.

           At the end of a successful apply-log run innobackupex

           prints "completed OK!".

140810 14:46:21  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/data/backup/physical/full/2014-08-10_14-25-17/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/data/backup/physical/full/2014-08-10_14-25-17 --incremental-dir=/data/backup/physical/inc/2014-08-10_14-32-19/ --tmpdir=/tmp

xtrabackup version 2.2.3 based on MySQL server 5.6.17 Linux (x86_64) (revision id: )

incremental backup from 2875985858 is enabled.

xtrabackup: cd to /data/backup/physical/full/2014-08-10_14-25-17

xtrabackup: This target seems to be already prepared.

xtrabackup: error: This incremental backup seems not to be proper for the target.

xtrabackup:  Check 'to_lsn' of the target and 'from_lsn' of the incremental.

innobackupex: Error: 

innobackupex: ibbackup failed at /usr/bin/innobackupex line 2610.

总结：

1、在主从环境，因在master进行的操作，都在slave执行成功后，那将master进行恢复后，因恢复过程中没有产生二进制日志，slave不会恢复到跟master一致，所以也要在slave使用同样的方式进行恢复；

2、备份和准备时使用xtrabackup版本要跟mysql版本对应(使用相应版本的库文件进行编译xtrabackup)，以免发现意外情况，或者使用--ibbackup=IBBACKUP-BINARY指定对应的xtrabackup二进制文件；

3、当应用了增量备份后，如想将全备再还原到初始状态是不可行的，所以在做准备备份时，提前备份好一份全备，以免不时之需；

4、当增量备份没有带--redo-only后，之后的增量备份不能再应用；

5、在进行恢复时，要保证数据库目录为空。

参考：

1、 http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/incremental_backups_innobackupex.html

来自为知笔记(Wiz)

转载于:https://my.oschina.net/anthonyyau/blog/299768