MySQL Binlog 的关键作用与优化策略

前言

MySQL 的日志系统包含了多种类型，其中最重要的包括错误日志、查询日志、慢查询日志、事务日志、二进制日志（binlog）、以及回滚日志（undo log）。在这些日志中，二进制日志（binlog）和事务日志（redo log）尤为关键。

binlog 是用来做什么的？

简而言之，binlog 在 MySQL 数据库中扮演着至关重要的角色，它是实现数据备份、主从复制、故障恢复等功能的基础。通过 binlog，MySQL 能够确保数据一致性和可靠性。

今天，我们将重点讨论 binlog（二进制日志）及其作用。

请在此添加图片描述

什么是 Binlog 日志

Binlog（Binary Log）是 MySQL 数据库生成的一种日志类型，用于记录所有对数据库的操作，主要包括 DML（数据操作语言）和 DDL（数据定义语言）操作。每次执行增、删、改等操作时，都会在 binlog 中留下记录。此外，对数据库结构的变更（如创建、修改表结构）也会被记录。

binlog 的重要性：

  1. 数据恢复：当数据库发生故障时，binlog 是恢复丢失数据的重要工具。它记录了每一条变更操作，确保能够在恢复时重新执行这些操作。
  2. 主从复制：在 MySQL 的主从架构中，binlog 用于同步主库的变更到从库，从而保证数据一致性。
  3. 审计功能：binlog 为数据库操作提供了详尽的审计日志，便于追溯和排查问题。

为什么需要 Binlog 日志

数据备份与恢复： Binlog 日志的关键作用之一是用于数据库的增量备份。每次对数据进行修改时，Binlog 记录了这次变更操作。在数据库受损或数据丢失的情况下，我们可以依靠这些 Binlog 日志来还原数据，确保不会丢失关键的更新。

主从复制： Binlog 日志在实现 MySQL 主从复制中扮演着关键角色。主数据库生成的 Binlog 日志会被同步到从数据库，从而使从数据库能够按照相同的顺序执行这些日志，保持与主数据库的数据一致性。这种复制机制不仅用于提高读取性能，还在分布式系统中发挥着重要作用。

审计： Binlog 日志提供了对数据库操作历史的详尽记录，可用于审计和排查问题。通过查看 Binlog，我们可以追溯到每一次对数据库的操作，了解何时发生了什么变更。这为问题排查和系统审计提供了有力的支持。

Binlog 日志因此成为数据库管理中不可或缺的工具，它保障了数据的完整性，支持数据库备份和恢复，同时提供了强大的审计功能。

Binlog 日志的实现原理

Binlog 日志的实现原理是 MySQL 数据库系统中的关键机制，确保了数据库操作的一致性和持久性。以下是Binlog 日志实现的主要步骤：

1. 数据操作的记录：

  • 当执行对数据库产生影响的 SQL 语句（例如增、删、改）时，这些操作会首先被记录到 Binlog 日志中。这确保了每次数据变更都被详细地记录下来。

2. 二阶段提交：

  • 为了保证 Binlog 日志与实际数据库操作的原子性，MySQL 使用了二阶段提交策略。首先，将数据操作记录到 Binlog 日志中，然后才执行实际的数据修改。这种方式防止了在数据写入日志前出现意外导致的数据不一致性。

3. 日志的读取与重放：

  • 当需要读取 Binlog 日志时，比如进行主从复制或者数据恢复，MySQL 会按照操作的顺序读取 Binlog 日志中的内容，并重放这些操作以还原数据的状态。这确保了数据库在恢复时能够准确地按照历史操作还原数据。

4. 输出格式：

  • Binlog 日志可以以不同的输出格式存在，包括纯文本格式和二进制格式。纯文本格式便于人类阅读，而二进制格式则更为紧凑和高效，适合用于主从复制等场景。

总体来说，Binlog 日志的实现原理基于将数据变更记录到日志中，使用二阶段提交确保操作的原子性，以及在需要时读取和重放日志以还原数据状态。这个机制对于数据库的数据备份、恢复和主从复制等场景起到了至关重要的作用。

Binlog 日志的使用示例

首先确保MySQL开启了Binlog日志功能

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 (0.00 sec)

字段解释

log_bin：

  • 含义： 表示是否启用二进制日志。
  • 值： OFF 表示未启用，ON 表示已启用。

log_bin_basename：

  • 含义： 指定二进制日志文件的基本名称。
  • 值： 通常是一个文件名，用于构造二进制日志文件的完整路径。

log_bin_index：

  • 含义： 指定二进制日志索引文件的名称。
  • 值： 通常是一个文件名，用于存储二进制日志文件的索引信息。

log_bin_trust_function_creators：

  • 含义： 表示是否信任具有非确定性函数创建者的二进制日志事务。
  • 值： OFF 表示不信任，ON 表示信任。

log_bin_use_v1_row_events：

  • 含义： 表示是否使用 v1 行事件格式。
  • 值： OFF 表示不使用 v1 格式，ON 表示使用。

