MySQL: Concept: Binary Log

1st September 2020 at 4:31pm
MySQL: Concept

MySQL 的 binary log 也称 binlog,包含了一切使 DB 数据变化的「事件」(或者可能变化的事件,比如没有删掉任意一行的 DELETE 操作)。Binlog 不会纪录 SELECT SHOW 这类不修改数据的语句。对这类语句的分析,使用 query log。

主要用途

  • 主从复制(replication):源服务器会将 binary log 发送至备机,以实现完全一致的数据同步
  • 数据恢复(recovery)

Formats

三种格式:

  • statement-based:纪录 SQL 语句
  • row-based:默认的方式,纪录行的变化
  • mixed:默认用 statement-based 方式,但出现特定情形时自动切换为 row-based

在主从同步时,有一些 SQL 语句是未确定性的(nondeterministic,比如包含 UUID() 函数),可能会导致主从间的不一致。MySQL 在遇到这种情况时会抛异常。用 row-based 可以避免这种问题。mixed 也可解决这种问题,会在未确定性出现的情况下使用 row-based。MariaDB 已将 mixed 作为其默认格式。但在 MySQL 运维实践层面,我不确定哪种是最好的。

CREATE TABLE ALTER DROP GRANT 等修改表结构或者 MySQL 内部表的命令,MySQL 会无视配置的 binlog 格式,都使用 statement-based。

客户端可以设置自己当前会话的 binlog 格式:

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

一些情况:

  • 如果要执行的 SQL 非常耗性能,但只改动很少的行,那使用 ROW 可以显著减少备机的性能消耗
  • 如果要执行非常多条 SQL,但只改动很少部分的数据,使用 ROW 比较合适
  • 如果执行的 SQL 很少,但会改动大量的行(比如一个匹配大量行的 WHERE 从句),那使用 STATEMENT 合适

References