MYSQL快速备份工具xtrabackup测试（I）

 @written by Jane Hoo
--------------------------------------------------------

1 、创建专门用于备份的备份用户
mysql> GRANT SELECT, RELOAD, SUPER, LOCK TABLES, SHOW VIEW, EVENT ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY '1234';
2、创建测试数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)


mysql> create database xkdb;
Query OK, 1 row affected (0.03 sec)


mysql> use xkdb;
Database changed
mysql> show tables;
Empty set (0.00 sec)


mysql> create table test_t1 as select * from information_schema.tables;
Query OK, 83 rows affected (0.10 sec)
Records: 83  Duplicates: 0  Warnings: 0
3、全量备份
[root@test08 mysql]# mkdir /xkbackup-test
[root@test08 ~]#  /usr/bin/innobackupex --user=backupuser --password=1234 --defaults-file=/etc/my.cnf  /xkbackup-test
150128 11:28:29  innobackupex: completed OK!
4、全量备份恢复演练（成功恢复）
[root@test08 mysql]# service mysql stop
Shutting down MySQL..[  OK  ]
[root@test08 mysql]# mv data data0128
[root@test08 mysql]# mkdir data
[root@test08 mysql]# chown mysql.mysql data
[root@test08 mysql]# innobackupex  --defaults-file=/etc/my.cnf --apply-log --redo-only /xkbackup-test/2015-01-28_11-28-25/ --user=backupuser --password=1234
150128 15:40:58  innobackupex: completed OK!
[root@test08 mysql]# innobackupex  --defaults-file=/etc/my.cnf --copy-back /xkbackup-test/2015-01-28_11-28-25/ --user=backupuser --password=1234
150128 17:22:33  innobackupex: completed OK!
[root@test08 mysql]# chown -R mysql.mysql data
[root@test08 mysql]# service mysql start
Starting MySQL...............[  OK  ]
 
5、增量数据准备
mysql> insert into test_t1 select * from information_schema.tables;
Query OK, 83 rows affected (0.09 sec)
Records: 83  Duplicates: 0  Warnings: 0  
      mysql> create table test_t2 as select * from information_schema.tables;
      Query OK, 84 rows affected (0.09 sec)
      Records: 84  Duplicates: 0  Warnings: 0
6、增量备份
[root@test08 mysql]# innobackupex --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/xkbackup-test/2015-01-28_11-28-25/ --user=backupuser --password=1234 /xkbackup-test/inc/
150128 17:40:00  innobackupex: completed OK!
7、增量恢复
[root@test08 mysql]# innobackupex  --defaults-file=/etc/my.cnf /xkbackup-test/2015-01-28_11-28-25/ --incremental --incremental-dir=/xkbackup-test/inc/2015-01-28_17-39-57/ --apply-log
[root@test08 mysql]# service mysql stop
Shutting down MySQL..[  OK  ]
[root@test08 mysql]# pwd
/data/mysql
[root@test08 mysql]# mv data data012802
[root@test08 mysql]# mkdir data
[root@test08 mysql]# chown -R mysql.mysql data
[root@test08 mysql]# innobackupex  --user=backupuser --password=1234 --copy-back --defaults-file=/etc/my.cnf /xkbackup-test/2015-01-28_11-28-25/
8、查看是否恢复（成功恢复）
 
9、新增库看能否增量恢复成功、多层增量能否恢复
a、全备
[root@test08 mysql]# /usr/bin/innobackupex --user=backupuser --password=1234 --defaults-file=/etc/my.cnf  /xkbackup-test
150128 18:50:22  innobackupex: completed OK!
[root@test08 mysql]# cd /xkbackup-test/
[root@test08 xkbackup-test]# ls
2015-01-28_11-28-25  2015-01-28_18-50-18  inc
[root@test08 xkbackup-test]# mkdir 01
[root@test08 xkbackup-test]# cp -r 2015-01-28_18-50-18/* 01/
b、数据准备
mysql> create database xkdb02;
Query OK, 1 row affected (0.03 sec)


mysql> use xkdb02;
Database changed
mysql> show tables;
Empty set (0.00 sec)


mysql> create table t1 as select * from information_schema.tables;
Query OK, 85 rows affected (0.11 sec)
Records: 85  Duplicates: 0  Warnings: 0


c、增备
[root@test08 xkbackup-test]#  innobackupex --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/xkbackup-test/2015-01-28_18-50-18/ --user=backupuser --password=1234 /xkbackup-test/inc/
[root@test08 xkbackup-test]# mkdir 01-inc 
[root@test08 xkbackup-test]# cd inc
[root@test08 inc]# cp -r 2015-01-28_19-00-03/ ../
01/                  01-inc/              2015-01-28_11-28-25/ 2015-01-28_18-50-18/ inc/                 
[root@test08 inc]# cp -r 2015-01-28_19-00-03/ ../01-inc
[root@test08 inc]# ls ../01-inc/
2015-01-28_19-00-03

d、再次增加数据增备
mysql> create database xkdb03;
Query OK, 1 row affected (0.02 sec)


mysql> create table xkdb03.test_01 as select * from information_schema.tables;
Query OK, 86 rows affected (0.08 sec)
Records: 86  Duplicates: 0  Warnings: 0


mysql> create table xkdb02.test_02 as select * from information_schema.tables;
Query OK, 87 rows affected (0.06 sec)
Records: 87  Duplicates: 0  Warnings: 0


[root@test08 inc]# innobackupex --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/xkbackup-test/2015-01-28_18-50-18/ --user=backupuser --password=1234 /xkbackup-test/inc/
150128 19:09:42  innobackupex: completed OK!
生成备份文件夹   2015-01-28_19-09-39
[root@test08 inc]# cp -r 2015-01-28_19-09-39/ ../01-inc/
查看对应的全量，增量备份文件
 
e、恢复测试
[root@test08 mysql]# innobackupex  --defaults-file=/etc/my.cnf --apply-log --redo-only /xkbackup-test/01 --user=backupuser --password=1234
150128 19:19:44  innobackupex: completed OK!
[root@test08 mysql]# innobackupex --defaults-file=/etc/my.cnf /xkbackup-test/01 --incremental --incremental-dir=/xkbackup-test/01-inc/2015-01-28_19-00-03/ --apply-log
150128 19:22:06  innobackupex: completed OK!
[root@test08 mysql]# innobackupex --defaults-file=/etc/my.cnf /xkbackup-test/01 --user=backupuser --password=1234 --copy-back 
150128 19:25:47  innobackupex: completed OK!
[root@test08 mysql]# pwd
/data/mysql
[root@test08 mysql]# chown -R mysql.mysql data
[root@test08 mysql]# service mysql start
Starting MySQL........................[  OK  ]
查看恢复的数据(成功)
 
f、迭代恢复测试
[root@test08 01]# innobackupex  --defaults-file=/etc/my.cnf  /xkbackup-test/01 --incremental --incremental-dir=/xkbackup-test/01-inc/2015-01-28_19-09-39/ --apply-log
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: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 2633
        main::apply_log() called at /usr/bin/innobackupex line 1561
innobackupex: Error: 
innobackupex: ibbackup failed at /usr/bin/innobackupex line 2633.


结论：1、新建一个库、一个表的增量，能够正常恢复；
      2、全量备份只能和一个增量备份搭配进行恢复；
      3、可在恢复过一次的备份文件（全备+增量apply的基础）上，再进行增量备份，能够恢复；

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30109892/viewspace-1423867/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30109892/viewspace-1423867/