MySQL Last_SQL_Errno: 1062----经典错误，主键冲突

一、基础信息

1. centos7.4

2.mysql 5.7.21

3.基于gtid的复制

二、异常描述

误把从节点当成主节点插入一条数据，同一条数据在主、从节点插入都进行了一次插入操作，导致主键冲突，slave下的sql线程异常。

建议设置从库为只读：

set global read_only=1;--针对普通mysql数据库用户设置为只读
set global super_read_only=1;--针对super类mysql数据库用户设置为只读，比如root用户

 

my.cnf 文件设置永久只读

read_only=1
super_read_only=1;

 

三、处理过程  

1.查看报错信息-- 报错大致如下

mysql> show slave status \g
*************************** 1. row ***************************
               slave_io_state: waiting for master to send event
                  master_host: 192.168.1.1
                  master_user: repuser
                  master_port: 3306
                connect_retry: 60
              master_log_file: mysql_binlog.000023
          read_master_log_pos: 2266930
               relay_log_file: devmidsrv01kf-relay-bin.000003
                relay_log_pos: 423
        relay_master_log_file: mysql_binlog.000016
             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: 1062
                   last_error: could not execute write_rows event on table testdb1.students; duplicate entry '8' for key 'primary', error_code: 1062; handler error ha_err_found_dupp_key; the event's master log mysql_binlog.000016, end_log_pos 340380520
                 skip_counter: 0
          exec_master_log_pos: 340380190
              relay_log_space: 6541566399
              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 testdb1.students; duplicate entry '8' for key 'primary', error_code: 1062; handler error ha_err_found_dupp_key; the event's master log mysql_binlog.000016, end_log_pos 340380520
  replicate_ignore_server_ids: 
             master_server_id: 757
                  master_uuid: 7bd36140-a92d-11e9-ba1f-005056a4099b
             master_info_file: /data/mysql/3306/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: 191205 16:19:02
               master_ssl_crl: 
           master_ssl_crlpath: 
           retrieved_gtid_set: 7bd36140-a92d-11e9-ba1f-005056a4099b:835084-2324874
            executed_gtid_set: 7bd36140-a92d-11e9-ba1f-005056a4099b:1-835083,
bcad977d-a934-11e9-a589-005056a483f4:1-29
                auto_position: 1
         replicate_rewrite_db: 
                 channel_name: 
           master_tls_version: 
1 row in set (0.00 sec)

 

2.确认需要跳过的gtid是关键

找到这个已经执行的gtid，加1

7bd36140-a92d-11e9-ba1f-005056a4099b:1-835083

 

3.跳过gtid

stop slave;

set @@session.gtid_next= '7bd36140-a92d-11e9-ba1f-005056a4099b:1-835084';

begin; commit;

set session gtid_next = automatic;

start slave;

 

4、检查

mysql> show slave status \g
*************************** 1. row ***************************
               slave_io_state: waiting for master to send event
                  master_host: 192.168.1.1
                  master_user: repuser
                  master_port: 3306
                connect_retry: 60
              master_log_file: mysql_binlog.000023
          read_master_log_pos: 2266930
               relay_log_file: devmidsrv01kf-relay-bin.000003
                relay_log_pos: 423
        relay_master_log_file: mysql_binlog.000016
             slave_io_running: yes
            slave_sql_running: yes