MySQL binlog

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日志为止。这样就可以完成数据库恢复的功能。

怎么配置binlog

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

  1. mysql> show variables like 'log_bin';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | log_bin | ON |
  6. +---------------+-------+
  7. 1 row in set, 1 warning (0.02 sec)

上例中bin_log系统变量的值为ON,表明当前服务器生成binlog,若为OFF表明当前服务器不生成binlog。
如果当前服务器不生成binlog,要开启binlog,那么就需要重启服务器,设置log-bin启动选项:

  1. --log-bin[=base_name]

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

  1. basename.000001
  2. basename.000002
  3. basename.000003
  4. basename.000004
  5. ...

也就是这组日志文件名称都包含一个basename,然后以一个数字结尾。
启动选项log-bin[=base_name]中的base_name就是这组binlog日志文件名称都包含的部分。如果不指定base_name(即单纯的使用—log-bin),那MySQL服务器会默认将主机名-bin作为binlog日志文件的basename。
看一下例子。
如果启动服务器的命令是:

  1. mysqld --log-bin

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

  1. xiaohaizi-bin.000001
  2. xiaohaizi-bin.000002
  3. xiaohaizi-bin.000003
  4. xiaohaizi-bin.000004
  5. ...

如果启动命令是:

  1. mysqld --log-bin=xx

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

  1. xx.000001
  2. xx.000002
  3. xx.000003
  4. xx.000004
  5. ...

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

  1. mysqld --log-bin=/Users/xiaohaizi/xx

这样binlog日志就会被写入/Users/xiaohaizi/路径下,binlog日志文件名就像是这样:

  1. xx.000001
  2. xx.000002
  3. xx.000003
  4. xx.000004
  5. ...

小贴士:
log-bin启动选项也可以放在配置文件中,这里就不赘述了。

binlog在文件系统中的内容

刚强调,binlog日志不是单个文件,而是一组包含共同basename的文件。比方说现在机器上有以下4个binlog文件:

  1. xiaohaizi-bin.000001
  2. xiaohaizi-bin.000002
  3. xiaohaizi-bin.000003
  4. xiaohaizi-bin.000004

这些binlog文件并不能直接被当作文本打开,毕竟人家的名字是binlog,存储的是二进制数据。
除了真正存储binlog日志的文件外,MySQL服务器还会在相同的路径下生成一个关于binlog的索引文件,在系统上它的名称就是:

  1. xiaohaizi-bin.index

这个索引文件是一个文本文件,可以直接打开:

  1. shell> cat xiaohaizi-bin.index
  2. ./xiaohaizi-bin.000001
  3. ./xiaohaizi-bin.000002
  4. ./xiaohaizi-bin.000003
  5. ./xiaohaizi-bin.000004

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

查看binlog的语句

binlog中记录数据库发生更改的各种事件(events),这些事件的种类非常多,完整的事件类型如下所示:

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

其中的一些熟悉的事件:

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

