首页 > 编程笔记 > MySQL笔记 阅读：15

MySQL二进制日志用法详解

MySQL 中的二进制日志主要记录 MySQL 数据库的变化。二进制日志以一种有效的格式并且是事务安全的方式包含更新日志中可用的所有信息。

二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的语句。语句以“事件”的形式保存，描述数据更改。

二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。如果想要记录所有语句（例如，为了识别有问题的查询），需要使用一般查询日志。

使用二进制日志的主要目的是最大可能地恢复数据库，因为二进制日志包含备份后进行的所有更新。本节将介绍二进制日志的相关内容。

MySQL启动和设置二进制日志

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

my.ini 中 [mysqld] 组下面有关于二进制日志的设置： 
log-bin [=path/ [filename] ]
expire_logs_days = 10
maxbinlogsize = 1OOM

除了上述文件之外，还有一个名称为 filename.index 的文件，文件内容为所有日志的清单，可以使用记事本打开该文件。

expire_logs_days 定义了 MySQL 清除过期日志的时间，即二进制日志自动删除前的保留天数。默认值为 0，表示“没有自动删除”。当 MySQL 启动或刷新二进制日志时可能删除过期日志。

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

如果正在使用大的事务，二进制日志文件大小还可能会超过 max_binlog_size 定义的大小。

在 my.ini 配置文件中的 [MySQLd] 组下，添加以下几个参数与参数值： 
[mysqld]
log-bin
expire_logs_days = 10
max_binlog_size = 100M
添加完毕之后，关闭并重新启动 MySQL 服务进程，即可打开二进制日志，然后可以通过 SHOW VARIABLES 语句来查询日志设置。

【实例】使用 SHOW VARIABLES 语句查询日志设置，执行的语句及结果如下：
mysql> SHOW VARIABLES LIKE 'log_%' ;
+--------------------------------------+---------------------------------------------------------------------+
|Variable_name                         | Value                                                               |
+--------------------------------------+---------------------------------------------------------------------+
|log_bin                               | ON                                                                  |
|log_bin_basename                      | C:\ProgramData\MySQL\MySQL Server 8.0\Data\X0NHUNO7YDZVSSI-bin      |
|log_bin_index                         |C:\ProgramData\MySQL\MySQL Server 8.0\Data\X0NHUNO7YDZVSSI-bin.index |
|log_bin_trust_function_creators       |OFF                                                                  |
|log_bin_use_v1_row_events             | OFF                                                                 |
|log_error                             | .\X0NHUNO7YDZVSSI.err                                               |
|log_error_services                    |log_filter_internal;log_sink_internal                                |
|log_error_suppression_list            |                                                                     |
|log_error_verbosity                   |2                                                                    |
|log_output                            | FILE                                                                |
|log_queries_not_using_indexes         |OFF                                                                  |
|log_slave_updates                     |ON                                                                   |
|log_slow_admin_statements             |OFF                                                                  |
|log_slow_slave_statements             |OFF                                                                  |
|log_statements_unsafe_for_binlog      |ON                                                                   |
|log_throttle_queries_not_using_indexes|0                                                                    |
|log_timestamps                        | UTC                                                                 |
+--------------------------------------+---------------------------------------------------------------------+
通过上面的查询结果可以看出，log_bin 变量的值为 ON，表明二进制日志已经打开。MySQL 重新启动之后，读者可以在自己机器上的 MySQL 数据文件夹下面看到新生成的文件后缀为 .000001 和 .index 的两个文件，文件名称为默认主机名称。

例如，在笔者的机器上的文件名称为 X0NHUNO7YDZVSSI-bin.000001 和 X0NHUNO7YDZVSSI-bin.index。

提示，数据库文件最好不要与日志文件放在同一个磁盘上，这样当数据库文件所在的磁盘发生故障时，可以使用日志文件恢复数据。

MySQL查看二进制日志

