故障分析 | MySQL 使用 Mysqldump 备份导入数据导致主从异常

作者：雷文霆

爱可生华东交付服务部 DBA 成员，主要负责Mysql故障处理及相关技术支持。爱好看书，电影。座右铭，每一个不曾起舞的日子，都是对生命的辜负。

本文来源：原创投稿

*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

---

1 环境

Mysql版本：5.7

架构：2套，1主1从

复制模式：基于GTID

有两套Mysql主从，开发侧的需求是进行某个数据库的迁移(可以理解为数据库替换)，操作为drop database test01，然后备份远程数据库test01,最后进行本地数据库恢复。
备份工具: Mysqldump
恢复方式：source 备份文件
第1套的备份参数：--single-transaction  --add-drop-table 
第2套的备份参数：--single-transaction  --add-drop-table --set-gtid-purged=off
以上备份参数是在故障处理时收集的背景信息，
对于Mysqldump建议加上 --single-transaction和--master-data=2。前者可实现innodb一致性备份,后者可以记录备份信息。

参数解释：
--single-transaction 将事务隔离级别设置为RR，并在备份数据之前向服务器发送SQL语句，显示开启一个事务快照。
--add-drop-table  默认开启， Add a DROP TABLE before each create。会在创建表前添加drop table语句(一般在追加表中数据时使用,比如归档)
# 备份文件类似：
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
--set-gtid-purged  默认为ON
是否在导出的sql 文件头部添加 set global gtid_purged='xxx:xxx' 信息。
# 默认为ON时，备份文件开头类似：
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0; # 需要重点关注的地方(导入不记录binlog)
-- GTID state at the beginning of the backup 
SET @@GLOBAL.GTID_PURGED='xxx:1-100';

# 使用 --set-gtid-purged=off 的特点
1.不用reset master就可以直接导入。
2.会在本地生成新的事务信息。(导入记录binlog)

2 问题描述

DBA接到应用迁移需求后，在主库上执行了drop database操作，直到备份前复制都是正常的。

第1套主从：没有添加--set-gtid-purged=off 选项
在主库source 备份文件之后，由于备份文件中[包含SET @@SESSION.SQL_LOG_BIN= 0;]，导入的数据没有记录binlog。
导致从库没有备份文件中的数据，之后复制会报SQL线程1146,数据不存在。

第2套主从：添加--set-gtid-purged=off 选项
在主库source 备份文件之后，由于备份文件中[不包含SET @@SESSION.SQL_LOG_BIN= 0;]导入的数据记录binlog。

3 复现步骤

第1套：
# 在主库上创建数据
create database test01;
use test01;
create table table01(id int primary key);
# 模拟备份操作：
mysqldump -h172.20.134.2 -uadmin -P3306 -p123456   --single-transaction  --databases test01  > /opt/test01_set-gtid-purgedis_on.sql

## 备份文件内容：
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_