二进制日志 binlog本文已参与「新人创作礼」活动，一起开启掘金创作之路。二进制日志 binlog 二进制日志 bi

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

二进制日志 binlog

二进制日志 binlog 记录了数据库中的写入性操作（增删改）, 操作语句以事件的形式进行保存, 描述数据的更改.

文件内容包括了执行的 sql 语句（增删改）所影响的记录的全部字段的信息, 基于 binlog 日志可以做到类似于 Oracle 的闪回功能, 其实都是依赖于 binlog 中的日志记录.

MySQL 中的 binlog 日志包括两类文件 :

二进制索引文件, 后缀名为 .index
日志文件, 后缀名为 .00000* , 记录数据库所有的增删改.

主要使用场景 :

主从复制, Master 会将其产生的 binlog 发送给 slave, slave 读取这个 binlog 的内容, 并在 slave 端执行相同的操作 ( 重做 ), 从而实现主从同步.
数据恢复, binlog 包含了所有的写入性操作, 因此可以用来恢复数据. 建议 binlog 单独保存到一个磁盘上, 这样即使磁盘损坏以后也能进行数据恢复.

binlog 和 redo log 比较

binlog 和 redo log 都可以恢复数据, 但是两者有本质的不同 :

作用不同 : redo log 是保证事务的持久性的, 是事务层面的, binlog 作为还原的功能, 是数据库层面的.
内容不同：redo log 是物理日志, 是数据页面的修改之后的物理记录, binlog 是逻辑日志, 可以简单认为记录的就是 sql 语句.
两者日志产生的时间, 可以释放的时间, 在可释放的情况下清理机制, 都是完全不同的.
恢复数据的效率, 基于物理日志的 redo log 恢复数据的效率要高于语句逻辑日志的 binlog.
关于事务提交时, redo log 和 binlog 的写入顺序, 为了保证主从复制时候的主从一致（当然也包括使用 binlog 进行基于时间点还原的情况）, 是要严格一致的,
MySQL 通过两阶段提交完成事务的一致性, 也即 redo log 和 binlog 的一致性的,
理论上是先写 redo log, 再写 binlog, 两个日志都提交成功（刷入磁盘）, 事务才算真正的完成.

binlog 如何产生

binlog 的产生

事务提交的时候, MySQL 按照事务提交的顺序, 将事务中的 sql 语句按照一定的格式记录到 binlog 中. 在记录了 binlog 后, MySQL 会告诉存储引擎, 可以提交事务了.

这里与 redo log 很明显的差异就是 : redo log 并不一定是在事务提交的时候刷新到磁盘, redo log 是在事务开始之后就开始逐步写入磁盘. 因此对于事务的提交, 即便是较大的事务, 提交都是很快的, 但是在开启了 binlog 的情况下, 对于较大事务的提交, 可能会变得比较慢一些. 这是因为 binlog 是在事务提交的时候一次性写入的造成的.

生成新编号的 binlog 文件的操作

手动刷新, 执行 flush logs 命令, 会开始产生一个新编号的 binlog 文件.
每次重启 MySQL 服务时, 都会生成一个新编号的 binlog 文件. ( 每当 MySQL 服务重启时, 会自动执行 flash logs 命令 )
mysqldump 备份数据时加 -F 选项也会产生一个新编号的 binlog 文件.

binlog 什么时候释放

binlog 的默认保持时间由参数 expire_logs_days 配置, 对于非活动的日志文件, 在生成时间超过 expire_logs_days 天数之后, 会被自动删除.

binlog 的配置

MySQL 默认是没有开启 binlog 的, 需要手动在 my.ini 中进行配置.

# bin-log
server_id=1		#必须指定 server_id, 否则无法启动 mysql 服务
log_bin = mysql-bin	#该参数表示开启bin-log,同时指定了 bin-log 文件的前缀
binlog_format = ROW	#bin-log文件的格式
expire_logs_days = 30	#保存日期
max_binlog_size=200m	# 每个bin log文件大小
binlog_cache_size=10m	#binlog缓存大小
max_binlog_cache_size=20m	#最大binlog缓存大小

binlog 的操作

基本操作

查看 binlog 是否开启：show variables like 'log_bin'

查看 binlog 文件的列表以及大小：show binary logs

查看指定 binlog 文件的事件：show BINLOG EVENTS in 'mysql-bin.000003'

Log_name : 此条 log 存在哪个文件中.
Pos : log 在 bin-log 中的开始位置.
Event_type : log 的类型信息.
Server_id : 表示 log 是那个服务器产生, server_id 可以在 my.ini 配置.
End_log_pos : log 在 bin-log 中的结束位置.
Info : log 的一些备注信息, 可以直观的看出进行了什么操作.

从指定起始位置查询, 从 pos 点 609 开始查询：show BINLOG EVENTS in 'mysql-bin.000003' from 609

mysqlbinlog

查看 binlog 文件内容, 需要使用 mysqlbinlog 工具.

解析指定 bin-log 文件, 并输出到指定文件.

# --base64-output 可以控制输出语句输出 base64 编码的 bin-log 语句;
# decode-rows 把基于行的事件解码成 SQL 语句.
mysqlbinlog --no-defaults --base64-output=decode-rows -v e:\mysql-bin.000003 > e:000003.txt

解析指定时间段的 binlog 文件, 并输出到指定文件.

mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-datetime="2020-07-15 9:10:00" --stop-datetime="2020-07-15 9:15:00" e:\mysql-bin.000003 > e:000003-2.txt

解析指定位置范围的 binlog 文件, 并输出到指定文件.

mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-position="293" --stop-position="705" e:\mysql-bin.000003 > e:\000003-3.txt

一个 mysql-bin.000003 文件解析后的结果如下 :

实例-binlog 恢复数据

开发时, 不小心删掉了一张表中的所有数据. 数据库有开启 binlog, 所以可以恢复.

误操作后, 应该立刻记录下误操作的时间点, 方便恢复.

先查看 binlog 是否开启：show variables like 'log_bin';
找到最新的 binlog 文件：show binary logs;
查看指定 binlog 文件的事件：show BINLOG EVENTS in 'mysql-bin.000003';
可能一个 binlog 中的事件特别多, 所以可以跳着查看：show BINLOG EVENTS in 'mysql-bin.000003' from 609;
直到找到误操作的起始位置和结束位置.
然后解析这两个位置之间的 binlog, 解析出的是完整的数据：

mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-position="293" --stop-position="705" e:\mysql-bin.000003 > e:\000003-3.txt

看一下是不是这个时间点的这个操作.
如果是的话, 将解析出的数据, 手工恢复或者使用 binlog2sql 工具进行恢复. （mysqlbinlog命令只能解析binlog, 不能反向恢复数据）
恢复成功.