面试官：聊聊MySQL的Binlog

MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。

其中，比较重要的还要属二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。

那 binlog 到底是用来干嘛的？

可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

今天就来聊聊 binlog（归档日志）。

---

Binlog（Binary Log）日志是MySQL Server层生成的一种记录，包含了数据库执行的所有操作，无论是SQL语句的执行还是数据库数据的变更。这个重要的日志类型记录了数据库实例的所有DML（数据操作语言）和DDL（数据定义语言）操作。

Binlog对于MySQL数据库系统至关重要。每当执行增、删、改操作（即DML操作）时，这些操作都会被记录在Binlog日志文件中。同时，对数据库结构进行更改的DDL操作也会在Binlog中留下记录。

Binlog的重要性不仅在于它存储了所有对数据库的更改，还在于当数据库发生故障时，它有助于数据的恢复。通过阅读Binlog日志中的数据变更内容，我们能够重新执行这些操作，从而实现对数据的恢复。

因此，Binlog不仅是数据库更改的追踪器，同时也是在意外情况下确保数据一致性和完整性的关键工具。有了Binlog，数据库管理员可以追溯数据库历史的变更并在需要时进行恢复。

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

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

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

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

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

1. 数据操作的记录：

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

2. 二阶段提交：

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

3. 日志的读取与重放：

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

4. 输出格式：

• 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()或18680等操作。

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 ./

​

MySQL 的二进制日志（binlog）在事务执行过程中起着关键作用。

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

写入时机

• 在事务执行期间，日志首先被写入到每个线程的 binlog cache 中。
• 在事务提交时，binlog cache 的内容会一次性写入到 binlog 文件中。

Binlog Cache 大小控制

• binlog_cache_size 参数决定了每个线程的 binlog cache 大小。
• 当 cache 大小不足以存储当前事务的所有信息时，额外的数据会暂存到磁盘（Swap）。

Binlog 文件刷盘流程

write 操作

• 将日志写入到文件系统的 page cache 中，存储在内存中，提供较高的写入速度。

fsync 操作

• 将数据持久化到磁盘，确保数据的安全性。
• 由 sync\_binlog 参数控制刷盘的时机。

sync_binlog参数的调整

sync_binlog = 0

• 每次提交事务都只进行 write 操作，系统自行判断何时执行 fsync。
• 性能提升，但在机器宕机时可能丢失最近的 binlog。

sync_binlog = 1

• 每次提交事务都执行 fsync，确保数据安全。
• 性能开销较大，适用于对数据一致性要求极高的场景。

sync_binlog = N`（N > 1）

• 每次提交事务都进行 write 操作，累积 N 个事务后再执行一次 fsync。
• 在性能和数据安全性之间寻找平衡，适用于 IO 瓶颈较为明显的情况。

通过调整这些参数，可以根据实际需求优化性能或确保数据的安全性。

在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。

同样的，如果机器宕机，会丢失最近N个事务的binlog日志。

1. 数据恢复： Binlog 日志为数据库提供了可靠的数据恢复机制，有效应对数据丢失或数据库崩溃等紧急情况。
2. 主从复制： 通过 Binlog 日志，实现了 MySQL 数据库的主从复制，提升了数据库的可用性和读取性能，为分布式系统提供了强大的支持。
3. 数据审计： Binlog 日志记录了数据库的所有变更操作，可用于进行数据审计，追踪和验证数据库中的操作历史。

1. 占用存储： Binlog 日志文件在大量数据操作的情况下占据一定硬盘空间，特别是在大规模的插入、更新和删除操作中，会对存储造成一定的压力。
2. 性能影响： 记录 Binlog 会对 MySQL 的性能产生一定影响，尤其在高并发的环境下，可能引起性能瓶颈。

1. 定期清理 Binlog 文件，确保不会占用过多的硬盘空间，维护系统的正常运行。
2. 在使用 Binlog 进行数据库恢复时，按照顺序进行恢复，确保不跳过任何关键的日志文件，以维持数据的一致性。
3. 对于 Binlog 格式的选择，应根据实际需求权衡。二进制格式紧凑高效，适用于高性能场景；文本格式可读性强，便于人类阅读，但在速度上相对较慢。