作者：雷文霆

愛可生華東交付服務部 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_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED='7b3a89d7-4866-11ec-b99b-0242ac148602:1-659286,
7b3adf4b-4866-11ec-b9e0-0242ac148604:1-3338';
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */;
USE `test01`;
--
-- Table structure for table `table01`
--
DROP TABLE IF EXISTS `table01`;
CREATE TABLE `table01` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
LOCK TABLES `table01` WRITE;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

# 模擬遷移操作
在主庫執行：
drop database test01;這個是DBA 幫助執行的
source /opt/test01_set-gtid-purgedis_on.sql;

# 登錄從庫檢查復制狀態 [正常的，因為還沒有主庫沒有涉及到這個庫的操作]
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.134.2
Master_User: universe_op
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 34139867
Relay_Log_File: mysql-relay.000003
Relay_Log_Pos: 1646803
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

# 在主庫模擬正常的業務操作
insert into table01(id)values(1);
 select * from table01;
+----+
| id |
+----+
|  1 |
+----+
# 從庫異常信息(SQL線程異常，顯示操作的表不存在，原因是導入的數據沒有記錄到binlog)
show slave statusG
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.20.134.2
 Master_User: universe_op
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000002
 Read_Master_Log_Pos: 34704375
 Relay_Log_File: mysql-relay.000003
 Relay_Log_Pos: 2180665
 Relay_Master_Log_File: mysql-bin.000002
 Slave_IO_Running: Yes
 Slave_SQL_Running: No
 Replicate_Do_DB:
 Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 1146
 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '7b3a89d7-4866-11ec-b99b-0242ac148602:662273' at master log mysql-bin.000002, end_log_pos 34674016. See error log and/or performance_schema.replication_Applier_status_by_worker table for more details about this failure or others, if any.

select * from performance_schema.replication_applier_status_by_workerG
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: 7b3a89d7-4866-11ec-b99b-0242ac148602:662273
LAST_ERROR_NUMBER: 1146
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '7b3a89d7-4866-11ec-b99b-0242ac148602:662273' at master log mysql-bin.000002, end_log_pos 34674016; Error executing row event: 'Table 'test01.table01' doesn't exist'
LAST_ERROR_TIMESTAMP: 2021-11-22 16:27:10

第2套：
# 模擬備份操作：
mysqldump -h172.20.134.2 -uadmin -P3306 -p123456   --single-transaction --set-gtid-purged=off  --databases test01  > /opt/test01_set-gtid-purgedis_off.sql

備份文件內容：
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */;
USE `test01`;
DROP TABLE IF EXISTS `table01`;
CREATE TABLE `table01` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
LOCK TABLES `table01` WRITE;
INSERT INTO `table01` VALUES (1),(2),(3),(4);
UNLOCK TABLES;

# 模擬遷移操作
在主庫執行：
drop database test01; #這個是DBA 幫助執行的

(小插曲：)
source /opt/test01_set-gtid-purgedis_on.sql; # 此處導入了第一次的備份，發現有如下報錯，才添加了--set-gtid-purged=off備份參數(這就是為什么在主庫的binlog中看到了，兩個事務，均執行了相同的drop database 操作)
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

binlog的異?，F象：(出現了)
SET @@SESSION.GTID_NEXT= '7b3a89d7-4866-11ec-b99b-0242ac148602:660309'
drop database test01  # 執行者是DBA
----中間無創建語句----
SET @@SESSION.GTID_NEXT= '7b3a89d7-4866-11ec-b99b-0242ac148602:666084'
drop database test01  # 執行者是遷移人員

source /opt/test01_set-gtid-purgedis_off.sql; 再次執行了導入操作，主庫寫入了數據，并記錄到了binlog中

# 從庫異常信息(SQL線程異常，顯示操作的庫不存在,原因是第一次導入的數據沒有記錄binlog,加了--set-gtid-purged=off備份參數之后，記錄了binlog。第一次導入的數據對從庫是不可見的，所以主庫在執行完第一次導入之后的drop database 在從庫就會顯示不存在這個庫)
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.134.2
Master_User: universe_op
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 36372942
Relay_Log_File: mysql-relay.000004
Relay_Log_Pos: 1696956
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1008
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '7b3a89d7-4866-11ec-b99b-0242ac148602:666084' at master log mysql-bin.000002, end_log_pos 36372942. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
select * from performance_schema.replication_applier_status_by_workerG
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: 7b3a89d7-4866-11ec-b99b-0242ac148602:666084
LAST_ERROR_NUMBER: 1008
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '7b3a89d7-4866-11ec-b99b-0242ac148602:666084' at master log mysql-bin.000002, end_log_pos 36372942; Error 'Can't drop database 'test01'; database doesn't exist' on query. Default database: 'test01'. Query: 'drop database test01'
LAST_ERROR_TIMESTAMP: 2021-11-22 17:15:50

