MySQL二进制日志详解 | binlog存储、解析、日志恢复

1 MySQL二进制日志概念及应用

MySQL的二进制日志binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的DDL和DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。

Binary log主要应用场景：

一是用于数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
二是用于数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

2 关于MySQL二进制日志的配置

查看记录二进制日志是否开启：在MySQL8中默认情况下，二进制文件是开启的。

mysql>  show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |  //开关
| log_bin_basename                | /data/logs/3306/binlog       | // 存放路径
| log_bin_index                   | /data/logs/3306/binlog.index |
| log_bin_trust_function_creators | ON                          |//  函数创建 
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |//变更sql记录下来
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

• log_bin:如果返回值为ON，则表示二进制日志功能已经开启；如果返回值为OFF，则需要手动配置并重新启动MySQL服务来开启该功能。
• log_bin_basename:是binlog日志的基本文件名，后面会追加标识来表示每一个文件
• log_bin_index:是binlog文件的素引文件，这个文件管理了所有的binlog文件的目录
• log_bin_trust_function_creators:限制存储过程，前面我们已经讲过了，这是因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，需要限制存储函数的创建、修改、调用
• log_bin_use_v1_row_events此只读系统变量已弃用。ON表示使用版本1二进制日志行，OFF表示使用版本2二进制日志行（MySQL5.6的默认值为2)。

过期时间
指定二进制日志过期天数（expire_logs_days）:该参数为全局动态调整参数，默认值为0，即关闭，取值范围0－99；

mysql> SHOW VARIABLES LIKE 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 3     |
+------------------+-------+
1 row in set (0.00 sec)

注意：在双机复制环境下，应确保过期天数不应小于从库追赶主库binlog日志的时间。
可以在从库执行show slave status\G命令查看主从延迟时间，存在大事务时延时会增加

触发过期删除的条件:

• 重启MYSQL；

• BINLOG文件大小达到参数max_binlog_size限制；max_binlog_size 定义了单个文件的大小限制，如果二进制日志写入的内容大小超出给定值，日志就会发生滚动（关闭当前文件，重新打开一个新的日志文件）。不能将该变量设置为大于 1GB 或小于 4096B（字节），其默认值是 1GB。

• 手动执行命令：重置binlog（reset master）

binlog缓存
参数binlog_cache_size控制单个线程内 binlog cache 所占内存的大小。
binlog cache记录着所有未提交的事务在运行期间产生的binlog数据，binlog cache 是在每个线程内空间独立的。如果启用了bin log日志，MySQL 会为每个客户端分配一个二进制日志缓存。如果经常使用大型事务，则可以增加此缓存大小以获得更好的性能，可通过 binlog_cache_size 配置其大小，默认 32768 bytes。

mysql> show variables like 'binlog_cache%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| binlog_cache_size | 2097152 |
+-------------------+---------+
1 row in set (0.00 sec)

如果binlog cache空间足够，在事务提交的时候，cache中的内容会被清空，同时这些数据会被写入到日志文件中。
因为bin log内容无论多大在事务提交时都需要一次性写入，所以当 bin log cache放不下的时候，就需要暂存到磁盘（生成一个binlog cache 临时文件，同时清空binlog cache，由参数max_binlog_cache_size控制该文件大小），然后提交被写入到日志文件中。
binlog临时文件会被存放到 tmpdir 的目录下，并以"ML"作为文件名开头。但该文件无法用ls命令看到（但可以使用 lsof|grep delete 来观察到这种文件），因为使用了LINUX创建临时API（mkstemp），以避免其他进程破坏文件内容。也就是说，这个文件是mysqld进程内部专用的。

mysql> show variables like '%max_binlog_cache_size%';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
1 row in set (0.00</