[技术分享]-mysql binlog_binlog.000028-优快云博客

本文链接：https://blog.youkuaiyun.com/qiaoqiao0609/article/details/86677538

本文详细介绍了MySQL的binlog机制，包括如何查询binlog状态，binlog事件类型如QUERY_EVENT、FORMAT_DESCRIPTION_EVENT、ROWS_EVENT、XID_EVENT、ROTATE_EVENT和GTID_LOG_EVENT的用途，以及在主主同步和数据恢复中的作用。同时，讨论了binlog日志格式和DML、DDL操作在binlog中的记录方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-> 首先需要一组有监听权限的userName & passWord

-> 使用命令 SHOW BINARY LOGS; 查询当前sql binlog情况mysql> SHOW BINARY LOGS;

+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.011330 | 1073747807 |
| mysql-bin.011331 | 1073742698 |
| mysql-bin.011332 | 1073743433 |
+------------------+------------+

-> 使用如下命令查询当前正在写入的binlog：

mysql> show master status\G;

*************************** 1. row ***************************
             File: mysql-bin.011335
         Position: 675373082
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

其中 File: mysql-bin.011335 指出了当前正在写入的binlog 分片

-> 查询一个log分片里具体的事件：

mysql>  show binlog events in 'mysql-bin.011335' limit 10;

+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                                                                              |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.011335 |    4 | Format_desc |      1318 |         120 | Server ver: 5.6.35-log, Binlog ver: 4                                                                                                             |
| mysql-bin.011335 |  120 | Query       |      1318 |         211 | BEGIN                                                                                                                                             |
| mysql-bin.011335 |  211 | Query       |      1318 |         423 | use `media_data`; update taurus_doc_daily_201901 set sum_click = 756 ,updated_time = 1548657218 where doc_id = '0LBAQBes' and date ='2019-01-28'  |
| mysql-bin.011335 |  423 | Xid         |      1318 |         454 | COMMIT /* xid=22756080822 */                                                                                                                      |
| mysql-bin.011335 |  454 | Query       |      1318 |         545 | BEGIN                                                                                                                                             |
| mysql-bin.011335 |  545 | Query       |      1318 |         758 | use `media_data`; update taurus_doc_daily_201901 set sum_click = 29 ,updated_time = 1548657218 where doc_id = 'V_029sxL6i' and date ='2019-01-28' |
| mysql-bin.011335 |  758 | Xid         |      1318 |         789 | COMMIT /* xid=22756080825 */                                                                                                                      |
| mysql-bin.011335 |  789 | Query       |      1318 |         880 | BEGIN                                                                                                                                             |
| mysql-bin.011335 |  880 | Query       |      1318 |        1093 | use `media_data`; update taurus_doc_daily_201901 set sum_click = 10 ,updated_time = 1548657218 where doc_id = 'V_021JFdJH' and date ='2019-01-28' |
| mysql-bin.011335 | 1093 | Xid         |      1318 |        1124 | COMMIT /* xid=22756080828 */                                                                                                                      |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------+

其中：

Pos:日志文件的开始位置 Event_type:该条操作的事件类型 Server_id:服务器标识 End_log_pos: 日志文件的结束位置

->server-id的作用：

1. mysql的同步数据中是包含server-id的，用于标识该语句最初从哪个server写入，所以server-id一定要有
2. 每一个同步中的slave在master上都有对应的一个master线程，该线程就是通过slave的server-id来标识的；每个slave在master端最多有一个master线程，如果两个slave的server-id相同，则后一个连接成功时，前一个会被踢掉，slave主动连接master之后，如果slave上面执行了slave stop；则连接断开，但是master上对应的线程并没有退出；当slave start之后，master不能再创建一个线程而保留原来的线程，那样同步就可能有问题
3. 在mysql做主主同步时，多个主需要构成一个环状，但是同步的时候又要保证一条数据不会陷入死循环，这里就是靠server-id来实现的

binlog 日志格式：

1.开始事物的时间:
SET TIMESTAMP=1350355892/*!*/; BEGIN
2.sqlevent起点
#at 1643330 :为事件的起点，是以1643330字节开始。
3.sqlevent 发生的时间点
#121016 10:51:32:是事件发生的时间，
4.server id
server id 1 :为master 的serverId
5.sqlevent终点及花费时间，错误码
end_log_pos 1643885:为事件的终点，是以1643885 字节结束。
execTime 0: 花费的时间
error_code=0:错误码
Xid:事件指示提交的XA事务

========================================================================================

DML（data manipulation language）：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。
DDL（data definition language）：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

========================================================================================Q

MySQL binlog中的事件类型

QUERY_EVENT
QUERY_EVENT以文本的形式来记录事务的操作。
QUERY_EVENT类型的事件通常在以下几种情况下使用：
1. 事务开始时，执行的BEGIN操作。
2. STATEMENT格式中的DML操作
3. ROW格式中的DDL操作

