演示增量备份
#启动一个全备 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选项