第78讲：MySQL数据库Binlog日志的核心概念与应用案例

1.Binlog二进制日志的基本概念

1.1.什么是Binlog二进制

MySQL数据库中的Binlog二进制日志中，记录了所有的DDL数据库定义语句和DML数据操纵语句，但是在二进制日志中不包括DQL数据查询语句。

Binlog二进制日志会记录数据的所有操作记录，几乎是实时的，当数据库发生故障导致数据丢失，并且也没有备份时，可以从Binlog日志中恢复数据库的数据。

Binlog日志的作用：

• 灾难时可恢复数据库中的数据。
• MySQL主从复制通过Binlog来同步数据。

Binlog日志默认会保留30年内产生的二进制日志，并且每当重启MySQL后，就会向新的Binlog日志中写入数据。

1.2.Binlog日志的三种记录格式

针对Binlog的日志记录格式有ROW、STATEMENT、MIXED三种。

1）ROW格式

ROW格式是MySQL针对Binlog日志的默认记录格式，简称RBR，是针对行的记录模式，对表中的每一行数据变更都进行记录。

ROW格式的记录由于是针对每一行的变化都进行记录，就会导致日志量级很大，占用部分磁盘空间，并且可读性交叉，会通过自己的语法格式记录一行数据修改前后的内容，虽然可读性差，但是记录到日志里的行变化数据是非常准确的，事务提交后数据变动前后是什么样子记录到日志里就会是什么样子，非常的严谨。

ROW会将数据变化的前后内容都以独特的格式记录在日志中，因此当需要通过Binlog进行日志恢复时，不会出现由于某个字段是函数写入的值，例如时间函数这类的，ROW格式的Binlog记录的旧数据是什么样子的不会因为函数的取值的变化而变化，保证数据的一致性，避免恢复后的数据异常。

ROW格式也是MySQL 5.7默认的格式，企业环境中建议使用。

查看ROW格式的Binlog时需要通过mysqlbinlog命令的-v参数将Binlog中的数据重构成我们易读取的SQL语句。

2）STATEMENT格式

SYAYEMENT格式是针对SQL语句进行记录的，简称SBR，当数据有变化时，将对应的SQL语句记录到日志中，日志里相对于ROW格式来说很少，例如当用户删除了表中id>1000的数据，对于SYAYEMENT格式就只会记录这一条SQL语句，而ROW格式会记录每一行数据执行前后的内容。

SYAYEMENT格式的可读性很强，但是对于语句中的某个字段值是以函数来取值的，就会造成数据从错乱，例如时间函数，当时写在表中的时间是以now函数获取的那个时候的时间，而数据恢复时又是通过now函数，获取的则是当前时间，就会造成数据不一致。

3）MIXED格式

MIXED格式是混合类型的格式，即记录STATEMENT格式又会记录ROW格式，由MySQL自己判断什么样的数据是以什么样的格式去记录。

通过以下命令可以查看当前数据库Binlog的记录格式。

1.3.Binlog日志中Event事件的概念

Event事件是Binlog二进制日志中记录的最小单元，在Binlog中DDL、DCL等语句一个语句就是一个Event事件。

在Binlog日志中对于DML语句来讲，只会记录已经提交事务的DML语句，

如下所示：一组事务大概分成了四个Event事件，每个Event事件都有开始标识位和结束标识位作为位置号，在起始位置和结束位置内记录的就是事件的内容，也就具体SQL。

一个事件的结束标识位和下一个事件的开始标识位是一样的，也就表示每一个事件之间是由顺序关联的，不会出现偏差，例如begin这个事件的开始标识位是120，结束标识位是340，DML1的开始标识与begin的结束标识是相同的，都是340。

这个开始标识和结束标识相当于一个位置号，都是顺序记录的。

				开始标识  结束标识
begin;			   120	   340
	DML1		   340	   460
	DML2		   460     550
commit;             550     760

当我们需要只针对Binlog中部分的事件进行数据恢复，就可以通过开始标识和结束标识（Position号）来进行截取，通过Position号截取的内容要比时间截取的更加精准。还是如上所示，当我们要截取这个事务的日志数据，要进行数据恢复，那么就只需要截取开始标识位120到结束标识位760之间的事件信息，进行数据恢复即可。

2.开启MySQL的Binlog二进制日志

