MySQL8.0入门-MySQL日志

日志简介

MySQL日志主要分为4类，使用这些日志文件，可以查看MySQL内部发生的事情。这4类日志分别是：

• 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题。
• 查询日志：记录建立的客户端连接和执行的语句。
• 二进制日志：记录所有更改数据的语句，可以用于数据复制。
• 慢查询日志：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。

默认情况下，所有日志创建于MySQL数据目录中。通过刷新日志，可以强制MySQL关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。当执行一个FLUSH LOGS语句或执行MySQLadmin flush-logs或MySQLadmin refresh时，将刷新日志。

启动日志功能会降低MySQL数据库的性能。例如，在查询非常频繁的MySQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志。同时，日志会占用大量的磁盘空间。

二进制日志

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

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

启动和设置二进制日志

默认情况，二进制日志是开启的。

my.ini中的[MySQLd]组下有关二进制日志的设置：

windows下的默认路径C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

log-bin=[=path/[filename]]

log-bin定义开启二进制日志；path表明日志文件所在的目录路径；filename指定了日志文件的名称，如文件的全名为filename.000001、filename.000002等，除了上述文件之外，还有一个名称为filename.index的文件，文件内容为所有日志的清单，可以使用记事本打开该文件。

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

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

show variables like "log_%";查看日志的详细信息。

查看二进制日志

MySQL二进制日志存储了所有的变更信息，MySQL二进制日志是经常用到的。当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件。MySQL服务重新启动一次，以“.000001”为后缀的文件会增加一个，并且后缀名加1递增；如果日志长度超过了max_binlog_size的上限（默认是1GB），就会创建一个新的日志文件。

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

PS C:\Program Files\MySQL\MySQL Server 8.0\bin> .\mysqlbinlog 'C:/ProgramData/MySQL/MySQL Server 8.0/Data/HOLY-bin.000001'
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220202 14:27:23 server id 1  end_log_pos 126 CRC32 0x6c93cb5f  Start: binlog v 4, server v 8.0.28 created 220202 14:27:23 at startup
ROLLBACK/*!*/;
BINLOG '
SyT6YQ8BAAAAegAAAH4AAAAAAAQAOC4wLjI4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABLJPphEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAV/Lk2w=
'/*!*/;
# at 126
#220202 14:27:23 server id 1  end_log_pos 157 CRC32 0x783830bb  Previous-GTIDs
# [empty]
# at 157
#220202 14:27:26 server id 1  end_log_pos 180 CRC32 0xe6f5dace  Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

需要添加环境变量C:\Program Files\MySQL\MySQL Server 8.0\bin，这样可以再命令行直接执行mysqlbinlog。

二进制日志路径最好用引号括起来，这样避免路径识别错误

删除二进制日志

MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法：RESET MASTER删除所有的二进制日志文件；PURGE MASTERLOGS只删除部分二进制日志文件。

1. 使用RESET MASTER语句删除所有二进制日志文件

RESET MASTER;

执行完该语句后，所有二进制日志将被删除，MySQL会重新创建二进制日志，新的日志文件扩展名将重新从000001开始编号。

2. 使用PURGE MASTER LOGS语句删除指定日志文件

PURGE {MATER | BINARY} LOGS TO 'log_name';
PURGE {MATER | BINARY} LOGS BEFORE 'date';

第1种方法指定文件名，执行该命令将删除文件名编号比指定文件名编号小的所有日志文件。第2种方法指定日期，执行该命令将删除指定日期以前的所有日志文件。

比如，删除2022年2月5日之前创建的所有日志文件：

PURGE MASTER LOGS BEFORE '20220205';

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

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

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

MySQLbinlog恢复数据的语法如下：

mysqlbinlog [option] filename | mysql -user -ppass

option是一些可选的选项，filename是日志文件名。比较重要的两对option参数是--start-datetime、--stop-datetime和--start-position、--stop-position。--start-datetime、--stop-datetime可以指定恢复数据库的起始时间点和结束时间点。--start-position、--stop-position可以指定恢复数据的开始位置和结束位置。

使用MySQLbinlog恢复MySQL数据库到2022年2月5日16:43:00时的状态，执行命令及结果如下：

mysqlbinlog --stop-datetime="2022-02-05 16:43:00" "C:\ProgramData\MySQL\MySQL Server 8.0\Data\HOLY-bin.000001" | mysql -uroot -p

输入密码，执行恢复操作，没有报错就代表执行成功。这种方法对于意外操作非常有效，比如因操作不当误删了数据表。

暂时停止二进制日志功能

如果在MySQL的配置文件中配置启动了二进制日志，MySQL会一直记录二进制日志。修改配置文件，可以停止二进制日志，但是需要重启MySQL数据库。MySQL提供了暂时停止二进制日志的功能。通过SET SQL_LOG_BIN语句可以使用MySQL暂停或者启动二进制日志。

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)

