xtrabackup之Innobackupex增量备份及恢复

演示增量备份

#启动一个全备
innobackupex \
> --defaults-file=/mysql/mysqldata/3306/my.cnf \
> --host=127.0.0.1 \
> --user=xtrabk \
> --password='onlybackup' /mysql/mysqldata/backup/

............
160527 05:47:55  innobackupex: completed OK!
[mysql@hadoop1 mysqldata]$

#新增记录，以便区分全备与增备
[mysql@hadoop1 mysqldata]$ mysql
mysql> create database polestar_myisam;
mysql> create database polestar_innodb;
mysql> create table m_polestar engine=myisam select * from mysql.user;
mysql> create table i_polestar engine=innodb select * from mysql.user;

#启动一个增量备份 
innobackupex \
--defaults-file=/mysql/mysqldata/3306/my.cnf \
--host=127.0.0.1 \
--user=xtrabk \
--password='onlybackup' \
--incremental-basedir=/mysql/mysqldata/backup/2016-05-27_05-47-34 \
--incremental /mysql/mysqldata/backup/

............
160527 06:08:43  innobackupex: completed OK!
[mysql@hadoop1 mysqldata]$

#再新增记录，以便区分第一次增备
mysql> use polestar_myisam;
Database changed
mysql> insert into m_polestar  select * from mysql.user;
mysql> use polestar_innodb;
mysql> insert into i_polestar  select * from mysql.user;

#以上次增量备份为基础 再次增量备份
innobackupex \
--defaults-file=/mysql/mysqldata/3306/my.cnf \
--host=127.0.0.1 \
--user=xtrabk \
--password='onlybackup' \
--incremental-basedir=/mysql/mysqldata/backup/2016-05-27_06-08-22 \
--incremental /mysql/mysqldata/backup/

.......
160527 06:15:15  innobackupex: completed OK!
[mysql@hadoop1 mysqldata]$ 

　　至此，三次备份完成（一次全备，两个增量备份）．下面看下三个备份集中的LSN号顺序：

[mysql@hadoop1 backup]$ more ./2016-05-27_05-47-34/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1626134
last_lsn = 1626134
[mysql@hadoop1 backup]$ more ./2016-05-27_06-08-22/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1626134
to_lsn = 1643331
last_lsn = 1643331
[mysql@hadoop1 backup]$ more ./2016-05-27_06-14-55/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1643331
to_lsn = 1650836
last_lsn = 1650836
[mysql@hadoop1 backup]$

恢复增量备份

#先备份整个备份集
[mysql@hadoop1 mysqldata]$ ls
3306   3307  backup
[mysql@hadoop1 mysqldata]$ cp -rf backup  backup02

#先做基于全备的apply，注意，此时使用了--redo-only   
innobackupex \
--defaults-file=/mysql/mysqldata/3306/my.cnf \
--apply-log \
--redo-only \
/mysql/mysqldata/backup/2016-05-27_05-47-34

..........
160527 06:22:33  innobackupex: completed OK!
[mysql@hadoop1 3306]$

注意目录中文件的修改时间变化
[mysql@hadoop1 2016-05-27_05-47-34]$ pwd
/mysql/mysqldata/backup/2016-05-27_05-47-34
[mysql@hadoop1 2016-05-27_05-47-34]$ ls -l
total 104592
-rw-rw-r-- 1 mysql mysql       241 May 27 05:47 backup-my.cnf
-rw-rw---- 1 mysql mysql 104857600 May 27 06:22 ibdata01
drwx------ 2 mysql mysql      4096 May 27 05:47 mysql
drwxrwxr-x 2 mysql mysql      4096 May 27 05:47 performance_schema
drwxrwxr-x 2 mysql mysql      4096 May 27 05:47 polestar
drwxrwxr-x 2 mysql mysql      4096 May 27 05:47 polestar02
drwxrwxr-x 2 mysql mysql      4096 May 27 05:47 test
-rw-rw-r-- 1 mysql mysql        13 May 27 05:47 xtrabackup_binary
-rw-rw-r-- 1 mysql mysql        23 May 27 05:47 xtrabackup_binlog_info
-rw-rw---- 1 mysql mysql        77 May 27 06:22 xtrabackup_checkpoints
-rw-rw---- 1 mysql mysql   2097152 May 27 06:22 xtrabackup_logfile
[mysql@hadoop1 2016-05-27_05-47-34]$

--基于第一个增备的apply
innobackupex \
--defaults-file=/mysql/mysqldata/3306/my.cnf \
--apply-log \
--redo-only \
/mysql/mysqldata/backup/2016-05-27_05-47-34 \
--incremental-dir=/mysql/mysqldata/backup/2016-05-27_06-08-22
...........
160527 06:26:42  innobackupex: completed OK!
[mysql@hadoop1 3306]$

