演示增量备份
#启动一个全备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]$ mysqlmysql> 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 changedmysql> 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_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 1626134last_lsn = 1626134[mysql@hadoop1 backup]$ more ./2016-05-27_06-08-22/xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 1626134to_lsn = 1643331last_lsn = 1643331[mysql@hadoop1 backup]$ more ./2016-05-27_06-14-55/xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 1643331to_lsn = 1650836last_lsn = 1650836[mysql@hadoop1 backup]$
恢复增量备份
#先备份整个备份集[mysql@hadoop1 mysqldata]$ ls3306 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 -ltotal 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 ibdata01drwx------ 2 mysql mysql 4096 May 27 05:47 mysqldrwxrwxr-x 2 mysql mysql 4096 May 27 05:47 performance_schemadrwxrwxr-x 2 mysql mysql 4096 May 27 05:47 polestardrwxrwxr-x 2 mysql mysql 4096 May 27 05:47 polestar02drwxrwxr-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]$
--基于第一个增备的applyinnobackupex \--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 -ltotal 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 ibdata01drwx------ 2 mysql mysql 4096 May 27 05:47 mysqldrwxrwxr-x 2 mysql mysql 4096 May 27 05:47 performance_schemadrwxrwxr-x 2 mysql mysql 4096 May 27 06:26 polestardrwxrwxr-x 2 mysql mysql 4096 May 27 06:26 polestar02drwxrwx--- 2 mysql mysql 4096 May 27 06:26 polestar_innodbdrwxrwxr-x 2 mysql mysql 4096 May 27 06:26 polestar_myisamdrwxrwxr-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 -ltotal 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 ibdata01drwx------ 2 mysql mysql 4096 May 27 05:47 mysqldrwxrwxr-x 2 mysql mysql 4096 May 27 05:47 performance_schemadrwxrwxr-x 2 mysql mysql 4096 May 27 06:26 polestardrwxrwxr-x 2 mysql mysql 4096 May 27 06:26 polestar02drwxrwx--- 2 mysql mysql 4096 May 27 06:26 polestar_innodbdrwxrwxr-x 2 mysql mysql 4096 May 27 06:26 polestar_myisamdrwxrwxr-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选项