MySQL[二十三]二进制日志 - 《MySQL 是怎样运行的学习笔记》

1.binlog的作用
2.怎么配置binlog
3.binlog在文件系统中的内容
4.查看binlog的语句
5.mysqlbinlog工具的使用
6.阶段小结
7.binlog日志版本
8.binlog日志文件结构概览
9.基于Statement和基于Row的binlog
- 9.1基于语句的binlog
- 9.2基于行的binlog
10.基于语句的binlog的问题

1.binlog的作用

binlog是binary log的缩写，即二进制日志。binlog中记载了数据库发生的变化，比方说新建了一个数据库或者表、表结构发生改变、表中的数据发生了变化时都会记录相应的binlog日志。

binlog主要用在下边两个方面：

用途一：用于复制。

单台物理机器所能同时处理的请求是有限的。为了提高并发处理请求的能力，一般将MySQL服务部署在多台物理机器中，这些服务器中维护相同的数据副本。

典型的部署方案就是一主多从，即一台主服务器（Master）和多台从服务器（Slave）。对于改变数据库状态的请求（DDL、DML等），就将它们发送给主服务器，对于单纯的查询（如SELECT语句）请求，就将它们发送给从服务器。为了让各个从服务器中存储的数据和主服务器中存储的数据一致，每当我们改变了主服务器中的数据后，就需要将改变的信息同步给各个从服务器。binlog日志中正好记录了数据库发生的各种改变的信息，从服务器读取主服务器产生的binlog日志，然后执行这些binlog日志中所记录的数据库变化语句，从而达到主从服务器数据一致的效果。

用途二：用于恢复。

写DELETE语句不加WHERE子句，一整个表的数据都就没了！为了数据的安全性，我们需要定时备份数据库（mysqldump命令），不过这种全量备份不可能每秒都做一遍，而是每天或者每个月做一次全量备份。那如果在两次全量备份中间写了不加WHERE条件的DELETE语句该怎么办呢？只能将数据库恢复到前一次全量备份时的样子吗？我们有binlog日志，我们可以从上一次全量备份开始，执行自该次备份后产生的binlog日志，直到我们写DELETE语句之前的binlog日志为止。这样就可以完成数据库恢复的功能。

2.怎么配置binlog

MySQL服务器并不一定会生成binlog日志，我们可以通过查看log_bin系统变量来判断当前MySQL服务器是否生成binlog日志：

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set, 1 warning (0.02 sec)

上例中bin_log系统变量的值为ON，表明当前服务器生成binlog，若为OFF表明当前服务器不生成binlog。

如果当前服务器不生成binlog，我们想开启binlog，那么就需要重启服务器，设置log-bin启动选项：

--log-bin[=base_name]

binlog日志并不是仅写到一个文件中，而是写入一组文件中，这组文件的命名是这样的：

basename.000001
basename.000002
basename.000003
basename.000004
...

也就是这组日志文件名称都包含一个basename，然后以一个数字结尾。

启动选项log-bin[=base_name]中的base_name就是这组binlog日志文件名称都包含的部分。如果我们不指定base_name（即单纯的使用—log-bin），那MySQL服务器会默认将主机名-bin作为binlog日志文件的basename。

如果启动服务器的命令是：

mysqld --log-bin

表示开启binlog，并将binlog写入MySQL服务器的数据目录下。我的主机名是yhd，那MySQL服务器程序生成的binlog日志文件名就像是这样：

yhd-bin.000001
yhd-bin.000002
yhd-bin.000003
yhd-bin.000004
...

如果启动命令是：

mysqld --log-bin=xx

表示开启binlog，并将binlog写入MySQL服务器的数据目录下，binlog日志文件名就像是这样：

xx.000001
xx.000002
xx.000003
xx.000004
...

我们可以在将启动选项log-bin[=base_name]的base_name指定为一个绝对路径，那么binlog日志就不会被放到默认的数据目录中，而是写到我们指定的绝对路径下了。比方说启动命令是：

