mysql主从库同步错误：1062 Error 'Duplicate entry '1438019' for key 'PRIMARY'' on query

最新推荐文章于 2025-03-06 09:14:21 发布

猪中梁朝伟

最新推荐文章于 2025-03-06 09:14:21 发布

阅读量2.9k

点赞数 1

本文链接：https://blog.csdn.net/xiaochenwj1995/article/details/104841856

版权

mysql主从库在同步时会发生1062 Last_SQL_Error: Error ‘Duplicate entry ‘的问题：

通过网上查找资料，发现这个问题是因为插入重复主键导致从库不工作了，错误消息如下

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.147.134
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: bin_log.000005
          Read_Master_Log_Pos: 937
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 25937
        Relay_Master_Log_File: bin_log.000001
             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: Error 'Duplicate entry '%-test-' for key 'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO db SELECT * FROM tmp_db WHERE @had_db_table=0'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 25655
              Relay_Log_Space: 1594860
              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: Error 'Duplicate entry '%-test-' for key 'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO db SELECT * FROM tmp_db WHERE @had_db_table=0'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1

解决办法
通过修改mysql的配置文件，让从库的同步线程忽略这个错误：修改mysql配置文件 /etc/my.cnf 在 [mysqld]下加一行 slave_skip_errors = 1062

[root@centos7 ~]# vim /etc/my.cnf

[mysqld]
...
slave_skip_errors = 1062
...

然后从新开始新的主从复制。

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> reset slave all;  ##清除所有的主从复制信息
Query OK, 0 rows affected (0.01 sec)

重新开始


MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.147.134',   MASTER_USER='repluser',   MASTER_PASSWORD='123456',   MASTER_PORT=3306,   MASTER_LOG_FILE='bin_log.000001',    MASTER_LOG_POS=245,   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.147.134
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: bin_log.000005
          Read_Master_Log_Pos: 937
               Relay_Log_File: mariadb-relay-bin.000006
                Relay_Log_Pos: 1219
        Relay_Master_Log_File: bin_log.000005
             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: 937
              Relay_Log_Space: 1797
              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)

验证，发现从数据库已经从主数据库复制过来数据了。

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb_innodb     |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> select user,host,password from mysql.user;
+----------+-----------------+-------------------------------------------+
| user     | host            | password                                  |
+----------+-----------------+-------------------------------------------+
| root     | localhost       |                                           |
| root     | centos7.cwj.com |                                           |
| root     | 127.0.0.1       |                                           |
| root     | ::1             |                                           |
|          | localhost       |                                           |
|          | centos7.cwj.com |                                           |
| repluser | 192.168.%.%     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----------+-----------------+-------------------------------------------+