sql_log_bin：

  • 含义： 表示是否记录 SQL 语句到二进制日志。
  • 值： OFF 表示不记录 SQL 语句，ON 表示记录。

在配置中，log_bin 是 OFF，这表示你的 MySQL 实例当前未启用二进制日志。如果你想启用二进制日志，你需要将 log_bin 设置为 ON，并提供相应的 log_bin_basename 和 log_bin_index。

开启Binlog

注意，注意，注意，这个路径是我的docker容器内挂载MySQL服务的路径！！！ 如果你不是docker部署，或者挂载路径不一致，请按照自己的修改，不要直接CV，然后发现不好用！！！

 vim /mydata/mysql/conf/my.cnf

添加

[mysqld]
log-bin=mysql-bin
server_id=1
# 配置定时清理
expire_logs_days = 5
# binlog每个日志文件大小
max_binlog_size = 200m
# binlog日志格式，MySQL默认采用的是STATEMENT，建议使用MIXED
binlog_format = MIXED

重启MySQL服务

docker restart mysql

Binlog日志格式

STATEMENT模式（SBR）:

优点：

  • 不需要记录每一条SQL语句和每一行的数据变化，减少了binlog日志量，有助于提高性能。

缺点：

  • 某些情况可能导致主从之间的数据不一致，例如在SQL语句中使用sleep()或6204970等操作。

ROW模式（RBR）:

优点：

  • 任何情况下都可以复制，并且不会受到存储过程、函数等调用或触发器无法正确复制的问题的影响。

缺点：

  • binlog日志文件可能会变得非常大。
  • 在主节点执行update语句时，所有的变化都会写入binlog中，相较于STATEMENT模式，可能导致更频繁的binlog并发写问题。

MIXED模式:

特点：

  • 是STATEMENT和ROW两种模式的混合使用。
  • 一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog。
  • MySQL会根据执行的SQL语句的特性自动选择日志保存方式。

这种混合模式充分利用了两者的优势，同时避免了各自的缺点。

确认Binlog开启

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.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日志文件列表

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       724 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

构建数据