mysqld --log-bin=/Users/yhd/xx

这样binlog日志就会被写入/Users/yhd/路径下，binlog日志文件名就像是这样：

xx.000001
xx.000002
xx.000003
xx.000004
...

log-bin启动选项也可以放在配置文件中。

3.binlog在文件系统中的内容

binlog日志不是单个文件，而是一组包含共同basename的文件。比方说现在我的机器上有以下4个binlog文件：

yhd-bin.000001
yhd-bin.000002
yhd-bin.000003
yhd-bin.000004

这些binlog文件并不能直接被当作文本打开，毕竟人家的名字是binlog，存储的是二进制数据。

除了真正存储binlog日志的文件外，MySQL服务器还会在相同的路径下生成一个关于binlog的索引文件，在我的系统上它的名称就是：

yhd-bin.index

这个索引文件是一个文本文件，我们可以直接打开：

shell> cat yhd-bin.index
./yhd-bin.000001
./yhd-bin.000002
./yhd-bin.000003
./yhd-bin.000004

可以看到，这个索引文件只是简单的将各个binlog文件的路径存储了起来而已。

4.查看binlog的语句

binlog中记录数据库发生更改的各种事件（events），这些事件的种类非常多，完整的事件类型如下所示：

enum Log_event_type { 
  UNKNOWN_EVENT= 0, 
  START_EVENT_V3= 1, 
  QUERY_EVENT= 2, 
  STOP_EVENT= 3, 
  ROTATE_EVENT= 4, 
  INTVAR_EVENT= 5, 
  LOAD_EVENT= 6, 
  SLAVE_EVENT= 7, 
  CREATE_FILE_EVENT= 8, 
  APPEND_BLOCK_EVENT= 9, 
  EXEC_LOAD_EVENT= 10, 
  DELETE_FILE_EVENT= 11, 
  NEW_LOAD_EVENT= 12, 
  RAND_EVENT= 13, 
  USER_VAR_EVENT= 14, 
  FORMAT_DESCRIPTION_EVENT= 15, 
  XID_EVENT= 16, 
  BEGIN_LOAD_QUERY_EVENT= 17, 
  EXECUTE_LOAD_QUERY_EVENT= 18, 
  TABLE_MAP_EVENT = 19, 
  PRE_GA_WRITE_ROWS_EVENT = 20, 
  PRE_GA_UPDATE_ROWS_EVENT = 21, 
  PRE_GA_DELETE_ROWS_EVENT = 22, 
  WRITE_ROWS_EVENT = 23, 
  UPDATE_ROWS_EVENT = 24, 
  DELETE_ROWS_EVENT = 25, 
  INCIDENT_EVENT= 26, 
  HEARTBEAT_LOG_EVENT= 27, 
  IGNORABLE_LOG_EVENT= 28,
  ROWS_QUERY_LOG_EVENT= 29,
  WRITE_ROWS_EVENT = 30,
  UPDATE_ROWS_EVENT = 31,
  DELETE_ROWS_EVENT = 32,
  GTID_LOG_EVENT= 33,
  ANONYMOUS_GTID_LOG_EVENT= 34,
  PREVIOUS_GTIDS_LOG_EVENT= 35, 
  ENUM_END_EVENT 
  /* end marker */ 
};

其中的一些我们熟悉的事件：

WRITE_ROWS_EVENT：插入记录。
UPDATE_ROWS_EVENT：更新记录。
DELETE_ROWS_EVENT：删除记录。

像创建、修改数据库或者表结构这些语句也都可以找到对应类型的事件。

为了查看binlog中包含了哪些事件，可以使用下边这个语句（带中括号[]的表示可以省略的语句）：

SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]

其中：