在MySQL5.7版本中Binlog默认不开启。

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | OFF                         |				#OFF表示关闭binlog
| log_bin_basename                |                             |
| log_bin_index                   |                             |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+

开启Binlog日志。

1.在主配置文件中开启Binlog
[root@mysql ~]# vim /etc/my.cnf 
[mysqld]
server_id=1
log_bin=mysql-bin					#binlog日志的名称，不指定路径默认在数据目录创建，mysql-bin只是binlog的前缀
binlog_format=row					#binlog的记录类型
max_binlog_size=1G					#binlog日志文件的大小

2.重启MySQL
[root@mysql ~]# systemctl restart mysqld

3.查看生成的Binlog日志
[root@mysql ~]# ll /data/mysql/mysql-bin.*
-rw-r----- 1 mysql mysql 154 6月  30 17:00 /data/mysql/mysql-bin.000001
-rw-r----- 1 mysql mysql  19 6月  30 17:00 /data/mysql/mysql-bin.index
#二进制日志默认从000001开始，每当重启一次MySQL就会产生一个新的的binlog日志

每当重启一次数据库或者当Binlog日志容量超出最大限制时，就会产生一个新的Binlog日志文件，在mysql-bin.index文件中记录了当前MySQL实例中所有的Binlog日志文件有哪些以及其路径，MySQL在读Binlog日志时首先会读取index这个文件。

3.查看Binlog二进制日志中的Event事件信息

我们可以通过命令来查看当前数据库正在往哪一个Binlog日志文件中写入日志数据，并且我们再前面也提到了针对DDL、DCL、DML这类的语句，每一条都是以一个事件记录在Binlog日志中的，我们可以通过通过命令来查看指定的Binlog日志文件中有那些事件，还可以看到事件的内容是什么，也就是了解这个事件做了什么操作。

当我们清楚了Event事件之后，就可以再通过命令去查看Event事件中的具体内容，包括修改数据的SQL等等。

3.1.查看当前数据库有那些Binlog日志

首先来查看一下当前数据库中有那些Binlog日志文件，以及正在使用的是哪个Binlog日志文件。

通过以下命令可以查看当前数据库中有哪些Binlog日志文件，一般来说最后一行就是我们当前正在使用的Binlog日志文件，第二列的File_size并不是说该Binlog日志文件当前的容量有多大，这个File_size指的是这个Binlog日志中最大结束标识位是多少，也就是Position号偏移量记录到了那里。

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       154 |
+------------------+-----------+

通过以下命令可以看到当前数据库正在往哪一个Binlog日志中记录数据，当前数据库正在往mysql-bin.000002日志文件中写入数据，Position一列记录的就是该日志文件中目前记录到的结束标识位号，也就是最大的标识位号，默认从4开始，1-3作为保留，另外前154个标识位号是记录MySQL的一些信息的，是MySQL5.7版本独有的特性，后面的字段是主从复制中才会用到的。

可以看到这个Position号与前面的File_size是一致的，所以更加证明了前面那个命令显示的不是文件大小，而是该日志中记录到哪一个结束标识号了。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3.2.产生一些DDL/DML语句

产生一些DDL、DML语句，方便后面观察Binlog日志中针对每一条语句产生的时间信息。

1.创建个数据库
mysql> create database db_2;

2.创建个表
mysql> use db_2;
mysql> create table t1 (id int);

3.插入一条数据
mysql> insert into t1 values (1);

3.3.观察Binlog日志中产生的Event事件

通过以下命令可以查看指定Binlog日志中的事件信息，每一个事件都对应一条DDL、DCL、DML语句。

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.36-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 | 219 | Query          |         1 |         313 | create database db_2                  |
| mysql-bin.000002 | 313 | Anonymous_Gtid |         1 |         378 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 | 378 | Query          |         1 |         476 | use `db_2`; create table t1 (id int)  |
| mysql-bin.000002 | 476 | Anonymous_Gtid |         1 |         541 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 | 541 | Query          |         1 |         613 | BEGIN                                 |
| mysql-bin.000002 | 613 | Table_map      |         1 |         658 | table_id: 108 (db_2.t1)               |
| mysql-bin.000002 | 658 | Write_rows     |         1 |         698 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000002 | 698 | Xid            |         1 |         729 | COMMIT /* xid=16 */                   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
11 rows in set (0.00 sec)