注意文件的修改时间和上次恢复时的不同
[mysql@hadoop1 2016-05-27_05-47-34]$ ls -l
total 104600
-rw-rw-r-- 1 mysql mysql       241 May 27 05:47 backup-my.cnf
-rw-rw---- 1 mysql mysql 104857600 May 27 06:26 ibdata01
drwx------ 2 mysql mysql      4096 May 27 05:47 mysql
drwxrwxr-x 2 mysql mysql      4096 May 27 05:47 performance_schema
drwxrwxr-x 2 mysql mysql      4096 May 27 06:26 polestar
drwxrwxr-x 2 mysql mysql      4096 May 27 06:26 polestar02
drwxrwx--- 2 mysql mysql      4096 May 27 06:26 polestar_innodb
drwxrwxr-x 2 mysql mysql      4096 May 27 06:26 polestar_myisam
drwxrwxr-x 2 mysql mysql      4096 May 27 05:47 test
-rw-rw-r-- 1 mysql mysql        13 May 27 05:47 xtrabackup_binary
-rw-rw-r-- 1 mysql mysql        24 May 27 06:26 xtrabackup_binlog_info
-rw-rw---- 1 mysql mysql        77 May 27 06:26 xtrabackup_checkpoints
-rw-rw---- 1 mysql mysql   2097152 May 27 06:22 xtrabackup_logfile
[mysql@hadoop1 2016-05-27_05-47-34]$ 

--基于最后一个增备的apply 
innobackupex \
--defaults-file=/mysql/mysqldata/3306/my.cnf \
--apply-log \
/mysql/mysqldata/backup/2016-05-27_05-47-34 \
--incremental-dir=/mysql/mysqldata/backup/2016-05-27_06-14-55

...........
160527 06:33:17  innobackupex: completed OK!
[mysql@hadoop1 3306]$ 

注意文件修改时间的变化
[mysql@hadoop1 2016-05-27_05-47-34]$ ls -l
total 104600
-rw-rw-r-- 1 mysql mysql       241 May 27 05:47 backup-my.cnf
-rw-rw---- 1 mysql mysql 104857600 May 27 06:33 ibdata01
drwx------ 2 mysql mysql      4096 May 27 05:47 mysql
drwxrwxr-x 2 mysql mysql      4096 May 27 05:47 performance_schema
drwxrwxr-x 2 mysql mysql      4096 May 27 06:26 polestar
drwxrwxr-x 2 mysql mysql      4096 May 27 06:26 polestar02
drwxrwx--- 2 mysql mysql      4096 May 27 06:26 polestar_innodb
drwxrwxr-x 2 mysql mysql      4096 May 27 06:26 polestar_myisam
drwxrwxr-x 2 mysql mysql      4096 May 27 05:47 test
-rw-rw-r-- 1 mysql mysql        13 May 27 05:47 xtrabackup_binary
-rw-rw-r-- 1 mysql mysql        24 May 27 06:33 xtrabackup_binlog_info
-rw-rw---- 1 mysql mysql        77 May 27 06:33 xtrabackup_checkpoints
-rw-rw---- 1 mysql mysql   2097152 May 27 06:22 xtrabackup_logfile
[mysql@hadoop1 2016-05-27_05-47-34]$ 

数据库恢复

innobackupex \
--defaults-file=/mysql/mysqldata/3306/my.cnf \
--copy-back \
/mysql/mysqldata/backup/2016-05-27_05-47-34

......

160527 06:38:03  innobackupex: completed OK!
[mysql@hadoop1 3306]$

--验证
[mysql@hadoop1 3306]$mysqld_safe --defaults-file=/mysql/mysqldata/3306/my.cnf &
mysql> use polestar_myisam;
mysql> show tables;
+---------------------------+
| Tables_in_polestar_myisam |
+---------------------------+
| m_polestar                | 
+---------------------------+
mysql> select count(*) from m_polestar;
+----------+
| count(*) |
+----------+
|       14 | 
+----------+
mysql> 

小结

a、增量备份是基于增量或全备的基础之上完成的。
b、增量备份的基础是InnoDB引擎使用了LSN机制，非InnoDB引擎不存在增量备份的说法，每次都是全备。
c、对于增量备份的恢复期间需要对已提交的事务前滚，未提交的事务回滚。
d、增量备份的恢复应按照备份的顺利逐个逐个replay，需要使用--apply-log --redo-only选项。
e、仅仅最后一个增量备份不需要使用--redo-only选项