最常见的3种同步故障情况
这3种情况是在HA切换时,由于是异步复制,且sync_binlog=0,会造成一小部分binlog没接收完导致同步报错。
- 第一种:在master上删除一条记录,而slave上找不到。
Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000006, end_log_pos 254
- 第二种:主键重复。在slave已经有该记录,又在master上插入了同一条记录。
Last_SQL_Error: Could not execute Write_rows event on table hcy.t1; Duplicate entry '2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924
- 第三种:在master上更新一条记录,而slave上找不到,丢失了数据。
Last_SQL_Error: Could not execute Update_rows event on table hcy.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000010, end_log_pos 263
解决方案:
首先停掉Slave服务:stop slave;
到主服务器上查看主机状态:
记录File和Position对应的值
进入master
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysql-bin.000006 | 282167 | | |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
然后到slave服务器上执行手动同步:
change master to master_host='10.1.1.144', master_user='lb', master_password='ygW_2018', master_port=3306, master_log_file='mysql-bin.000006', master_log_pos=282167 ;
启动slave 服务: start slave;
查看主从状态:show slave status\G;显示如下则正常:
查看指定binlog文件的内容
show binlog events in 'mysql-bin.000001';
获取binlog文件列表
mysql> show binary logs;