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

最新推荐文章于 2022-08-02 11:31:53 发布

Two手揣兜

最新推荐文章于 2022-08-02 11:31:53 发布

阅读量1.2k

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/u013820054/article/details/51909472

版权

MySQL 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

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