mysql数据库基于gtid复制,mysql基于gtid复制的错误解决

1.错误描述

Last_SQL_Errno: 1032

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction 'fec611e4-99a2-11e6-b35d-000c29f8c231:294' at master log mysql-bin.000005, end_log_pos 8539. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

2.导出二进制日志

[root@hadoop-master ~]# mysqlbinlog --no-defaults  /data/mysql/mysql-bin.000006>mysql005.sql

ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 71, event_type: 35

ERROR: Could not read entry at offset 123: Error in log format or read error.

错误分析：

[root@hadoop-master ~]# mysqlbinlog -V#查看mysqlbinlog的版本

mysqlbinlog: unknown variable 'default-character-set=utf8'#不能识别utf8格式

[root@hadoop-master ~]# mysqlbinlog --no-defaults -V#忽略格式

mysqlbinlog Ver 3.3 for redhat-linux-gnu at x86_64

[root@hadoop-master ~]# /usr/local/mysql/bin/mysqlbinlog -V

mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'

[root@hadoop-master ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -V

/usr/local/mysql/bin/mysqlbinlog Ver 3.4 for Linux at x86_64

原来是mysqlbinlog的版本和在用的mysql版本不一致造成的(在该主机上安装有rpm包的mysql和编译安装的mysql，两者版本不一致)，指定路径执行如下命令就OK了

[root@hadoop-master ~]# /usr/local/mysql/bin/mysqlbinlog --no-defaults  /data/mysql/mysql-bin.000006>mysql005.sql

导出日志成功

3.查看从库报错位置的日志内容

# at 8280

#161102 19:14:27 server id 1  end_log_pos 8359 CRC32 0x1bcbd89b         Table_map: `phpcmsv9`.`v9_sessio

n` mapped to number 108

# at 8359

#161102 19:14:27 server id 1  end_log_pos 8539 CRC32 0x5370f32f         Delete_rows: table id 108 flags: STMT_END_F

初步判断是从库中的数据缺少导致的错误。

4.查看缺少数据

主库

mysql> select * from v9_session;

+----------------------------+--------+----------------+------------+--------+---------+--------+-------+-------+-------------------+

| sessionid                  | userid | ip             | lastvisit  | roleid | groupid | m      | c     | a     | data              |

+----------------------------+--------+----------------+------------+--------+---------+--------+-------+-------+-------------------+

| k72hd5veq4bsh3tn7ro7teu4n6 |      0 | 172.18.104.39  | 1478138576 |      0 |       0 |        |       |       | code|s:5:"a5wdn"; |

| i1bpheatv33dqlpku3s9ki2j33 |      0 | 172.18.109.207 | 1478138403 |      0 |       0 | member | index | login | code|s:0:"";      |

从库

mysql> select * from v9_session;

Empty set (0.00 sec)

发现从库没有该表的数据

5.恢复从库中没有的数据

mysqldump -u root -p phpcmsv9 v9_session>sess.sql#主库中或其他从库中导出v9_session表

mysql -u root -p phpcmsv9< sess.sql #在导入数据到从库的表中

如果导出表时包含了gtid，导入时会报错：

Warning: Using a password on the command line interface can be insecure.

ERROR 1840 (HY000) at line 24: GTID_PURGED can only be set when GTID_EXECUTED is empty

解决：reset master

再次执行导入操作成功。

重新启动slave：start slave

如果同步仍然报错，可以重新同步主库即可解决总从同步的错误。

(责任编辑：liangzh)