MySQL之binlog日志

原文链接：https://zhuanlan.zhihu.com/p/697078870

binlog 是什么
binlog 配置和查看
binlog 的类型
binlog 如何恢复数据
binlog 是逻辑日志还是物理日志
binlog 作用

注意：以下所有的操作都在 MySQL 8.0 版本实现。

1、binlog 是什么

首先介绍一下，对于一个 SQL 语句，它常常被分为以下几种类型：

DDL：Data Definition Language，数据定义语言
1. 用来操作数据库、表、列等，比如 CREATE、ALTER、DROP
DML：Data Manipulation Language，数据操作语言
1. 用来操作表里的数据，比如 INSERT、UPDATE、DELETE
DCL：Data Control Language，数据控制语言
1. 用来操作访问权限等，比如 GRANT
DQL：Data Query Language，数据查询语言
1. 用来查询数据，比如 SELECT
TCL：Transaction Control Language，事务控制语言
1. 用于数据库的事务管理，比如 COMMIT，ROLLBACK

而 binlog，即 binary log，是 MySQL 的二进制日志文件，这个文件记录了我们所有的 DDL，DML，TCL 等操作，比如表的创建，数据的插入、更新和删除等。

比如我们下面执行的创建数据库、表，插入、更新数据，在 binlog 配置开启的情况下，都会被记录到 binlog 中：

CREATE DATABASE db_test DEFAULT CHARACTER set utf8;create table users (id int not null auto_increment primary key,name varchar(20) not null,email varchar(100) default ""
);insert into users (name, email) values("张三", "12345@qq.com"), ("李四", "345123@qq.com");update users set email = "4123@qq.com" where id=1;

2、binlog 配置和查看

1) binlog 查看配置

我们可以通过下面的命令查看 binlog 日志是否打开，以及存储的文件夹：

show variables like '%log_bin%';

显示的内容如下：

其主要字段含义分别如下：

log_bin 表示是否开启 binlog
log_bin_basename 表示的是 binlog 所在的文件夹
log_bin_index 表示的是 binlog 的索引文件，我们可以在其中找到所有的 binlog 日志
sql_log_bin 用于主从复制，这个参数关闭后，主库的改动不会记录到 binlog，不会复制到从库

log_bin_basename 指向的是 binlog 存储的文件夹，在后面我们查看 binlog 具体内容的时候，会需要进入到这个文件夹进行查看。

2) binlog 查看

我们可以通过下面的命令查看全部的 binlog 文件，在这里，一个 binlog 版本就是一个文件：

show binary logs;show master logs;

上面这两条命令都可以显示出 binlog 文件，内容显示如下：

3、binlog 的类型

binlog 记录的类型有三种，一种是 STATEMENT，一种是 ROW，一种是 MIXED。

在介绍其具体含义前，我们先介绍一下如何查看和修改 binlog 类型，以及查看 binlog 内容。

1. 查看 binlog 类型

我们可以通过下面的命令查看 binlog 保存的类型：

SHOW VARIABLES LIKE 'binlog_format';

输出内容如下：

表示 binlog 保存的类型是 ROW

2. 修改 binlog 类型

修改 binlog 类型我们到 binlog 文件中修改，这里我们修改 mysqld.cnf，修改 binlog_format 的值，没有这个配置项的话加一行，如下：

binlog_format = STATEMENT

保存之后重启 MySQL，再次查询就可以看到这个参数有变化了：

3. binlog 类型及具体含义

其含义和示例分别如下：

1) STATEMENT

STATEMENT 类型记录的是执行 SQL 语句的内容，比如 INSERT、UPDATE、DELETE 等语句本身。

比如：

insert into users (name, email) values("张三", "12345@qq.com"), ("李四", "345123@qq.com");
update users set email = "4123@qq.com" where id=1;

在 STATEMENT 类型下，上面这两句 SQL 在 binlog 中记录的就是这几句 SQL 本身。

通过查询 binlog 日志，比如我们下面执行的语句，可以看到输出的 info 那一列有我们刚刚执行的 insert 和 update 语句：

show binlog events in 'binlog.000017';

我们也可以使用 mysqlbinlog 命令在 shell 对这个日志文件进行查看：

mysqlbinlog binlog.000017

就可以看到输出的信息里会包含我们前面执行的两条命令

