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 mysql
Binlog日志格式
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.sql
docker 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 格式的选择,应根据实际需求权衡。二进制格式紧凑高效,适用于高性能场景;文本格式可读性强,便于人类阅读,但在速度上相对较慢。
- 点赞
- 收藏
- 关注作者
评论(0)