binlog_format 为 MIXED 的分析

妖狐艹你老母 2023-02-21 06:23 1阅读 0赞

os: centos 7.4.1708  
db: mysql 8.0.20

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # 
    # yum list installed |grep -i mysql80
    mysql-community-client.x86_64              8.0.20-1.el7                @mysql80-community
    mysql-community-common.x86_64              8.0.20-1.el7                @mysql80-community
    mysql-community-devel.x86_64               8.0.20-1.el7                @mysql80-community
    mysql-community-libs.x86_64                8.0.20-1.el7                @mysql80-community
    mysql-community-libs-compat.x86_64         8.0.20-1.el7                @mysql80-community
    mysql-community-server.x86_64              8.0.20-1.el7                @mysql80-community
    mysql-community-test.x86_64                8.0.20-1.el7                @mysql80-community
    mysql80-community-release.noarch           el7-3                       installed
    
    # mysql -e "select version();"
    +-----------+
    | version() |
    +-----------+
    | 8.0.20    |
    +-----------+

# binlog\_format = MIXED #

# vi /etc/my.cnf
    
    binlog_format = MIXED

# systemctl restart mysqld.service
    # mysql
    
    mysql> use test;
    
    mysql> show variables like 'log_bin';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_bin       | ON    |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql> show variables like 'binlog_format';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | binlog_format | MIXED |
    +---------------+-------+
    1 row in set (0.01 sec)
    
    mysql> show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000026 |      156 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    
    mysql> insert into tmp_t0 values(21,'21','21'),(22,'22','22');
    Query OK, 2 rows affected (0.33 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000026 |      479 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)

# /usr/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-position=156 --stop-position=479  /var/lib/mysql/binlog.000026
    
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    # at 125
    #200629 10:10:20 server id 1  end_log_pos 125 CRC32 0x5171427c 	Start: binlog v 4, server v 8.0.20 created 200629 10:10:20 at startup
    # Warning: this binlog is either in use or was not closed properly.
    ROLLBACK/*!*/;
    # at 156
    #200629 10:11:42 server id 1  end_log_pos 235 CRC32 0x1cf4aeea 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no	original_committed_timestamp=1593396702688893	immediate_commit_timestamp=1593396702688893	transaction_length=323
    # original_commit_timestamp=1593396702688893 (2020-06-29 10:11:42.688893 CST)
    # immediate_commit_timestamp=1593396702688893 (2020-06-29 10:11:42.688893 CST)
    /*!80001 SET @@session.original_commit_timestamp=1593396702688893*//*!*/;
    /*!80014 SET @@session.original_server_version=80020*//*!*/;
    /*!80014 SET @@session.immediate_server_version=80020*//*!*/;
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 235
    #200629 10:11:42 server id 1  end_log_pos 317 CRC32 0x0b6c415d 	Query	thread_id=8	exec_time=0	error_code=0
    SET TIMESTAMP=1593396702/*!*/;
    SET @@session.pseudo_thread_id=8/*!*/;
    SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    SET @@session.sql_mode=1168113696/*!*/;
    SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
    /*!\C utf8mb4 *//*!*/;
    SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
    SET @@session.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
    BEGIN
    /*!*/;
    # at 317
    #200629 10:11:42 server id 1  end_log_pos 448 CRC32 0x53b48a8b 	Query	thread_id=8	exec_time=0	error_code=0
    use `test`/*!*/;
    SET TIMESTAMP=1593396702/*!*/;
    insert into tmp_t0 values(21,'21','21'),(22,'22','22')
    /*!*/;
    # at 448
    #200629 10:11:42 server id 1  end_log_pos 479 CRC32 0x663f971c 	Xid = 10
    COMMIT/*!*/;
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    # End of log file
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

参考:  
https://dev.mysql.com/doc/refman/8.0/en/binary-log-setting.html