第85讲：MySQLDump与Binlog日志实现企业级数据备份恢复案例

1.企业级数据备份恢复案例描述

案例背景：

某互联网公司的MySQL版本时5.7.35，操作系统是Centos7.5，数据量大概在100G左右，每日的数据增量大概是10M以内。

数据备份策略：

每天晚上0点使用mysqldump进行全库备份，并且针对Binlog日志也进行备份。

故障描述：

某周三下午3点，由于某些原因导致数据库中的数据全部损坏，导致平台无法正常使用。

故障处理过程：

• 1）首先发布平台维护公告，宣布停服，全员抓紧实现进行数据修复。
• 2）然后评估数据损坏的程度：
  • 数据是否是全部丢失，如果数据全部丢失那么建议直接在线上环境的数据库中进行数据恢复。
  • 是否只有部分数据丢失，如果是部分表数据丢失，可能是运维、开发人员误操作导致，建议从备份中以及Binlog中导出某些表的数据，然后在预发布环境的数据库中进行数据恢复，恢复完成后待测试同事校验，没问题后还原到线上环境。
• 3）确定是数据全部丢失后，那么直接将周三凌晨的全量备份在线上数据库中还原，追溯到周三凌晨时的数据状态。
• 4）全备数据恢复完成后，从Binlog日志中截取从凌晨到故障发生时的Binlog日志，然后进行数据恢复。
• 5）由测试同事校验数据的一致性。
• 6）一切没问题后，恢复线上使用。

处理结果：

经过30~40分钟左右的处理，平台恢复。

下面我们开始模拟这个案例。

2.第一环节：周三凌晨进行数据全量备份

周三凌晨的自动数据备份：

[root@mysql ~]# mysqldump -uroot -p123456 -A -R --triggers -E --master-data=2 --single-transaction > /data/backup/all_db_bak-`date +%F`.sql

周三上班后检查数据备份情况。

1.检查备份是否存在
[root@mysql ~]# ll /data/backup/all_db_bak-`date +%F`.sql
-rw-r--r-- 1 root root 884223 7月   2 0:45 /data/backup/all_db_bak-2022-07-02.sql

2.检查备份中的内容

3.记录凌晨备份文件中的Binlog状态信息（备份开始时间点的Position号和GTID号）
#每天养成习惯，记录下备份文件中备份开始时间段的Binlog状态，当天出现数据问题，没有备份时，可以快速找到数据对应的Binlog位置。
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=14417;
SET @@GLOBAL.GTID_PURGED='e0a2c0cc-f835-11ec-8a3c-005056b791aa:1-66';

3.第二环节：模拟周三凌晨备份完之后到下午3点前的业务操作

模拟周三凌晨备份完之后到下午三点数据库异常之前，所产生的业务操作。

CREATE TABLE xscjb (
	xh INT COMMENT '学号',
	xm VARCHAR ( 20 ) COMMENT '姓名',
	ywcj INT COMMENT '语文成绩',
	sxcj INT COMMENT '数学成绩',
	yycj INT COMMENT '英语成绩'
) COMMENT '学生成绩表';
    
insert into xscjb VALUES (1, '小明', 45, 75, 93 );
insert into xscjb VALUES (2, '小红' , 47, 56, 25);
insert into xscjb VALUES (3, '小兰', 82, 91, 89);
insert into xscjb VALUES (4, '小黄', 88, 75, 66);
insert into xscjb VALUES (5, '小李', 93, 96, 91);
insert into xscjb VALUES (6, '小江', 97, 67, 65);
insert into xscjb VALUES (7, '小王', 75, 58, 32);

update xscjb set ywcj = '100' where xh = '7';
update xscjb set sxcj = '77' where xm = '小兰';
update xscjb set yycj = '99' where xm = '小王';

delete from xscjb where xh = '4';

数据库异常时刻，xscjb业务表最后的样子。

4.第三环节：模拟数据库异常数据丢失导致平台无法使用

模拟数据库文件损坏，数据丢失，导致平台无法使用。

直接将平台的数据库删除就行了。
mysql> drop database db_1;
[root@mysql ~]# rm -rf /data/mysql/db_1/

模拟结果：平台库数据库全部损坏，已崩，数据库实例还能用。（本次模拟过程中，没有将数据库所有文件删除，是因为Binlog也在这个路径，如果模拟数据库文件全部损坏，那么修复的时候就需要重新初始化数据库的，Binlog也会被覆盖，因此这里只模拟平台库数据库文件损坏，全部丢失。）

如果只删除了磁盘中某个数据库的所有文件，那么在交互式中是无法删除这个数据库中，需要先重启MySQL实例，然后才能进行删除，因此再删库时，要先删库，再删磁盘文件。

5.第四环节：发布停服公告全员进入数据恢复处理阶段