像创建、修改数据库或者表结构这些语句也都可以找到对应类型的事件,这里就不一一展开了。
为了查看binlog中包含了哪些事件,可以使用下边这个语句(带中括号[]的表示可以省略的语句):

  1. SHOW BINLOG EVENTS
  2. [IN 'log_name']
  3. [FROM pos]
  4. [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日志文件的内容,这里的机器上就是xiaohaizi-bin.000001这个文件的内容:

  1. mysql> SHOW BINLOG EVENTS;
  2. +----------------------+-----+----------------+-----------+-------------+---------------------------------------+
  3. | Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
  4. +----------------------+-----+----------------+-----------+-------------+---------------------------------------+
  5. | xiaohaizi-bin.000001 | 4 | Format_desc | 3 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
  6. | xiaohaizi-bin.000001 | 123 | Previous_gtids | 3 | 154 | |
  7. | xiaohaizi-bin.000001 | 154 | Anonymous_Gtid | 3 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
  8. | xiaohaizi-bin.000001 | 219 | Query | 3 | 296 | BEGIN |
  9. | xiaohaizi-bin.000001 | 296 | Table_map | 3 | 367 | table_id: 138 (xiaohaizi.s1) |
  10. | xiaohaizi-bin.000001 | 367 | Update_rows | 3 | 634 | table_id: 138 flags: STMT_END_F |
  11. | xiaohaizi-bin.000001 | 634 | Xid | 3 | 665 | COMMIT /* xid=65 */ |
  12. | xiaohaizi-bin.000001 | 665 | Stop | 3 | 688 | |
  13. +----------------------+-----+----------------+-----------+-------------+---------------------------------------+
  14. 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:关于本事件的一些说明。

如果您对MySQL了解不多的话,那上边执行SHOW BINLOG EVENTS语句的输出的大部分事件可能都比较懵,可能能看懂两个事件:
•起始偏移量为219(Pos=219)的事件是一个表明开始事务(BEGIN)的事件。•起始偏移量为367(Pos=367)的事件是一个更新记录的事件(UPDATE)。
好了现在先不细究各种类型的事件都代表什么,目前只需要知道binlog日志是由若干个事件组成的就好了。
如果想看其他binlog日志的详细情况,那就需要用到IN子句了:

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

这样就看到了xiaohaizi-bin.000004这个binlog日志文件中存储了哪些事件了。
大家可以自行测试一下FROM子句和LIMIT子句的用法,这里就不展示了。

mysqlbinlog工具的使用

由于binlog是二进制格式的,不能直接以文本的形式查看。使用SHOW BINLOG EVENTS又只能看到粗略的信息,如果想查看binlog日志文件的详细信息的话,就需要使用MySQL提供的实用工具——mysqlbinlog。
像mysqld、mysql这些可执行文件一样,mysqlbinlog也被放在了MySQL安装目录下的bin目录下。
可以将想查看的binlog日志文件路径作为mysqlbinlog的参数,就能查看文本形式的事件详细信息了。比方说看一下xiaohaizi-bin.000001:

  1. shell> mysqlbinlog ./xiaohaizi-bin.000001
  2. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  3. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  4. DELIMITER /*!*/;
  5. # at 4
  6. #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
  7. ROLLBACK/*!*/;
  8. BINLOG '
  9. irWoYQ8DAAAAdwAAAHsAAAAAAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  10. AAAAAAAAAAAAAAAAAACKtahhEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
  11. AVtxCKM=
  12. '/*!*/;
  13. # at 123
  14. #211202 20:01:14 server id 3 end_log_pos 154 CRC32 0x0d6a1ce6 Previous-GTIDs
  15. # [empty]
  16. # at 154
  17. #211202 20:07:07 server id 3 end_log_pos 219 CRC32 0xab157b64 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
  18. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  19. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  20. # at 219
  21. #211202 20:07:07 server id 3 end_log_pos 296 CRC32 0xedb6b609 Query thread_id=2 exec_time=0 error_code=0
  22. SET TIMESTAMP=1638446827/*!*/;
  23. SET @@session.pseudo_thread_id=2/*!*/;
  24. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  25. SET @@session.sql_mode=1436549152/*!*/;
  26. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  27. /*!\C utf8 *//*!*/;
  28. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
  29. SET @@session.lc_time_names=0/*!*/;
  30. SET @@session.collation_database=DEFAULT/*!*/;
  31. BEGIN
  32. /*!*/;
  33. # at 296
  34. #211202 20:07:07 server id 3 end_log_pos 367 CRC32 0x43cd57ee Table_map: `xiaohaizi`.`s1` mapped to number 138
  35. # at 367
  36. #211202 20:07:07 server id 3 end_log_pos 634 CRC32 0xe2981d9e Update_rows: table id 138 flags: STMT_END_F
  37. BINLOG '
  38. 67aoYRMDAAAARwAAAG8BAAAAAIoAAAAAAAEACXhpYW9oYWl6aQACczEACAMPDw8PDw8PDiwBLAEs
  39. ASwBLAEsASwB/u5XzUM=
  40. 67aoYR8DAAAACwEAAHoCAAAAAIoAAAAAAAEAAgAI//8ApAMAABgAZmt3YW91b2syY2sxY2RlMzA2
  41. bzZ2NHcxCQAxMzA4NzI2NzgTAHBqdHFxc2dsMngxMjd4MWZjdngBAG0MAHBycmp3bmtxbjV1aRoA
  42. NHN3cWJsNXEzd3V2bzUyZGdscmI1eWlmencJAGxzMjFoNHZhNwCkAwAAGABma3dhb3VvazJjazFj
  43. ZGUzMDZvNnY0dzEJADEzMDg3MjY3OBMAcGp0cXFzZ2wyeDEyN3gxZmN2eAEAbQwAcHJyandua3Fu
  44. NXVpGgA0c3dxYmw1cTN3dXZvNTJkZ2xyYjV5aWZ6dwIAeHieHZji
  45. '/*!*/;
  46. # at 634
  47. #211202 20:07:07 server id 3 end_log_pos 665 CRC32 0xe586ffeb Xid = 65
  48. COMMIT/*!*/;
  49. # at 665
  50. #211202 20:07:19 server id 3 end_log_pos 688 CRC32 0x8c69bad2 Stop
  51. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  52. DELIMITER ;
  53. # End of log file
  54. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  55. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

其中以# at xx开头的表示这是一个事件的开始,诸如:
# at 4:表示在偏移量为4的地方有一个事件,下边是该事件的详细信息。
# at 123表示在偏移量为123的地方有一个事件,下边是该事件的详细信息。

binlog日志版本

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

  • v1
  • v2
  • v3
  • v4

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

binlog日志文件结构概览

废话少说,先看一下一个binlog日志文件的基本格式:
binlog - 图1
从上图中可以看出:

  • 每个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为例展示一下:
binlog - 图2
其中:

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

    event header结构

    每个事件都会包括一个通用的event header,看一下这个event header的结构:
    binlog - 图3
    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),可能在将来的版本中使用,大家目前忽略这个字段就好了。

    event data

    event data由2部分组成,分别是:

  • 固定大小部分

  • 可变大小部分

