2 MySQL日志开启

本文目标

部署一个MySQL测试库，并且设置为master，启用binlog，用来作为数据同步的源库

MySQL8开启binlog

参考：https://debezium.io/documentation/reference/2.0/connectors/mysql.html#enable-mysql-binlog

my.cnf内关键信息如下：

log-bin=mysql-bin
binlog-format=row
server-id=1
binlog_row_image=full
expire_logs_days=7
gtid_mode=ON
enforce_gtid_consistency=ON

重启生效

bin/mysql restart

验证开启是否正常

[yinyx@localhost data]$ ../bin/mysqlbinlog ./mysql-bin.000002 --base64-output=decode-rows -vv
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#221202 15:55:42 server id 1  end_log_pos 126 CRC32 0x8298fd3c 	Start: binlog v 4, server v 8.0.31 created 221202 15:55:42 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 126
#221202 15:55:42 server id 1  end_log_pos 157 CRC32 0xdf113d14 	Previous-GTIDs
# [empty]
# at 157
#221202 16:02:35 server id 1  end_log_pos 236 CRC32 0x5c03a884 	GTID	last_committed=0	sequence_number=1	rbr_only=no	original_committed_timestamp=1669968155523479	immediate_commit_timestamp=1669968155523479	transaction_length=298
# original_commit_timestamp=1669968155523479 (2022-12-02 16:02:35.523479 CST)
# immediate_commit_timestamp=1669968155523479 (2022-12-02 16:02:35.523479 CST)
/*!80001 SET @@session.original_commit_timestamp=1669968155523479*//*!*/;
/*!80014 SET @@session.original_server_version=80031*//*!*/;
/*!80014 SET @@session.immediate_server_version=80031*//*!*/;
SET @@SESSION.GTID_NEXT= '7bdc8394-71cf-11ed-b2d5-000c293c9462:1'/*!*/;
# at 236
#221202 16:02:35 server id 1  end_log_pos 455 CRC32 0xde84b358 	Query	thread_id=10	exec_time=0	error_code=0	Xid = 74
use `test`/*!*/;
SET TIMESTAMP=1669968155/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
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*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `test`.`t1`  (
  `f1` bigint(255) NOT NULL,
  `f2` varchar(50) NULL,
  `f3` datetime NULL,
  PRIMARY KEY (`f1`)
)
/*!*/;
# at 455
#221202 16:03:02 server id 1  end_log_pos 534 CRC32 0xffc0d059 	GTID	last_committed=1	sequence_number=2	rbr_only=yes	original_committed_timestamp=1669968182050159	immediate_commit_timestamp=1669968182050159	transaction_length=302
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1669968182050159 (2022-12-02 16:03:02.050159 CST)
# immediate_commit_timestamp=1669968182050159 (2022-12-02 16:03:02.050159 CST)
/*!80001 SET @@session.original_commit_timestamp=1669968182050159*//*!*/;
/*!80014 SET @@session.original_server_version=80031*//*!*/;
/*!80014 SET @@session.immediate_server_version=80031*//*!*/;
SET @@SESSION.GTID_NEXT= '7bdc8394-71cf-11ed-b2d5-000c293c9462:2'/*!*/;
# at 534
#221202 16:03:02 server id 1  end_log_pos 617 CRC32 0xb5a8d4af 	Query	thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1669968182/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
BEGIN
/*!*/;
# at 617
#221202 16:03:02 server id 1  end_log_pos 675 CRC32 0x4c1bc379 	Table_map: `test`.`t1` mapped to number 115
# at 675
#221202 16:03:02 server id 1  end_log_pos 726 CRC32 0x4ecb24e5 	Write_rows: table id 115 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='a' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3='2022-12-02 16:02:57' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
# at 726
#221202 16:03:02 server id 1  end_log_pos 757 CRC32 0xe3d1d5f9 	Xid = 96
COMMIT/*!*/;
# at 757
#221202 16:03:33 server id 1  end_log_pos 836 CRC32 0x11091c71 	GTID	last_committed=2	sequence_number=3	rbr_only=no	original_committed_timestamp=1669968213699382	immediate_commit_timestamp=1669968213699382	transaction_length=253
# original_commit_timestamp=1669968213699382 (2022-12-02 16:03:33.699382 CST)
# immediate_commit_timestamp=1669968213699382 (2022-12-02 16:03:33.699382 CST)
/*!80001 SET @@session.original_commit_timestamp=1669968213699382*//*!*/;
/*!80014 SET @@session.original_server_version=80031*//*!*/;
/*!80014 SET @@session.immediate_server_version=80031*//*!*/;
SET @@SESSION.GTID_NEXT= '7bdc8394-71cf-11ed-b2d5-000c293c9462:3'/*!*/;
# at 836
#221202 16:03:33 server id 1  end_log_pos 1010 CRC32 0x693946bf 	Query	thread_id=10	exec_time=0	error_code=0	Xid = 99
SET TIMESTAMP=1669968213/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
ALTER TABLE `test`.`t1` 
MODIFY COLUMN `f3` datetime(0) NULL DEFAULT now() AFTER `f2`
/*!*/;
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*/;
[yinyx@localhost data]$