MySQL：聊聊Binlog
原创
前言
MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。
其中，比较重要的还要属二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。
那 binlog 到底是用来干嘛的？
可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠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 日志来还原数据，确保不会丢失关键的更新。
主从复制： 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 mysqlBinlog日志格式
STATEMENT模式（SBR）:
优点：
- 不需要记录每一条SQL语句和每一行的数据变化，减少了binlog日志量，有助于提高性能。
缺点：
- 某些情况可能导致主从之间的数据不一致，例如在SQL语句中使用
sleep()或last_insert_id()等操作。
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.sqldocker cp mysql:/20231114.sql ./Binlog写入机制
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日志。
优点
- 数据恢复： Binlog 日志为数据库提供了可靠的数据恢复机制，有效应对数据丢失或数据库崩溃等紧急情况。
- 主从复制： 通过 Binlog 日志，实现了 MySQL 数据库的主从复制，提升了数据库的可用性和读取性能，为分布式系统提供了强大的支持。
- 数据审计： Binlog 日志记录了数据库的所有变更操作，可用于进行数据审计，追踪和验证数据库中的操作历史。
缺点
- 占用存储： Binlog 日志文件在大量数据操作的情况下占据一定硬盘空间，特别是在大规模的插入、更新和删除操作中，会对存储造成一定的压力。
- 性能影响： 记录 Binlog 会对 MySQL 的性能产生一定影响，尤其在高并发的环境下，可能引起性能瓶颈。
使用注意事项
- 定期清理 Binlog 文件，确保不会占用过多的硬盘空间，维护系统的正常运行。
- 在使用 Binlog 进行数据库恢复时，按照顺序进行恢复，确保不跳过任何关键的日志文件，以维持数据的一致性。
- 对于 Binlog 格式的选择，应根据实际需求权衡。二进制格式紧凑高效，适用于高性能场景；文本格式可读性强，便于人类阅读，但在速度上相对较慢。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。