演示增量备份
#启动一个全备
innobackupex \
> --defaults-file=/mysql/mysqldata//my.cnf \
> --host=127.0.0.1 \
> --user=xtrabk \
> --password='onlybackup' /mysql/mysqldata/backup/ ............
:: 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//my.cnf \
--host=127.0.0.1 \
--user=xtrabk \
--password='onlybackup' \
--incremental-basedir=/mysql/mysqldata/backup/--27_05-- \
--incremental /mysql/mysqldata/backup/ ............
:: 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//my.cnf \
--host=127.0.0.1 \
--user=xtrabk \
--password='onlybackup' \
--incremental-basedir=/mysql/mysqldata/backup/--27_06-- \
--incremental /mysql/mysqldata/backup/ .......
:: innobackupex: completed OK!
[mysql@hadoop1 mysqldata]$
至此，三次备份完成（一次全备，两个增量备份）．下面看下三个备份集中的LSN号顺序：
[mysql@hadoop1 backup]$ more ./--27_05--/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn =
to_lsn =
last_lsn =
[mysql@hadoop1 backup]$ more ./--27_06--/xtrabackup_checkpoints
backup_type = incremental
from_lsn =
to_lsn =
last_lsn =
[mysql@hadoop1 backup]$ more ./--27_06--/xtrabackup_checkpoints
backup_type = incremental
from_lsn =
to_lsn =
last_lsn =
[mysql@hadoop1 backup]$
恢复增量备份
#先备份整个备份集
[mysql@hadoop1 mysqldata]$ ls
backup
[mysql@hadoop1 mysqldata]$ cp -rf backup backup02
#先做基于全备的apply，注意，此时使用了--redo-only
innobackupex \
--defaults-file=/mysql/mysqldata//my.cnf \
--apply-log \
--redo-only \
/mysql/mysqldata/backup/--27_05-- ..........
:: innobackupex: completed OK!
[mysql@hadoop1 ]$
注意目录中文件的修改时间变化
[mysql@hadoop1 --27_05--]$ pwd
/mysql/mysqldata/backup/--27_05--
[mysql@hadoop1 --27_05--]$ ls -l
total
-rw-rw-r-- mysql mysql May : backup-my.cnf
-rw-rw---- mysql mysql May : ibdata01
drwx------ mysql mysql May : mysql
drwxrwxr-x mysql mysql May : performance_schema
drwxrwxr-x mysql mysql May : polestar
drwxrwxr-x mysql mysql May : polestar02
drwxrwxr-x mysql mysql May : test
-rw-rw-r-- mysql mysql May : xtrabackup_binary
-rw-rw-r-- mysql mysql May : xtrabackup_binlog_info
-rw-rw---- mysql mysql May : xtrabackup_checkpoints
-rw-rw---- mysql mysql May : xtrabackup_logfile
[mysql@hadoop1 --27_05--]$
--基于第一个增备的apply
innobackupex \
--defaults-file=/mysql/mysqldata//my.cnf \
--apply-log \
--redo-only \
/mysql/mysqldata/backup/--27_05-- \
--incremental-dir=/mysql/mysqldata/backup/--27_06--
...........
:: innobackupex: completed OK!
[mysql@hadoop1 ]$
注意文件的修改时间和上次恢复时的不同
[mysql@hadoop1 --27_05--]$ ls -l
total
-rw-rw-r-- mysql mysql May : backup-my.cnf
-rw-rw---- mysql mysql May : ibdata01
drwx------ mysql mysql May : mysql
drwxrwxr-x mysql mysql May : performance_schema
drwxrwxr-x mysql mysql May : polestar
drwxrwxr-x mysql mysql May : polestar02
drwxrwx--- mysql mysql May : polestar_innodb
drwxrwxr-x mysql mysql May : polestar_myisam
drwxrwxr-x mysql mysql May : test
-rw-rw-r-- mysql mysql May : xtrabackup_binary
-rw-rw-r-- mysql mysql May : xtrabackup_binlog_info
-rw-rw---- mysql mysql May : xtrabackup_checkpoints
-rw-rw---- mysql mysql May : xtrabackup_logfile
[mysql@hadoop1 --27_05--]$
--基于最后一个增备的apply
innobackupex \
--defaults-file=/mysql/mysqldata//my.cnf \
--apply-log \
/mysql/mysqldata/backup/--27_05-- \
--incremental-dir=/mysql/mysqldata/backup/--27_06-- ...........
:: innobackupex: completed OK!
[mysql@hadoop1 ]$
注意文件修改时间的变化
[mysql@hadoop1 --27_05--]$ ls -l
total
-rw-rw-r-- mysql mysql May : backup-my.cnf
-rw-rw---- mysql mysql May : ibdata01
drwx------ mysql mysql May : mysql
drwxrwxr-x mysql mysql May : performance_schema
drwxrwxr-x mysql mysql May : polestar
drwxrwxr-x mysql mysql May : polestar02
drwxrwx--- mysql mysql May : polestar_innodb
drwxrwxr-x mysql mysql May : polestar_myisam
drwxrwxr-x mysql mysql May : test
-rw-rw-r-- mysql mysql May : xtrabackup_binary
-rw-rw-r-- mysql mysql May : xtrabackup_binlog_info
-rw-rw---- mysql mysql May : xtrabackup_checkpoints
-rw-rw---- mysql mysql May : xtrabackup_logfile
[mysql@hadoop1 --27_05--]$
数据库恢复
innobackupex \
--defaults-file=/mysql/mysqldata//my.cnf \
--copy-back \
/mysql/mysqldata/backup/--27_05-- ...... :: innobackupex: completed OK!
[mysql@hadoop1 ]$
--验证
[mysql@hadoop1 ]$mysqld_safe --defaults-file=/mysql/mysqldata//my.cnf &
mysql> use polestar_myisam;
mysql> show tables;
+---------------------------+
| Tables_in_polestar_myisam |
+---------------------------+
| m_polestar |
+---------------------------+
mysql> select count(*) from m_polestar;
+----------+
| count(*) |
+----------+
| |
+----------+
mysql>
小结
a、增量备份是基于增量或全备的基础之上完成的。
b、增量备份的基础是InnoDB引擎使用了LSN机制，非InnoDB引擎不存在增量备份的说法，每次都是全备。
c、对于增量备份的恢复期间需要对已提交的事务前滚，未提交的事务回滚。
d、增量备份的恢复应按照备份的顺利逐个逐个replay，需要使用--apply-log --redo-only选项。
e、仅仅最后一个增量备份不需要使用--redo-only选项