MySQL 日志二进制日志

28.4　二进制日志

二进制日志中以“事件”的形式记录了数据库中数据的变化情况，对于MySQL数据库的灾难恢复起着重要的作用。本节就对MySQL中的二进制日志进行简单的介绍。

28.4.1　开启二进制日志

可以在my.cnf文件或者my.ini文件中进行如下配置来开启二进制日志。

[mysqld]
log_bin = /data/mysql/log/bin_log/mysql-bin
binlog_format= mixed
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=512m
expire_logs_days = 10

各项配置说明如下：

·log_bin：表示开启二进制日志。如果没有为此项赋值，则MySQL会在DATADIR选项指定的目录（MySQL的数据存放目录）下创建二进制文件。

·binlog_format：二进制文件的格式。取值可以是STATEMENT、ROW和MIXED。

·binlog_cache_size：二进制日志的缓存大小。

·max_binlog_cache_size：二进制日志的最大缓存大小。

·max_binlog_size：单个二进制日志文件的最大大小，当文件大小超过此选项配置的值时，会发生日志滚动，重新生成一个新的二进制文件。

·expire_logs_days：二进制日志的过期时间。如果配置了此选项，则MySQL会自动清理过期的二进制日志。此选项的默认值为0，表示MySQL不会清理过期日志。

配置完成后，重启MySQL才能使配置生效。此时，会在/data/mysql/log/bin_log目录下生成MySQL的二进制文件。

[root@binghe150 ~]# ll /data/mysql/log/bin_log
total 8
-rw-r----- 1 mysql mysql 155 Jan 17 15:55 mysql-bin.000001
-rw-r----- 1 mysql mysql  41 Jan 17 15:55 mysql-bin.index

28.4.2　查看二进制日志

二进制日志文件不能以纯文本文件的形式来查看，可以使用MySQL的mysqlbinlog命令进行查看。接下来简单介绍一下查看MySQL二进制日志的步骤。

（1）向t_goods_category数据表中插入两条测试数据。

mysql> INSERT INTO t_goods_category (id, t_category) VALUES (7, '滋补类产品');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t_goods_category (id, t_category) VALUES (8, '图书');
Query OK, 1 row affected (0.00 sec)

（2）使用mysqlbinlog命令查看二进制日志。

[root@binghe150 ~]# mysqlbinlog --no-defaults /data/mysql/log/bin_log/mysql-bin.000001
###################省略n行内容#################################
BEGIN
SET TIMESTAMP=1579248019/*!*/;
INSERT INTO t_goods_category (id, t_category) VALUES (7, '滋补类产品')
###################省略n行内容#################################
INSERT INTO t_goods_category (id, t_category) VALUES (8, '图书')
###################省略n行内容#################################
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

二进制日志中记录了向t_goods_category数据表中插入数据的SQL语句。

注意：查看/data/mysql/log/bin_log目录下生成的MySQL二进制文件时，发现有一个mysql-bin.index文件，这个文件不记录二进制内容，其中记录的是当前目录下存在的所有二进制文件的完整路径。可以以纯文本文件的形式来查看mysql-bin.index文件。

[root@binghe150 ~]# cat /data/mysql/log/bin_log/mysql-bin.index
/data/mysql/log/bin_log/mysql-bin.000001

28.4.3　删除二进制日志

MySQL中除了通过配置二进制日志的过期时间，由MySQL自动删除过期的二进制日志外，还提供了3种安全的手动删除二进制日志的方法。

在正式介绍手动删除MySQL二进制日志的方法之前，先对MySQL进行多次重启操作，使MySQL能够生成多个二进制日志文件，以便进行删除测试。

多次重启MySQL后，再次查看/data/mysql/log/bin_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/bin_log 
total 44
-rw-r----- 1 mysql mysql 865 Jan 17 16:20 mysql-bin.000001
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000002
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000003
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000004
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000005
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000006
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000007
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000008
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000009
-rw-r----- 1 mysql mysql 155 Jan 17 16:21 mysql-bin.000010
-rw-r----- 1 mysql mysql 410 Jan 17 16:21 mysql-bin.index

接下来，正式介绍如何以安全的方式手动删除MySQL的二进制日志文件。

1．根据编号删除二进制日志

根据编号删除二进制日志，语法格式如下：

PURGE { BINARY | MASTER } LOGS TO 'log_name'

在MySQL命令行执行此语法格式的SQL语句，会删除比指定文件名编号小的所有二进制日志文件。例如，删除比mysql-bin.000003文件编号小的所有二进制日志文件。

mysql> PURGE MASTER LOGS TO 'mysql-bin.000003';
Query OK, 0 rows affected (0.01 sec)

SQL语句执行成功，查看/data/mysql/log/bin_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/bin_log
total 36
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000003
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000004
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000005
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000006
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000007
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000008
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000009
-rw-r----- 1 mysql mysql 155 Jan 17 16:21 mysql-bin.000010
-rw-r----- 1 mysql mysql 328 Jan 17 16:29 mysql-bin.index

发现mysql-bin.000001文件和mysql-bin.000002文件被删除了。说明根据编号删除二进制日志时，只会删除比当前指定的文件编号小的二进制日志文件，不会删除当前指定的二进制日志文件。

2．根据时间删除二进制日志

根据时间删除二进制日志，语法格式如下：

PURGE { BINARY | MASTER } LOGS BEFORE datetime_expr

执行此语法格式的SQL语句时，MySQL会删除指定时间以前的二进制日志。

例如，删除“2020-01-17 16:21:00”之前的二进制日志文件。

mysql> PURGE MASTER LOGS BEFORE '2020-01-17 16:21:00';
Query OK, 0 rows affected (0.00 sec)

SQL语句执行成功，查看/data/mysql/log/bin_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/bin_log
total 20
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000007
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000008
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000009
-rw-r----- 1 mysql mysql 155 Jan 17 16:21 mysql-bin.000010
-rw-r----- 1 mysql mysql 164 Jan 17 16:37 mysql-bin.index

“2020-01-17 16:21:00”之前的二进制日志文件已经被删除了，但不会删除“2020-01-17 16:21:00”时间点的二进制日志文件。

3．删除所有二进制日志

在MySQL命令行执行如下命令即可删除所有二进制日志文件。

mysql> RESET MASTER;
Query OK, 0 rows affected (0.01 sec)

SQL语句执行成功，再次查看/data/mysql/log/bin_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/bin_log
total 8
-rw-r----- 1 mysql mysql 155 Jan 17 16:41 mysql-bin.000001
-rw-r----- 1 mysql mysql  41 Jan 17 16:41 mysql-bin.index

此时/data/mysql/log/bin_log目录下的所有二进制文件已经被删除，并且二进制文件重新从000001开始编号。

28.4.4　暂时停止与开启二进制日志

在MySQL命令行执行如下命令暂时停止二进制日志：

mysql> SET sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

暂时开启二进制日志，则需要在MySQL命令行执行如下命令：

mysql> SET sql_log_bin = 1;
Query OK, 0 rows affected (0.00 sec)

28.4.5　关闭二进制日志

只需要在my.cnf文件或者my.ini文件中注释或者删除log_bin配置项，并重启MySQL服务器即可关闭MySQL的二进制日志，读者可自行实践，这里不再举例。