不积跬步,无以至千里;不积小流,无以成江海。

解决mysql主从同步Last_Error: Error ‘Duplicate entry’

数据库 康康 531℃ 0评论

在myslq主从同步时,主库成功开启master服务,并将从库的master_log_pos=19500保持与主库相同

mysql> show master status\G
*************************** 1. row ***************************
 File: mysql-bin.000002
 Position: 19500
 Binlog_Do_DB: hms
Binlog_Ignore_DB: information_schema,mysql,yunbao_db
1 row in set (0.00 sec)

在启动从库salve服务时报错:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.25.199.52
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 19500
               Relay_Log_File: iZ25fn41e46Z-relay-bin.000004
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: hms
          Replicate_Ignore_DB: information_schema,mysql,shopxx,yunbao_web_db,chni_web_db
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Error 'Duplicate entry '0070900000247' for key 'PRIMARY'' on query. Default database: 'hms'. Query: 'insert into cus_customer (address, ascount, asstate, birthday, blid, bodycheckid, bodycheckname, cardtypeid, checkbodydata, citid, createtime, createuserid, CTypeId, cus_sou_device, cus_source, cuserid, cusername, cusstate, customername, cyid, directoruserid, directorusername...
    Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 21614
              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
......

分析操作步骤,查看到 Relay_Log_Pos的值与 Read_Master_Log_Pos 相差较大

可以判断由于从库启动后从 Relay_Log_Pos 位置读取日志向从库写入,而从库实际上数据已和主库同步,导致插入重复数据

解决:
重新设置master_log_pos即可

mysql> stop slave;
mysql> change master to master_log_pos=19500;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 10.25.199.52
 Master_User: slaveuser
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000002
 Read_Master_Log_Pos: 20544
 Relay_Log_File: iZ25fn41e46Z-relay-bin.000002
 Relay_Log_Pos: 1297
 Relay_Master_Log_File: mysql-bin.000002
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: hms
 Replicate_Ignore_DB: information_schema,mysql,shopxx,yunbao_web_db,chni_web_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: 20544
 Relay_Log_Space: 1460
 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
1 row in set (0.00 sec)

 

转载请注明:左手代码右手诗 » 解决mysql主从同步Last_Error: Error ‘Duplicate entry’

喜欢 (2)or分享 (0)
头像
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
隐藏