CREATE TABLE `t` (
	`id` INT (11) NOT NULL,
	`a` INT (11) DEFAULT NULL,
	`t_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	KEY `a` (`a`),
	KEY `t_modified` (`t_modified`)
) ENGINE = INNODB;

insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

查看当前的Binlog日志文件列表

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       724 |
| mysql-bin.000002 |      2044 |
+------------------+-----------+
2 rows in set (0.00 sec)

注意：不能通过cat 直接打开binlog 文件，否则会出现乱码

cd /var/lib/mysql/mysql-bin
cat mysql-bin.000002

请在此添加图片描述

​

$ mysqlbinlog /var/lib/mysql/mysql-bin.000002
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'

原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令。

两个方法解决这个问题

在MySQL的配置**/my.cnf中将default-character-set=utf8** 修改为 **character-set-server = utf8**但是这需要重启MySQL服务，如果MySQL服务正在忙，那这样的代价会比较大。 用命令打开

mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000002

​

请在此添加图片描述

下载文件到本地

mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000002 > 20231114.sql

docker cp mysql:/20231114.sql ./

​

Binlog写入机制

MySQL 的二进制日志（binlog）不仅用于记录数据库的操作（例如增、删、改），它在事务处理、数据恢复、主从复制等方面都起着至关重要的作用。在事务执行过程中，binlog 的写入机制是保证数据一致性和持久性的重要环节。

下面详细解释了 binlog 的写入机制以及一些关键参数的作用：

1. 写入时机

当数据库执行修改数据的操作时（如 INSERT、UPDATE、DELETE 等），这些操作首先会被记录到 binlog 缓存（binlog cache） 中。这个缓存是每个线程独立的内存空间，用于暂存即将提交的事务日志。

  • 事务提交时：当事务被提交时，binlog 缓存的内容会被一次性写入到实际的 binlog 文件中。这个过程保证了事务的日志记录是原子性的，只有在事务提交后，数据变更才会真正写入 binlog 文件中，避免了在事务失败时产生不一致的日志。

2. Binlog Cache 大小控制

MySQL 提供了 binlog_cache_size 参数来控制每个线程的 binlog 缓存大小。这个缓存存储的是待写入的事务日志。

  • binlog_cache_size：如果这个缓存大小不够存储当前事务的所有日志内容，超出的部分会被暂存到磁盘的交换空间（Swap）。这样可以防止内存溢出，但会牺牲一些性能。

注意：如果 binlog_cache_size 设置得过小，可能会导致频繁的磁盘交换，影响性能；设置得过大，又可能浪费内存资源。

请在此添加图片描述

3. Binlog 文件刷盘流程

在 binlog 被写入时，MySQL 会先将日志数据写入内存中的 page cache，然后在适当的时机将其持久化到磁盘。这一过程有两个关键操作：

  • write 操作：MySQL 将日志内容写入内存中的缓存区域（page cache）。这一操作速度较快，因为是内存操作。
  • fsync 操作：将内存中的数据（page cache）刷到磁盘，这才是将数据持久化的关键步骤，保证数据在硬盘中不丢失。

具体的刷盘时机由 sync_binlog 参数来控制。

请在此添加图片描述

sync_binlog 参数的调整

sync_binlog 是 MySQL 用来控制 binlog 刷盘时机的重要参数。通过调整该参数，可以在 性能 和 数据安全性 之间找到平衡。

1. sync_binlog = 0

  • 含义：每次提交事务时，MySQL 只进行 write 操作，至于什么时候执行 fsync 操作则由操作系统自行判断。
  • 优点：这种配置能提高写入性能，因为每次事务提交时不强制进行磁盘刷盘。
  • 缺点：如果系统崩溃，最近的 binlog 数据可能会丢失。因为操作系统并不保证 write 操作后的数据一定会立刻刷到磁盘。

这种配置通常用于对性能要求更高、数据一致性要求相对宽松的环境，但不适用于要求严格一致性的场景。

2. sync_binlog = 1

  • 含义：每次事务提交时，MySQL 都会执行一次 fsync 操作，确保日志数据立即被刷到磁盘。
  • 优点：数据安全性得到极大保证，因为每个事务的日志都被强制写入磁盘，确保不会丢失。
  • 缺点：会增加磁盘 I/O 开销，因此性能上可能会有所下降。适用于对数据一致性和安全性有极高要求的场景。

例如，在金融、支付等领域，数据一致性是至关重要的，因此选择 sync_binlog = 1 配置。

3. sync_binlog = N（N > 1）

  • 含义：每次提交事务时，MySQL 进行 write 操作，并在累积了 N 次事务后再执行一次 fsync 操作。
  • 优点：在性能和数据安全性之间找到一个平衡点。适用于在 I/O 瓶颈比较明显的环境中，既要保证一定的数据安全性，又希望减少频繁的磁盘刷盘操作。
  • 缺点：如果机器突然宕机，可能会丢失最近 N 次事务的 binlog 日志。

这种配置适用于大部分需要平衡性能和数据安全性的中等负载应用。

请在此添加图片描述

IO 瓶颈优化

在高并发的系统中，频繁的 fsync 操作可能会导致磁盘 I/O 瓶颈。此时，可以通过将 sync_binlog 设置为较大的值来减轻 I/O 压力。这样，MySQL 会在提交多次事务后才执行一次 fsync，减少了磁盘刷盘的频率，从而提高性能。

但需要注意的是，如果机器突然宕机，设置较大值的 sync_binlog 会导致丢失最近 N 次事务的 binlog 日志。因此，在设置该参数时，需要根据实际的负载情况、系统的可靠性要求以及性能需求来做综合考虑。

Binlog 的优缺点

优点

  1. 数据恢复：在出现数据库故障或数据丢失时，binlog 提供了强有力的数据恢复机制。通过回放 binlog 中的操作，可以将数据库恢复到故障发生之前的状态。
  2. 主从复制：binlog 是 MySQL 主从复制的基础。主数据库上的 binlog 会被从数据库读取和执行，确保主从数据库的数据一致性。这是分布式系统和高可用架构的重要支撑。
  3. 数据审计：binlog 记录了所有数据修改操作，是数据库审计和追溯的重要工具。通过分析 binlog，可以追踪某个操作发生的具体时间和修改内容，帮助排查问题。

缺点

  1. 占用存储：binlog 文件会随着数据库操作的增多而不断增长，特别是在高并发的场景中，binlog 文件可能占用大量硬盘空间。为了防止磁盘空间被耗尽，需要定期清理旧的 binlog 文件。
  2. 性能影响：记录 binlog 会带来一定的性能开销，尤其是在高并发的数据库操作中，会对性能产生一定的影响。在极端高负载的环境下，binlog 写入可能成为性能瓶颈。

使用注意事项

  • 定期清理 Binlog 文件：可以通过 expire_logs_days 参数配置 MySQL 自动清理过期的 binlog 文件，避免占用过多的磁盘空间。
  • 按顺序恢复数据：在进行数据库恢复时，需要按 binlog 文件的顺序逐个应用，确保数据的一致性。如果跳过某个 binlog 文件，可能会导致数据丢失或不一致。
  • 选择合适的 Binlog 格式：STATEMENT 格式会记录 SQL 语句，适合大部分场景，但某些情况下可能导致主从数据不一致。ROW 格式会记录每一行的数据变更，适合复杂的数据库操作。MIXED 格式则结合了两者的优点，适合大多数情况。
posted @   张不惑  阅读(88)  评论(0)  编辑  收藏  举报  
相关博文：
· Spring Boot 与腾讯云 MySQL 监听 Binlog 数据变化，并使用 UI 展示页面效果
· 【Binlog实战】：基于Spring监听Binlog日志
· Mysql数据库之Binlog日志使用总结
· 深入理解MySQL Binlog：从原理到实践
· 图解MySQL【日志】——Binlog
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