2) ROW

ROW 类型记录的是执行 SQL 语句前后的变更，会包含变更前后完整数据行的内容。

将 binlog_format 的配置改为 ROW 后，重新执行几条插入或者更新数据的命令，在 MySQL 客户端执行 show binlog events 命令，就可以看到日志记录里有对应的记录，但是这里输出的信息并不全，我们还是可以使用 mysqlbinlog 来查看。

mysqlbinlog binlog.000018

但是可以看到输出的内容里，数据的变化都被处理过：

BEGIN
/*!*/;
# at 323
#240415  1:03:05 server id 1  end_log_pos 386 CRC32 0x0e2e4dda  Table_map: `db_test`.`users` mapped to number 93
# has_generated_invisible_primary_key=0
# at 386
#240415  1:03:05 server id 1  end_log_pos 474 CRC32 0xcebf294e  Update_rows: table id 93 flags: STMT_END_FBINLOG '
SQwcZhMBAAAAPwAAAIIBAAAAAF0AAAAAAAEAB2RiX3Rlc3QABXVzZXJzAAMDDw8EPAAsAQQBAQAC
ASHaTS4O
SQwcZh8BAAAAWAAAANoBAAAAAF0AAAAAAAEAAgAD//8AAQAAAAblvKDkuIkLADQxMjNAcXEuY29t
AAEAAAAG5byg5LiJDQAxMTExMTNAcXEuY29tTim/zg==
'/*!*/;
# at 474
#240415  1:03:05 server id 1  end_log_pos 505 CRC32 0xd7ae1d48  Xid = 55
COMMIT/*!*/;

会看不到真实记录的信息，我们直接使用 cat 或者 vim 命令查看文件内容，可以从其中窥得一二，可以看到一些我们修改或者新增的字段内容。

或者我们还是可以使用 mysqlbin，但是加上 -vv 参数，我们可以看到原始执行的 SQL 语句，类似于下面的内容：

BINLOG '
2RAcZhMBAAAAPwAAANUCAAAAAF0AAAAAAAEAB2RiX3Rlc3QABXVzZXJzAAMDDw8EPAAsAQQBAQAC
ASGMO256
2RAcZh4BAAAAPwAAABQDAAAAAF0AAAAAAAEAAgAD/wADAAAABueOi+S6lA4AMTIxMjM0NUBxcS5j
b22PdURn
'/*!*/;
### INSERT INTO `db_test`.`users`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='王五' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3='1212345@qq.com' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

3) MIXED

MIXED 类型则是 STATEMENT 和 ROW 两种类型的综合，它会根据具体的情况来选择 STATEMENT 或者是 ROW 其中一种类型来进行存储。

比如说涉及函数或者一些比较复杂和无法还原的操作时，就会选择 ROW 格式来存储变更的数据行，如果是一些比较简单的插入、更新或者删除操作，那么则会选择 STATEMENT 格式来进行存储。

4. binlog 三种类型的优缺点

关于这三种 binlog 类型的优缺点总结如下：

1) STATEMENT

STATEMENT 记录的 SQL 语句，适用于简单 SQL 语句的场景，比如单条数据的插入、更新、删除等。

优点是 binlog 日志文件相对较小，而且可读性强，因为可以直接查看 SQL 语句了解操作

但同时存在的问题是某些复杂操作可能会出现不一致的情况。

2) ROW

ROW 类型存储的是数据行的变更，会包含变更前后的内容，适用于需要精确记录数据行变更的场景。

优点是可以精确记录数据行的变更情况，避免 STATEMENT 类型可能出现的不一致的问题。

缺点则是因为记录了完整的数据行内容，所以可能导致文件较大，而且可读性较差，比如我们前面使用 -vv 参数才能了解其执行的 SQL 语句。

3) MIXED

MIXED 则会根据具体情况自动选择 STATEMENT 或 ROW 类型来记录数据更改。

优点是兼顾了 STATEMENT 和 ROW 两种类型的有点，既可以节约空间，也可以精确记录数据变更。

缺点的话，因为是系统是自动选择类型来记录，所以可能存在选择不够理想的记录方式从而导致一些不一致或者是性能问题。

4、binlog 如何恢复数据

