MySQL 5.7 多主複製報錯Coordinator stopped because there were error(s)
root@(none) 10:08:14>show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.220.183
Master_User: repl
Master_Port: 62306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2331205
Relay_Log_File: mysqld-relay-bin-master_5.000002
Relay_Log_Pos: 842
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
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: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 1544856. 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
Exec_Master_Log_Pos: 1544614
Relay_Log_Space: 788005
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 1544856. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 183
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 171212 09:03:42
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master_5
Master_TLS_Version:
1 row in set (0.00 sec)
檢視並行複製狀態表
可以看到主鍵重複的錯誤
root@(none) 10:08:29>select * from performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
CHANNEL_NAME: master_5
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: master_5
WORKER_ID: 2
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 3. row ***************************
CHANNEL_NAME: master_5
WORKER_ID: 3
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 4. row ***************************
CHANNEL_NAME: master_5
WORKER_ID: 4
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: ANONYMOUS
LAST_ERROR_NUMBER: 1062
LAST_ERROR_MESSAGE: Worker 3 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 1544856; Could not execute Write_rows_v1 event on table beike.bk_beike_resource; Duplicate entry '825896' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 1544856
LAST_ERROR_TIMESTAMP: 2017-12-12 09:03:42
4 rows in set (0.00 sec)
檢視錯誤日誌
可以看到主鍵重複的錯誤
[root@mysql7-131 ~]# tail -20 /data/db_log/alter.log
2017-12-12T00:59:35.546364Z 41887 [Warning] Slave SQL for channel 'master_5': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756
2017-12-12T01:01:04.363474Z 41865 [Note] Slave I/O thread killed while reading event for channel 'master_5'
2017-12-12T01:01:04.363530Z 41865 [Note] Slave I/O thread exiting for channel 'master_5', read up to log 'mysql-bin.000002', position 2331205
2017-12-12T01:01:56.064777Z 42022 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-12-12T01:01:56.067328Z 42022 [Note] Slave I/O thread for channel 'master_5': connected to master 'repl@192.168.220.183:62306',replication started in log 'mysql-bin.000002' at position 2331205
2017-12-12T01:01:56.069112Z 42022 [Warning] Slave I/O for channel 'master_5': Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
2017-12-12T01:01:56.069613Z 42022 [Warning] Slave I/O for channel 'master_5': Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.55-38.8-log), even though it is on the slave (version: 5.7.17-11-log), Error_code: 1193
2017-12-12T01:02:22.311421Z 42022 [Note] Slave I/O thread killed while reading event for channel 'master_5'
2017-12-12T01:02:22.311465Z 42022 [Note] Slave I/O thread exiting for channel 'master_5', read up to log 'mysql-bin.000002', position 2331205
2017-12-12T01:03:42.602052Z 42122 [Note] Slave SQL thread for channel 'master_5' initialized, starting replication in log 'mysql-bin.000002' at position 1544345, relay log '/data/db_log/mysqld-relay-bin-master_5.000002' position: 573
2017-12-12T01:03:42.602215Z 42122 [Note] 'SQL_SLAVE_SKIP_COUNTER=1' executed at relay_log_file='/data/db_log/mysqld-relay-bin-master_5.000002', relay_log_pos='573', master_log_name='mysql-bin.000002', master_log_pos='1544345' and new position at relay_log_file='/data/db_log/mysqld-relay-bin-master_5.000002', relay_log_pos='842', master_log_name='mysql-bin.000002', master_log_pos='1544614'
2017-12-12T01:03:42.602478Z 42126 [ERROR] Slave SQL for channel 'master_5': Worker 3 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 1544856; Could not execute Write_rows_v1 event on table beike.bk_beike_resource; Duplicate entry '825896' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 1544856, Error_code: 1062
2017-12-12T01:03:42.602529Z 42122 [Warning] Slave SQL for channel 'master_5': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.220.183
Master_User: repl
Master_Port: 62306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2331205
Relay_Log_File: mysqld-relay-bin-master_5.000002
Relay_Log_Pos: 842
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
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: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 1544856. 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
Exec_Master_Log_Pos: 1544614
Relay_Log_Space: 788005
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 1544856. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 183
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 171212 09:03:42
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master_5
Master_TLS_Version:
1 row in set (0.00 sec)
檢視並行複製狀態表
可以看到主鍵重複的錯誤
root@(none) 10:08:29>select * from performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
CHANNEL_NAME: master_5
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: master_5
WORKER_ID: 2
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 3. row ***************************
CHANNEL_NAME: master_5
WORKER_ID: 3
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION:
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 4. row ***************************
CHANNEL_NAME: master_5
WORKER_ID: 4
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: ANONYMOUS
LAST_ERROR_NUMBER: 1062
LAST_ERROR_MESSAGE: Worker 3 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 1544856; Could not execute Write_rows_v1 event on table beike.bk_beike_resource; Duplicate entry '825896' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 1544856
LAST_ERROR_TIMESTAMP: 2017-12-12 09:03:42
4 rows in set (0.00 sec)
檢視錯誤日誌
可以看到主鍵重複的錯誤
[root@mysql7-131 ~]# tail -20 /data/db_log/alter.log
2017-12-12T00:59:35.546364Z 41887 [Warning] Slave SQL for channel 'master_5': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756
2017-12-12T01:01:04.363474Z 41865 [Note] Slave I/O thread killed while reading event for channel 'master_5'
2017-12-12T01:01:04.363530Z 41865 [Note] Slave I/O thread exiting for channel 'master_5', read up to log 'mysql-bin.000002', position 2331205
2017-12-12T01:01:56.064777Z 42022 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2017-12-12T01:01:56.067328Z 42022 [Note] Slave I/O thread for channel 'master_5': connected to master 'repl@192.168.220.183:62306',replication started in log 'mysql-bin.000002' at position 2331205
2017-12-12T01:01:56.069112Z 42022 [Warning] Slave I/O for channel 'master_5': Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
2017-12-12T01:01:56.069613Z 42022 [Warning] Slave I/O for channel 'master_5': Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.55-38.8-log), even though it is on the slave (version: 5.7.17-11-log), Error_code: 1193
2017-12-12T01:02:22.311421Z 42022 [Note] Slave I/O thread killed while reading event for channel 'master_5'
2017-12-12T01:02:22.311465Z 42022 [Note] Slave I/O thread exiting for channel 'master_5', read up to log 'mysql-bin.000002', position 2331205
2017-12-12T01:03:42.602052Z 42122 [Note] Slave SQL thread for channel 'master_5' initialized, starting replication in log 'mysql-bin.000002' at position 1544345, relay log '/data/db_log/mysqld-relay-bin-master_5.000002' position: 573
2017-12-12T01:03:42.602215Z 42122 [Note] 'SQL_SLAVE_SKIP_COUNTER=1' executed at relay_log_file='/data/db_log/mysqld-relay-bin-master_5.000002', relay_log_pos='573', master_log_name='mysql-bin.000002', master_log_pos='1544345' and new position at relay_log_file='/data/db_log/mysqld-relay-bin-master_5.000002', relay_log_pos='842', master_log_name='mysql-bin.000002', master_log_pos='1544614'
2017-12-12T01:03:42.602478Z 42126 [ERROR] Slave SQL for channel 'master_5': Worker 3 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 1544856; Could not execute Write_rows_v1 event on table beike.bk_beike_resource; Duplicate entry '825896' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 1544856, Error_code: 1062
2017-12-12T01:03:42.602529Z 42122 [Warning] Slave SQL for channel 'master_5': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2148576/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql5.7主從複製，主主複製MySql
- MySQL 5.7 多主一從（多源複製）同步配置MySql
- MySQL5.7半同步複製報錯案例分析MySql
- MySQL5.7主從複製教程MySql
- MySQL5.7主從複製-半同步複製搭建MySql
- mysql 5.7 主從複製搭建及原理MySql
- MySQL主從複製報錯：Got fatal error 1236 from master when reading data fromMySqlGoErrorAST
- MySQL 5.7基於GTID的主從複製MySql
- MySQL 5.7 基於GTID搭建主從複製MySql
- MySQL 5.7的安裝及主從複製(主從同步)MySql主從同步
- MySQL 5.6複製報錯Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;MySqlError
- MySQL5.7在滴滴雲主機上的主從複製MySql
- MySQL 主從複製錯誤1837MySql
- MySQL 5.7 並行複製MySql並行
- MySQL 5.7並行複製MySql並行
- mysql 5.7半同步複製MySql
- mysql主從複製(一):一主多從MySql
- MySQL叢集之 主從複製 主主複製 一主多從 多主一叢 實現方式MySql
- [Mysql]Mysql5.7並行複製MySql並行
- 故障案例：主從同步報錯Fatal error: The slave I/O thread stops because master and slave have equal MySQL server主從同步ErrorthreadASTMySqlServer
- Mysql5.7半同步複製MySql
- mysql複製--主從複製配置MySql
- Mysql啟動報錯：Job for mysqld.service failed because the control process exited with error code.MySqlAIError
- MySQL主從複製MySql
- MySQL主從複製之GTID複製MySql
- 半同步複製報錯mysql8.0.25MySql
- GTID複製報錯處理：Last_Error: Error 'Can't drop database 'test';ASTErrorDatabase
- MySQL 5.7傳統複製到GTID線上切換（一主一從）MySql
- MySQL主從複製錯誤——列型別轉換錯誤MySql型別
- MySQL主從複製之半同步複製MySql
- MySQL主從複製之非同步複製MySql非同步
- Windows 環境下，MySQL 的主從複製和主主複製WindowsMySql
- windows環境下，Mysql的主從複製和主主複製WindowsMySql
- MySQL 5.7初始化報錯error while loading shared libraries: libnuma.so.1MySqlErrorWhile
- MySQL 多源複製MySql
- MySQL主從複製原理MySql
- MySQL的主從複製MySql
- mysql--主從複製MySql
- mysql 8.4 主從複製MySql