MySQL 5.7 多主复制报错Coordinator stopped because there were error(s)

妖狐艹你老母 2024-04-18 00:38 30阅读 0赞

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2148576/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2148576/