mysql5.7同步复制报错1060故障处理
mysql5.7同步复制报错故障处理
# 报错 1060，具体如下
Last_Errno: 1060
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000311, end_log_pos 352951786. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
# 可以使用命令查看具体的执行sql语句
# 查找 positions 的位置向后10行
/usr/local/percona-server-5.7.14/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000311 | grep -A 10 352951786
# 用这条语句定位不到具体的执行sql
[root@newcms:/data/mysql_data]# /usr/local/percona-server-5.7.14/bin/mysqlbinlog --base64-output=DECODE-ROWS -v --start-position=352951786 --stop-position=352951786 mysql-bin.000311 | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
# 推荐使用如下定位命令：
[root@newcms:/data/mysql_data]# /usr/local/percona-server-5.7.14/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000311 | grep -A 10 352951786
#191202 9:13:24 server id 1189 end_log_pos 352951786 CRC32 0xb6b23b6f Query thread_id=4299701 exec_time=0 error_code=0
SET TIMESTAMP=1575249204/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
# 可以看到关键的执行sql语句，添加了字段，并且新增内容
ALTER TABLE `template_page_9342` ADD COLUMN `content` longtext NOT NULL
/*!*/;
# at 352951786
#191202 9:13:24 server id 1189 end_log_pos 352951851 CRC32 0x5636b59f Anonymous_GTID last_committed=3226 sequence_number=3227
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 352951851
#191202 9:13:24 server id 1189 end_log_pos 352951929 CRC32 0x8f64d217 Query thread_id=4299701 exec_time=0 error_code=0
SET TIMESTAMP=1575249204/*!*/;
BEGIN
/*!*/;
# at 352951929
#191202 9:13:24 server id 1189 end_log_pos 352952048 CRC32 0x96da7aa4 Table_map: `cms_global`.`template_fields` mapped to number 139
# at 352952048
# 对比主库和从库的表结构，可以看到是一致的，直接跳过即可
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;
# 主库
mysql> desc template_page_9342;
+-------------+----------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------+------+-----+---------+----------------+
| page_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | char(100) | NO | | NULL | |
| keyword | char(250) | NO | | NULL | |
| description | char(200) | NO | | NULL | |
| url | varchar(255) | NO | | NULL | |
| entity_id | int(10) unsigned | NO | | NULL | |
| entity_name | char(100) | NO | | NULL | |
| is_default | enum('Y','N') | NO | | Y | |
| html_time | datetime | NO | | NULL | |
| action_name | char(20) | NO | | NULL | |
| user_id_a | int(11) unsigned | NO | | NULL | |
| user_id_e | int(11) unsigned | NO | | NULL | |
| add_time | datetime | NO | | NULL | |
| edit_time | datetime | NO | | NULL | |
| user_name | char(20) | NO | | NULL | |
| state | enum('able','disable','deleted') | NO | | able | |
| content | longtext | NO | | NULL | |
| style | longtext | NO | | NULL | |
| script | longtext | NO | | NULL | |
+-------------+----------------------------------+------+-----+---------+----------------+
# 从库
MySQL [cms_global]> desc template_page_9342;
+-------------+----------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------+------+-----+---------+----------------+
| page_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | char(100) | NO | | NULL | |
| keyword | char(250) | NO | | NULL | |
| description | char(200) | NO | | NULL | |
| url | varchar(255) | NO | | NULL | |
| entity_id | int(10) unsigned | NO | | NULL | |
| entity_name | char(100) | NO | | NULL | |
| is_default | enum('Y','N') | NO | | Y | |
| html_time | datetime | NO | | NULL | |
| action_name | char(20) | NO | | NULL | |
| user_id_a | int(11) unsigned | NO | | NULL | |
| user_id_e | int(11) unsigned | NO | | NULL | |
| add_time | datetime | NO | | NULL | |
| edit_time | datetime | NO | | NULL | |
| user_name | char(20) | NO | | NULL | |
| state | enum('able','disable','deleted') | NO | | able | |
| content | longtext | NO | | NULL | |
| style | longtext | NO | | NULL | |
| script | longtext | NO | | NULL | |
+-------------+----------------------------------+------+-----+---------+----------------+
# 在主库中查找binlog报错，ERROR: Error in Log_event::read_log_event(): 'Sanity check failed' 是因为默认的mysqlbinlog命令是yum安装的低版本mysqlbinlog，需要指定我们运行的mysql高版本的命令
[root@newcms:/data/mysql_data]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000311 | grep -A 10 352951786
ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 31, event_type: 35
ERROR: Could not read entry at offset 123: Error in log format or read error.
[root@newcms:/data/mysql_data]# mysqlbinlog --base64-output=DECODE-ROWS -v --start-position=352951786 --stop-position=352951786 mysql-bin.000311 | more
ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 65, event_type: 34
ERROR: Could not read entry at offset 352951786: Error in log format or read error.
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
总结：
在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，查看主从同步是否恢复
