MySQL5.7多主复制报错Coordinatorstoppedbecausetherewereerror(s)怎么办

这篇文章给大家分享的是有关MySQL 5.7多主复制报错Coordinator stopped because there were error(s)怎么办的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。

创新互联建站长期为上1000家客户提供的网站建设服务,团队从业经验10年,关注不同地域、不同群体,并针对不同对象提供差异化的产品和服务;打造开放共赢平台,与合作伙伴共同营造健康的互联网生态环境。为牙克石企业提供专业的网站设计、成都网站制作,牙克石网站改版等技术服务。拥有十多年丰富建站经验和众多成功案例,为您定制开发。

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

感谢各位的阅读!关于“MySQL 5.7多主复制报错Coordinator stopped because there were error(s)怎么办”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!


网页标题:MySQL5.7多主复制报错Coordinatorstoppedbecausetherewereerror(s)怎么办
链接地址:http://scyanting.com/article/peccjj.html