MySQL:GTID切换回传统复制报错

来源：互联网发布：api数据编辑：程序博客网时间：2024/06/04 00:41

最近测试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=0SET 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=0SET 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=0SET 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=0SET 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-传统，最好保持主从数据一致

0 0