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 |
+----------+-----------------+-------------------------------------------+