4 分析過程

第1套：
1.記錄從庫復制異常信息，報錯為記錄不存在，屬于數據不一致。
2.咨詢，各方的操作記錄。
3.解析主庫的binlog文件，驗證數據的導入情況。[從遷移開始到應用服務開啟，之間沒有數據記錄]
4.檢查備份命令和備份文件內容。[這里就會發現數據是不記錄binlog的方式導入的]

第2套：
和第1套不同的是，在主庫的binlog中兩條連續事務，記錄了相同的drop database 操作[在現場]。
第一次刪除為DBA執行的，遷移人員導入之后，發現有報錯，為了方便第二次導入，執行了第二次刪除。均屬于正常操作。

5 結論

對于--set-gtid-purged參數

1.Mysqldump中sql_log_bin默認是關閉的。
如果數據要導入主庫，可以通過--set-gtid-purged=off備份參數，不會在備份文件中記錄SET @@GLOBAL.GTID_PURGED的值。
不需要reset master可直接導入。
2.全備的情況下不添加，--set-gtid-purged 默認為ON(常用于重做主從)，部分備份時添加 --set-gtid-purged=off(可在主上做部分恢復，在從上不推薦使用，即便是通過SET @@SESSION.SQL_LOG_BIN= 0;source alldb.sql;的方式導入，之后的數據更新可能會導致復制出現數據已存在的異常。也不適應與備份文件較大的情況。)
`正確的操作是導入從庫之后，主從數據可以保持一致，然后reset master;set @@global.gtid_purged='gtid段'; change master to重建復制。`
3.備份文件默認，庫是不存在就創建，表是存在就刪除重建。
4.對于需要導入主庫的場景，建議開啟set-gtid-purged=off參數，導入數據時，記錄binlog(更新事務號和Position)，不影響復制。
5.對于需要導入從庫的場景，建議保持默認或是不設置此參數，導入數據時，不記錄binlog。

# 以下摘自官網：[link]https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_master-data
The --set-gtid-purged option has the following effect on binary logging when the dump file is reloaded:

--set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not added to the output.

--set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to the output.

--set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added to the output if GTIDs are enabled on the server you are backing up (that is, if AUTO evaluates to ON).

6 解決辦法

第1套：因為備份文件是不記錄binlog的，所以可以在從庫執行同樣的source /opt/test01_set-gtid-purgedis_on.sql;
補齊數據
之后重啟SQL線程：
stop slave SQL_THREAD;
start slave SQL_THREAD;

第2套：因為第一次導入的數據不會傳輸到從庫，且已手動刪除。所以只需要處理第二次導入的數據，是記錄binlog的。報錯信息是因為從庫沒有這個庫。我們需要跳過SET @@SESSION.GTID_NEXT= '7b3a89d7-4866-11ec-b99b-0242ac148602:666084'
drop database test01  # 執行者是遷移人員，逃過這個事物。

# 確認從庫執行的數據信息：
show master statusG
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 27756998
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 7b3a89d7-4866-11ec-b99b-0242ac148602:1-666083,
7b3adf4b-4866-11ec-b9e0-0242ac148604:1-3338

Retrieved_Gtid_Set: 7b3a89d7-4866-11ec-b99b-0242ac148602:657353-666084
Executed_Gtid_Set: 7b3a89d7-4866-11ec-b99b-0242ac148602:1-666083,
7b3adf4b-4866-11ec-b9e0-0242ac148604:1-3338

stop slave;
SET @@SESSION.GTID_NEXT= '7b3a89d7-4866-11ec-b99b-0242ac148602:666084';
BEGIN; COMMIT;# 將該gtid設為空事務
show variables like '%gtid_next%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| gtid_next     | AUTOMATIC |
+---------------+-----------+
start slave;

7 使用建議

# 導入Mysqldump備份時，我們需要評估導入方式。
1.source 還是 mysql客戶端方式。
2.導入的主機是 主庫 or 從庫。
3.導入前后記錄數據庫的狀態信息，方便問題排查。