mysql> show binlog events in 'mysql-bin.000021';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                          |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000021 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4         |
| mysql-bin.000021 | 120 | Query       |         1 |         195 | BEGIN                                         |
| mysql-bin.000021 | 195 | Query       |         1 |         298 | insert into test.t1 values(1,'a')             |
| mysql-bin.000021 | 298 | Xid         |         1 |         329 | COMMIT /* xid=25 */                           |
| mysql-bin.000021 | 329 | Query       |         1 |         408 | BEGIN                                         |
| mysql-bin.000021 | 408 | Query       |         1 |         515 | use `test`; insert into test.t1 values(2,'b') |
| mysql-bin.000021 | 515 | Xid         |         1 |         546 | COMMIT /* xid=33 */                           |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+

FORMAT_DESCRIPTION_EVENT
FORMAT_DESCRIPTION_EVENT是binlog version 4中为了取代之前版本中的START_EVENT_V3事件而引入的。它是binlog文件中的第一个事件，而且，该事件只会在binlog中出现一次。MySQL根据FORMAT_DESCRIPTION_EVENT的定义来解析其它事件。
它通常指定了MySQL Server的版本，binlog的版本，该binlog文件的创建时间。

mysql> show binlog events in 'mysql-bin.000021';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                          |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000021 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4         |

ROWS_EVENT
对于ROW格式的binlog，所有的DML语句都是记录在ROWS_EVENT中。
ROWS_EVENT分为三种：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT，分别对应i
sert，update和delete操作。
对于insert操作，WRITE_ROWS_EVENT包含了要插入的数据
对于update操作，UPDATE_ROWS_EVENT不仅包含了修改后的数据，还包含了修改前的值。
对于delete操作，仅仅需要指定删除的主键（在没有主键的情况下，会给定所有列）
对于QUERY_EVENT事件，是以文本形式记录DML操作的。而对于ROWS_EVENT事件，并不是文本形式，所以在通过mysqlbinlog查看基于ROW格式的binlog时，需要指定-vv --base64-output=decode-rows。

mysql> show binlog events in 'mysql-bin.000027';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000027 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000027 | 120 | Query       |         1 |         188 | BEGIN                                 |
| mysql-bin.000027 | 188 | Table_map   |         1 |         236 | table_id: 80 (test.t1)                |
| mysql-bin.000027 | 236 | Write_rows  |         1 |         278 | table_id: 80 flags: STMT_END_F        |
| mysql-bin.000027 | 278 | Xid         |         1 |         309 | COMMIT /* xid=198 */                  |
| mysql-bin.000027 | 309 | Query       |         1 |         377 | BEGIN                                 |
| mysql-bin.000027 | 377 | Table_map   |         1 |         425 | table_id: 80 (test.t1)                |
| mysql-bin.000027 | 425 | Update_rows |         1 |         475 | table_id: 80 flags: STMT_END_F        |
| mysql-bin.000027 | 475 | Xid         |         1 |         506 | COMMIT /* xid=199 */                  |
| mysql-bin.000027 | 506 | Query       |         1 |         574 | BEGIN                                 |
| mysql-bin.000027 | 574 | Table_map   |         1 |         622 | table_id: 80 (test.t1)                |
| mysql-bin.000027 | 622 | Delete_rows |         1 |         664 | table_id: 80 flags: STMT_END_F        |
| mysql-bin.000027 | 664 | Xid         |         1 |         695 | COMMIT /* xid=200 */                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+

XID_EVENT

在事务提交时，不管是STATEMENT还是ROW格式的binlog，都会在末尾添加一个XID_EVENT事件代表事务的结束。该事件记录了该事务的ID，在MySQL进行崩溃恢复时，根据事务在binlog中的提交情况来决定是否提交存储引擎中状态为prepared的事务。

ROTATE_EVENT

当binlog文件的大小达到max_binlog_size的值或者执行flush logs命令时，binlog会发生切换，这个时候会在当前的binlog日志添加一个ROTATE_EVENT事件，用于指定下一个日志的名称和位置。

mysql> show binlog events in 'mysql-bin.000028';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000028 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000028 | 120 | Rotate      |         1 |         167 | mysql-bin.000029;pos=4                |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+

GTID_LOG_EVENT

在启用GTID模式后，MySQL实际上为每个事务都分配了个GTID

GTID即全局事务ID（global transaction identifier），GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。下面是一个GTID的具体形式：

4e659069-3cd8-11e5-9a49-001c4270714e:1-77

# at 448
#160818  5:37:32 server id 1  end_log_pos 496 CRC32 0xaeb24aac     GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'cad449f2-5d4f-11e6-b353-000c29c64704:3'/*!*/;

# at 496
#160818  5:37:32 server id 1  end_log_pos 571 CRC32 0x042ca092     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1471469852/*!*/;
BEGIN /*!*/;

# at 571
#160818  5:37:32 server id 1  end_log_pos 674 CRC32 0xa35beb37     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1471469852/*!*/;
insert into test.t1 values(2,'b') /*!*/;

