MySQL復制問題的三個參數分析
今天星期二,早上居然起晚瞭,上班遲到瞭,簡直是。。。廢話不多說,在昨天的文章中,我們提到瞭三個參數,分別是:
- slave_exec_mode參數;
- sql_slave_skip_counter=N參數;
- slave-skip-errors=N參數。
這三個參數都可以解決並行復制中的一些指定的錯誤,例如duplicate key 1062錯誤等,今天我們簡單試驗一下,這三個參數的區別:
01 sql_slave_skip_counter參數
這個參數的設置主要是為瞭跳過某些錯誤的”event”,註意這裡的用詞是event而不是事務,是因為它的本質是跳過一個一個事件,需要註意的是,這個參數需要在偏移量復制模式中使用,如果使用的是gtid的復制模式,則不可以使用這個參數。我們來看例子,首先搭建一套復制關系:
master 10.30.124.68
slave 10.30.124.128
這倆實例互為主從。我們創建測試表test.yeyz,並插入一些數據,其中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)
我們可以發現,從節點的數據比主節點多一條,多瞭id=5的記錄,然後我們在主節點上插入數據:
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
此時觀察從節點:
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
可以發現,從節點已經SQL線程斷開瞭, 這個時候,在主節點上查詢這個錯誤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可以看出來,我們的一個insert操作實際上生成瞭5個enent,分別對應的pos是從259~492,關於event,待會兒再說。
因為主節點上插入瞭id=5的記錄,跟從節點上的記錄沖突瞭,查看錯誤日志,可以發現:
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
我們通過sql_slave_skip_counter參數的設置來解決這個問題,步驟如下:
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;
在昨天的文章中我們說過,sql_slave_skip_counter後面跟的值是event的個數,所以這裡我們相當於跳過瞭一個event,mysql中規定,如果跳過一個event之後,還在某一個事務裡面,那麼會繼續跳過這個事務。
使用這個參數跳過一個event之後,我們再來看從庫表中的數據和復制情況,可以看到:
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)
可以發現,master中數據插入成功,而slave中數據插入失敗,也就是說:
該參數跳過錯誤的時候,會導致主從的數據不一致。
02 slave_skip_errors參數
這個參數是跳過制定的錯誤,也就是說,需要我們設置對應的error_code,從下面的日志中的內容可以看出,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
我們需要手動將這個參數的值也該為1062,需要註意的是,這個參數的改動需要重啟mysql服務,因為這個參數是一個隻讀的參數。
修改後的情況如下:
[email protected]:(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)
此時我們更新master表和slave表的數據,更新後的情況如下:
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)
我們發現,slave表比master表多一條數據,也就是id=7的記錄,此時我們在master上執行:
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上面的復制情況和數據情況,如下:
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)
可以看到,復制沒有出現錯誤,即使從庫上已經有id=7的記錄。而且發現,從庫的數據跟之前保持一致,也就是說,主庫插入的id=8的記錄沒有被同步過來。
總結一下:該參數在跳過復制錯誤的時候,需要重啟mysql服務,然後可能導致主從數據不一致。
03 slave-skip-errors=N參數
再看最後一個參數,這個參數表示的是並行復制過程中的從庫復制模式,默認值是strict嚴格模式,和上面一樣,我們先看主庫和從庫的數據情況:
master數據:
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數據:
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)
此時我們在從庫上修改參數如下:
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)
修改完參數,我們在主庫上進行insert操作:
insert into test.yeyz values (9,9),(10,10);
查看從庫的復制狀態和數據情況,如下:
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)
可以發現,既沒有出現復制錯誤,主庫上插入的數據也同步過來瞭。
總結一下:
- slave_exec_mode參數;
- sql_slave_skip_counter=N參數;
- slave-skip-errors=N參數。
這三個參數都能解決復制過程中的不一致情況,區別如下:
slave_exec_mode參數可以保證主從數據一致,其他兩個不可以。
slave-skip-errors參數可以跳過制定的錯誤,但是需要重啟實例,不能保證數據一致。
sql_slave_skip_counter參數需要在偏移量的復制模式下使用,不能保證數據一致。
以上就是MySQL復制問題的三個參數分析的詳細內容,更多關於MySQL復制問題的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- None Found