对于 MySQL 中的操作，我们有时候可能会误操作，导致会意外的更新或者删除一些数据、或者是删除表、删除库等，那么这些操作如何使用 binlog 来进行恢复呢，以下做一个简单的介绍。

在介绍如何恢复数据前，先介绍前面的 binlog 文件保存的机制。

对于 MySQL 而言，一个 binlog 文件内容为从某一刻开始保存的日志记录，当我们使用 sudo service mysql restart 重启操作时，系统会自动为我们新建一个 binlog 日志文件，重启之后的所有数据操作都会被放到新的 binlog 文件里。

除了 MySQL 的手动重启，我们还可以使用 flush logs 操作来手动新建一个 binlog 日志。

下面正式开始介绍如何使用 binlog 恢复数据。

1. 恢复数据的前提

恢复数据的前提就是我们已经打开了 binlog 日志记录配置，而且最好有一个备份库，这个备份库的作用是用于在误操作数据后可以快速从最近的时间点进行恢复。

备份库的内容则是与当前生产库，或者说是目标库落后一定时间间隔的数据，来源可以是我们每天或者更小时间粒度对目标数据库的定时保存。

2. 恢复数据的步骤

有了 binlog 日志和备份数据库的基础之后，比如我们误操作更新了大范围的数据，或者删除了某张表，下面便开始恢复数据的步骤。

1) 停止写入更新操作

如果可能的话，应该尽量先停止往 MySQL 对应表的写入操作，避免数据污染操作。

2) 执行 flush logs

执行 flush logs 操作，重新创建一个 binlog，将误操作确定在某个 binlog 中。

3) 导出 binlog 日志

确定备份库的时间点，然后找到包含失误操作的 binlog，导出从备份库的时间点开始一直到发生误操作的时间点前的 binlog。

将这部分 binlog 在备份库重新执行，这样，我们的备份库就可以恢复数据到发生误操作的前一个时间点。

如果我们没有第一步停止写入更新的操作，就可以跳过误操作的 SQL，将发生误操作后的 SQL 语句也导入到备份库，这样备份库就拥有了不发生误操作情况下的全部数据。

4) 替换

将备份库的数据替换到目标库，然后重新开启服务。

以上，就是使用 binlog 进行恢复数据的大致操作，具体图示见下图：

尽管恢复数据在某些情况下是可行的，但是我们仍然要注意尽量避免在生产中直接操作数据库，而应该尽量通过业务代码来实现对数据库的操作。

5、binlog 是逻辑日志还是物理日志

介绍一下 MySQL 中关于逻辑日志与物理日志的区别。

所谓逻辑日志指的是记录数据库操作的日志，比如 INSERT、UPDATE 等逻辑操作。

而物理日志则是指记录数据的各种存储细节，比如 MySQL 具体某个数据页的写入和修改数据等。

所以，binlog 则属于逻辑日志。

6、binlog 作用

binlog 作为 MySQL 日志的一种，有很多作用，比如前面介绍的数据备份与恢复，还可以用于数据同步，比如主从复制模式下将主库的更改操作操作就是通过 binlog 的方式来同步到从库的。

当然，通过 binlog 本身我们可以还原所有的数据操作，我们也可以针对这部分进行对应的数据统计和分析等。

7、开启binlog日志记录

修改MySQL配置文件，linux中配置文件为my.conf，window下为my.ini，下面以centos为例演示：

编辑配置文件

# 在centos中mysql的配置文件一般都在/etc/mysql目录下，如果不在可以通过 find / -name "my.cnf" 查找
vi /etc/mysql/my.cnf

添加配置（放到[mysqld]下面）

# 服务ID
server-id=1
# binlog 配置 只要配置了log_bin地址 就会开启
log_bin = /var/lib/mysql/mysql_bin
# 日志存储天数 默认0 永久保存
# 如果数据库会定期归档，建议设置一个存储时间不需要一直存储binlog日志，理论上只需要存储归档之后的日志
expire_logs_days = 30
# binlog最大值
max_binlog_size = 1024M
# 规定binlog的格式，binlog有三种格式statement、row、mixad，默认使用statement，建议使用row格式
binlog_format = ROW
# 在提交n次事务后，进行binlog的落盘，0为不进行强行的刷新操作，而是由文件系统控制刷新日志文件，如果是在线交易和账有关的数据建议设置成1，如果是其他数据可以保持为0即可
sync_binlog = 1