[IN 'log_name']：log_name表示我们要查看哪个binlog日志文件的内容。
[FROM pos]：pos表示我们要查看binlog文件的起始偏移量（通过指定这个值可以直接去查看某个偏移量处的事件）。
LIMIT [offset,] row_count：这个LIMIT子句的含义和我们写SQL语句中LIMIT子句的含义是一样的，offset表示我们要从哪个事件开始查看，row_count表示我们要查看多少个事件。

如果我们直接执行SHOW BINLOG EVENTS，表示查看第1个binlog日志文件的内容，在我的机器上就是yhd-bin.000001这个文件的内容：

mysql> SHOW BINLOG EVENTS;
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| yhd-bin.000001 |   4 | Format_desc    |         3 |         123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| yhd-bin.000001 | 123 | Previous_gtids |         3 |         154 |                                       |
| yhdi-bin.000001 | 154 | Anonymous_Gtid |         3 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| yhd-bin.000001 | 219 | Query          |         3 |         296 | BEGIN                                 |
| yhd-bin.000001 | 296 | Table_map      |         3 |         367 | table_id: 138 (yhd.s1)          |
| yhd-bin.000001 | 367 | Update_rows    |         3 |         634 | table_id: 138 flags: STMT_END_F       |
| yhd-bin.000001 | 634 | Xid            |         3 |         665 | COMMIT /* xid=65 */                   |
| yhd-bin.000001 | 665 | Stop           |         3 |         688 |                                       |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
8 rows in set (0.01 sec)

可以看到共输出了8个列，其中：

Log_name：表示binlog日志的文件名。
Pos：表示该事件在binlog日志文件中的起始偏移量。
Event_type：表示这个事件的类型。
Server_id：表示产生该事件的server_id（server_id是一个系统变量，我们可以通过配置让不同的MySQL服务器拥有不同的server_id）。
End_log_pos：表示下一个事件的在binlog日志文件中的起始偏移量。
Info：关于本事件的一些说明。

起始偏移量为219（Pos=219）的事件是一个表明开始事务（BEGIN）的事件。
起始偏移量为367（Pos=367）的事件是一个更新记录的事件（UPDATE）。

目前只需要知道binlog日志是由若干个事件组成的就好了。

如果想看其他binlog日志的详细情况，那就需要用到IN子句了：

mysql> SHOW BINLOG EVENTS IN 'yhd-bin.000004';
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                      |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------+
| yhd-bin.000004 |   4 | Format_desc    |         3 |         123 | Server ver: 5.7.21-log, Binlog ver: 4     |
| yhd-bin.000004 | 123 | Previous_gtids |         3 |         154 |                                           |
| yhd-bin.000004 | 154 | Anonymous_Gtid |         3 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'      |
| yhd-bin.000004 | 219 | Query          |         3 |         327 | use `yhd`; create table tt1 (c int) |
| yhd-bin.000004 | 327 | Anonymous_Gtid |         3 |         392 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'      |
| yhd-bin.000004 | 392 | Query          |         3 |         469 | BEGIN                                     |
| yhd-bin.000004 | 469 | Table_map      |         3 |         520 | table_id: 167 (yhd.tt1)             |
| yhd-bin.000004 | 520 | Write_rows     |         3 |         560 | table_id: 167 flags: STMT_END_F           |
| yhd-bin.000004 | 560 | Xid            |         3 |         591 | COMMIT /* xid=71 */                       |
| yhd-bin.000004 | 591 | Anonymous_Gtid |         3 |         656 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'      |
| yhd-bin.000004 | 656 | Query          |         3 |         733 | BEGIN                                     |
| yhd-bin.000004 | 733 | Table_map      |         3 |         784 | table_id: 167 (yhd.tt1)             |
| yhd-bin.000004 | 784 | Update_rows    |         3 |         830 | table_id: 167 flags: STMT_END_F           |
| yhd-bin.000004 | 830 | Xid            |         3 |         861 | COMMIT /* xid=73 */                       |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------+
14 rows in set (0.00 sec)