MySQL 二进制日志存储了所有的变更信息，MySQL 二进制日志是经常用到的。当 MySQL 创建二进制日志文件时，先创建一个以 filename 为名称、以 .index 为后缀的文件，再创建一个以 filename 为名称、以 .000001 为后缀的文件。

MySQL 服务重新启动一次，以 .000001 为后缀的文件会增加一个，并且后缀名加 1 递增；如果日志长度超过了 max_binlog_size 的上限（默认是 1GB），就会创建一个新的日志文件。

SHOW BINARY LOGS 语句可以查看当前的二进制日志文件个数及其文件名。MySQL 二进制日志并不能直接查看，如果要查看日志内容，可以通过 mysqlbinlog 命令查看。

【实例】使用 SHOW BINARY LOGS 查看二进制日志文件个数及文件名，执行命令及结果如下： 
mysql> SHOW BINARY LOGS;
+----------------------------+-----------+------------+
| Log_name                | File_size | Encrypted  |
+----------------------------+-----------+------------+
| X0NHUNO7YDZVSSI-bin.000001 |     178   | N?o        |
+----------------------------+-----------+------------+
1 row in set (0.00 sec)
可以看到，当前只有一个二进制日志文件。日志文件的个数与 MySQL 服务启动的次数相同。每启动一次 MySQL 服务，就会产生一个新的日志文件。

【实例】使用 mysqlbinlog 查看二进制日志，执行命令及结果如下： 
C:\> mysqlbinlog D:/mysql/log/binlog.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190130 15:27:48 server id 1  end_log_pos 107   Start: binlog v 4, server v 8.0.28-log created
160330
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
9JBcTg8BAAAAZwAAAGsAAAABAAQANS41LjEzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAA
AAAAAAAAAAAAAAAAAAD0kFxOEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#190330 15:34:17 server id 1  end_log_pos 175   Query   thread_id=2     exec_time=0
error_code=0
SET TIMESTAMP=1314689657/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1,
@@session
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_
offset=1/*!*/;
/*!\C gb2312 *//*!*/;
SET @@session.character_set_client=24,@@session.collation_connection=24,
@@session.collation_server=24/
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 175
#190330 15:34:17 server id 1  end_log_pos 289   Query   thread_id=2     exec_time=0
error_code=0
use test/*!*/;
SET TIMESTAMP=1314689657/*!*/;
UPDATE fruits set f_price = 5.00  WHERE f_id = 'a1'
/*!*/;
# at 289
#190330 15:34:17 server id 1  end_log_pos 316   Xid = 14
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
这是一个简单的日志文件，日志中记录了一些用户的操作。从文件内容中可以看到，用户对 fruits 表进行了更新操作，语句为“UPDATE fruits set f_price = 5.00 WHERE f_id = 'a1';”。

MySQL删除二进制日志

MySQL 的二进制文件可以配置为自动删除，同时 MySQL 也提供了安全的手动删除二进制文件的方法：
RESET MASTER语句删除所有二进制日志文件，语法如下： 
RESET MASTER;
执行完该语句后，所有二进制日志将被删除，MySQL 会重新创建二进制日志，新的日志文件扩展名将重新从 000001 开始编号。

PURGE MASTER LOGS语句删除指定日志文件，语法如下： 
PURGE {MASTER | BINARY} LOGS TO 'log_name'
PURGE {MASTER | BINARY} LOGS BEFORE 'date'
第 1 种方法指定文件名，执行该命令将删除文件名编号比指定文件名编号小的所有日志文件。第 2 种方法指定日期，执行该命令将删除指定日期以前的所有日志文件。

【实例】使用PURGE MASTER LOGS 删除创建时间比 X0NHUNO7YDZVSSI-bin.000003 早的所有日志文件。

