简介
Binlog（Binary Log）是MySQL数据库中的一种日志文件，它记录了数据库中所有修改数据的操作，包括插入（INSERT）、更新（UPDATE）、删除（DELETE）以及数据定义语言（DDL）操作，如创建（CREATE）、修改（ALTER）和删除（DROP）表等。。
配置参数
查看BINLOG文件
About 5 min
查看
mysqlbinlog
前提
binlog_format=rowbinlog_row_image=fullbinlog_rows_query_log_events=off
命令
输出详细的 SQL
# 解析binlog
mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000008
# ***********************输出如下***********************
# at 2607 --表示这是一个 position
#240717 13:52:04 server id 1 end_log_pos 2686 CRC32 0xd673a51b Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=yes original_committed_timestamp=1721195524808310 immediate_commit_timestamp=1721195524808310 transaction_length=357
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1721195524808310 (2024-07-17 13:52:04.808310 CST)
# immediate_commit_timestamp=1721195524808310 (2024-07-17 13:52:04.808310 CST)
/*!80001 SET @@session.original_commit_timestamp=1721195524808310*//*!*/;
/*!80014 SET @@session.original_server_version=80037*//*!*/;
/*!80014 SET @@session.immediate_server_version=80037*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2686
#240717 13:52:04 server id 1 end_log_pos 2772 CRC32 0xc813e61b Query thread_id=22 exec_time=0 error_code=0
SET TIMESTAMP=1721195524/*!*/;
BEGIN
/*!*/;
# at 2772
#240717 13:52:04 server id 1 end_log_pos 2847 CRC32 0x3ce01deb Table_map: `mybatis`.`role` mapped to number 108
# has_generated_invisible_primary_key=0
# at 2847
#240717 13:52:04 server id 1 end_log_pos 2933 CRC32 0x63218733 Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `mybatis`.`role`
### SET
### @1=2
### @2='TEACHAER'
### @3='Teacher'
### @4='教师'
### @5='老师'
### @6=b'1'
### @7='2024-07-17 13:52:04'
### @8='2024-07-17 13:52:04'
# at 2933
#240717 13:52:04 server id 1 end_log_pos 2964 CRC32 0xfe84517b Xid = 413
COMMIT/*!*/;
# at 2964
#240717 15:26:09 server id 1 end_log_pos 3043 CRC32 0x23b7e7b1 Anonymous_GTID last_committed=6 sequence_number=7 rbr_only=yes original_committed_timestamp=1721201169478520 immediate_commit_timestamp=1721201169478520 transaction_length=427
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1721201169478520 (2024-07-17 15:26:09.478520 CST)
# immediate_commit_timestamp=1721201169478520 (2024-07-17 15:26:09.478520 CST)
/*!80001 SET @@session.original_commit_timestamp=1721201169478520*//*!*/;
/*!80014 SET @@session.original_server_version=80037*//*!*/;
/*!80014 SET @@session.immediate_server_version=80037*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3043
#240717 15:26:09 server id 1 end_log_pos 3138 CRC32 0xd7ffff4a Query thread_id=22 exec_time=0 error_code=0
SET TIMESTAMP=1721201169/*!*/;
BEGIN
/*!*/;
# at 3138
#240717 15:26:09 server id 1 end_log_pos 3213 CRC32 0x0e6db3ba Table_map: `mybatis`.`role` mapped to number 108
# has_generated_invisible_primary_key=0
# at 3213
#240717 15:26:09 server id 1 end_log_pos 3360 CRC32 0xba48de84 Update_rows: table id 108 flags: STMT_END_F
### UPDATE `mybatis`.`role`
### WHERE
### @1=2
### @2='TEACHAER'
### @3='Teacher'
### @4='教师'
### @5='老师'
### @6=b'1'
### @7='2024-07-17 13:52:04'
### @8='2024-07-17 13:52:04'
### SET
### @1=2
### @2='TEACHAER'
### @3='Teacher'
### @4='教师'
### @5='老师，教师'
### @6=b'1'
### @7='2024-07-17 13:52:04'
### @8='2024-07-17 15:26:09'
# at 3360
#240717 15:26:09 server id 1 end_log_pos 3391 CRC32 0x5086dcb7 Xid = 447
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
About 2 min
DML（数据操纵语言）
- 目的：用于对数据库中的数据进行检索、插入、更新和删除。
- 常用语句：
SELECT：从数据库中检索数据。INSERT：向数据库表中添加新的数据行。UPDATE：修改数据库表中的现有数据。DELETE：从数据库表中删除数据。
- 特点：DML操作直接影响数据库的数据内容，但不改变表的结构。
About 2 min