如果在互为主从的架构下，开始log-slave-updates不就会导致一个事务在两个mysql之间不断循环?

程序员文章站 2022-03-24 09:00:00

实验:双主架构。master1 IP:172.16.4.61 server_id=333301master2 IP:172.16.4.62 server_id=333302解析二进制日志：mysqlbinlog --start-position='155093235' --stop-position="155094483" --base64-output=decode-rows -v -v -d wxh53 /logs/3333/binglog/3333-binlog.000022>...

实验:

双主架构。

master1 IP:172.16.4.61 server_id=333301

master2 IP:172.16.4.62 server_id=333302

解析二进制日志：

mysqlbinlog --start-position='155093235' --stop-position="155094483" --base64-output=decode-rows -v -v -d wxh53 /logs/3333/binglog/3333-binlog.000022>wx1.log

mysqlbinlog --start-position='155093589' --stop-position="155094837" --base64-output=decode-rows -v -v -d wxh53 /logs/3333/binglog/3333-binlog.000001>wx1.log

日志如下：

#201009 11:46:17 server id 333301 end_log_pos 155093024 CRC32 0x8b8350bc Anonymous_GTID last_committed=331 sequence_numbe
r=332 rbr_only=noSET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 155093024
#201009 11:46:17 server id 333301 end_log_pos 155093589 CRC32 0xec728d56 Query thread_id=107 exec_time=0 error_code=0
use `wxh53`/*!*/;
SET TIMESTAMP=1602215177/*!*/;
SET @@session.pseudo_thread_id=107/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `sq_aud_array` ( `INPUT_ID` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '日至写入
id', `IND` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数组的下标', `STR` text CHARACTER SET
utf8 COLLATE utf8_general_ci NULL COMMENT '数据信息', `INPUT_DATE` datetime(0) NULL DEFAULT NULL COMMENT '操作日期' ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

发现双主二进制日志里的server_id是主库的（首次产生二进制日志），说明双主架构数据库可通过server_id来避免内循环。

本文地址：https://blog.csdn.net/w892824196/article/details/108976128