mysql主从 1061 log同步错误处理

心已赠人 2022-07-24 08:07 334阅读 0赞

1062和1063错误可以跳过  
开发在从库上添加索引导致不同步。  
报错如下  
Last\_Errno: 1061  
error 'Duplicate key name 'wolf\_cert\_no'' on query. Default database: 'wolf'. Query: 'ALTER TABLE cpn\_wolf ADD INDEX wolf\_cert\_no (cert\_no)'  
库 wolf  
表 cpn\_wolf  
索引  wolf\_cert\_no (cert\_no)  
  
  
mysql> show index from cpn\_wolf;  
\+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table      | Non\_unique | Key\_name           | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
\+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| cpn\_wolf |          0 | PRIMARY            |            1 | id          | A         |     1432485 |     NULL | NULL   |      | BTREE      |         |               |  
| cpn\_wolf |          1 | wolf\_cert\_no |            1 | cert\_no     | A         |     1432485 |     NULL | NULL   | YES  | BTREE      |         |               |  
\+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
2 rows in set (0.00 sec)  
  
  
从库上删除  
ALTER TABLE \`cpn\_wolf\` DROP INDEX \`cert\_no\`;  
  
  
从起从库  
use mysql  
stop slave；  
start slave；  
  
  
  
  
mysql> show slave status\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State: Waiting for master to send event  
                  Master\_Host: 10.249.2.81  
                  Master\_User: wolf  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: 81-log-bin.000113  
          Read\_Master\_Log\_Pos: 12001316  
               Relay\_Log\_File: 82-relay-log.000202  
                Relay\_Log\_Pos: 6159429  
        Relay\_Master\_Log\_File: 81-log-bin.000103  
             Slave\_IO\_Running: Yes  
            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: 1061  
                   Last\_Error: Error 'Duplicate key name 'wolf\_cert\_no'' on query. Default database: 'wolf'. Query: 'ALTER TABLE cpn\_wolf ADD INDEX wolf\_cert\_no (cert\_no)'  
                 Skip\_Counter: 0  
          Exec\_Master\_Log\_Pos: 6159265  
              Relay\_Log\_Space: 377880603  
              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: 1061  
               Last\_SQL\_Error: Error 'Duplicate key name 'wolf\_cert\_no'' on query. Default database: 'wolf'. Query: 'ALTER TABLE cpn\_wolf ADD INDEX wolf\_cert\_no (cert\_no)'  
  Replicate\_Ignore\_Server\_Ids:   
             Master\_Server\_Id: 281  
                  Master\_UUID: 3a38cbd3-359c-11e5-ab1b-90e2ba7e4104  
             Master\_Info\_File: /data/mysql\_3306/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: 160506 13:27:58  
               Master\_SSL\_Crl:   
           Master\_SSL\_Crlpath:   
           Retrieved\_Gtid\_Set:   
            Executed\_Gtid\_Set:   
                Auto\_Position: 0  
1 row in set (0.00 sec)