[MySQL] 数据库复制异常恢复之Error_code: 1062 (Duplicate entry)

比眉伴天荒 2023-07-23 03:46 20阅读 0赞

环境：两台服务器双机备份，数据库采用主主复制  
说明：以下主机指HA集群的主机

Error\_code: 1062  
该报错通常会出现在主机或备机，通常最后两台服务器都有该报错（形式相同，内容不同），我们以主机上的报错为准去恢复备机数据。步骤如下：  
1、记录主机（node1）执行show slave status\\G;的报错  
Last\_SQL\_Error: Error 'Duplicate entry '4200000357' for key 'PRIMARY'' on query. Default database: 'somedb'. Query: 'insert into sometable (columns…) values (values…)'

说明：主机和备机报错为同一张表。如果仅备机有报错，记录备机报错；如果主机和备机同一张表报错，记录较小的那一个id。

2、停止HA服务  
\# service rgmanager stop; service cman stop

3、停止数据库主从复制  
分别在主、备机上关闭 MySQL 主从复制和 MySQL 事件调度器。

mysql> stop slave;
    Query OK, 0 rows affected (0.01 sec)
    mysql> stop slave io_thread;
    Query OK, 0 rows affected (0.07 sec)
    mysql> set global event_scheduler=0;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select @@event_scheduler;
    +-------------------+
    | @@event_scheduler |
    +-------------------+
    | OFF               |
    +-------------------+
    1 row in set (0.00 sec)

4、数据恢复  
4.1 删除不一致的表记录  
删除报错中备机（node2）与主机不一致的表记录

***mysql>*** use somedb;  
***mysql>***delete from sometable where id>4200000356;  
说明：开始不一致的id是4200000357，所以把alaems表中大于420000036的记录都删除

4.2 记录需要恢复到的位置  
查看备机错误日志，记录ERROR出现后stopped的位置  
2017-05-23T05:47:16.711096Z 1511 \[ERROR\] Slave SQL for channel '': Error 'Duplicate entry '4200000358' for key 'PRIMARY'' on query. Default database: 'somedb'. Query: 'insert into sometable (columns…) values (values…)', Error\_code: 1062  
2017-05-23T05:47:16.711117Z 1511 \[Warning\] Slave: Duplicate entry '4200000358' for key 'PRIMARY' Error\_code: 1062  
2017-05-23T05:47:16.711122Z 1511 \[ERROR\] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log '**mysql-bin.000008**' position **129383305**

4.3 重新定位备机复制主机数据库的位置  
在备机（node2）执行如下操作  
***mysql>***change master to master\_log\_file='**mysql-bin.000008**',master\_log\_pos=**129383305**;

说明：将备机读取主机MySQL操作的二进制文件位置改为备机错误日志中提示的位置（发生该报错后stoped的位置，即该位置之前的操作是无误的，重新定位后，会从该位置开始继续读取主机的数据库操作）

4.4 重新定位主机复制备机数据库的位置  
说明：如果主机也有报错执行，执行该步骤；反之则跳过该步骤(1)    首先在备机（node2）刷新其mysql-bin.xxx

mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000002 |  3952751 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    mysql> flush logs;
    Query OK, 0 rows affected (0.36 sec)
    
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000003 |      154 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    mysql> change master to master_log_file='mysql-bin.000003',master_log_pos=154;
    Query OK, 0 rows affected (0.00 sec)

(2)    然后在主机（node1）重新定位其读取备机二进制文件的位置

***mysql>*** change master to master\_log\_file='mysql-bin.000003',master\_log\_pos=154;  
Query OK, 0 rows affected (0.00 sec)

说明：因为数据库采用主主复制，主机会复制备机MySQL执行的操作，而数据库恢复以主机数据为基准，所以刷新备机的mysq-bin.xxx以新的位置开始，主机就不会去执行备机与自己数据库不一致的部分。flush logs后，备机mysql-bin.xxx数值加1，重新在一个文件中记录备机操作。

5、启动复制  
分别在主、备机上开启和MySQL 事件调度器和MySQL 主从复制。

mysql> set global event_scheduler=1;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select @@event_scheduler;
    +-------------------+
    | @@event_scheduler |
    +-------------------+
    | ON                |
    +-------------------+
    1 row in set (0.00 sec)
    mysql> start slave io_thread;
    Query OK, 0 rows affected (0.07 sec)
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)

6、 查看主机和备机同步状态

***mysql>*** show slave status\\G;  
数据库恢复正常，不过备机延时较大，但只要主机正常，不影响其业务。  
主机（node1）延迟：  
Seconds\_Behind\_Master: 0  
备机（node2）延迟：  
Seconds\_Behind\_Master: 86669

等待同步完成，即两台服务器上述Seconds\_Behind\_Master:0

7、启动HA服务  
\# service cman start; service rgmanager start