xtrabackup完全备份和xtrabackup增量备份配合使用

增量备份优点：减少备份数据重复，节省磁盘空间，缩短备份时间
增量备份的实现，依赖于innodb页上面的LSN（log sequence number），每次对数据库的修改都会导致LSN自增。增量备份会复制指定LSN<日志序列号>之后的所有数据页。

mysql> create database haha;
Query OK, 1 row affected (0.01 sec)

mysql> use haha;
Database changed

mysql> create table test(id int,name varchar(18));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1,"zz");
Query OK, 1 row affected (0.05 sec)

mysql>  insert into test values(111,"mm");
Query OK, 1 row affected (0.00 sec)

mysql>  insert into test values(112,"xx");
Query OK, 1 row affected (0.01 sec)

innobackupex --defaults-file=/etc/my.cnf --us
er=root --password=123456 --socket=/usr/local/mysql/mysql.sock --port=3306 /opt/mysqlbackup/full/

cat /opt/mysqlbackup/full/2021-03-11_18-17-25/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2729222
last_lsn = 2729231
compact = 0
recover_binlog_info = 0
flushed_lsn = 2729231
##检查备份文件夹下的xtrabackup-checkpoints,查看信息

mysql> use haha;
Database changed

mysql> insert into test values(200,"haha");
Query OK, 1 row affected (0.00 sec)

使用--incremental创建增量备份
语法：innobackupex --user=root --password=123456 --incremental /增量1路径 --incremental-basedir=全备路径，后面指定在哪个全备上进行增量备份

innobackupex --user=root --password=123456 --incremental /opt/mysqlbackup/inc/ --incremental-basedir=/opt/mysqlbackup/full/2021-03-11_18-17-25/ --socket=/usr/local/mysql/mysql.sock

--incremental：这个选项告诉 xtrabackup 创建一个增量备份，而不是完全备份。

--incremental-basedir：指定上次完整备份或者增量备份文件的位置(即如果是第一次增量备份则指向完全备份所在目录，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录)。

增量备份创建的另外一种方法：
cat /opt/mysqlbackup/full/2021-03-11_18-17-25/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 2729222
last_lsn = 2729231
compact = 0
recover_binlog_info = 0
flushed_lsn = 2729231
##查看完备的结束时的lsn

innobackupex --user=root --password=123456 --socket=/usr/local/mysql/mysql.sock --incremental /opt/mysqlbackup/inc/ --incremental-lsn=2729222

cat /opt/mysqlbackup/inc/2021-03-11_18-27-18/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2729222
to_lsn = 2729593
last_lsn = 2729602
compact = 0
recover_binlog_info = 0
flushed_lsn = 2729602

进行第2次增备份：（以增量1为基准：/opt/mysqlbackup/inc/2021-03-11_18-27-18/ ）

mysql> insert into test values(201,"hehe");
Query OK, 1 row affected (0.00 sec)

innobackupex --user=root --password=123456 --socket=/usr/local/mysql/mysql.sock --incremental /opt/mysqlbackup/inc/ --incremental-basedir=/opt/mysqlbackup/inc/2021-03-11_18-27-18/

cat /opt/mysqlbackup/inc/2021-03-11_18-30-48/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2729593
to_lsn = 2729933
last_lsn = 2729942
compact = 0
recover_binlog_info = 0
flushed_lsn = 2729942

恢复步骤：
准备完全备份
恢复增量备份到完全备份（开始恢复的增量备份要添加–redo-only参数，到最后一次增量备份要去掉–redo-only）
对整体的完全备份进行恢复，回滚未提交的数据
注：–redo-only 用于准备增量备份内容把数据合并到全备份目录

innobackupex --apply-log --redo-only /opt/mysqlbackup/full/2021-03-11_18-17-25/
##准备全备

innobackupex --apply-log --redo-only /opt/mysqlbackup/full/2021-03-11_18-17-25/ --incremental-dir=/opt/mysqlbackup/inc/2021-03-11_18-27-18/
##将第一次增备添加到全备中

innobackupex --apply-log /opt/mysqlbackup/ful
l/2021-03-11_18-17-25/ --incremental-dir=/opt/mysqlbackup/inc/2021-03-11_18-30-48/
##将第二次增备添加到全备中

innobackupex --apply-log /opt/mysqlbackup/full/2021-03-11_18-17-25/
##将合并后的完备再一次准备

/etc/init.d/mysqld stop

mv /data/mysql/data/ /tmp/

innobackupex --defaults-file=/etc/my.cnf --copy-back /opt/mysqlbackup/full/2021-03-11_18-17-25/

chown mysql:mysql -R /data/mysql/data/

/etc/init.d/mysqld start

mysql> select * from haha.test;
+------+------+
| id   | name |
+------+------+
|    1 | zz   |
|  111 | mm   |
|  112 | xx   |
|  200 | haha |
|  201 | hehe |
+------+------+
5 rows in set (0.00 sec)
##全备和两次增备数据全部恢复