[root@db03 backup]# cat /backup/inc2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1640828
to_lsn = 1645877
6）再次写入数据
7）第三次增量备份
[root@db03 backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/inc2 /backup/inc3
6.Xtrabackup增量恢复数据
1）将全备执行redo
[root@db03 backup]# innobackupex --apply-log --redo-only /backup/full_2020-07-23
2）将第一次增备只执行redo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1/ /backup/full_2020-07-23
#验证
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1640828 #该值本来是inc1的位置点
3）将第二次增备只执行redo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2/ /backup/full_2020-07-23
#验证
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 1645877 #该值本来是inc2的位置点
4）将最后一次增备执行redo和undo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --incremental-dir=/backup/inc3/ /backup/full_2020-07-23
#验证
[root@db03 backup]#
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 1649869
5）将整体数据进行一次CSR
[root@db03 backup]# innobackupex --apply-log /backup/full_2020-07-23/
6）恢复数据
[root@db03 mysql]# mv data data.bak
[root@db03 mysql]# innobackupex ---back /backup/full_2020-07-23/
[root@db03 mysql]# chown -R mysql.mysql data
[root@db03 mysql]# systemctl start mysqld
7.总结
1.增备：
优点：占用磁盘空间小，没有重复数据
缺点：恢复麻烦
2.全备：
优点：恢复只需一次
缺点：占用磁盘空间，每次全备都有重复数据
8.企业及增量恢复实战
要求
企业级增量恢复实战
背景：
某大型网站，mysql数据库，数据量500G，每日更新量100M-200M
备份策略：
xtrabackup，每周六0:00进行全备，周一到周五及周日00:00进行增量备份。
故障场景：
周三下午2点出现数据库意外删除表操作。
思路
# 数据恢复流程
1. 将周六全备执行redo
2. 将周日增备只执行redo并合并到周六全备
3. 将周一增备只执行redo并合并到周六全备
4. 将周二增备执行redo和undo并合并到周六全备
5. 将合并到周六的数据整体执行一次CSR
6. 通过binlog日志将周三0：00-14:00数据导出
7. 通过xtrabackup将数据恢复
8. 将binlog日志导入数据库
环境模拟
主机名 ip 角色
db01 172.16.1.51 数据库服务器
环境准备
# 0.前期工具准备：
[root@db01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
注：（下载方式）
wget httpss://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
# 1. 模拟创建大型网站500G数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| test |
| world |
+--------------------+
# 2.准备备份目录
[root@db01 ~]# mkdir /backup
# 3. 通过xtrabackup工具执行全量备份
[root@db01 ~]# innobackupex --user=root --no-timestamp /backup/full
# 4. 查看备份全量文件
[root@db01 backup]# ll /backup/full/
total 12316
-rw-r----- 1 root root 418 Jul 23 15:00 backup-my.cnf
-rw-r----- 1 root root 12582912 Jul 23 15:00 ibdata1
drwxr-x--- 2 root root 4096 Jul 23 15:00 mysql
drwxr-x--- 2 root root 4096 Jul 23 15:00 performance_schema
drwxr-x--- 2 root root 58 Jul 23 15:00 student
drwxr-x--- 2 root root 20 Jul 23 15:00 test
drwxr-x--- 2 root root 144 Jul 23 15:00 world
-rw-r----- 1 root root 24 Jul 23 15:00 xtrabackup_binlog_info
-rw-r----- 1 root root 113 Jul 23 15:00 xtrabackup_checkpoints
-rw-r----- 1 root root 471 Jul 23 15:00 xtrabackup_info
-rw-r----- 1 root root 2560 Jul 23 15:00 xtrabackup_logfile
# 5. 查看备份类型及备份点
[root@db01 full]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2588601
last_lsn = 2588601
compact = 0
recover_binlog_info = 0
# 6. 模拟周日写入数据
mysql> select * from tcy;
+------+--------------+
| id | name |
+------+--------------+
| 77 | 周日数据 |
+------+--------------+
# 7. 对周日数据执行增量备份
[root@db01 backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/full /backup/sunday
# 8. 查看备份数据类型及数据点
[root@db01 backup]# cat /backup/sunday/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2588601
to_lsn = 2595794
last_lsn = 2595794
compact = 0
recover_binlog_info = 0
# 9. 模拟周一数据写入
mysql> select * from tcy;
+------+--------------+
| id | name |
+------+--------------+
| 77 | 周日数据 |
| 11 | 周一数据 |
+------+--------------+
2 rows in set (0.00 sec)
# 10.模拟对周一数据执行增量备份
[root@db01 backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/sunday /backup/monday
# 11.查看备份数据数据类型及数据点
[root@db01 backup]# cat /backup/monday/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2595794
to_lsn = 2596831
last_lsn = 2596831
compact = 0
recover_binlog_info = 0
# 12.模拟周二数据写入
mysql> select * from tcy;
+------+--------------+
| id | name |
+------+--------------+
| 77 | 周日数据 |
| 11 | 周一数据 |
| 22 | 周二数据 |
+------+--------------+
3 rows in set (0.00 sec)
# 13. 对周二数据执行增量备份
[root@db01 backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/monday /backup/tuesday
# 14. 查看备份数据类型及数据点
[root@db01 backup]# cat /backup/tuesday/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2596831
to_lsn = 2597868
last_lsn = 2597868
compact = 0
recover_binlog_info = 0
# 15. 模拟周三数据写入
mysql> select * from tcy;
+------+--------------+
| id | name |
+------+--------------+
| 77 | 周日数据 |
| 11 | 周一数据 |
| 22 | 周二数据 |
| 33 | 周三数据 |
+------+--------------+
# 16. 模拟故障（对表和库进行删除）
mysql> drop table tcy;
mysql> drop database student;