这样我们就看到了yhd-bin.000004这个binlog日志文件中存储了哪些事件了。

5.mysqlbinlog工具的使用

由于binlog是二进制格式的，我们不能直接以文本的形式查看。使用SHOW BINLOG EVENTS又只能看到粗略的信息，如果我们想查看binlog日志文件的详细信息的话，就需要使用MySQL给我们提供的实用工具——mysqlbinlog。

像mysqld、mysql这些可执行文件一样，mysqlbinlog也被放在了MySQL安装目录下的bin目录下。

我们可以将想查看的binlog日志文件路径作为mysqlbinlog的参数，就能查看文本形式的事件详细信息了。比方说我们看一下yhd-bin.000001：

shell> mysqlbinlog ./yhd-bin.000001
DELIMITER ;
# at 4
#211202 20:01:14 server id 3  end_log_pos 123 CRC32 0xa308715b   Start: binlog v 4, server v 5.7.21-log created 211202 20:01:14 at startup
ROLLBACK/*!*/;
BINLOG '
irWoYQ8DAAAAdwAAAHsAAAAAAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACKtahhEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AVtxCKM=
'/*!*/;
# at 123
#211202 20:01:14 server id 3  end_log_pos 154 CRC32 0x0d6a1ce6   Previous-GTIDs
# [empty]
# at 154
#211202 20:07:07 server id 3  end_log_pos 219 CRC32 0xab157b64   Anonymous_GTID  last_committed=0  sequence_number=1  rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#211202 20:07:07 server id 3  end_log_pos 296 CRC32 0xedb6b609   Query  thread_id=2  exec_time=0  error_code=0
SET TIMESTAMP=1638446827/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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/*!*/;
BEGIN
/*!*/;
# at 296
#211202 20:07:07 server id 3  end_log_pos 367 CRC32 0x43cd57ee   Table_map: `yhd`.`s1` mapped to number 138
# at 367
#211202 20:07:07 server id 3  end_log_pos 634 CRC32 0xe2981d9e   Update_rows: table id 138 flags: STMT_END_F
BINLOG '
67aoYRMDAAAARwAAAG8BAAAAAIoAAAAAAAEACXhpYW9oYWl6aQACczEACAMPDw8PDw8PDiwBLAEs
ASwBLAEsASwB/u5XzUM=
67aoYR8DAAAACwEAAHoCAAAAAIoAAAAAAAEAAgAI//8ApAMAABgAZmt3YW91b2syY2sxY2RlMzA2
bzZ2NHcxCQAxMzA4NzI2NzgTAHBqdHFxc2dsMngxMjd4MWZjdngBAG0MAHBycmp3bmtxbjV1aRoA
NHN3cWJsNXEzd3V2bzUyZGdscmI1eWlmencJAGxzMjFoNHZhNwCkAwAAGABma3dhb3VvazJjazFj
ZGUzMDZvNnY0dzEJADEzMDg3MjY3OBMAcGp0cXFzZ2wyeDEyN3gxZmN2eAEAbQwAcHJyandua3Fu
NXVpGgA0c3dxYmw1cTN3dXZvNTJkZ2xyYjV5aWZ6dwIAeHieHZji
'/*!*/;
# at 634
#211202 20:07:07 server id 3  end_log_pos 665 CRC32 0xe586ffeb   Xid = 65
COMMIT/*!*/;
# at 665
#211202 20:07:19 server id 3  end_log_pos 688 CRC32 0x8c69bad2   Stop
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*/;

其中以# at xx开头的表示这是一个事件的开始：

# at 4：表示在偏移量为4的地方有一个事件，下边是该事件的详细信息。
# at 123表示在偏移量为123的地方有一个事件，下边是该事件的详细信息。

…

6.阶段小结

