MySQL二进制日志Binlog

枯河垂钓

已于 2025-03-30 18:37:20 修改

阅读量361

点赞数 7

分类专栏： # MySQL日志文件文章标签： mysql dba

于 2024-11-16 00:31:16 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_57024726/article/details/143809596

版权

MySQL日志文件专栏收录该内容

15 篇文章

订阅专栏

MySQL的Binlog主要记录的是修改数据库数据的操作，也就是DML（Data Manipulation Language）操作，包括INSERT、UPDATE、DELETE等语句，在8.0和更高版本中CREATE、ALTER、DROP等DDL（Data Definition Language）操作也会记录。

一、开启Binlog

vim /data/mysql/conf/my.cnf

查看binlog的路径

log-bin           = /data/mysql/binlog/mysql-bin

log-bin后面就是Binlog文件的路径以及前缀名字，mysql-bin就是binlog的前缀名字

查看是否开启Binlog

show variables like "%log_bin%"

如果log_bin的value值为ON则表示Binlog已经开启了

为了跟踪使用了哪些Binlog文件，MySQL还创建了Binlog索引文件（通常以.index为后缀），Binlog索引文件中配置的就是Binlog的文件名

[ccj@localhost binlog]$ cd /data/mysql/binlog
[ccj@localhost binlog]$ ls
mysql-bin.000022  mysql-bin.000023  mysql-bin.index
[ccj@localhost binlog]$ sudo cat mysql-bin.index 
/data/mysql/binlog/mysql-bin.000022
/data/mysql/binlog/mysql-bin.000023

二、如何关闭Binlog

修改my.cnf，注释掉log-bin,并添加disable_log_bin或者skip_log_bin

# log-bin           = /data/mysql/binlog/mysql-bin

disable_log_bin

三、关闭当前会话的 Binlog

set session sql_log_bin=0;

四、设置Binlog的大小

修改my.cnf，添加max_binlog_size（默认1G）

max_binlog_size=512M

查看Binlog大小

show global variables like "max_binlog_size";

五、Binlog的作用

复制、备份、迁移

六、Binlog的记录格式

Binlog有三种记录格式：STATEMENT、ROW 和 MIXED

1. STATEMENT 格式（基于语句的复制）

特点：记录执行的SQL语句，而不是数据的变化。从库会执行与主库相同的SQL语句。
优点：
- 日志量小，节省存储空间。
- 对于简单的、确定性的操作，效率较高。
缺点：
- 对于非确定性操作（如NOW()、UUID()等函数），可能导致主从数据不一致。
- 存储过程、触发器等复杂操作可能无法正确复制。
适用场景：适用于简单的、确定性的数据更改操作。

2. ROW 格式（基于行的复制）

特点：记录每一行数据的变化，包括旧值和新值。
优点：
- 数据一致性高，能够确保主从数据一致。
- 不依赖于SQL语句，避免了非确定性问题。
缺点：
- 日志量大，占用更多存储空间。
- 对于大量数据变更的操作，性能开销较大。
适用场景：适用于对数据一致性要求高的场景，如复杂的SQL操作。

3. MIXED 格式（混合模式）

特点：结合了STATEMENT和ROW两种模式，MySQL会根据SQL语句的特性自动选择记录方式。
优点：
- 兼顾了STATEMENT格式的空间效率和ROW格式的准确性。
- 适用于大多数场景，避免了单一格式的缺点。
缺点：
- 自动选择模式可能导致不可预期的行为，调试和排查问题时较为复杂。
适用场景：适用于需要平衡存储和数据一致性的场景。

总结

STATEMENT 格式适合简单的、确定性的操作，存储空间小，但可能引发主从不一致。
ROW 格式适合复杂的、对一致性要求高的操作，但存储开销大。
MIXED 格式是两者的折中方案，适用于大多数场景。

七、Binlog的日志格式修改

静态修改（通过配置文件）
- 编辑 MySQL 配置文件：
```
vim /data/mysql/conf/my.cnf
```
- 修改 binlog_format 的值：
```
binlog_format = ROW  # 也可以改为 STATEMENT 或 MIXED
```
- 重启 MySQL 服务使修改生效：
```
systemctl restart mysql
```
动态修改（通过 SQL 语句）
- 修改会话级 Binlog 格式（仅影响当前会话）：
```
SET SESSION binlog_format = 'ROW';  -- 或 STATEMENT 或 MIXED
```
- 修改全局 Binlog 格式（影响所有新连接）：
```
SET GLOBAL binlog_format = 'ROW';  -- 或 STATEMENT 或 MIXED
```