MySQL 日志系统 - binLog - MySQL 数据库 | 方家小白 = 和你一起遇见更好的自己

# 前言

MySQL 手册 - binary Log

# 简介

binLog 日志系统是由一组二进制文件和一个索引文件组成。

binLog 全称是 binary log ，即为二进制日志文件。 binlog 是记录所有数据库表结构变更以及表数据修改的二进制日志。不会记录 Select , show 等操作。 binLog 日志是以事件形式记录，包含了语句执行的消耗时间，正确重现该语句所需的服务器状态信息，错误代码，维护 binlog 本身所需的元数据等。

准确地说:
二进制日志事件描述了可用于重现服务器上发生的相同全局状态更改的操作。

索引文件是记录了当前的二进制日志文件的文本文件。

日志文件使用.NNNNNN 后缀顺序编号。索引文件的后缀为.index。所有文件共享一个通用基名。默认的二进制日志文件命名基名称为 “HOSTNAME-bin”。使用默认的基本名称，二进制日志包含具有以下名称的文件：

# bin-log文件
fangjiaxiaobai-bin.0000001
fangjiaxiaobai-bin.0000002
fangjiaxiaobai-bin.0000003
...
# 索引文件
fangjiaxiaobai-bin.index

# binLog 的使用场景。

主从复制：主库中开启 binlog , 从库读取 binlog 后，执行 binlog ，从而达到数据一致性。
数据恢复：通过保存的 binlog ，可以使用 mysqlbinlog 工具来恢复数据。

# binlong 记录模式

binLog 文件默认为 <主机名>_binlog_<序列号> 格式。也可以在配置文件中指定名称。
文件记录模式有 STATEMENT , ROW , MIXED 三种。具体含义如下:

ROW 😦 row-based replication , RBR )：日志中会记录每一行数据被修改的情况，然后 Slave 端对相同的数据进行修改.
- 优点：可以清楚的记录每行数据的修改细节，能完全实现主从数据同步和数据的恢复。
- 缺点：批量操作会产生大量的日志，尤其是 alter table 操作，会让日志爆涨。
STATEMENT : ( statement-based replication,SBR )：
- 优点：日志量小，减少磁盘 IO, 提供存储和恢复速度。
- 缺点：不可靠。如果是主从架构的话使用 last_insert_id() , now() 等函数，会导致主从数据不一致。
MIXED : ( mixed-based replication , MBR )：以上两种模式的混合使用。默认使用 STATEMENT 模式保存 binlog , 也可以根据自己需要切换到 ROW 模式，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog , MySQL 会根据执行的 SQL 语句选择写入模式。

# binLog 文件格式

binlog 文件的格式也随着 mysql 的发展，发生了几次变更。
二进制日志文件格式有几个版本:
v1版本 ：在 MySQL3.23 版本中使用。
v2版本 ：在早期的 MySQL 4.0 中，简单地使用了 v2 格式。)，但它已经过时，不再受支持。
v3版本 ：在 MySQL 4.0.2 到 4.1 中使用。
v4版本 ：适用于 MySQL 5.0 及以上版本。

一个 binlog 文件由两部分组成: 4 字节的魔数 和 标识文件格式的初始描述符事件。并且：初识描述符事件也是由两部分组成: header 和 data 部分。

这里只介绍 V4 版本:
BinLog-event图示结构.png

在 event header 这一部分，可以看到有如下字段:

timestamp : 4 个字节。标识某条语句开始执行的时间。它表示为自 1970 年以来的秒数 ( UTC )，与 SQL 时间戳数据类型类似。
type_code : 1 个字节。事件的类型。 1 表示开始事件 V3 , 2 表示查询事件，以此类推。这些数字在 Log event.h 中的 enum 日志事件类型枚举中定义。(请参阅附录 1。)
server_id :4 字节。最初创建事件的 mysqld 服务器的 ID 。它来自服务器配置文件中为复制目的设置的 server-id 选项。使用循环复制时（使用选项 --log-slave-updates 启用），服务器 ID 可以避免无限循环
event_length : 4 个字节。这次活动的总规模。这包括头和数据部分。大多数事件小于 1000 字节，除了使用加载数据 INFILE 时 (其中事件包含加载的文件，所以它们可以很大)
next_position : 4 个字节。下一个事件在主程序二进制日志中的位置。这个字段主要是在 slave 端使用：
- 为了显示 Slave 的状态，能够在主坐标系统中显示最后执行的事件的坐标。
- 为了验证 START SLAVE UNTIL MASTER LOG FILE=x , MASTER LOG POS=y ，这样可以使用 MASTER 的坐标。
flags : 1 个字节。标识。具体含义见附录 2。
extra_headers ：大小可变的。此字段的大小由作为文件中的第一个事件发生的格式描述事件决定。目前，大小为 0 ，因此，实际上，该字段在任何事件中都不会实际发生。当大小变为非零时，该字段仍然不会出现在格式描述事件或旋转事件类型的事件中。