binlog - 图4
不过并不是所有事件都有这两个部分,有的事件可以仅有其中的一个部分或者两个部分都没有。
MySQL中支持几十种binlog事件,不同事件具有不同的event data部分。
先看一下binlog的事件类型有多少(上一篇文章中引用MySQL internal文档中的内容,有点陈旧,所以这次直接从MySQL5.7.22的源码中获取Log_event_type结构):

  1. enum Log_event_type
  2. {
  3. /**
  4. Every time you update this enum (when you add a type), you have to
  5. fix Format_description_event::Format_description_event().
  6. */
  7. UNKNOWN_EVENT= 0,
  8. START_EVENT_V3= 1,
  9. QUERY_EVENT= 2,
  10. STOP_EVENT= 3,
  11. ROTATE_EVENT= 4,
  12. INTVAR_EVENT= 5,
  13. LOAD_EVENT= 6,
  14. SLAVE_EVENT= 7,
  15. CREATE_FILE_EVENT= 8,
  16. APPEND_BLOCK_EVENT= 9,
  17. EXEC_LOAD_EVENT= 10,
  18. DELETE_FILE_EVENT= 11,
  19. /**
  20. NEW_LOAD_EVENT is like LOAD_EVENT except that it has a longer
  21. sql_ex, allowing multibyte TERMINATED BY etc; both types share the
  22. same class (Load_event)
  23. */
  24. NEW_LOAD_EVENT= 12,
  25. RAND_EVENT= 13,
  26. USER_VAR_EVENT= 14,
  27. FORMAT_DESCRIPTION_EVENT= 15,
  28. XID_EVENT= 16,
  29. BEGIN_LOAD_QUERY_EVENT= 17,
  30. EXECUTE_LOAD_QUERY_EVENT= 18,
  31. TABLE_MAP_EVENT = 19,
  32. /**
  33. The PRE_GA event numbers were used for 5.1.0 to 5.1.15 and are
  34. therefore obsolete.
  35. */
  36. PRE_GA_WRITE_ROWS_EVENT = 20,
  37. PRE_GA_UPDATE_ROWS_EVENT = 21,
  38. PRE_GA_DELETE_ROWS_EVENT = 22,
  39. /**
  40. The V1 event numbers are used from 5.1.16 until mysql-trunk-xx
  41. */
  42. WRITE_ROWS_EVENT_V1 = 23,
  43. UPDATE_ROWS_EVENT_V1 = 24,
  44. DELETE_ROWS_EVENT_V1 = 25,
  45. /**
  46. Something out of the ordinary happened on the master
  47. */
  48. INCIDENT_EVENT= 26,
  49. /**
  50. Heartbeat event to be send by master at its idle time
  51. to ensure master's online status to slave
  52. */
  53. HEARTBEAT_LOG_EVENT= 27,
  54. /**
  55. In some situations, it is necessary to send over ignorable
  56. data to the slave: data that a slave can handle in case there
  57. is code for handling it, but which can be ignored if it is not
  58. recognized.
  59. */
  60. IGNORABLE_LOG_EVENT= 28,
  61. ROWS_QUERY_LOG_EVENT= 29,
  62. /** Version 2 of the Row events */
  63. WRITE_ROWS_EVENT = 30,
  64. UPDATE_ROWS_EVENT = 31,
  65. DELETE_ROWS_EVENT = 32,
  66. GTID_LOG_EVENT= 33,
  67. ANONYMOUS_GTID_LOG_EVENT= 34,
  68. PREVIOUS_GTIDS_LOG_EVENT= 35,
  69. TRANSACTION_CONTEXT_EVENT= 36,
  70. VIEW_CHANGE_EVENT= 37,
  71. /* Prepared XA transaction terminal event similar to Xid */
  72. XA_PREPARE_LOG_EVENT= 38,
  73. /**
  74. Add new events here - right above this comment!
  75. Existing events (except ENUM_END_EVENT) should never change their numbers
  76. */
  77. ENUM_END_EVENT /* end marker */
  78. };

