MySQL 日志（十四）

MySQL 日志

• MySQL 日志记录了 MySQL 数据库日常操作和错误信息。

日志简介

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

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

• 通过刷新日志，可以强制关闭和重新打开日志文件（或者在某个情况下切换到一个新的日志）。当执行一个 FLUSH LOGS 语句或执行 mysqladmin flush-logs 或 mysqladmin refresh 时，将刷新日志。

二进制日志

• 二进制日志包含所有更新了数据或者已经存在更新了数据（例如，没有匹配任何行的一个 DELETE）的语句。使用二进制日志的主要目的是最大可能地恢复数据库，因为二进制日志包含备份后进行的所有更新。

启动和设置二进制日志

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

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

  log-bin [=path/[filename] ]
  expire_logs_days = 10
  max_binlog_size = 100M
  

  log-bin 定义开启二进制日志；日志文件后缀为 000001，000002...，每启动一次 MySQL 服务会自动生成一个新的日志文件，后缀名加 1 递增；filename.index 文件内容为所有日志的清单，可以使用记事本打开。

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

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

• 通过使用 SHOW VARIABLES 语句可以查询日志设置，执行的语句及结果如下：

  SHOW VARIABLES LIKE 'log_%';
  

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

查看二进制日志

• show binary logs 语句可以查看当前的二进制日志文件个数及其文件名。

• MySQL 二进制日志并不能直接查看，如果要查看日志内容，可以通过 mysqlbinlog 命令查看，在命令行下执行的语句格式如下：

  mysqlbinlog 路径  --不需要加引号，正反斜杆都可以
  

• 在 MySQL 中查看二进制日志，执行语句格式如下：

  SHOW BINLOG EVENTS IN 'log_name';
  

• 使用 SHOW MASTER STATUS; 可以查看当前的二进制日志 POS 位置。

删除二进制日志

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

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

   RESET MASTER 语法格式如下：

   RESET MASTER;
   

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

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

   PURGE MASTER LOGS 语法如下：

   PURGE {MASTER | BINARY} LOGS TO 'log_name'
   PURGE {MASTER | BINARY} LOGS BEFORE 'date'
   

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

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

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

• 使用 mysqlbinlog 工具从指定的时间点开始直到现在，或另一个指定的时间点的日志中恢复数据。

• mysqlbinlog 恢复数据的语法如下：

  mysqlbinlog [option] filename | mysql -u username -p
  

  option 是一些可选的选项，filename 是日志文件名。

  option 参数是 --start-date、--stop-date、--start-position、--stop-position。--start-date 和--stop-date 可以指定恢复数据库的起始时间点和结束时间点。--start-position、--stop-position 可以指定恢复数据的开始位置和结束位置。

暂时停止二进制日志功能

• 修改配置文件，可以停止二进制日志，但是需要重启 MySQL 数据库。MySQL 提供了暂时停止二进制日志的功能。通过 SET SQL_LOG_BIN 语句可以暂停或者启动二进制日志。

• SET SQL_LOG_BIN 的语法如下：

  SET sql_log_bin = {0|1}  --0暂停，1启动
  

错误日志

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

启动和设置错误日志

• 在默认情况下，错误日志会记录到数据库的数据目录下。如果没有在配置文件中指定文件名，则文件名默认为 hostname.err。

• 如果执行了 FLUSH LOGS，错误日志文件会重新加载。

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

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

  修改配置项后，需要重启 MySQL 服务以生效。

查看错误日志

• MySQL 错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看 MySQL 错误日志。

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

• SHOW VARIABLES 语句如下：

  SHOW VARIABLES LIKE 'log_error';
  

删除错误日志

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

• flush logs 只是重新打开日志文件，并不做日志备份和创建的操作。如果日志文件不存在，MySQL 启动或者执行 flush logs 时会创建新的日志文件。

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

  mysqladmin -u username -p flush-logs
  

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

  flush logs;
  

通用查询日志

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

启动和设置通用查询日志

• MySQL 服务器默认情况下并没有开启通用查询日志。如果需要通用查询日志，可以通过修改 my.ini（或 my.cnf）配置文件来开启。在 my.ini（或 my.cnf）的 [mysqld] 组下加入 log 选项，形式如下：

  [mysqld]
  general-log=1
  general_log_file="path/filename.log"
  

  如果不指定目录和文件名，通用查询日志将默认存储在 MySQL 数据目录中的 hostname.log 文件中。

查看通用查询日志

• 通用查询日志中记录了用户的所有操作。通用查询日志是以文本文件的形式存储在文件系统中，可以使用文本编辑器直接打开通用日志进行查看。

• 通过 SHOW VARIABLES 语句可以查看通用查询日志开启状态及日志所在路径，语句如下：

  SHOW VARIABLES LIKE '%general%';
  

删除通用查询日志

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

慢查询日志

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

启动和设置慢查询日志

• MySQL 中慢查询日志默认是关闭的，可以通过配置文件 my.ini 或者 my.cnf 中的 slow-query-log 选项启动慢查询日志。另外，还需要配置 long_query_time 选项指定记录阈值。

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

  [mysqld]
  slow-query-log = 1
  slow_query_log_file = "path/filename.log"
  long-query-time = n    --单位为秒，默认值为10秒
  

删除慢查询日志

• 和通用日志一样，慢查询日志也可以直接删除。删除后在不重启服务器的情况下，需要执行 mysqladmin flush-logs 或者 flush logs; 语句重建日志文件。

注意事项

• 日志既会影响 MySQL 的性能，又会占用大量磁盘空间。因此，如果不必要，应尽可能少地开启日志。在开发环境中优化查询效率低的语句，可以开启慢查询日志；如果需要记录用户的所有查询操作，可以开启通用查询日志；如果需要记录数据的变更，可以开启二进制日志；错误日志是默认开启的。
• 二进制日志还可以用来进行数据恢复，还可以用来数据复制。在数据库定期备份的情况下，如果出现数据丢失，可以先用备份恢复大部分数据，然后使用二进制日志恢复最近备份后变更的数据。在双机热备情况下，可以使用 MySQL 的二进制日志记录数据的变更，然后将变更部分复制到备份服务器上。