binlog日志用于主从复制以及数据恢复。
启动选项--log-bin[=basename]可以控制MySQL服务器是否生成binlog，并且控制binlog日志文件所在路径以及文件名称。
为了记录binlog，MySQL服务器在文件系统上创建了一系列存储真实binlog数据的文件（这些文件都以数字编号），以及binlog索引文件。
binlog日志文件中记载了数据库发生更改的若干事件。
使用SHOW BINLOG EVENTS语句可以查看某个binlog日志文件中存储的各种事件。
mysqlbinlog实用工具可以用文本形式查看某个binlog日志文件所记载各种事件。

7.binlog日志版本

binlog是自MySQL 3.23.14版本开始诞生的，到现在为止，共经历了4个版本：

其中的v4版本从MySQL 5.0就开始使用，直到今天。所以着重介绍v4版本的binlog格式，其他版本就不关注了。

8.binlog日志文件结构概览

先看一下一个binlog日志文件的基本格式：

从上图中可以看出：

每个binlog日志文件的前4个字节是固定的，即：0xfe626963。

0xfe626963中的0x626963的ascii码是’bin’，0xfe626963也被称作魔数（magic number），如果一个文件不以0xfe626963开头，那这个文件肯定不算是一个binlog日志。很多软件都会在磁盘文件的某个地方添加一个类似的魔数来表明该文件是本软件处理的文件格式，比方说Intel处理器的BIOS会将磁盘上的第一个扇区加载到内存中，这个扇区的最后两个字节必须为魔数0x55aa，Java的class文件字节码的开头四个字节为魔数0xCAFEBABE。

每个binlog日志文件都是由若干事件构成的。
每个binlog日志文件所存储的第1个事件都是一个称作格式描述事件（format description event）的特殊事件。

其中，每个事件都可以被分成event header和event data两个部分，我们以上图的事件2为例展示一下：

其中：

event header部分描述了该事件是什么类型、什么时候生成的、由哪个服务器生成的等信息。
event data部分描述了该事件所特有的一些信息，比方说在插入一条记录时，需要将这条记录的内容记录在event data中。

8.1event header结构

每个事件都会包括一个通用的event header，看一下这个event header的结构：

event header中包含了如下几部分内容：

timestamp（4字节）：产生该事件时的时间戳。
typecode（1字节）：该事件的类型，事件的类型在枚举结构Log_event_type中列举出来。比方说格式描述事件的typecode就是15。
server_id（4字节）：产生该事件的主机的server_id。
event_length（4字节）：该事件总大小（包括event header + event data）。
next_position（4字节）：下一个事件的位置。
flags（2字节）：该事件的一些附加属性（称作flags）。
extra_headers（不确定大小）：目前这个字段尚未使用（也就是占用的大小为0），可能在将来的版本中使用。

8.2event data

event data由2部分组成，分别是：

固定大小部分
可变大小部分

不过并不是所有事件都有这两个部分，有的事件可以仅有其中的一个部分或者两个部分都没有。

MySQL中支持几十种binlog事件，不同事件具有不同的event data部分。

先看一下binlog的事件类型有多少，直接从MySQL5.7.22的源码中获取Log_event_type结构：

