MySQL之binlog归档日志

海是鹏印出的影

已于 2024-04-23 10:10:59 修改

阅读量1.1k

点赞数 19

分类专栏： MySQL 文章标签：数据库

于 2024-04-21 14:07:39 首次发布

本文链接：https://blog.csdn.net/qq_36211262/article/details/138032662

版权

binlog（二进制归档日志）

binlog 二进制日志记录保存所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。启动 binlog 记录功能，会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处大于对服务器的影响。

查看binlog相关参数

在这里插入图片描述
上图显示了MySQL服务器中与二进制日志（binary log，简称binlog）相关的一些系统变量的状态。这些系统变量用于控制和配置MySQL的binlog行为。

log_bin：

表示是否开启了binlog。值为OFF意味着二进制日志功能当前是关闭的。这意味着MySQL实例不会记录任何二进制日志，这将影响复制和数据恢复功能。
log_bin_basename：

这个变量的值通常表示二进制日志文件的路径前缀。由于log_bin是OFF，这个路径可能没有被设置或者不被使用。
log_bin_index：

这个变量表示所有二进制日志文件的索引文件名。这个索引文件包含了所有的binlog文件名。这里也因为二进制日志没有开启，所以这个值可能没有被设置。
log_bin_trust_function_creators：

这个设置通常用于控制是否限制存储函数（如触发器、存储过程等）在没有明确声明二进制日志属性的情况下创建。OFF表示这种创建是受限的，这是一种安全措施。
log_bin_use_v1_row_events：

这个设置决定是否使用版本1的行事件格式记录binlog。OFF表示使用的是更新版本的行事件格式。
sql_log_bin：

这个变量控制当前会话是否记录二进制日志。值为ON意味着当前会话对于写操作会记录二进制日志，即使全局的log_bin设置为OFF。这个设置可以用来控制特定会话是否需要记录binlog，独立于全局设置。

综合来看，即便全局的binlog被关闭，当前的会话仍然可以通过sql_log_bin变量单独控制其binlog的记录行为。如果你需要使用MySQL复制或者需要binlog来支持数据恢复和审计等功能，你可能需要将log_bin设置为ON。

启用 binlog 功能

在 MySQL 5.7 版本中，binlog 默认是关闭的，8.0 版本默认是启用的。上图中 log_bin 的值是OFF代表关闭状态，启用 binlog 功能，需要修改配置文件 my.ini（windows）或 my.cnf（linux），然后重启数据库。

在配置文件中的【mysqld】部分，增加如下配置：

# 是 binlog 的名字前缀 可以在前面添加指定路径比如：D:\mysqlServer\mysql-5.7.27\Data\mysql-bin；不添加默认在Data文件夹下
log-bin=mysql-binlog
# Server Id 是数据库服务器id 这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=1
# 其他配置
binlog_format = row # 指定binlog的记录格式为行格式
expire_logs_days = 15 # 设置binlog文件的过期天数，默认15天。超过这个时间的binlog文件会被自动删除
max_binlog_size = 200M # 限制每个binlog文件的最大大小，默认为1GB。此设置避免单个文件过大，便于管理和传输

重启数据库后，我们再去看data数据目录会多出两个文件，第一个就是binlog日志文件，第二个是binlog文件的索引文件
在这里插入图片描述
当然也可以执行命令查看有多少binlog文件

show binary logs;

此命令会列出所有的binlog文件以及索引文件。索引文件中包含了所有binlog文件的列表，使得MySQL可以追踪和管理这些日志文件。

binlog 的日志格式

STATEMENT（基于SQL语句的复制）

描述正确。记录执行的SQL语句本身，对于大多数操作，这种模式下的日志量是最小的，因为它不记录每一行数据的变更，而是记录SQL操作。然而，对于使用了非确定性函数（如UUID()、NOW()）的SQL语句，这可能导致主从数据不一致的问题。

ROW（基于行的复制）

描述正确。记录对数据行的具体修改，而不是记录执行的SQL语句。这种方式下，即使是非确定性函数也不会引起主从不一致，因为复制的是数据变化的结果，而非执行的SQL语句。缺点是可能会产生大量的日志数据，尤其是当对多行数据进行操作时。

MIXED（混合模式复制）

描述正确。MySQL会智能地在STATEMENT和ROW模式之间切换，以尽可能地优化复制的效率和准确性。在大多数情况下使用STATEMENT模式来减少日志数据量，但在可能导致数据不一致的操作中使用ROW模式。MIXED模式在很多情况下提供了一个平衡的选择。

补充

对于选择哪种binlog格式，需要根据实际应用的需求和数据库操作的特点进行选择。ROW模式虽然在某些情况下会产生更多的日志数据，但它提供了最高的数据一致性保障，特别是在进行跨数据库分布式事务处理时。
MIXED模式通常是一个折中的选择，对于大多数应用来说，它提供了一个既能确保数据一致性又不会产生过多日志数据的解决方案。

binlog写入磁盘机制

sync_binlog 参数

为0时：

描述准确。当sync_binlog设置为0时，事务的binlog信息首先被写入操作系统的page cache，并不直接写入磁盘。这种方式依赖于操作系统的IO策略来决定何时将缓存中的数据刷新到磁盘，从而提高了写入性能，但在发生系统崩溃时存在数据丢失的风险。
为1时：

描述准确。设置sync_binlog为1意味着每个事务的binlog信息在提交时都会立即通过fsync操作被刷新到磁盘，确保了数据的持久性和一致性，但这可能对性能产生影响，特别是在高事务率的系统中。
设置为N (N>1)时：

描述准确。当sync_binlog设置为大于1的值时，MySQL将累积N个事务的binlog信息在内存中，然后一次性通过fsync操作写入磁盘。这是一种折中的策略，既考虑了数据的持久性也考虑了性能优化。但如果系统在fsync之前崩溃，最近的N个事务可能会丢失。

binlog日志文件的重新生成

服务器启动或重新启动：

MySQL服务器的启动或重启会导致binlog日志文件的重新生成。
执行FLUSH LOGS命令：

手动执行FLUSH LOGS命令会刷新日志文件，包括binlog日志文件，从而生成新的日志文件。
日志文件大小达到max_binlog_size：
当前的binlog文件大小达到max_binlog_size设置的值时，MySQL会关闭当前的binlog文件，并创建一个新的binlog文件继续记录后续的日志信息。

删除 binlog

RESET MASTER：删除当前的binlog文件

此命令用于删除所有的binlog文件，并重置binlog索引。这个命令确实会删除当前服务器上存在的所有binlog文件，并从头开始创建新的binlog文件（如mysql-bin.000001）。这个命令在测试环境中可能很有用，但在生产环境中使用时需要非常谨慎，因为它会丢失所有的binlog日志。
PURGE BINARY LOGS TO ‘mysql-bin.000006’：删除指定日志文件之前的所有日志文件

它会删除指定日志文件之前的所有日志文件，但保留指定的日志文件及之后的所有文件。这是一种常用的binlog管理操作，用于释放磁盘空间。需要注意的是࿰

最低0.47元/天解锁文章