MySQL基于GTID的主从复制错误：1062

145次阅读

共计 4811 个字符，预计需要花费 13 分钟才能阅读完成。

说明：
环境
MySQL-master：172.16.200.43
mysql-slave：172.16.200.44
系统：CentOS7
版本：MySQL5.6.35

一、基于 GTID 复制环境的搭建前提

主从环境的搭建和 5.5 没有什么区别，唯一需要注意的是：开启 GTID 需要启用这三个参数：

#GTID
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates   = 1

gtid-mode 用来设置是否开启 GTID 功能，如果要开启 GTID 功能，需要同时开启 log-bin 和 log_slave_updates 功能，另外还需要开启 enforce_gtid_consistency 功能。gtid_mode 参数可以设置为 on、off、upgrade_step_1、upgrade_step_2 四种值，其中 upgrade_step_1 和 upgrade_step_2 是给将来 mysql 可能的新功能预留的，对当前的 myql 没有任何意义。同时，mysql 建议在 mysql_upgrade 的时候，关闭 gtid_mode 功能和 enforce_gtid_consistency 功能，因为 Mysql 在 upgrade 期间可能会操作非事务的 MyISAM 存储引擎表，会引起报错。

二、配置步骤

1、43 上面授权 44 从库的账号

MySQL [(none)]> grant replication slave on *.* to  'slave'@'172.16.200.44' identified by '000000';
MySQL [(none)]> flush privileges;

2、配置主从同步

MySQL [(none)]> stop slave;
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.200.43', MASTER_USER='slave', MASTER_PASSWORD='000000', MASTER_AUTO_POSITION=1;
MySQL [(none)]> start slave;

3、关闭防火墙

systemctl stop firewalld.service   # 停止 firewall
systemctl disable firewalld.service # 禁止 firewall 开机启动

三、测试跳过一个事务

1、43 上面新建库 test

2、43 上面新建表 info_area

CREATE TABLE `info_area` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '地区 ID',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT ' 名称 ',
  `rel_id` varchar(50) NOT NULL DEFAULT '' COMMENT ' 关系 ID',
  `pid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '父 ID',
  `level` int(11) NOT NULL DEFAULT '0' COMMENT '类别，1、省份 2、市 3、区 4、县',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNQ_RID` (`rel_id`) USING BTREE,
  KEY `IDX_PID` (`rel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=55185 DEFAULT CHARSET=utf8 COMMENT='地区表';

3、43 插入 3 条语句,44 完全同步

INSERT INTO `test`.`info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10000', '北京', '10000', '10000', '1');
INSERT INTO `test`.`info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10002', '三环以内', '100001000110002', '10001', '3');
INSERT INTO `test`.`info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10003', '三环到四环之间', '100001000110003', '10001', '3');

4、43 删除 id=10001

delete from info_area where id=10001;

5、44 上面插入 10001

INSERT INTO `test`.`info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10001', '朝阳区', '1000010001', '10000', '2');

6、43 上面插入 10001

INSERT INTO `test`.`info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10001', '朝阳区', '1000010001', '10000', '2');

由于主从复制，44 也会同步该条数据，但是 44 上面刚刚已经插入过 id=10001，所以此时报错

MySQL [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.200.43
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 83009452
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 83009382
        Relay_Master_Log_File: mysql-bin.000007
             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 test.info_area; Duplicate entry '10001' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 83009421
           Retrieved_Gtid_Set: 0f869100-71d1-11e7-be5e-000c29f2e72e:1-14
            Executed_Gtid_Set: 0f869100-71d1-11e7-be5e-000c29f2e72e:1-13,
22fd263e-71d1-11e7-be5e-000c29fffc35:1
                Auto_Position: 1

7、解决 gtid 下 1062 错误

mysql> stop slave;
mysql> set GTID_NEXT='0f869100-71d1-11e7-be5e-000c29f2e72e:14';  
mysql> begin;
mysql> commit;
mysql> set GTID_NEXT='AUTOMATIC';
mysql> start slave;
注：传统方式
mysql> stop slave;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;  
mysql> start slave;

8、恢复正常

MySQL [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.200.43
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 83009452
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 448
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 83009452
              Relay_Log_Space: 83010163
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 0f869100-71d1-11e7-be5e-000c29f2e72e
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 0f869100-71d1-11e7-be5e-000c29f2e72e:1-14
            Executed_Gtid_Set: 0f869100-71d1-11e7-be5e-000c29f2e72e:1-14,
22fd263e-71d1-11e7-be5e-000c29fffc35:1
                Auto_Position: 1
1 row in set (0.00 sec)