MySQL中slave_exec_mode參數詳解
今天無意當中看到參數slave_exec_mode，從手冊里的說明看出該參數和MySQL復制相關，是可以動態(tài)修改的變量，默認是STRICT模式（嚴格模式），可選值有IDEMPOTENT模式（冪等模式）。設置成IDEMPOTENT模式可以讓從庫避免1032（從庫上不存在的鍵）和1062（重復鍵，需要存在主鍵或則唯一鍵）的錯誤，該模式只有在ROW EVENT的binlog模式下生效，在STATEMENT EVENT的binlog模式下無效。IDEMPOTENT模式主要用于多主復制和NDB CLUSTER的情況下，其他情況不建議使用。從上面的介紹來看，這個參數的讓從庫跳過指定的錯誤，那問題來了：
1：和 sql_slave_skip_counter 比，有什么好處？
2：和 slave-skip-errors = N比，有什么好處？
帶著這2個問題，本文來進行相關的測試和說明。
環(huán)境：
MySQL版本：Percona MySQL 5.7
復制模式：ROW，沒有開啟GTID
測試：
① 1062 錯誤：Could not execute ... event on table db.x; Duplicate entry 'xx' for key 'PRIMARY', Error_code: 1062;
主從上的測試表結構：
CREATE TABLE `x` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
主從上的表記錄：
M：
select * from x; +----+ | id | +----+ | 2 | | 3 | +----+ 2 rows in set (0.01 sec)
S：
select * from x; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec)
主從上的表記錄本來就不一致了，主上缺少了id=1的記錄。
此時從上的slave_exec_mode為默認的STRICT模式：
show variables like 'slave_exec_mode'; +-----------------+--------+ | Variable_name | Value | +-----------------+--------+ | slave_exec_mode | STRICT | +-----------------+--------+ 1 row in set (0.00 sec)
M上的binlog模式為：
show variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec)
在M上執(zhí)行：
insert into x values(1),(4),(5); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
因為從上已經存在了id=1的記錄，此時從的復制就報了1062的錯誤:
Last_SQL_Errno: 1062 Last_SQL_Error: Could not execute Write_rows event on table dba_test.x; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin-3306.000006, end_log_pos 7124
出現這個錯誤時，大家的一致做法就是執(zhí)行：sql_slave_skip_counter=N。
1、set global sql_slave_skip_counter=N中的N是指跳過N個event 2、最好記的是N被設置為1時，效果跳過下一個事務。 3、跳過第N個event后，位置若剛好落在一個事務內部，則會跳過這整個事務 4、一個insert/update/delete不一定只對應一個event，由引擎和日志格式決定
sql_slave_skip_counter的單位是“event”，很多人認為該參數的單位是“事務”，其實是錯誤的，因為一個事務里包含了多個event，跳過N個可能還是在同一個事務當中。對于上面出現1062的錯誤，把N設置成1～4效果是一樣的，都是跳過一個事務。因為執(zhí)行的SQL生成了4個event：
show binlog events in 'mysql-bin-3306.000006' from 6950; +-----------------------+------+------------+-----------+-------------+---------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-----------------------+------+------------+-----------+-------------+---------------------------------+ | mysql-bin-3306.000006 | 6950 | Query | 169 | 7026 | BEGIN | | mysql-bin-3306.000006 | 7026 | Table_map | 169 | 7074 | table_id: 707 (dba_test.x) | | mysql-bin-3306.000006 | 7074 | Write_rows | 169 | 7124 | table_id: 707 flags: STMT_END_F | | mysql-bin-3306.000006 | 7124 | Xid | 169 | 7155 | COMMIT /* xid=74803 */ | +-----------------------+------+------------+-----------+-------------+---------------------------------+ 4 rows in set (0.00 sec)
所以處理該錯誤的方法有：
1：skip_slavesql_slave_skip_counter
stop slave; Query OK, 0 rows affected (0.00 sec) set global sql_slave_skip_counter=[1-4]; Query OK, 0 rows affected (0.00 sec) start slave; Query OK, 0 rows affected (0.00 sec)
2：在配置文件里指定slave-skip-errors=1062（需要重啟）
這2種方法都能讓復制恢復正常，但是會讓主從數據不一致（謹慎使用），讓從庫丟失了id=4和5的記錄。并且第2種方法還需要重啟數據庫，這時本文介紹的slave_exec_mode參數就派上用場了。在從庫上設置該參數：
set global slave_exec_mode='IDEMPOTENT'; Query OK, 0 rows affected (0.00 sec) stop slave; Query OK, 0 rows affected (0.00 sec) start slave; Query OK, 0 rows affected (0.00 sec)
同樣在主上執(zhí)行：
insert into x values(1),(4),(5);
可以驚喜的發(fā)現主從數據是同步的，沒有出現復制異常：
M： select * from x; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ 5 rows in set (0.00 sec) S： select * from x; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ 5 rows in set (0.01 sec)
上面的測試可以看到，參數設置成slave_exec_mode='IDEMPOTENT' 后，可以跳過出一個錯誤的event。
② 1032錯誤：Could not execute ... event on table db.x; Can't find record in 'x', Error_code: 1032;
這個錯誤的出現是因為ROW模式下的復制，對數據的一致性有了很嚴的要求
主從上的測試表結構：
CREATE TABLE `x` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
主從上的表記錄：
M：
select * from x; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec)
S：
select * from x; +----+ | id | +----+ | 1 | | 3 | +----+ 2 rows in set (0.00 sec)
主從上的表記錄本來就不一致了，從上缺少了id=2的記錄。此時從上的slave_exec_mode為默認的STRICT模式：
show variables like 'slave_exec_mode'; +-----------------+--------+ | Variable_name | Value | +-----------------+--------+ | slave_exec_mode | STRICT | +-----------------+--------+ 1 row in set (0.00 sec)
M上的binlog模式為：
show variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec)
在M上執(zhí)行：
BEGIN; INSERT INTO x SELECT 4; DELETE FROM x WHERE id = 2; INSERT INTO x SELECT 5; COMMIT;
因為從上不存在了id=2的記錄，此時從的復制就報了1032的錯誤:
Last_SQL_Errno: 1032 Last_SQL_Error: Could not execute Delete_rows event on table dba_test.x; Can't find record in 'x', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin-3306.000006, end_log_pos 12102
同樣的，在上面測試中說明的2種方法可以讓復制正常，但是數據也一樣會丟失。丟失了id=4和5的記錄，繼續(xù)在從庫上設置該參數：
set global slave_exec_mode='IDEMPOTENT'; Query OK, 0 rows affected (0.00 sec) stop slave; Query OK, 0 rows affected (0.00 sec) start slave; Query OK, 0 rows affected (0.00 sec)
在M上執(zhí)行同樣的操作：
BEGIN; INSERT INTO x SELECT 4; DELETE FROM x WHERE id = 2; INSERT INTO x SELECT 5; COMMIT;
也可以驚喜的發(fā)現主從數據是同步的，沒有出現復制異常。
注意：slave_exec_mode='IDEMPOTENT'不能對DDL操作冪等，并且也不能對字段長度不同導致的錯誤進行冪等，如把例子中的從庫表的id字段類型int改成bigint。并且只能在binlog_format為ROW的模式下使用，而且只能對1032和1062進行冪等模式。
總結：
對于上面的測試總結，針對slave_exec_mode參數，它可以跳過1062和1032的錯誤，并且不影響同一個事務中正常的數據執(zhí)行。如果是多個SQL組成的事務，則可以跳過有問題的event。
看著這個參數很不錯，但手冊上說明不建議在普通的復制環(huán)境中開啟。對于NDB以外的存儲引擎，只有在確定可以安全地忽略重復鍵錯誤和沒有鍵的錯誤時，才應使用IDEMPOTENT模式。這參數是專門針對NBD Cluster進行設計的，NBD Cluster模式下，該參數只能設置成IDEMPOTENT模式。所以要根據自己的應用場景來決定，正常情況下，主從是一致的，有任何錯誤發(fā)生都要報錯，不過在做特殊處理時，可以臨時開啟。
另外在GTID模式下的復制，sql_slave_skip_counter是不支持的，該模式下的復制可以自行測試。
相關文章
這篇文章主要介紹了MySQL 聲明變量及存儲過程的相關內容，小編覺得挺不錯的，這里分享給大家，需要的朋友可以參考下。2017-10-10
MySQL MaxCompute與AnalyticDB實現數據處理與轉換過程詳解
AnalyticDB MySQL（簡稱ads）與 MaxCompute（簡稱odps）進行數據轉換時，個別語法有差別，記錄下來，方便備查，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習吧2022-12-12
這篇文章主要介紹了Mysql優(yōu)化神器(推薦)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-12-12
MySQL深入詳解delete與Truncate及drop的使用區(qū)別
對于drop、truncate和delete雖然簡單,但是真要使用或者面試時候問到還是需要有一定的總結,下面這篇文章主要給大家介紹了關于mysql中drop、truncate與delete區(qū)別的相關資料,需要的朋友可以參考下2022-07-07
這篇文章主要介紹了一文教你學會定位線上MySQL鎖超時問題，文章圍繞主題展開詳細的內容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-08-08
這篇文章主要給大家介紹了關于MySQL聯表查詢的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-05-05
svm各種工具箱（先放著了，省的找起來麻煩^.^）2010-03-03
MySQL中使用PROFILING來查看SQL執(zhí)行流程的實現步驟
在MySQL中,PROFILING功能提供了一種方式來分析SQL語句的執(zhí)行時間,包括查詢執(zhí)行的各個階段,如發(fā)送、解析、優(yōu)化、執(zhí)行等,這對于診斷性能問題非常有用,本文給大家介紹了MySQL中使用PROFILING來查看SQL執(zhí)行流程的實現步驟,需要的朋友可以參考下2024-07-07
因很多同學安裝mysql總是出問題,所以下面這篇文章主要給大家介紹了關于MySQL安裝失敗的原因及解決步驟,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2022-06-06
這篇文章主要介紹了mysql如何比對兩個數據庫表結構的方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2019-09-09