Mysql主从复制遇到错误

报错一

1.1 问题

mysql的从服务器出现问题【不能和主服务器进行同步复制】

1.2 原因

直接删除了从库的数据【从库多是用来查询，而不是用于增删的】，导致再在主服务器上删除相同数据时，就会出现错误【因为从服务器会同步主服务器的操作，但却无数据可删了】

1.3 解决办法
  • 找出从服务器的报错code
  • 修改配置文件内容，主要如下：
    vi /etc/my.cnf 
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误

报错二

2.1 问题

查看从库状态，如下：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.22.5.104
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001047
          Read_Master_Log_Pos: 732659303
               Relay_Log_File: bimy117-relay-bin.000618
                Relay_Log_Pos: 40993
        Relay_Master_Log_File: mysql-bin.001047
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: datamart,appmetadatadb,rollupdb
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1677
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.001047, end_log_pos 732659272. 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: 732658577
              Relay_Log_Space: 41928
              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: 1677
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.001047, end_log_pos 732659272. 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: 223306104
                  Master_UUID: b7d9469a-7b99-11e8-b5cd-6c92bf93b724
             Master_Info_File: /data1/mysqldata/data/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: 181016 14:27:56
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
2.2 原因

查看主从表中的字段，发现主从库表中的字段数不一致，导致出错。

2.3解决办法

修改主从库表中的字段至一致即可。