MySQL Binlog详解：提升数据库可靠性的核心技术

文章目录

- 1. 引言
- - 1.1 什么是MySQL Bin Log？
  - 1.2 Bin Log的作用和应用场景
- 2. Bin Log的基本概念
- - 2.1 Bin Log的工作原理
  - 2.2 Bin Log的三种格式
- 3. 配置与管理Bin Log
- - 3.1 启用Bin Log
  - 3.2 配置Bin Log参数
  - 3.3 管理Bin Log文件
  - 3.4 查看Bin Log内容
  - 3.5 使用mysqlbinlog工具
  - 3.6 解析与重放Bin Log
- 4. Bin Log在复制中的应用
- - 4.1 主从复制原理
  - 4.2 配置主从复制
  - 4.3 监控与管理复制
  - 4.4 增量备份
  - 4.5 基于Bin Log的恢复
- 5. 写在最后

1. 引言

1.1 什么是MySQL Bin Log？

MySQL Bin Log（Binary Log，二进制日志）是MySQL数据库的一种日志文件，用于记录对数据库执行的所有修改DML操作（例如INSERT、UPDATE、DELETE等），但不包括SELECT等DQL只读操作。Bin Log是MySQL实现复制、恢复和审计的重要工具。详情见：MySQL 中的 DDL、DML、DQL 和 DCL

1.2 Bin Log的作用和应用场景

Bin Log的主要作用包括：主从复制、数据恢复、数据备份、数据订阅

应用场景	概念	案例
主从复制	主库将binlog中的更新操作记录发送到从库，从库读取binlog并执行SQL语句	电子商务平台：主数据库记录订单操作到binlog，从数据库同步订单数据以保持所有节点一致性。
数据恢复	使用binlog记录来逆向执行SQL语句恢复数据	金融系统：管理员通过解析binlog恢复意外删除的交易记录，确保数据完整性和避免财务损失。
数据备份	binlog用于增量备份，节省备份时间和空间成本	社交媒体平台：每天定时备份binlog文件，用于快速恢复到最新状态而不必全量备份整个数据库。
数据订阅	使用binlog实时监控数据库更新操作	零售公司实时数据分析：通过解析binlog，捕获销售记录插入操作并实时传递到数据分析平台进行销售趋势分析。

通过这些具体的案例，可以更清晰地看到MySQL binlog在实际应用中的重要性和多样性。合理使用binlog功能，可以极大地提高系统的可靠性、恢复能力和业务响应速度。

2. Bin Log的基本概念

2.1 Bin Log的工作原理

当MySQL服务器启用Bin Log功能后，所有对数据库的修改操作都会以事件的形式记录到Bin Log文件中。这些事件按照执行顺序存储，形成一个连续的操作日志序列。在需要恢复或复制数据时，可以通过重放这些事件来重现数据库的状态。

2.2 Bin Log的三种格式

MySQL Bin Log有三种记录格式：
以下是将MySQL Bin Log的三种记录格式整理成表格形式：

记录格式	描述	优点	缺点
Statement-Based Logging (SBL)	记录执行的SQL语句本身	日志量小，适合简单的SQL操作	某些情况下可能无法保证数据一致性，例如非确定性的函数（如NOW()）
Row-Based Logging (RBL)	记录每一行数据的具体变化	更精确，适合复杂的操作和复制	日志量大，磁盘和网络开销较大
Mixed Logging (ML)	根据具体情况在Statement和Row两种模式之间切换	兼顾两者的优点	实现和管理相对复杂

3. 配置与管理Bin Log

3.1 启用Bin Log

mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set, 1 warning (0.00 sec)

要启用Bin Log，需要在MySQL配置文件（通常是my.cnf或my.ini）中进行如下配置：

[mysqld]
log-bin=mysql-bin
server-id=1

修改完配置后，重启mysql。执行SHOW VARIABLES LIKE 'log_bin'; Value 值为 ON即可。

mysql> show variables like "%log_bin%";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+---------------------------------+--------------------------------------------------------------------------+
| Variable_name                   | Value                                                                    |
+---------------------------------+--------------------------------------------------------------------------+
| log_bin                         | ON                                                                       |
| log_bin_basename                | C:\Users\hiszm\MySQL5.7.26\data\binlog       |
| log_bin_index                   | C:\Users\hiszm\MySQL5.7.26\data\binlog.index |
| log_bin_trust_function_creators | OFF                                                                      |
| log_bin_use_v1_row_events       | OFF                                                                      |
| sql_log_bin                     | ON                                                                       |
+---------------------------------+--------------------------------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)

3.2 配置Bin Log参数

常见的Bin Log配置参数包括：