enum Log_event_type
{
  UNKNOWN_EVENT= 0,
  START_EVENT_V3= 1,
  QUERY_EVENT= 2,
  STOP_EVENT= 3,
  ROTATE_EVENT= 4,
  INTVAR_EVENT= 5,
  LOAD_EVENT= 6,
  SLAVE_EVENT= 7,
  CREATE_FILE_EVENT= 8,
  APPEND_BLOCK_EVENT= 9,
  EXEC_LOAD_EVENT= 10,
  DELETE_FILE_EVENT= 11,
  NEW_LOAD_EVENT= 12,
  RAND_EVENT= 13,
  USER_VAR_EVENT= 14,
  FORMAT_DESCRIPTION_EVENT= 15,
  XID_EVENT= 16,
  BEGIN_LOAD_QUERY_EVENT= 17,
  EXECUTE_LOAD_QUERY_EVENT= 18,
  TABLE_MAP_EVENT = 19,
  PRE_GA_WRITE_ROWS_EVENT = 20,
  PRE_GA_UPDATE_ROWS_EVENT = 21,
  PRE_GA_DELETE_ROWS_EVENT = 22,
  WRITE_ROWS_EVENT_V1 = 23,
  UPDATE_ROWS_EVENT_V1 = 24,
  DELETE_ROWS_EVENT_V1 = 25,
  INCIDENT_EVENT= 26,
  HEARTBEAT_LOG_EVENT= 27,
  IGNORABLE_LOG_EVENT= 28,
  ROWS_QUERY_LOG_EVENT= 29,
  WRITE_ROWS_EVENT = 30,
  UPDATE_ROWS_EVENT = 31,
  DELETE_ROWS_EVENT = 32,
  GTID_LOG_EVENT= 33,
  ANONYMOUS_GTID_LOG_EVENT= 34,
  PREVIOUS_GTIDS_LOG_EVENT= 35,
  TRANSACTION_CONTEXT_EVENT= 36,
  VIEW_CHANGE_EVENT= 37,
  XA_PREPARE_LOG_EVENT= 38,
  ENUM_END_EVENT 
};

在MySQL 5.7.22这个版本中，共支持38种不同的binlog事件类型。

8.3格式描述事件

每个binlog日志文件都以格式描述事件作为第一个事件，它对应的Log_event_type就是FORMAT_DESCRIPTION_EVENT。我们看一下这种事件的结构：

格式描述事件共占用119字节，是由event header和event data两部分构成的，其中event header是各个事件都有的部分。另外，在event data部分，格式描述事件的event data中只有固定长度部分，没有可变长度部分，其中的各个字段含义如下：

binlog_version：使用的binlog版本。
server_version：产生此事件的MySQL服务器的版本。
create_timestamp：产生此事件时的时间戳，该字段的值和event header中timestamp中的值一样。
header_length：此事件的event header占用的存储空间大小。
post-header length：使用1个字节来表示每个事件的event data部分占用的存储空间大小（不包括校验和相关字段），当前我使用的MySQL版本为5.7.22，共包含38种不同的事件，post-header length字段就占用了38个字节。
checksum_alg：表示计算事件校验和的算法（该字段为1时表示采用CRC32算法）。
checksum：表示本事件的校验和。

真正打开一个binlog日志文件来看一下：

魔数：      FE62696E 
timestamp:     8AB5A861  
typecode:     0F          
server_id:     03000000 
event_length:     77000000 
next_postion:     7B000000 
flags:      0000     
binlog_version:    0400   
server_version:    352E37 2E32312D 6C6F6700 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 000000  
create_timestamp:    8AB5A861 
header_length:     13       
post-header length(共38种): 380D0008 00120004 04040412 00005F00 041A0800 00000808 08020000 000A0A0A 2A2A0012 3400
checksum_alg：    01 
checksum：      5B7108A3

其他事件的event data部分可以参考MySQL internal文档。另外，也可以使用mysqlbinlog，配合—hexdump启动选项来直接分析binlog的二进制格式。

9.基于Statement和基于Row的binlog

同一条SQL语句，随着启动选项binlog-format的不同，可能生成不同类型的binlog事件：

当以启动选项--binlog-format=STATEMENT启动MySQL服务器时，生成的binlog称作基于语句的日志。此时只会将一条SQL语句将会被完整的记录到binlog中，而不管该语句影响了多少记录。
当以启动选项--binlog-format=ROW启动MySQL服务器时，生成的binlog称作基于行的日志。此时会将该语句所改动的记录的全部信息都记录上。
当以启动选项--binlog-format=MIXED启动MySQL服务器时，生成的binlog称作基于行的日志。此时在通常情况下采用基于语句的日志，在某些特殊情况下会自动转为基于行的日志（这些具体情况请参考：https://dev.mysql.com/doc/refman/8.0/en/binary-log-mixed.html）。

