MySQL的binlog的三种格式

lijunyi2022-07-19mysqlmysql

binlog 的格式

binlog 有三种格式:

  • Statement(Statement-Based Replication,SBR):每一条会修改数据的 SQL 都会记录在 binlog 中。
  • Row(Row-Based Replication,RBR):不记录 SQL 语句上下文信息,仅保存哪条记录被修改。
  • Mixed(Mixed-Based Replication,MBR):Statement 和 Row 的混合体。

Statement

Statement 模式只记录执行的 SQL,不需要记录每一行数据的变化,因此极大的减少了 binlog 的日志量,避免了大量的 IO 操作,提升了系统的性能。

但是,正是由于 Statement 模式只记录 SQL,而如果一些 SQL 中包含了函数,那么可能会出现执行结果不一致的情况。比如说 uuid() 函数,每次执行的时候都会生成一个随机字符串,在 master 中记录了 uuid,当同步到 slave 之后,再次执行,就获取到另外一个结果了。

🚨🚨所以使用 Statement 格式会出现一些数据一致性问题。!!!

Row

从 MySQL5.1.5 版本开始,binlog 引入了 Row 格式,Row 格式不记录 SQL 语句上下文相关信息,仅仅只需要记录某一条记录被修改成什么样子了。

Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节,这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

不过 Row 格式也有一个很大的问题,那就是日志量太大了,特别是批量 update、整表 delete、alter 表等操作,由于要记录每一行数据的变化,此时会产生大量的日志,大量的日志也会带来 IO 性能问题。

Mixed

从 MySQL5.1.8 版开始,MySQL 又推出了 Mixed 格式,这种格式实际上就是 Statement 与 Row 的结合。

在 Mixed 模式下,系统会自动判断该用 Statement 还是 Row:一般的语句修改使用 Statement 格式保存 binlog;对于一些 Statement 无法准确完成主从复制的操作,则采用 Row 格式保存 binlog。

Mixed 模式中,MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式,也就是在 Statement 和 Row 之间选择一种。

修改 binlog_format

一、查看当前数据库 binlog_format 设置:

mysql> SHOW VARIABLES LIKE 'binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.08 sec)

二、设置binlog_format

mysql> set binlog_format='MIXED';
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW VARIABLES LIKE 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.05 sec)

全局设置

上面的修改只正对当前数据库有效,设置全局可以按下面来操作

1、命令操作

set global binlog_format='MIXED';

2、修改配置文件

修改 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件,在配置文件中,添加 binlog_format 选项

# 这个参数表示启用 binlog 功能,并指定 binlog 的存储目录
log-bin=javaboy_logbin

#设置 binlog_format 格式
binlog_format=MIXED

# 设置一个 binlog 文件的最大字节
# 设置最大 100MB
max_binlog_size=104857600
Last Updated 6/14/2024, 3:05:31 AM