此时已经下午3点了，数据库突然损坏，平台库文件由于某某全部损坏，导致平台无法访问。

停服公告已发布，下面开始进入问题处理解决阶段。

经过一番分析后，确定平台对应的数据库文件全部损坏，该库的数据全部丢失，不是部分表数据丢失，下面需要紧急进入数据修复阶段，预计耗时未知！！！！

6.第五环节：数据加急恢复过程中！

6.1.还原平台库的今日凌晨时的全量备份数据

1.找到今日凌晨时的数据备份文件。
[root@mysql ~]# ll /data/backup/all_db_bak-2022-07-02.sql 
-rw-r--r-- 1 root root 884223 7月   2 0:45 /data/backup/all_db_bak-2022-07-02.sql

2.直接在线上生产库中还原凌晨备份的数据。
mysql> set sql_log_bin=0;
mysql> source /data/backup/all_db_bak-2022-07-02.sql 

此时已经从全量备份中恢复了平台库，但是全量备份只包含今日凌晨之前的数据，今日凌晨到现在的数据还没有恢复。

6.2.从Binlog中还原凌晨到现在时刻没有备份的数据

我们已经从全备中将平台库的数据恢复到了今日凌晨时的状态，但是从凌晨到故障发生前的数据还没来得及备份就丢失了，下面从Binlog中恢复没有备份的数据。

下面我们需要从Binlog中截取今日凌晨到现在时刻的Binlog，我们都知道Binlog日志截取最麻烦的就是找起点和终点，终点很好找，平台库已经挂了，那么Binlog中最后一个GTID事务号，就是终点。

由于我们使用mysqldump备份时，增加了--maste-data=2这个参数，此参数会在备份文件中帮我们记录：从备份开始的时间算，Binlog中最近的一个GTID号、当前使用的Binlog日志名称、Binlog中事件的最近一个Position标识位号，有了这三个信息之后，找起点不再是难事，我们可以非常快速的指定我们要截取那些Binlog日志。

GTID号更加好找，我们以GTID号截取Binlog数据。

1）确定要截取的Binlog日志GTID号范围

下面的这两行就是从备份文件中找到的关于Binlog日志的状态。从中我们可以得知当前使用的Binlog是mysql-bin.000012，最近一个事件的Position号是14417，最近的一个GTID号是66。

并且记录的GTID号格式是e0a2c0cc-f835-11ec-8a3c-005056b791aa:1-66这样子，其实也就告诉了我们在这个备份文件中，将GTID号1-66这个范围之间所有产生的日志都进行备份了。

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=14417;
SET @@GLOBAL.GTID_PURGED='e0a2c0cc-f835-11ec-8a3c-005056b791aa:1-66';

刚刚我们也做了全库备份的数据还原，也就表示GTID号1-66这个范围的数据已经全部被恢复了，但是从67GTID号开始一直到数据库崩溃时的这个范围是没有被恢复的。

下面我们去看一下Binlog的事件信息，看看GTID号67是不是新数据，然后再获取数据库崩溃时的最新GTID号。

在GTID号67这里，我们看到创建了一张新表，这个操作就是新的业务逻辑，没错就是从这里截取，备份文件中记录的是准确无误的。

起点找到了是GTID 67号，那么接着还早终点，直接翻到最后，最后一个GTID号就是数据库崩溃时的最后一个事务，最后一个GTID号是78。

2）截取凌晨到当前时间产生的Binlog日志

在上一步已经确定了GTID起点是67，终点是78，下面开始截取这一部分的Binlog日志

[root@mysql ~]# mysqlbinlog --skip-gtids --include-gtids='e0a2c0cc-f835-11ec-8a3c-005056b791aa:67-78' /data/mysql/mysql-bin.000012 > /data/backup/sjbkjd-binlog.sql

3）从Binlog中恢复从凌晨到数据库崩溃时间段的数据

mysql> set sql_log_bin=0;
mysql> source /data/backup/sjbkjd-binlog.sql

7.第六环节：校验数据的准确性

数据库全备已经恢复了，从凌晨到数据库崩溃时间段的数据也从Binlog中恢复了，下面由测试同事检测数据的准确性。

mysql> select * from db_1.xscjb;
+------+--------+------+------+------+
| xh   | xm     | ywcj | sxcj | yycj |
+------+--------+------+------+------+
|    1 | 小明   |   45 |   75 |   93 |
|    2 | 小红   |   47 |   56 |   25 |
|    3 | 小兰   |   82 |   77 |   89 |
|    5 | 小李   |   93 |   96 |   91 |
|    6 | 小江   |   97 |   67 |   65 |
|    7 | 小王   |  100 |   58 |   99 |
+------+--------+------+------+------+
6 rows in set (0.00 sec)

数据准确无误，全部恢复成功。

7.第七环节：平台恢复上线

此时数据已经全部恢复了，平台也能正常使用了，宣告再次上线。