MySQL復(fù)制問(wèn)題的三個(gè)參數(shù)分析
今天星期二，早上居然起晚了，上班遲到了，簡(jiǎn)直是。。。廢話(huà)不多說(shuō)，在昨天的文章中，我們提到了三個(gè)參數(shù)，分別是：
- slave_exec_mode參數(shù)；
- sql_slave_skip_counter=N參數(shù);
- slave-skip-errors=N參數(shù)。
這三個(gè)參數(shù)都可以解決并行復(fù)制中的一些指定的錯(cuò)誤，例如duplicate key 1062錯(cuò)誤等，今天我們簡(jiǎn)單試驗(yàn)一下，這三個(gè)參數(shù)的區(qū)別：
01 sql_slave_skip_counter參數(shù)
這個(gè)參數(shù)的設(shè)置主要是為了跳過(guò)某些錯(cuò)誤的"event",注意這里的用詞是event而不是事務(wù)，是因?yàn)樗谋举|(zhì)是跳過(guò)一個(gè)一個(gè)事件，需要注意的是，這個(gè)參數(shù)需要在偏移量復(fù)制模式中使用，如果使用的是gtid的復(fù)制模式，則不可以使用這個(gè)參數(shù)。我們來(lái)看例子，首先搭建一套復(fù)制關(guān)系：
master 10.30.124.68
slave 10.30.124.128
這倆實(shí)例互為主從。我們創(chuàng)建測(cè)試表test.yeyz，并插入一些數(shù)據(jù)，其中id為主鍵，具有唯一性，如下：
master上
mysql:(none) 22:25:56>>select * from test.yeyz; +----+------+ | id | age | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +----+------+ 4 rows in set (0.00 sec)
slave上
mysql:(none) 22:25:38>>select * from test.yeyz; +----+------+ | id | age | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +----+------+ 5 rows in set (0.00 sec)
我們可以發(fā)現(xiàn)，從節(jié)點(diǎn)的數(shù)據(jù)比主節(jié)點(diǎn)多一條，多了id=5的記錄，然后我們?cè)谥鞴?jié)點(diǎn)上插入數(shù)據(jù)：
mysql:(none) 22:26:06>>insert into test.yeyz values (5,5),(6,6); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
此時(shí)觀(guān)察從節(jié)點(diǎn)：
mysql:(none) 22:26:34>>show slave status\G Master_Host: 10.30.124.68 Master_User: dba_repl Master_Port: 4306 Connect_Retry: 60 Master_Log_File: mysqlbin.000002 Read_Master_Log_Pos: 523 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 319 Relay_Master_Log_File: mysqlbin.000002 Slave_IO_Running: Yes Slave_SQL_Running: No Last_Errno: 1062 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction 'ANONYMOUS' at master log mysqlbin.000002, end_log_pos 492. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0
可以發(fā)現(xiàn)，從節(jié)點(diǎn)已經(jīng)SQL線(xiàn)程斷開(kāi)了， 這個(gè)時(shí)候，在主節(jié)點(diǎn)上查詢(xún)這個(gè)錯(cuò)誤position 492處的binlog，可以看到：
mysql:(none) 22:30:28>>show binlog events in 'mysqlbin.000002' from 194; +-----------------+-----+----------------+-----------+-------------+--------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-----------------+-----+----------------+-----------+-------------+--------------------------------------------+ | mysqlbin.000002 | 194 | Anonymous_Gtid | 192 | 259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysqlbin.000002 | 259 | Query | 192 | 327 | BEGIN | | mysqlbin.000002 | 327 | Rows_query | 192 | 391 | # insert into test.yeyz values (5,5),(6,6) | | mysqlbin.000002 | 391 | Table_map | 192 | 439 | table_id: 108 (test.yeyz) | | mysqlbin.000002 | 439 | Write_rows | 192 | 492 | table_id: 108 flags: STMT_END_F | | mysqlbin.000002 | 492 | Xid | 192 | 523 | COMMIT /* xid=38 */ | +-----------------+-----+----------------+-----------+-------------+--------------------------------------------+ 6 rows in set (0.00 sec)
從上面的binlog可以看出來(lái)，我們的一個(gè)insert操作實(shí)際上生成了5個(gè)enent，分別對(duì)應(yīng)的pos是從259~492，關(guān)于event，待會(huì)兒再說(shuō)。
因?yàn)橹鞴?jié)點(diǎn)上插入了id=5的記錄，跟從節(jié)點(diǎn)上的記錄沖突了，查看錯(cuò)誤日志，可以發(fā)現(xiàn)：
Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 492 | 2019-07-16 22:26:25
我們通過(guò)sql_slave_skip_counter參數(shù)的設(shè)置來(lái)解決這個(gè)問(wèn)題，步驟如下：
mysql:(none) 22:29:32>>stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql:(none) 22:32:45>>set global sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec) mysql:(none) 22:33:06>>start slave;
在昨天的文章中我們說(shuō)過(guò)，sql_slave_skip_counter后面跟的值是event的個(gè)數(shù)，所以這里我們相當(dāng)于跳過(guò)了一個(gè)event，mysql中規(guī)定，如果跳過(guò)一個(gè)event之后，還在某一個(gè)事務(wù)里面，那么會(huì)繼續(xù)跳過(guò)這個(gè)事務(wù)。
使用這個(gè)參數(shù)跳過(guò)一個(gè)event之后，我們?cè)賮?lái)看從庫(kù)表中的數(shù)據(jù)和復(fù)制情況，可以看到：
slave表：
mysql:(none) 22:33:10>>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.30.124.68 Master_User: dba_repl Master_Port: 4306 Connect_Retry: 60 Master_Log_File: mysqlbin.000002 Read_Master_Log_Pos: 523 Relay_Log_File: slave-relay-bin.000003 Relay_Log_Pos: 319 Relay_Master_Log_File: mysqlbin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes mysql:(none) 22:33:16>>select * from test.yeyz; +----+------+ | id | age | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +----+------+ 5 rows in set (0.00 sec)
看看master表：
mysql:(none) 22:33:36>>select * from test.yeyz; +----+------+ | id | age | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | +----+------+ 6 rows in set (0.00 sec)
可以發(fā)現(xiàn)，master中數(shù)據(jù)插入成功，而slave中數(shù)據(jù)插入失敗，也就是說(shuō)：
該參數(shù)跳過(guò)錯(cuò)誤的時(shí)候，會(huì)導(dǎo)致主從的數(shù)據(jù)不一致。
02 slave_skip_errors參數(shù)
這個(gè)參數(shù)是跳過(guò)制定的錯(cuò)誤，也就是說(shuō)，需要我們?cè)O(shè)置對(duì)應(yīng)的error_code，從下面的日志中的內(nèi)容可以看出，error_code的值為1062
Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 492 | 2019-07-16 22:26:25
我們需要手動(dòng)將這個(gè)參數(shù)的值也該為1062，需要注意的是，這個(gè)參數(shù)的改動(dòng)需要重啟mysql服務(wù)，因?yàn)檫@個(gè)參數(shù)是一個(gè)只讀的參數(shù)。
修改后的情況如下：
mysql--dba_admin@127.0.0.1:(none) 22:38:55>>show variables like '%errors%'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_connect_errors | 1000000 | | slave_skip_errors | 1062 | +--------------------+---------+ 2 rows in set (0.01 sec)
此時(shí)我們更新master表和slave表的數(shù)據(jù)，更新后的情況如下：
master：
mysql:(none) 22:39:15>>select * from test.yeyz; +----+------+ | id | age | +----+------+ | 1 | 1 || 2 | 2 | | 3 | 3 || 4 | 4 | | 5 | 5 || 6 | 6 | +----+------+ 6 rows in set (0.00 sec)
slave上：
mysql:(none) 22:40:15>>select * from test.yeyz; +----+------+ | id | age | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | +----+------+ 7 rows in set (0.00 sec)
我們發(fā)現(xiàn)，slave表比master表多一條數(shù)據(jù)，也就是id=7的記錄，此時(shí)我們?cè)趍aster上執(zhí)行：
mysql:(none) 22:34:15>>insert into test.yeyz values (7,7),(8,8); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
查看slave上面的復(fù)制情況和數(shù)據(jù)情況，如下：
mysql:(none) 22:39:05>>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.30.124.68 Master_User: dba_repl Master_Port: 4306 Connect_Retry: 60 Master_Log_File: mysqlbin.000002 Read_Master_Log_Pos: 852 Relay_Log_File: slave-relay-bin.000005 Relay_Log_Pos: 648 Relay_Master_Log_File: mysqlbin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: mysql:(none) 22:40:15>>select * from test.yeyz; +----+------+ | id | age | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | +----+------+ 7 rows in set (0.00 sec)
可以看到，復(fù)制沒(méi)有出現(xiàn)錯(cuò)誤，即使從庫(kù)上已經(jīng)有id=7的記錄。而且發(fā)現(xiàn)，從庫(kù)的數(shù)據(jù)跟之前保持一致，也就是說(shuō)，主庫(kù)插入的id=8的記錄沒(méi)有被同步過(guò)來(lái)。
總結(jié)一下：該參數(shù)在跳過(guò)復(fù)制錯(cuò)誤的時(shí)候，需要重啟mysql服務(wù)，然后可能導(dǎo)致主從數(shù)據(jù)不一致。
03 slave-skip-errors=N參數(shù)
再看最后一個(gè)參數(shù)，這個(gè)參數(shù)表示的是并行復(fù)制過(guò)程中的從庫(kù)復(fù)制模式，默認(rèn)值是strict嚴(yán)格模式，和上面一樣，我們先看主庫(kù)和從庫(kù)的數(shù)據(jù)情況：
master數(shù)據(jù)：
mysql:(none) 22:39:20>>select * from test.yeyz; +----+------+ | id | age | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | | 8 | 8 | +----+------+ 8 rows in set (0.00 sec)
slave數(shù)據(jù)：
mysql:(none) 22:42:46>>select * from test.yeyz; +----+------+ | id | age | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | | 8 | 8 | | 9 | 9 | +----+------+ 9 rows in set (0.00 sec)
此時(shí)我們?cè)趶膸?kù)上修改參數(shù)如下：
mysql:(none) 22:42:59>>show variables like '%exec%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | gtid_executed_compression_period | 1000 | | max_execution_time | 0 | | rbr_exec_mode | STRICT | | slave_exec_mode | STRICT | +----------------------------------+--------+ 4 rows in set (0.00 sec) mysql:(none) 22:44:05>>set global slave_exec_mode='IDEMPOTENT'; Query OK, 0 rows affected (0.00 sec) mysql:(none) 22:44:10>>show variables like '%exec%'; +----------------------------------+------------+ | Variable_name | Value | +----------------------------------+------------+ | gtid_executed_compression_period | 1000 | | max_execution_time | 0 | | rbr_exec_mode | STRICT | | slave_exec_mode | IDEMPOTENT | +----------------------------------+------------+ 4 rows in set (0.00 sec)
修改完參數(shù)，我們?cè)谥鲙?kù)上進(jìn)行insert操作：
insert into test.yeyz values (9,9),(10,10);
查看從庫(kù)的復(fù)制狀態(tài)和數(shù)據(jù)情況，如下：
mysql:(none) 22:44:14>>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.30.124.68 Master_User: dba_repl Master_Port: 4306 Connect_Retry: 60 Master_Log_File: mysqlbin.000002 Read_Master_Log_Pos: 1183 Relay_Log_File: slave-relay-bin.000007 Relay_Log_Pos: 650 Relay_Master_Log_File: mysqlbin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes 1 row in set (0.00 sec) mysql:(none) 22:44:38>>select * from test.yeyz; +----+------+ | id | age | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | | 8 | 8 | | 9 | 9 | | 10 | 10 | +----+------+ 10 rows in set (0.00 sec)
可以發(fā)現(xiàn)，既沒(méi)有出現(xiàn)復(fù)制錯(cuò)誤，主庫(kù)上插入的數(shù)據(jù)也同步過(guò)來(lái)了。
總結(jié)一下：
- slave_exec_mode參數(shù)；
- sql_slave_skip_counter=N參數(shù);
- slave-skip-errors=N參數(shù)。
這三個(gè)參數(shù)都能解決復(fù)制過(guò)程中的不一致情況，區(qū)別如下：
slave_exec_mode參數(shù)可以保證主從數(shù)據(jù)一致，其他兩個(gè)不可以。
slave-skip-errors參數(shù)可以跳過(guò)制定的錯(cuò)誤，但是需要重啟實(shí)例，不能保證數(shù)據(jù)一致。
sql_slave_skip_counter參數(shù)需要在偏移量的復(fù)制模式下使用，不能保證數(shù)據(jù)一致。
以上就是MySQL復(fù)制問(wèn)題的三個(gè)參數(shù)分析的詳細(xì)內(nèi)容，更多關(guān)于MySQL復(fù)制問(wèn)題的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
wampserver下mysql導(dǎo)入數(shù)據(jù)庫(kù)的步驟
這篇文章主要介紹了wampserver下mysql導(dǎo)入數(shù)據(jù)庫(kù)的步驟,需要的朋友可以參考下2016-08-08
這篇文章主要介紹了MySQL中的權(quán)限,包括各個(gè)權(quán)限所能操作的事務(wù)以及操作權(quán)限的一些常用命令語(yǔ)句,需要的朋友可以參考下2015-05-05
MySQL錯(cuò)誤:Can‘t?connect?to?MySQL?server?on?localhost解決辦法
這篇文章主要給大家介紹了關(guān)于MySQL錯(cuò)誤:Can‘t?connect?to?MySQL?server?on?localhost的解決辦法,文中介紹的方法分多種情況,通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-05-05
詳解MySQL的慢查詢(xún)?nèi)罩竞湾e(cuò)誤日志
這篇文章主要詳細(xì)介紹了MySQL的慢查詢(xún)?nèi)罩竞湾e(cuò)誤日志,文中通過(guò)代碼示例講解的非常詳細(xì),對(duì)大家學(xué)習(xí)和了解MySQL的慢查詢(xún)?nèi)罩竞湾e(cuò)誤日志有一定的幫助,需要的朋友可以參考下2024-04-04
如果是做數(shù)據(jù)分析，MySQL里面最重要、最常用的就是數(shù)據(jù)查詢(xún)，數(shù)據(jù)查詢(xún)不只是簡(jiǎn)單查詢(xún)數(shù)據(jù)庫(kù)中存儲(chǔ)的數(shù)據(jù)，還要根據(jù)需求對(duì)數(shù)據(jù)進(jìn)行篩選、聚合，以及確定數(shù)據(jù)以什么樣的格式進(jìn)行顯示。MySQL提供了強(qiáng)大、靈活的語(yǔ)句和函數(shù)來(lái)實(shí)現(xiàn)查詢(xún)的操作2022-07-07
關(guān)于SQL語(yǔ)句中的AND和OR執(zhí)行順序遇到的問(wèn)題
在SQL語(yǔ)句中的AND和OR執(zhí)行順序中我們經(jīng)常會(huì)遇到一些問(wèn)題，下面有簡(jiǎn)單的解決方法，小編來(lái)和大家一起來(lái)看看2019-05-05
一個(gè) 20 秒 SQL 慢查詢(xún)優(yōu)化處理方案
這篇文章主要分享一個(gè) 20 秒 SQL 慢查詢(xún)優(yōu)化的經(jīng)歷與處理方案，頁(yè)面無(wú)法正確獲取數(shù)據(jù)，經(jīng)排查原來(lái)是接口調(diào)用超時(shí)，而最后發(fā)現(xiàn)是因?yàn)镾QL查詢(xún)長(zhǎng)達(dá)到20多秒而導(dǎo)致了問(wèn)題的發(fā)生，下面來(lái)看問(wèn)題具體介紹吧2022-01-01
這篇文章主要為大家詳細(xì)介紹了mysql 5.7.18 zip版安裝使用教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-06-06
Ubuntu中更改MySQL數(shù)據(jù)庫(kù)文件目錄的方法
這篇文章主要給大家介紹了關(guān)于在Ubuntu中更改MySQL數(shù)據(jù)庫(kù)文件目錄的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2018-11-11
MySQL分支和循環(huán)結(jié)構(gòu)方式
在MySQL中,IF函數(shù)用于根據(jù)條件返回不同的值,類(lèi)似于Java的三目運(yùn)算符,CASE語(yǔ)句則提供了兩種形式：簡(jiǎn)單CASE函數(shù)和搜索CASE函數(shù),分別類(lèi)似于Java中的switch-case結(jié)構(gòu)和多重if判斷,這些控制流函數(shù)在數(shù)據(jù)庫(kù)查詢(xún)和數(shù)據(jù)處理中非常有用,可以實(shí)現(xiàn)復(fù)雜的邏輯判斷2024-10-10