MySQL 二进制日志的使用

本文的操作环境是 macOS Mojave 10.14.1, mysql 5.7.19

二进制日志的作用

二进制日志主要记录 MySQL 数据库的变化。它会记录所有更改表数据和表结构的事件，比如 update、delete 等，它也会记录语句执行所用的时间。为了保证事件的准确性，会记录事件的长度。它不会记录 select、show 语句的执行。使用二进制日志主要有两种用途：

1. 主从备份。主库上的二进制日志记录主库的所有变更，主库会把日志包含的事件发给从库，从库执行这些事件，从而与主库的数据保持一致；
2. 从二进制日志恢复数据库。

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

在 commit 执行之前，写入二进制日志。

配置方式

默认情况下，二进制日志是关闭的，可以通过修改 MySQL 的配置文件来启动和设置二进制日志。

查看 MySQL 配置文件的路径：

shell> mysql --help | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

如果是用 brew 安装的 mysql，配置文件默认在 /usr/local/etc/my.cnf

在配置文件的 [mysqld] 下面添加：

# 由于 mysql 的 bug，必须配置 server-id，可以是任意值
server-id=11
# 开启二进制日志，日志文件名称是 mysql-bin
log-bin=mysql-bin
# 设置过期时间，10天后自动删除旧的日志。默认是 0，表示永不删除
expire_logs_days = 10
# 设置每个日志文件的大小，超过这个值，重新创建新的文件。最小值是4096B，最大值是 1GB，默认是1GB
max_binlog_size = 100M

注意：必须配置 server-id，否则启动 MySQL 会报错，官方文档有说明：

If you specify the --log-bin option without also specifying a --server-id, the server is not allowed to start. (Bug #11763963, Bug #56739)

日志文件默认保存在datadir目录下，也可以设置为其他路径，比如 log-bin=/usr/local/var/mysql/mysql-bin。即使在文件名称后面添加后缀，mysql 也会忽略，mysql 会添加数字后缀。

也可以直接配置为 log-bin，不加任何参数。此时日志文件的默认名称是 host_name-bin，host_name 是你的主机名称。

除了二进制日志，mysql 还会创建一个 mysql-bin.index 文件，该文件保存所有的二进制日志的文件名称。可以直接使用 cat 命令查看，不要手动修改这个文件。

另外，每次重新启动 mysql，也会创建新的二进制日志。

当前客户端可以暂时关闭二进制日志： SET sql_log_bin=OFF。

查看方法

使用 show variables like 'log_bin%'; 查询二进制日志设置：

image

使用 show binary logs; 查看二进制日志文件个数及文件名称：

image

使用 show master status 查看当前正在使用的日志：

image

使用 SHOW SLAVE STATUS 可以在从库上查看它在读取哪个日志；

使用 mysqlbinlog 查看二进制日志的内容，红色方框里面是该文件的创建时间：

image

二进制日志的格式

有三种格式：row-based logging（ROW），statement-based logging（STATEMENT），mixed-base logging（MIXED）。

There are several reasons why a client might want to set binary logging on a per-session basis:

• A session that makes many small changes to the database might want to use row-based logging.
• A session that performs updates that match many rows in the WHERE clause might want to use statement-based logging because it will be more efficient to log a few statements than many rows.
• Some statements require a lot of execution time on the master, but result in just a few rows being modified. It might therefore be beneficial to replicate them using row-based logging.

官方文档有这样两句话：

Statement may not be safe to log in statement format.

You can avoid these issues by using MySQL's row-based replication instead.

查看日志格式：

mysql> select @@session.binlog_format;
mysql> select @@global.binlog_format;

改变日志格式：

全局改变：

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

客户端改变：

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

下面这张图，上面的日志是 ROW 格式，选中的日志是 STATEMENT 格式：

image

删除二进制日志

使用 reset master; 删除所有的二进制日志文件。MySQL 会重新创建二进制日志，新的日志文件扩展名从 000001 开始编号。

MySQL 还提供了删除指定日志的方法：

purge master logs to 'log_name' 删除比 log_name 编号小的日志，比如 purge master logs to 'mysql-bin.000003';，编号小于 000003 的日志会被删除。

purge master logs before 'datatime' 删除 datatime 之前的日志，日期格式是 YYYY-MM-DD hh:mm:ss。

如果日志文件正在用于主从备份，它不会被删除。

使用二进制日志恢复数据库

在使用 mysql 的全量备份恢复数据库之后，可以再使用二进制日志恢复到指定时间点。比如当前时间是11点多，我不小心把一个重要的库删除了。幸好每天凌晨两点做了数据库全量备份，这时我就可以先恢复数据库到两点，然后再使用二进制日志恢复到删除之前的数据库。

直接使用日志文件恢复数据库：

shell> mysqlbinlog binlog_files | mysql -u root -p

可以把日志文件导出文本，然后编辑文本，删除一些不需要执行的语句，然后再恢复数据库：

shell> mysqlbinlog binlog_files > tmpfile
shell> ... edit tmpfile ...
shell> mysql -u root -p < tmpfile

如果有多个日志文件，在一个连接中使用它们：

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

恢复到指定时间点：

shell> mysqlbinlog --stop-datetime="2018-10-25 11:00:00" \
         /var/log/mysql/bin.123456 | mysql -u root -p

从指定时间点恢复：

shell> mysqlbinlog --start-datetime="2018-10-25 10:01:00" \
         /var/log/mysql/bin.123456 | mysql -u root -p

根据位置恢复：

shell> mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \
         | mysql -u root -p

shell> mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \
         | mysql -u root -p

位置信息可以从日志文件的 log_pos 获取。

参考

1. MySQL 二进制日志简介

2. 二进制日志所有参数

3. mysqlbinlog 的使用方法

4. 使用二进制日志恢复数据库