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

ゝ一世哀愁。 2022-07-21 08:58 131阅读 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-传统，最好保持主从数据一致