错误日志

错误日志文件包含了当MySQLd启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。

启动和设置错误日志

在默认情况下，错误日志会记录到数据库的数据目录下。如果没有在配置文件中指定文件名，则文件名默认为hostname.err。如果执行了FLUSH LOGS，错误日志文件会重新加载。

错误日志的启动和停止以及指定日志文件名都可以通过修改my.ini（或者my.cnf）来配置。错误日志的配置项是log-error。在[MySQLd]下配置log-error，则启动错误日志。如果需要指定文件名，则配置项如下：

[mysqld]
log-error=[path/[file_name]]

path为日志文件所在的目录路径，file_name为日志文件名。修改配置项后，需要重启MySQL服务以生效。

查看错误日志

通过错误日志可以监视系统的运行状态，便于及时发现故障、修复故障。MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看MySQL错误日志。

如果不知道日志文件的存储路径，可以使用SHOW VARIABLES语句查询错误日志的存储路径。SHOW VARIABLES语句如下：

SHOW VARIABLES LIKE 'log_error';

删除错误日志

MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除。

对于MySQL 5.5.7以前的版本，flush logs可以将错误日志文件重命名为filename.err_old，并创建新的日志文件。但是从MySQL 5.5.7开始，flush logs只是重新打开日志文件，并不做日志备份和创建的操作。如果日志文件不存在，MySQL启动或者执行flush logs时会创建新的日志文件。

在运行状态下删除错误日志文件后，MySQL并不会自动创建日志文件。flush logs在重新加载日志的时候，如果文件不存在，则会自动创建。所以在删除错误日志之后，如果需要重建日志文件，需要在服务器端执行以下命令：

mysqladmin -u root -p flush-logs

或者在客户端登录MySQL数据库，执行flush logs语句：

flush logs;

通用查询日志

通用查询日志记录MySQL的所有用户操作，包括启动和关闭服务、执行查询和更新语句等。

启动通用查询日志

MySQL服务器默认情况下并没有开启通用查询日志。通过show variables like '%general%';语句可以查询当前查询日志的状态。

mysql> show variables like '%general%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| general_log      | OFF      |
| general_log_file | HOLY.log |
+------------------+----------+
2 rows in set, 1 warning (0.00 sec)

从结果可以看出，通用查询日志的状态为OFF，表示通用日志是关闭的。

开启通用日志的方法如下：

set  @@global.general_log=1;

查看通用查询日志

通用查询日志中记录了用户的所有操作。通过查看通用查询日志，可以了解用户对MySQL进行的操作。通用查询日志是以文本文件的形式存储在文件系统中的，可以使用文本编辑器直接打开通用日志文件进行查看，Windows下可以使用记事本，Linux下可以使用vim、gedit等。

删除通用查询日志

通用查询日志是以文本文件的形式存储在文件系统中的。通用查询日志记录用户的所有操作，因此在用户查询、更新频繁的情况下，通用查询日志会增长得很快。数据库管理员可以定期删除比较早的通用日志，以节省磁盘空间。

可以用直接删除日志文件的方式删除通用查询日志。要重新建立新的日志文件，可使用语句MySQLadmin -flush logs。

慢查询日志

慢查询日志是记录查询时长超过指定时间的日志。慢查询日志主要用来记录执行时间较长的查询语句。通过慢查询日志，可以找出执行时间较长、执行效率较低的语句，然后进行优化。

启动和设置慢查询日志

MySQL中慢查询日志默认是关闭的，可以通过配置文件my.ini或者my.cnf中的log-slow-queries选项打开，也可以在MySQL服务启动的时候使用--log-slow-queries[=file_name]启动慢查询日志。启动慢查询日志时，需要在my.ini或者my.cnf文件中配置long_query_time选项指定记录阈值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志文件中。

在my.ini或者my.cnf开启慢查询日志的配置如下：

[mysqld]
log-slow-queries[=path/[filename]]
long_query_time=n

path为日志文件所在目录路径，filename为日志文件名。如果不指定目录和文件名称，默认存储在数据目录中，文件为hostname-slow.log，hostname是MySQL服务器的主机名。参数n是时间值，单位是秒。如果没有设置long_query_time选项，默认时间为10秒。

查看慢查询日志

MySQL的慢查询日志是以文本形式存储的，可以直接使用文本编辑器查看。在慢查询日志中，记录着执行时间较长的查询语句，用户可以从慢查询日志中获取执行效率较低的查询语句，为查询优化提供重要的依据。

删除慢查询日志

和通用查询日志一样，慢查询日志也可以直接删除。删除后在不重启服务器的情况下，需要执行MySQLadmin -u root-p flush-logs重新生成日志文件，或者在客户端登录到服务器执行flush logs语句重建日志文件。