# at 674
#160818  5:37:32 server id 1  end_log_pos 705 CRC32 0x1905d8c6     Xid = 12
COMMIT/*!*/;

mysql> show binlog events in 'mysql-bin.000033';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000033 |   4 | Format_desc    |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4                             |
| mysql-bin.000033 | 120 | Previous_gtids |         1 |         191 | cad449f2-5d4f-11e6-b353-000c29c64704:1                            |
| mysql-bin.000033 | 191 | Gtid           |         1 |         239 | SET @@SESSION.GTID_NEXT= 'cad449f2-5d4f-11e6-b353-000c29c64704:2' |
| mysql-bin.000033 | 239 | Query          |         1 |         314 | BEGIN                                                             |
| mysql-bin.000033 | 314 | Query          |         1 |         417 | insert into test.t1 values(1,'a')                                 |
| mysql-bin.000033 | 417 | Xid            |         1 |         448 | COMMIT /* xid=11 */                                               |
| mysql-bin.000033 | 448 | Gtid           |         1 |         496 | SET @@SESSION.GTID_NEXT= 'cad449f2-5d4f-11e6-b353-000c29c64704:3' |
| mysql-bin.000033 | 496 | Query          |         1 |         571 | BEGIN                                                             |
| mysql-bin.000033 | 571 | Query          |         1 |         674 | insert into test.t1 values(2,'b')                                 |
| mysql-bin.000033 | 674 | Xid            |         1 |         705 | COMMIT /* xid=12 */                                               |
| mysql-bin.000033 | 705 | Rotate         |         1 |         752 | mysql-bin.000034;pos=4                                            |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

PREVIOUS_GTIDS_LOG_EVENT

开启GTID模式后，每个binlog开头都会有一个PREVIOUS_GTIDS_LOG_EVENT事件，它的值是上一个binlog的PREVIOUS_GTIDS_LOG_EVENT+GTID_LOG_EVENT，实际上，在数据库重启的时候，需要重新填充gtid_executed的值，该值即是最新一个binlog的PREVIOUS_GTIDS_LOG_EVENT+GTID_LOG_EVENT。

mysql> show binlog events in 'mysql-bin.000033';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000033 |   4 | Format_desc    |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4                             |
| mysql-bin.000033 | 120 | Previous_gtids |         1 |         191 | cad449f2-5d4f-11e6-b353-000c29c64704:1                            |
| mysql-bin.000033 | 191 | Gtid           |         1 |         239 | SET @@SESSION.GTID_NEXT= 'cad449f2-5d4f-11e6-b353-000c29c64704:2' |
| mysql-bin.000033 | 239 | Query          |         1 |         314 | BEGIN                                                             |
| mysql-bin.000033 | 314 | Query          |         1 |         417 | insert into test.t1 values(1,'a')                                 |
| mysql-bin.000033 | 417 | Xid            |         1 |         448 | COMMIT /* xid=11 */                                               |
| mysql-bin.000033 | 448 | Gtid           |         1 |         496 | SET @@SESSION.GTID_NEXT= 'cad449f2-5d4f-11e6-b353-000c29c64704:3' |
| mysql-bin.000033 | 496 | Query          |         1 |         571 | BEGIN                                                             |
| mysql-bin.000033 | 571 | Query          |         1 |         674 | insert into test.t1 values(2,'b')                                 |
| mysql-bin.000033 | 674 | Xid            |         1 |         705 | COMMIT /* xid=12 */                                               |
| mysql-bin.000033 | 705 | Rotate         |         1 |         752 | mysql-bin.000034;pos=4                                            |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

mysql> show binlog events in 'mysql-bin.000034';
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                     |
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
| mysql-bin.000034 |   4 | Format_desc    |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4    |
| mysql-bin.000034 | 120 | Previous_gtids |         1 |         191 | cad449f2-5d4f-11e6-b353-000c29c64704:1-3 |
+------------------+-----+----------------+-----------+-------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql-bin.000033日志中的Previous_gtids是cad449f2-5d4f-11e6-b353-000c29c64704:1，

GTID是cad449f2-5d4f-11e6-b353-000c29c64704:2和cad449f2-5d4f-11e6-b353-000c29c64704:3，这样，在下一个日志，即mysql-bin.000034中的Previous_gtids是cad449f2-5d4f-11e6-b353-000c29c64704:1-3。

# at 120
#160818  5:39:38 server id 1  end_log_pos 191 CRC32 0x4e84f3b5     Previous-GTIDs
# cad449f2-5d4f-11e6-b353-000c29c64704:1-3

STOP_EVENT

当MySQL数据库停止时，会在当前的binlog末尾添加一个STOP_EVENT事件表示数据库停止。

mysql> show binlog events in 'mysql-bin.000030';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000030 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4 |
| mysql-bin.000030 | 120 | Stop        |         1 |         143 |                                       |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.04 sec)

# at 120
#160818  5:18:04 server id 1  end_log_pos 143 CRC32 0xf20ddc85     Stop