log_bin：启用Bin Log。
server_id：服务器唯一标识，用于复制。
binlog_format：设置Bin Log的格式（STATEMENT、ROW、MIXED）。
expire_logs_days：设置Bin Log文件的自动过期删除天数。
max_binlog_size：设置单个Bin Log文件的最大大小。

3.3 管理Bin Log文件

管理Bin Log文件的常用命令：

查看Bin Log文件列表：
```
SHOW BINARY LOGS;
```
查看Bin Log文件大小：
```
SHOW MASTER STATUS;
```

删除旧的Bin Log文件：

PURGE BINARY LOGS TO 'mysql-bin.000010';

或者：

PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

3.4 查看Bin Log内容


-- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB;  -- 使用InnoDB存储引擎

-- 插入数据，将日期设置为当前日期
insert into `simple_table` values(1, 1, '2024-07-07');
insert into `simple_table` values(2, 2, '2024-07-07');
insert into `simple_table` values(3, 3, '2024-07-07');
insert into `simple_table` values(4, 4, '2024-07-07');
insert into `simple_table` values(5, 5, '2024-07-07');

使用mysqlbinlog工具可以查看Bin Log的内容：

mysql> show binary logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2411 |
+---------------+-----------+
1 row in set (0.00 sec)

直接打开呢是乱码。

mysql> show binlog events in 'binlog.000001' from 0 limit 0,4\G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***

*************************** 1. row ***************************
   Log_name: binlog.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.26-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:
*************************** 3. row ***************************
   Log_name: binlog.000001
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: binlog.000001
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 765
       Info: use `d`; -- 创建一个名为 'simple_table' 的表
CREATE TABLE `simple_table` (
  `item_id` int(11) NOT NULL,  -- 项目编号
  `value` int(11) DEFAULT NULL,  -- 值
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 最后更新时间
  PRIMARY KEY (`item_id`),  -- 设置项目编号为主键
  KEY `value_index` (`value`),  -- 为值字段创建索引
  KEY `update_time_index` (`last_updated`)  -- 为最后更新时间字段创建索引
) ENGINE=InnoDB
4 rows in set (0.01 sec)

ERROR:
No query specified

3.5 使用mysqlbinlog工具

mysqlbinlog是一个命令行工具，用于解析Bin Log文件。常用选项包括：

--start-datetime：指定解析开始的时间。
--stop-datetime：指定解析结束的时间。
--start-position：指定解析开始的位置。
--stop-position：指定解析结束的位置。

例如，查看特定时间段的Bin Log：

mysqlbinlog --start-datetime="2024-07-01 00:00:00" --stop-datetime="2024-07-01 12:00:00" binlog.000001

3.6 解析与重放Bin Log

可以将Bin Log事件重放到MySQL服务器中，以实现数据恢复：

mysqlbin logbinlog.000001 | mysql -u root -p

4. Bin Log在复制中的应用

4.1 主从复制原理

MySQL主从复制的基本原理是主库记录Bin Log，从库读取并重放这些日志，从而实现数据同步。具体步骤如下：

主库执行写操作，并将这些操作记录到Bin Log。
从库连接到主库，读取Bin Log，并将其应用到自己的数据中。

4.2 配置主从复制

配置主库：
```
[mysqld]
log-bin=mysql-bin
server-id=1
```

配置从库：

[mysqld]
server-id=2
relay-log=relay-bin

在主库上创建复制用户：

CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

在从库上配置复制：

CHANGE MASTER TO
    MASTER_HOST='主库IP',
    MASTER_USER='replica',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='binlog.000001',
    MASTER_LOG_POS=0;
START SLAVE;

检查复制状态：
```
SHOW SLAVE STATUS\G
```

4.3 监控与管理复制

可以通过以下命令监控复制状态：

SHOW SLAVE STATUS\G

常见状态字段解释：

Slave_IO_Running：IO线程状态。
Slave_SQL_Running：SQL线程状态。
Seconds_Behind_Master：从库落后主库的时间。

4.4 增量备份

增量备份是指备份自上次完全备份或上次增量备份以来的所有更改。使用Bin Log可以实现增量备份。

执行完全备份：

mysqldump --all-databases --master-data=2 > full_backup.sql

记录当前Bin Log位置：

在完全备份文件中查找如下行：

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=12345;

备份Bin Log：

mysqlbinlog --start-position=12345 binlog.000001 > incremental_backup.sql

4.5 基于Bin Log的恢复

恢复完全备份：
```
mysql < full_backup.sql
```
应用增量备份：
```
mysql < incremental_backup.sql
```

5. 写在最后

MySQL Bin Log是一个强大的工具，广泛应用于数据恢复、复制和审计等场景。通过正确配置和使用Bin Log，可以大大提高MySQL数据库的可靠性和可用性。在实际应用中，掌握Bin Log的使用技巧和优化方法，对于数据库管理员和开发者来说至关重要。