我们也可以通过修改会话级别的binlog_format系统变量的形式来修改只针对本客户端执行语句生成的binlog日志的格式。

9.1基于语句的binlog

假如服务器启动时添加了--binlog-format=STATEMENT启动选项，我们执行如下语句：

UPDATE s1 SET common_field = 'xx' WHERE id > 9990;

然后使用mysqlbinlog实用工具查看一下相应的binlog内容：

mysqlbinlog --verbose yhd-bin.000007
...这里省略了很多内容
# at 308
#211207 21:00:27 server id 3  end_log_pos 440 CRC32 0x713f80ae  Query thread_id=2 exec_time=0 error_code=0
use `yhd`/*!*/;
SET TIMESTAMP=1638882027/*!*/;
update s1 set common_field= 'xx' where id > 9990
/*!*/;
...这里省略了很多内容

可见，基于语句的binlog只将更新语句是什么记录下来了。

9.2基于行的binlog

假如服务器启动时添加了--binlog-format=ROW启动选项，我们执行如下语句：

UPDATE s1 SET common_field = 'xxx' WHERE id > 9990;

然后使用mysqlbinlog实用工具查看一下相应的binlog内容：

mysqlbinlog --verbose yhd-bin.000008
...这里省略了很多内容
### UPDATE `yhd`.`s1`
### WHERE
###   @1=9991
###   @2='7cgwfh14w6nql61pvult6ok0ccwe'
###   @3='799105223'
###   @4='c'
###   @5='gjjiwstjysv1lgx'
###   @6='zg1hsvqrtyw2pgxgg'
###   @7='y244x02'
###   @8='xx'
### SET
###   @1=9991
###   @2='7cgwfh14w6nql61pvult6ok0ccwe'
###   @3='799105223'
###   @4='c'
###   @5='gjjiwstjysv1lgx'
###   @6='zg1hsvqrtyw2pgxgg'
###   @7='y244x02'
###   @8='xxx'
### UPDATE `yhd`.`s1`
### WHERE
###   @1=9992
###   @2='2sfq3oftc'
###   @3='815047282'
###   @4='ub'
###   @5='73hw14kbaaoa'
###   @6='fxnqzef3rrpc7qzxcjsvt14nypep4rqi'
###   @7='10vapb6'
###   @8='xx'
### SET
###   @1=9992
###   @2='2sfq3oftc'
###   @3='815047282'
###   @4='ub'
###   @5='73hw14kbaaoa'
###   @6='fxnqzef3rrpc7qzxcjsvt14nypep4rqi'
###   @7='10vapb6'
###   @8='xxx'
...这里省略了很多内容

可见，基于行的binlog将更新语句执行过程中每一条记录更新前后的值都记录下来了。

10.基于语句的binlog的问题

在有主从复制的场景中，使用基于语句的日志可能会造成主服务器和从服务器维护的数据不一致的情况。

比方说我们有一个表t：

CREATE TABLE t (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    c VARCHAR(100),
    PRIMARY KEY(ID)
);

如果我们执行如下语句：

INSERT INTO t(c) SELECT c FROM other_table;

这个语句是想将other_table表中列c的值都插入到表t的列c中，而表t的id列是自增列，可以自动生成。

如果主库和从库的服务器执行SELECT c FROM other_table返回记录的顺序不同的话（不同服务器版本、不同的系统变量配置都可能导致同一条语句返回结果的顺序不同），那么针对表t相同id值的记录来说，列c就可能具有不同的值，这就会造成主从之间数据的不一致。

而如果将binlog的格式改为基于行的日志的话，由于主库在执行完语句后将该语句插入的每条完整的记录都写入binlog日志，就不会造成主从之间不一致了。