可见在MySQL 5.7.22这个版本中,共支持38种不同的binlog事件类型。下边只举一个具体的例子进行描述。

举一个具体的例子——格式描述事件

每个binlog日志文件都以格式描述事件作为第一个事件,它对应的Log_event_type就是FORMAT_DESCRIPTION_EVENT。看一下这种事件的结构:
binlog - 图5
从图中可以知道,格式描述事件共占用119字节,是由event header和event data两部分构成的,其中event header是各个事件都有的部分,上边详细描述过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日志文件来看一下:

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

小贴士:其他事件的event data部分大家可以参考一下MySQL internal文档。另外,也可以使用mysqlbinlog,配合--hexdump启动选项来直接分析binlog的二进制格式。

基于语句(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日志的格式。

基于语句的binlog

假如服务器启动时添加了--binlog-format=STATEMENT启动选项,执行如下语句:

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

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

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

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

基于行的binlog

假如服务器启动时添加了--binlog-format=ROW启动选项,执行如下语句:

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

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

  1. mysqlbinlog --verbose xiaohaizi-bin.000008
  2. ...这里省略了很多内容
  3. ### UPDATE `xiaohaizi`.`s1`
  4. ### WHERE
  5. ### @1=9991
  6. ### @2='7cgwfh14w6nql61pvult6ok0ccwe'
  7. ### @3='799105223'
  8. ### @4='c'
  9. ### @5='gjjiwstjysv1lgx'
  10. ### @6='zg1hsvqrtyw2pgxgg'
  11. ### @7='y244x02'
  12. ### @8='xx'
  13. ### SET
  14. ### @1=9991
  15. ### @2='7cgwfh14w6nql61pvult6ok0ccwe'
  16. ### @3='799105223'
  17. ### @4='c'
  18. ### @5='gjjiwstjysv1lgx'
  19. ### @6='zg1hsvqrtyw2pgxgg'
  20. ### @7='y244x02'
  21. ### @8='xxx'
  22. ### UPDATE `xiaohaizi`.`s1`
  23. ### WHERE
  24. ### @1=9992
  25. ### @2='2sfq3oftc'
  26. ### @3='815047282'
  27. ### @4='ub'
  28. ### @5='73hw14kbaaoa'
  29. ### @6='fxnqzef3rrpc7qzxcjsvt14nypep4rqi'
  30. ### @7='10vapb6'
  31. ### @8='xx'
  32. ### SET
  33. ### @1=9992
  34. ### @2='2sfq3oftc'
  35. ### @3='815047282'
  36. ### @4='ub'
  37. ### @5='73hw14kbaaoa'
  38. ### @6='fxnqzef3rrpc7qzxcjsvt14nypep4rqi'
  39. ### @7='10vapb6'
  40. ### @8='xxx'
  41. ...这里省略了很多内容

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

基于语句的binlog的问题

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

  1. CREATE TABLE t (
  2. id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  3. c VARCHAR(100),
  4. PRIMARY KEY(ID)
  5. );

如果执行如下语句:

  1. 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日志,就不会造成主从之间不一致了。