Analysis of MySQL Dump GTID Settings and Replication Issues During Database Migration

Environment MySQL version 5.7, a primary‑secondary architecture with two sets (1 master, 1 slave) using GTID‑based replication.

有两套Mysql主从，开发侧的需求是进行某个数据库的迁移(可以理解为数据库替换)，操作为drop database test01，然后备份远程数据库test01,最后进行本地数据库恢复。
备份工具: Mysqldump
恢复方式：source 备份文件
第1套的备份参数：--single-transaction  --add-drop-table 
第2套的备份参数：--single-transaction  --add-drop-table --set-gtid-purged=off
...

Problem Description After the DBA executed drop database on the primary, the first replica did not record the imported data in the binlog because the dump file contained SET @@SESSION.SQL_LOG_BIN= 0; , leading to replication errors (error 1146) on the slave.

第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。

Reproduction Steps The article provides step‑by‑step commands to create a test database, dump it with different --set-gtid-purged options, import the dump on the master, and observe the resulting slave status.

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
...
source /opt/test01_set-gtid-purgedis_on.sql;
show slave status\G
...

Analysis The first set shows data inconsistency because the dump did not record binlog events. The second set, with --set-gtid-purged=off , records the transactions, so the slave can replicate correctly after the second import.

Conclusion For --set-gtid-purged :

1. mysqldump disables sql_log_bin by default. Use --set-gtid-purged=off when importing into the master to avoid SET @@GLOBAL.GTID_PURGED in the dump.
2. For full backups, keep the default (ON). For partial backups, consider OFF only when importing to the master.
3. After importing to the slave, reset master and set GTID_PURGED appropriately before rebuilding replication.

Solution - For the first set, re‑execute the dump on the slave and restart the SQL thread. - For the second set, skip the GTID_NEXT transaction that caused the error, then resume replication.

stop slave SQL_THREAD;
start slave SQL_THREAD;
...
SET @@SESSION.GTID_NEXT='7b3a89d7-4866-11ec-b99b-0242ac148602:666084';
BEGIN; COMMIT;
start slave;

Recommendations When importing mysqldump files, evaluate the import method (source vs. mysql client), the target host (master or slave), and record database state before and after import to facilitate troubleshooting.