Binary Log 即二进制日志,简称 binlog。
二进制日志用于记录对数据进行更改或者可能引起数据更改的 SQL 语句,不会包含那些没有修改任何数据的语句,例如 SELECT、SHOW 等操作。

日志的启用

默认情况下,二进制日志是关闭的,开启之后会对数据库性能有轻微的影响,但可以实现 2 个非常重要的功能:

  • 复制
    通过发送二进制日志中的事件,在 2 个数据库实例之间实现数据同步。
  • 恢复
    某些数据的恢复操作(比如增量恢复)需要使用二进制日志。

生产环境中必须要启用二进制日志,最简单的做法就是在配置文件中申明:

1
2
[mysqld]
log_bin

很多时候,我们会将二进制日志独立出来:

1
2
[mysqld]
log_bin = /opt/mysql/logs/binary/bin

/opt/mysql/logs/binary/ 表示二进制日志的存放路径,默认在 datadir 下。
bin 是二进制日志的 base_name(见下一节内容),默认为服务器的主机名。

日志的组成

“二进制日志文件” 通常表示单个编号文件,“二进制日志” 则表示所有的二进制日志文件集和索引文件。
binlog-files

二进制日志文件

二进制日志文件的文件名形式为 base_name.extension,其中:

  • base_name
    可以通过 log_bin 参数进行指定。
  • extension
    是一串数字,每次创建新日志文件时,该数字都会增加,从而形成一系列有序的文件。

二进制索引文件

为了跟踪已使用的二进制日志文件,mysqld 还创建了一个二进制日志索引文件。这是一个文本文件,它记录了当前保留着的所有的二进制日志文件的名称。
默认情况下,二进制日志索引文件与二进制日志文件有着相同的 base_name,扩展名 .index,我们可以使用 log_bin_index 参数来更改其名称。
binlog-index

mysqld 运行时,我们不应手动编辑此文件,这样做会让 mysqld 感到困惑。

日志的查看

二进制文件不是传统的文本格式,无法直接查看,MySQL 提供了一个专用的解析工具:mysqlbinlog。
常用参数:

参数 作用
--base64-output 指定以何种形式显示编码过的二进制语句
--database 查看指定数据库相关的日志
--start-datetime / --stop-datetime 指定日志的起始/结束的时间(非精确)
--start-position / --stop-position 指定日志的起始/结束的位点(精确)
-v 是否显示否额外的信息

例子:

1
/usr/local/mysql/bin/mysqlbinlog -v --base64-output=decode-rows bin.000001

日志的格式

二进制日志文件中记录的事件格式由 binlog_format 决定,该参数有 3 个值:STATEMENTROWMIXED

STATEMENT(SBR)

语句格式的二进制日志文件中记录着产生数据更改的原始 SQL 语句:
sbr

优点:

  • 日志文件较小
  • 日志便于阅读、审计和故障修复

缺点:
存在安全隐患,对于一些系统函数不能复制或不能准确复制,导致主从不一致

ROW(RBR)

行格式的二进制日志文件中记录着行数据的更改情况:
rbr

优点:

  • 安全,系统函数能准确复制,能保证主从数据的一致性
  • 某些情况下复制更快(SQL 复杂,表有主键)
  • 更少的锁

缺点:

  • 日志文件较大
  • 无法直观地看到用户执行的 SQL 语句
  • 如果单事务更新的行数过多,会造成大量的日志记录

MIX(MBR)

日志默认使用 STATEMENT 格式进行记录,会根据需要使用 ROW 格式。

生产环境中,强烈建议使用RBR,以保证复制和恢复后的数据一致性。

日志的切换

发生以下 3 种情况时,会触发二进制日志的切割和轮转:

  • 自动切换
    当单个日志文件的大小达到了 max_binlog_size 定义的值时
  • 手动切换
    MySQL 命令行下,执行 FLUSH BINARY LOGS;
  • 实例重启

对于日志的自动切换,由于事务不能跨日志记录,即使当前日志的大小已经超过了设定的值,也必须等该事务执行完毕才能进行日志切换。

日志的删除

二进制日志文件不能使用操作系统的删除命令直接删除,需要通过以下方式:

  • 自动删除
    使用完的日志在达到 expire_logs_days 定义的时长后
  • 手动操作
    MySQL 命令行下执行 PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr };

在一个复制集群中,如果需要手动删除主库上的某个二进制日志,务必确认该日志已经完全应用到从库上!