输出的内容每个字段的含义：

• Log_name：Binlog日志的名称

• Pos：开始的标识位号，默认从4开始，1-3是保留的标识位，前154个标识位是记录MySQL的一些信息。从上面输出的内容我们可以看到从标识位4-154之间的事件都是关于MySQL信息的一些事件内容。从154之后才是真正用户操作的信息。

• Event_type：事件的类型，主要关心Query类型。

• Server_id：声明这个时间是由哪个数据库实例产生的，主要是在主从复制模式下，说明都是由主节点产生的事件。

• End_log_pos：结束标识位。

• Info：具体时间的内容，DDL语句还好，在Info中显示除了具体的DDL操作语句，但是像DML类型的操作只是记录了这是一个事务，事务内的SQL语句却看不出来是什么，如果我们硬要看事务内执行的SQL，就需要去读Binlog日志了。

4.分析Binlog日志内容的技巧

通过Binlog的事件我们只能看到DDL执行的语句，对于DML语句是看不懂的，下面就需要去Binlog日志中读取DML的内容，在读取DML内容之前，我们先来掌握分析Binlog日志文件的技巧。

通过mysqlbinlog命令分析Binlog日志文件。

[root@mysql ~]# mysqlbinlog /data/mysql/mysql-bin.000002 

如下图所示，会输出很多很多的内容，但是实际上SET开头的语句是没有用的，并且占比也比较多，我们可以过滤掉。

[root@mysql ~]# mysqlbinlog /data/mysql/mysql-bin.000002 | grep -v 'SET'

这样一看就好多了，但是也别急，刚刚说了标识位4-154都是MySQL内部的信息，无需看，因此我们从第154个标识位处开始分析就行了，一个标识位到另一个标识位之间的内容就是这个Event事件的具体内容，DDL语句还是能清晰的看到，但是DML语句依然被ROW格式单独声明了，还是无法查看，不过后面有办法。

在日志中我们可以看到有很多行的注释，这个注释也是很有作用的：例如这个#220630 22:58:17 server id 1注释，就记录了再2022年的6月30日，22点58分17秒的时候，由server id为1的节点触发了下面的Binlog日志。我们也可以通过注释中的时间截取某个时间段的Binlog日志。

5.读取Binlog日志记录的DML语句详细内容

在前面无论我们是通过事件还是直接读取Binlog日志，对于DML语句的部分都是通过ROW格式独特的显示方式展示的，让我们无法读懂，从而不知道DML做了什么操作。

针对ROW格式独有的显示方式，可以通过mysqlbinlog命令的-v参数，将其重构成我们能读懂的SQL语句。

[root@mysql ~]# mysqlbinlog -v /data/mysql/mysql-bin.000002 

或者使用下面的这个命令，通过base64的方式将ROW格式重构成SQL语句。

[root@mysql ~]# mysqlbinlog --base64-output=decode-rows -vvvv /data/mysql/mysql-bin.000002 

将ROW格式的内容重构成SQL语句如下,INSERT语句会产生一个事务，我们将重构完关于事务的这个事件内容摘了出来，重构成SQL语句之后，就会在事务中显示具体的DML SQL语句。

# at 541
#220630 22:58:17 server id 1  end_log_pos 613 CRC32 0xfb4afd37 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1656601097/*!*/;
BEGIN
/*!*/;
# at 613
#220630 22:58:17 server id 1  end_log_pos 658 CRC32 0x19bd3592 	Table_map: `db_2`.`t1` mapped to number 108
# at 658
#220630 22:58:17 server id 1  end_log_pos 698 CRC32 0xce62c766 	Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `db_2`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 698
#220630 22:58:17 server id 1  end_log_pos 729 CRC32 0x6f054645 	Xid = 16
COMMIT/*!*/;

从第10行到12行，开头是###的都是DML语句，记录的都是INSERT插入数据的语句，不过和我们常规的INSERT不同，在这里多了一个SET指令，SET后面是@1=1，这个@1表示的是这条数据中的第一个字段，等于号后面是这个字段的值，整个INSERT连起来的意思就是说：在t1表中插入了一条数据，插入的这条数据中第一个字段的值为1，因为表中只有一个字段，因此只显示了一行，如果有多个字段，会显示多个@数字，数字是几就表示是第几个字段对应的值。