首先，为了演示这个语句的操作过程，需要准备多个日志文件，读者可以对 MySQL 服务进行多次重新启动。例如，这里有 3 个日志文件： 
mysql> SHOW binary logs;
+----------------------------+-----------+ -----------+
| Log_name                | File_size | Encrypted  +
+----------------------------+-----------+------------+
| X0NHUNO7YDZVSSI-bin.000001 |     178   | No        |
| X0NHUNO7YDZVSSI-bin.000002 |     641   | N?o        |
| X0NHUNO7YDZVSSI-bin.000003 |     345   | No        |
+----------------------------+-----------+------------+
3 rows in set (0.00 sec)
执行删除命令： 
mysql> PURGE MASTER LOGS TO " X0NHUNO7YDZVSSI-bin.000003";
Query OK, 0 rows affected (0.07 sec)

执行完成后，使用SHOW binary logs语句查看二进制日志： 
mysql> SHOW binary logs;
+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
| X0NHUNO7YDZVSSI-bin.000003 |     345   |
+----------------------------+-----------+
1 rows in set (0.00 sec)
可以看到，X0NHUNO7YDZVSSI-bin.000001、X0NHUNO7YDZVSSI-bin 000002 两个日志文件被删除了。

【实例】使用 PURGE MASTER LOGS 删除 2022 年 1 月 30 日前创建的所有日志文件，执行命令及结果如下： 
mysql> PURGE MASTER LOGS BEFORE '20220130';
Query OK, 0 rows affected (0.05 sec)
语句执行之后，2019 年 1 月 30 日之前创建的日志文件都将被删除，但 2022 年 1 月 30 日的日志会被保留（读者可根据自己机器中创建日志的时间修改命令参数）。使用 mysqlbinlog 可以查看指定日志的创建时间，如前面的实例所示，部分日志内容如下： 
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220130 15:27:48 server id 1  end_log_pos 107   Start: binlog v 4, server v 8.0.28-log created
160330
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
其中，220130 为日志创建的时间，即 2022 年 1 月 30 日。

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

如果 MySQL 服务器启用了二进制日志，在数据库出现意外丢失数据时，可以使用 mysqlbinlog 工具从指定的时间点开始（例如，最后一次备份）直到现在，或另一个指定的时间点的日志中恢复数据。

要想从二进制日志恢复数据，需要知道当前二进制日志文件的路径和文件名，一般可以从配置文件（my.cnf 或者 my.ini，文件名取决于 MySQL 服务器的操作系统）中找到路径。

mysqlbinlog 恢复数据的语法如下： 
mysqlbinlog [option] filename |mysql –uuser -ppass

比较重要的两对 option 参数是 --start-date 与 --stop-date、--start-position 与 --stop-position：
【实例】使用 mysqlbinlog 恢复 MySQL 数据库到 2022 年 1 月 30 日 15:27:48 时的状态，执行命令及结果如下： 
mysqlbinlog --stop-date="2022-01-30 15:27:48" D:\mysql\log\binlog\ X0NHUNO7YDZVSSI-bin.000003
| mysql –uuser –ppass
该命令执行成功后，会根据 X0NHUNO7YDZVSSI-bin.000003 日志文件恢复 2022 年 01 月 30 日 15:27:48 以前的所有操作。这种方法对于意外操作非常有效，比如因操作不当误删了数据表的情况。

暂时停止二进制日志功能

如果在 MySQL 的配置文件中配置启动了二进制日志，MySQL 会一直记录二进制日志。修改配置文件，可以停止二进制日志，但是需要重启 MySQL 数据库。

MySQL提供了暂时停止二进制日志的功能。通过 SET SQL_LOG_BIN 语句可以使用 MySQL 暂停或者启动二进制日志。

SET SQL_LOG_BIN 的语法如下： 
SET sql_log_bin = {0|1}

执行如下语句将暂停记录二进制日志： 
mysql> SET sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

执行如下语句将恢复记录二进制日志： 
mysql> SET sql_log_bin = 1;
Query OK, 0 rows affected (0.00 sec)

相关文章