接下来，我们详细的看下 event-data 部分的结构是什么样的.

binLog-event格式.png

event-data 表示：特定的事件数据。
事件的数据部分由固定大小的部分和可变大小的部分组成。根据事件类型，这两个部分中的一个或两个都可能为空。事件数据部分的大小是事件大小（包含在标头中）减去标头大小。固定数据部分的大小是事件类型的函数。可变数据部分的大小是事件大小减去标题的大小再减去固定数据部分的大小。

binLog 的基本概念就介绍到这里，下面展示一些 binlog 相关的命令:

# 查看binLog属性
mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------------------------------------------+
| Variable_name                   | Value                                                       |
+---------------------------------+-------------------------------------------------------------+
| log_bin                         | ON                                                          |
| log_bin_basename                | /usr/local/mysql-8.0.20-macos10.15-x86_64/data/binlog       |
| log_bin_index                   | /usr/local/mysql-8.0.20-macos10.15-x86_64/data/binlog.index |
| log_bin_trust_function_creators | OFF                                                         |
| log_bin_use_v1_row_events       | OFF                                                         |
| sql_log_bin                     | ON                                                          |
+---------------------------------+-------------------------------------------------------------+
6 rows in set (0.00 sec)

## 开启/关闭 binLog功能，需要在配置文件中修改。
# set global log_bin; 会报错。

## 查看binlog文件
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000038 |       156 | No        |
| binlog.000039 |       156 | No        |
| binlog.000040 |       179 | No        |
| binlog.000041 |       179 | No        |
| binlog.000042 |       179 | No        |
| binlog.000043 |      5307 | No        |
| binlog.000044 |       179 | No        |
| binlog.000045 |       156 | No        |
+---------------+-----------+-----------+
8 rows in set (0.01 sec)

mysql> show master status
    -> ;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000045 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binlog events;
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000038 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.20, Binlog ver: 4 |
| binlog.000038 | 125 | Previous_gtids |         1 |         156 |                                   |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)

ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log
mysql> show binlog events in 'binlog.000045';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000045 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.20, Binlog ver: 4 |
| binlog.000045 | 125 | Previous_gtids |         1 |         156 |                                   |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)


## 使用 mysqlbinlog 命令
mysqlbinlog '文件名'
mysqlbinlog '文件名' > test.sql

## 使用binLog恢复数据
# 指定时间恢复
mysqlbinlog --start-datetime='2020-12-01 00:00:00' --stop-datetime='2020-12-02 00:00:00'  binlog.000045 | mysql -uroot -p123456
# 指定位置恢复
mysqlbinlog --start-pos=100 --stop-pos=120  binlog.000045 | mysql -uroot -p123456

## 删除binlog
purge binary log to 'binlog.000001'
purge binary log before '2020-12-01 00:00:00'
reset master;

# 附录

1: 事件类型

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 */ 
};

2: 标识

LOG_EVENT_BINLOG_IN_USE_F = 0x1 (New in 5.0.3) ：用于指示二进制日志文件是否已正确关闭。它是在事件写入日志文件时设置的。稍后关闭日志文件时，该标志将被清除。该标志仅对 FORMAT_DESCRIPTION_EVENT 有意义。
LOG_EVENT_THREAD_SPECIFIC_F = 0x4 (New in 4.1.0)：仅由 mysqlbinlog 使用（根本没有复制代码使用），以便能够正确处理临时表。
LOG_EVENT_SUPPRESS_USE_F = 0x8 (New in 4.1.7)：禁止在要记录的实际语句之前生成 USE 语句。
LOG_EVENT_UPDATE_TABLE_MAP_VERSION_F = 0x10 (New in 5.1.4)：将事件写入日志后，导致二进制日志内部的表映射版本增加。

明日再见～

# 最后

希望与你一起遇见更好的自己～，一起交流，一起进步

MySQL