解決Mysql 主從或主主報1062錯誤

1062錯誤——主鍵沖突，出現這種情況就是從庫出現插入操作，主庫又插入相同的資料，iothread沒問題，sqlthread出錯

處理此種錯誤一般有兩種思路：

1、直接跳過錯誤執行語句

2、找到錯誤執行語句，修複主庫2資料

語句跳過操作方法如下：

1062 錯誤提示如下:

         Last_Errno: 1062

         Last_Error: Could not execute Write_rows event on table test.test; Duplicate entry '2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql2-bin.000001, end_log_pos 2799

         Skip_Counter: 0

—傳統模式

    mysql> stop slave;        #表示跳過一步錯誤，後面的數字可變，（或者N條event，一條一條跳過）

    mysql> set global sql_slave_skip_counter =1;

    mysql> start slave;

    mysql> show slave status/G;

    mysql> show slave status/G;/

    *************************** 1. row ***************************

                   Slave_IO_State: Waiting for master to send event

                      Master_Host: 192.168.1.21

                      Master_User: rep1

                      Master_Port: 3306

                    Connect_Retry: 60

                  Master_Log_File: mysql2-bin.000001

              Read_Master_Log_Pos: 3087

                   Relay_Log_File: cndba-relay-bin.000006

                    Relay_Log_Pos: 587

            Relay_Master_Log_File: mysql2-bin.000001

                 Slave_IO_Running: Yes

                Slave_SQL_Running: Yes

                  Replicate_Do_DB: test

    #還有一種方法跳過所有1062錯誤

    更改my.cnf檔案，在Replication settings下添加：

    slave-skip-errors ＝ 1062

    并重新開機資料庫，然後start salve。

    注意：因為要重新開機資料庫，不推薦，除非錯誤事件太多。

模拟1062場景：

主庫1建立表

    mysql> create table test(id int PRIMARY KEY ,name varchar(32));

    Query OK, 0 rows affected (0.06 sec)

    修改參數sql_log_bin 使主庫1操作不同步到主庫2

    mysql> set sql_log_bin=0;

    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into test values (1,'aa');

    Query OK, 1 row affected (0.02 sec)

    mysql> insert into test values (2,'bb');

    Query OK, 1 row affected (0.01 sec)

    mysql> insert into test values (3,'dd');

    Query OK, 1 row affected (0.00 sec)

    修改參數sql_log_bin 使主庫1操作說同步到主庫2

    mysql> set sql_log_bin=1;

    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into test values (4,'cc');

    Query OK, 1 row affected (0.08 sec)

    mysql> select * from test;

    +----+------+

    | id | name |

    +----+------+

    |  1 | aa   |

    |  2 | bb   |

    |  3 | dd   |

    |  4 | cc   |

    +----+------+

    4 rows in set (0.00 sec)

主庫2檢視資料同步情況主庫2檢視資料同步情況

    可以看到主庫2資料隻同步了一條資料

    mysql> select * from test;

    +----+------+

    | id | name |

    +----+------+

    |  4 | cc   |

    +----+------+

    1 row in set (0.00 sec)

在主庫2插入表test

    mysql> insert into test values (2,'bb');

    Query OK, 1 row affected (0.01 sec)

在主庫1檢視主從狀态

    發現報1062錯誤，發生這個錯誤的原因是因為主庫1已經存在id=2的資料，導緻主鍵沖突。

    mysql> show slave status/G;

    ......

                       Last_Errno: 1062

                       Last_Error: Could not execute Write_rows event on table test.test; Duplicate entry '2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql2-bin.000001, end_log_pos 2799

                     Skip_Counter: 0

              Exec_Master_Log_Pos: 2564

                  Relay_Log_Space: 1541

                  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: Could not execute Write_rows event on table test.test; Duplicate entry '2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql2-bin.000001, end_log_pos 2799

    ......

根本解決辦法：

    根據Last_Error中提示的master log和end_log_pos的位置查找這條主庫2上缺失的資料

    主庫2操作：

    [[email protected] data]# mysqlbinlog --no-defaults -v --base64-output=decode-rows  --stop-position=2799 /data/mysql/binlog/mysql2-bin.000001 | tail -20

    #180819 10:59:56 server id 21  end_log_pos 2706 CRC32 0x5a43437b     Query    thread_id=6    exec_time=0    error_code=0

    SET TIMESTAMP=1534647596;

    SET @@session.sql_mode=1436549152;

    SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2;

    BEGIN

    ;

    # at 2706

    #180819 10:59:56 server id 21  end_log_pos 2756 CRC32 0x0d6a3132     Table_map: `test`.`test` mapped to number 229

    # at 2756

    #180819 10:59:56 server id 21  end_log_pos 2799 CRC32 0xc6a8a0b0     Write_rows: table id 229 flags: STMT_END_F

    ### INSERT INTO `test`.`test`

    ### SET

    ###   @1=2

    ###   @2='bb'

    ROLLBACK ;

    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' ;

    DELIMITER ;

    # End of log file

    ;

    ;

    找到之後，手動轉變為delete 語句

    主庫1執行以下語句：

    mysql> delete from test where id=2;

    Query OK, 1 row affected (0.02 sec)

    啟動複制

    mysql> start slave;

    Query OK, 0 rows affected, 1 warning (0.00 sec)

    檢視主庫2狀态：

    mysql> show slave status/G;

    ......

                 Slave_IO_Running: Yes

                Slave_SQL_Running: Yes

    ......

确定插入的資料和庫裡的資料是不是相同，如果相同可以删除主庫1的資料來解決，否則可以考慮跳過這個事件來解決