发表于 2019-06-06

| 分类于 Mysql

| 浏览 250次

在MySQL主从同步集群部署中，经常会遇到主从不能同步的问题，以下对常见问题及解决办法进行了归纳列举

数据不一致：包括删除失败、主键重复、更新丢失

#更新丢失
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table social.test; Can't find record in 'jason', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;the event's master log mysql-bin.000019, end_log_pos 4563
#主键重复
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY'' on query. Default database: 'social'. Query: 'INSERT INTO `social`.`tool`(`createdat`, `updatedat`) VALUES ('2019-06-05 09:11:46', '2019-06-05 09:11:51')'
#删除失败
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Delete_rows_v1 event on table social.player_role; Can't find record in 'player_role', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000013, end_log_pos 20064466

在master上，用mysqlbinlog 分析下出错的binlog日志在干什么：

[root@localhost ~]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000013 | grep -A 10 20064466
#190605  9:12:28 server id 1  end_log_pos 20064466     Delete_rows: table id 442 flags: STMT_END_F
### DELETE FROM `social`.`player_role`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=0 is_null=0 */
###   @3='2019-06-04 20:10:48' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
###   @4='2019-06-04 20:10:48' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 20064466
#190605  9:12:28 server id 1  end_log_pos 20064493     Xid = 414815
COMMIT/*!*/;
# at 20064493
#190605  9:13:00 server id 1  end_log_pos 20064531     GTID 0-1-9152684 trans
/*!100001 SET @@session.gtid_seq_no=9152684*//*!*/;
BEGIN
/*!*/;
# at 20064531
#190605  9:13:00 server id 1  end_log_pos 20064795     Query    thread_id=1733    exec_time=0    error_code=0
SET TIMESTAMP=1559697180.6860/*!*/;

对于主键重复／删除失败／更新丢失，更新丢失主要是缺少更新的记录，进行补充下即可，主键重复／删除失败此时可以选择忽略错误，方法如下：

mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
mysql> show slave status\G

字段不一致：包括字段重复、丢失、不够长等

#字段重复
Last_SQL_Errno: 1060
Last_SQL_Error: Error 'Duplicate column name 'del_flag'' on query. Default database: 'social'. Query: 'ALTER TABLE `social`.`player` ADD COLUMN `del_flag` int(11) NULL AFTER `detail`'

对于数据库字段的同步失败，只需要将主从数据库的字段名称、类型等调整一致即可。之后重启slave，查看主从同步是否恢复

主从表不一致

Last_SQL_Errno: 1146
Last_SQL_Error: Error executing row event: 'Table 'social.player_role' doesn't exist'

主从提示缺少表可以进行添加，多余表可以删除，保证主从表的一致即可。之后重启slave，查看主从同步是否恢复