最近测试GTID切换回传统复制,报错如下
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Found a Gtid_log_event or Previous_gtids_log_event when @@GLOBAL.GTID_MODE = OFF.; the first event 'mysql-bin.000069' at 424920, the last event read from '/data/mysql/mysql3306/logs/mysql-bin.000069' at 424968, the last byte read from '/data/mysql/mysql3306/logs/mysql-bin.000069' at 424968.'
解析mysql-bin.000069日志
/usr/local/mysql/bin/mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.000069 > 1.log
# at 424920
#160714 11:24:25 server id 1593306 end_log_pos 424968 CRC32 0x4ccfebf3 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'babb81f3-e8c4-11e5-bb5e-080027d6e4e2:10809'/*!*/;
# at 424968
#160714 11:24:25 server id 1593306 end_log_pos 425040 CRC32 0xd8cdbb41 Query thread_id=106 exec_time=0 error_code=0
SET TIMESTAMP=1468466665/*!*/;
BEGIN
/*!*/;
# at 425040
#160714 11:24:25 server id 1593306 end_log_pos 425084 CRC32 0x05e03c37 Table_map: `test`.`t` mapped to number 116
# at 425084
#160714 11:24:25 server id 1593306 end_log_pos 425124 CRC32 0xb919347d Write_rows: table id 116 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=14
# at 425124
#160714 11:24:25 server id 1593306 end_log_pos 425197 CRC32 0x43c50bc1 Query thread_id=106 exec_time=0 error_code=0
SET TIMESTAMP=1468466665/*!*/;
COMMIT
/*!*/;
# at 425197
#160714 11:24:26 server id 1593306 end_log_pos 425245 CRC32 0x5b5e813b GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'babb81f3-e8c4-11e5-bb5e-080027d6e4e2:10810'/*!*/;
# at 425245
#160714 11:24:26 server id 1593306 end_log_pos 425317 CRC32 0x0adb8984 Query thread_id=106 exec_time=0 error_code=0
SET TIMESTAMP=1468466666/*!*/;
BEGIN
/*!*/;
# at 425317
#160714 11:24:26 server id 1593306 end_log_pos 425361 CRC32 0x85722172 Table_map: `test`.`t` mapped to number 116
# at 425361
#160714 11:24:26 server id 1593306 end_log_pos 425401 CRC32 0xe889c20b Write_rows: table id 116 flags: STMT_END_F
### INSERT INTO `test`.`t`
### SET
### @1=15
# at 425401
#160714 11:24:26 server id 1593306 end_log_pos 425474 CRC32 0x023e4997 Query thread_id=106 exec_time=0 error_code=0
SET TIMESTAMP=1468466666/*!*/;
COMMIT
根据报错,我从新指定POS点
change master to master_host='192.168.56.159',
master_port=3306,
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000069',
master_log_pos=424968;
那他应该会在425245继续出错,原因就是 我从GTID已经关闭,不能执行
SET @@SESSION.GTID_NEXT= 'babb81f3-e8c4-11e5-bb5e-080027d6e4e2:10810'/*!*/;
执行后
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Found a Gtid_log_event or Previous_gtids_log_event when @@GLOBAL.GTID_MODE = OFF.; the first event 'mysql-bin.000069' at 424968, the last event read from '/data/mysql/mysql3306/logs/mysql-bin.000069' at 425245, the last byte read from '/data/mysql/mysql3306/logs/mysql-bin.000069' at 425245.
整个原因是:我从GTID切换回传统复制,主从之间存在延迟,GTID会执行
SET @@SESSION.GTID_NEXT= 'babb81f3-e8c4-11e5-bb5e-080027d6e4e2:10810'/*!*/; 语句,但是从上@@GLOBAL.GTID_MODE = OFF,索引报错。
所以切换传统-GTID,GTID-传统,最好保持主从数据一致