介绍 Binary Log

Binary Log 被称为二进制日志， Binary Log 主要记录数据库的更新事件，比如创建数据表、更新表中的数据等信息。Binary Log 是在 MySQL 的执行器层（server 层）实现的，与存储引擎无关，因此只要开启使用 Binary Log，执行器就会把对数据库的更新事件记录到 Binary Log。
Binary Log 的作用 / 功能：

  • 数据恢复：反向解析 binlog 文件，并执行反向 SQL，恢复数据
  • 数据复制：在存储高可用架构中，用 Binary Log 把数据从一个服务器复制到另一个服务器，完成服务器间的数据同步

    操作 Binary Log

    20 | 日志（下）：系统故障，如何恢复数据？
    操作 Binary Log，主要包括查看 Binary Log、刷新 Binary Log，用 Binary Log 恢复数据，以及删除 Binary Log。

    查看 Binary Log

    查看当前正在写入的 Binary Log：show master status;
    查看所有的 Binary Log：show binary logs;
    查看指定 Binary Log 文件中所有的数据更新事件：show binlog events in '文件名';
    image.png
    image.png
    image.png
    image.png

    刷新 Binary Log

    刷新 Binary Log：flush binary logs;
    刷新 Binary Log 的意思是，关闭服务器正在写入的 Binary Log 文件，重新打开一个新的 Binary Log 文件，文件名的后缀在现有的基础上 + 1。

    使用 mysqlbinlog 恢复数据

    1. mysqlbinlog –start-positon=xxx --stop-position=yyy '二进制文件名' | mysql -u 用户 -p
    这条命令的意思是：执行 Binary Log 文件中从位置 xxx 开始，到 yyy 截止的所有数据更新操作。
    这里的截止位置也可以不写，意思是从位置 xxx 开始，执行 Binary Log 文件中的所有数据更新操作。
    数据恢复：mysqlbinlog

    删除所有的 Binary Log 文件

    删除所有的 Binary Log 文件：reset master;
    image.png
    该命令的执行结果：所有 Binary Log 文件都被删除了，MySQL 从头准备了一个“.000001”为后缀的新的 Binary Log 文件。

我们也可以通过以下的命令，删除比指定 Binary Log 文件编号小的所有 Binary Log 文件：purge master logs to '文件名';

Binary Log 的参数配置

binlog_cache_size：每个线程的 binlog cache 的内存大小（默认值是 32 KB）
sync_binlog：Binary Log 的写入策略（默认值是 1）
binlog_format：Binary Log 的格式（默认值是 row）
binlog_expire_logs_seconds：Binary Log 的过期时间（默认值是 30 天）

binlog_expire_logs_seconds 设置之后不会立即清除过期的 Binary Log，清除的触发条件是：

  • binlog大小超过max_binlog_size
  • 手动执行 flush logs
  • 重新启动时（MySQL将会创建一个新的 Binary Log 文件）

Binary Log 的写入策略

23 | MySQL是怎么保证数据不丢的？
提高持久性：存储高可用架构 & Binary Log - 图6
Binary Log 的写入策略是：

  • 事务执行的过程中，执行器先把 Binary Log 写入内存（binlog cache）；
  • 事务提交的时候，执行器再把 binlog cache 里事务的完整 Binary Log 写入 Binary Log 文件中，并清空 binlog cache。

    一个事务的 Binary Log 是不能被拆开的，因此无论这个事务多大，都要确保一次性写入磁盘。 这就涉及到了 binlog cache 的保存问题。 系统给 binlog cache 分配了一片连续的内存，每个线程一个 binlog cache，参数 binlog_cache_size 用来控制一个线程的 binlog cache 所占内存的大小。 如果 binlog cache 所占内存的大小超过了这个参数规定的值， binlog cache 中的内容就要暂存到磁盘的临时文件。事务提交的时候，再从磁盘读取，把事务的完整 Binary Log 写到 Binary Log 文件。

从上图中可以看到，每个线程有自己 binlog cache，但是共用同一份 Binary Log 文件，binlog cache 里的数据持久化到磁盘需要经过 write 和 fsync 两个过程 。

  • write 是指：把 binlog cache 里的 Binary Log 写入文件系统的页缓存（Page Cache）。并没有把数据持久化到磁盘，所以速度比较快。
  • fsync 是指：将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

  1. sync_binlog=0：表示每次提交事务时，只是执行 write，而不执行 fsync；
  2. sync_binlog=1：表示每次提交事务时，执行 fsync 后才返回（参数的默认值）；
  3. sync_binlog=N（N>1）：表示每次提交事务时，执行 write，但累积 N 个事务后才执行 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。
在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将 sync_binlog 参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 Binary Log。
通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务提交前，需要等待两次刷盘，一次是 Redo Log 刷盘（prepare 阶段），一次是 Binary Log 刷盘。

Binary Log 的格式

24 | MySQL是怎么保证主备一致的？
Binary Log 有三种格式，一种是 statement，一种是 row。还有一种叫作 mixed，其实 mixed 就是前两种格式的混合。使用哪种格式由参数 binlog_format 控制，默认值是 row。

  • statement 格式的 Binary Log 记录的是：SQL 语句
  • row 格式的 Binary Log 记录的是：事务的执行所必要的信息，能够保证使用 Binary Log 恢复数据的时候，执行结果和原库相同。
    • insert 语句的 Binary Log 里会记录：所有的字段信息
    • update 语句的 Binary Log 里会记录：修改前整行的数据和修改后整行的数据
    • delete 语句的 Binary Log 里会记录：被删掉的行的整行信息

基于上面的信息，我们来讨论一个问题：为什么会有 mixed 这种 Binary Log 格式的存在场景？
推论过程是这样的：

  • 因为有些 statement 格式的 Binary Log 可能会导致主备不一致，所以要使用 row 格式。
  • 但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 Binary Log 中，占用几十个字节的空间。但如果用 row 格式的 Binary Log，就要把这 10 万条记录都写到 Binary Log 中。这样做，不仅会占用更大的空间，同时写 Binary Log 也要耗费 IO 资源，影响执行速度。
  • 所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 Binary Log 。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

也就是说，mixed 格式可以利用 statment 格式的优点（节省存储空间），同时又避免了数据不一致的风险（statment 格式的缺点）。

MySQL 的存储高可用架构

MySQL 的存储高可用架构

参考资料

20 | 日志（下）：系统故障，如何恢复数据？

23 | MySQL是怎么保证数据不丢的？
24 | MySQL是怎么保证主备一致的？