MySQL8.0 日志

MySQL日志记录了MySQL数据库日常操作和错误信息。MySQL有不同类型的日志文件（各自存储了不同类型的日志），从日志当中可以查询到MySQL数据库的运行情况、用户操作、错误信息等，可以为MySQL管理和优化提供必要的信息。对于MySQL的管理工作而言，这些日志文件是不可缺少的。

PART1. 日志简介

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

1）错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题。

2）查询日志：记录建立的客户端连接和执行的语句。

3）二进制日志：记录所有更改数据的语句，可以用于数据复制。

4）慢查询日志：记录所有执行时间超过long_query_time 的所有查询或不使用索引的查询。

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

如果正使用MySQL复制功能，在复制服务器上可以维护更多日志文件，这种日志称为接替日志。

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

PART2. 二进制日志

二进制日志主要记录MySQL数据库的变化。二进制日志以一种有效的格式并且是事务安全的方式包含更新日志中可用的所有信息。二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的语句。语句以“事件”的形式保存，描述数据更改。

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

1. 启动和设置二进制日志

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

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

log-bin [=path/ [filename]]

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

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

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

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

在my.ini 配置文件中的[MySQLd]组下，添加以下几个参数与参数值：

[mysqld]
log-bin
expire_logs_days = 10
man_binlog_size = 100M

添加完毕之后，关闭并重新启动MySQL服务进程，即可打开二进制日志，然后可以通过show variables 语句来查询日志设置。

通过上面的查询结果可以看出，log_bin变量的值为ON，表明二进制日志已经打开。MySQL重新启动之后，读者可以在自己机器上的MySQL数据文件夹下面看到新生成的文件后缀为.000001和.index的两个文件，文件名称为默认主机名称。

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

2. 查看二进制日志

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

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

 

3. 删除二进制日志

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

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

RESET MASTER;

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

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

PURGE MASTER LOGS 语法如下：

PURGE [MASTER | BINARY] LOGS TO 'log_name'
PURGE [MASTER | BINARY] LOGS BEFORE 'date'

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

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

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

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

MySQLbinlog恢复数据的语法如下：

mysqlbinlog [option] filename |mysql -uuser -ppass

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

5. 暂时停止二进制日志功能

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

SET sql_log_bin = {0|1}
SET sql_log_bin = 0;--暂停记录二进制日志
SET sql_log_bin = 1;--恢复记录二进制日志

PART3 错误日志

错误日志文件包含了当MySQLd启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。在MySQL中，错误日志也是非常有用的，MySQL会将启动和停止数据库信息以及一些错误信息记录到错误日志文件中。

1. 启动和设置错误日志

在默认情况下，错误日志会记录到数据库的数据目录下。如果没有在配置文件中指定文件名，则文件名默认为hostname.err。例如，MySQL所在的服务器主机名为MySQL-db，记录错误信息的文件名为MySQL-db.err。如果执行了FLUSH LOGS，错误日志会重新加载。

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

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

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

2. 查看错误日志

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

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

SHOW VARIABLES LIKE 'log_error';

 

3. 删除错误日志

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

对于MySQL5.5.7以前的版本，flush logs可以将错误日志文件重命名为filename.err_old，并创建新的日志文件。但是从MySQL5.5.7开始，flush logs只是重新打开日志文件，并不做日志备份和创建的操作。如果日志文件不存在，MySQL启动并不会自动创建日志文件。flush logs在重新加载日志的时候，如果日志不存在，则会自动创建。所以在删除错误日志之后，如果需要重建日志文件需要在服务器执行如下命令：

mysqladmin -u root -p flush-logs

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

flush logs;

PART4. 通用查询日志

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

1. 启动通用查询日志

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

开启通用日志的方法：

set @@global.general_log = 1;

 

关闭通用日志：

set @@global.general_log=0;

 2. 查看通用查询日志

通用查询日志中记录了用户的所有操作。通过查看通用查询日志，可以了解用户对MySQL进行的操作。通过查询日志是以文本文件的形式存储在文件系统中的。

 

3. 删除通用查询日志

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

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

PART5 慢查询日志

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

1. 启动和设置慢查询日志

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

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

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

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

2. 查看慢查询日志

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

注意：借助慢查询日志分析工具，可以更加方便地分析慢查询语句。比较著名的慢查询工具有MySQL Dump Slow、MySQL SLA、MySQL Log Filter、MyProfi。关于这些慢查询分析工具的用法，可以参考相关软件的帮助文档。

3. 删除慢查询日志

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

PART6 MySQL8.0 的新特性——日志分类更详细

在MySQL8.0版本中，日志分类将更加详细。例如，在错误信息中添加了错误信息编号[MY-010311]和错误所属子系统[Server]。

-- end