MySQL - 服务器日志（慢查询日志、错误日志、通用查询日志、二进制日志、中继日志、元数据日志）

# 前言

我的 MySQL 版本 :MySQL 5.7.34

本文只写MySQL 5.7版本的内容, 内容来自 点我查看 - MySQL官网 Version 5.7 文档 。

MySQL服务器日志记录了用户的MySQL操作和错误信息等。每个日志都有自己的用途, 例如 数据恢复、主从复制、排查故障等。

## MySQL 服务器日志类型

• 错误日志（Error log）
• 通用查询日志（General query log）
• 二进制日志（Binary log）
• 中继日志（Relay log）
• 慢查询日志（Slow query log）
• 元数据日志（DDL Log）

一、错误日志（Error Log）

错误日志包含服务器启动和关闭期间以及在服务器运行期间发生的错误、警告和注释。

保存的错误日志类型是通过 log_error_verbosity 系统变量设置的。

• log_error_verbosity = 1（仅限错误）
• log_error_verbosity = 2（错误和警告）
• log_error_verbosity = 3（错误、警告和注释, 默认）

1.1 错误日志目标配置 1.1.1 查看配置示例

# 第一步: 终端输入连接 mysql $ mysql -u root -p # 第二步: 查看 错误日志文件配置 mysql> show variables like "log_error"; +---------------+-----------------------------------------+ | Variable_name | Value | +---------------+-----------------------------------------+ | log_error | /Applications/MAMP/logs/mysql_error.log | +---------------+-----------------------------------------+ 1 row in set (0.01 sec) 

1.1.2 配置文件

• Value 值为stderr是将错误日志输出至控制台。
• Value 值为错误文件路径是将错误日志追加至错误文件。

如果想要将日志追加至错误文件 :

1. 编辑MySQL配置文件my.cnf。

2. 在 [mysqld] 组中加入配置文件配置项。

   [mysqld] # 改为自己的路径与日志文件名, 如果不写文件名, 则默认为 host_name.err (主机名.err) log-error = dir/{filename}

3. 重启服务器, 使配置生效。

1.2 查看错误日志

• 找到你配置的dir/{filename}错误日志文件。
• 普通文件文件直接双击打开查看。
• 命令行 cat 、 tail 等命令查看。

1.3 删除或刷新错误日志

• 普通文件直接删除。

• 刷新的话, 看 第七节。

二、通用查询日志（General query log）

通用查询日志 在客户端连接或断开连接时, 将从客户端按顺序接收到的每个 DQL 语句写入查询日志。

• 通用查询日志默认被禁用。
• 不建议长期开启, 因为会占用很多I/O。
• 建议调试时开启, 调试完关闭。

2.1 临时开启通用查询日志

• 通过general_log全局变量临时开启或关闭, 重启 MySQL 服务后会初始化为关闭状态。

  • general_log = 0（关闭状态, 默认）
  • general_log = 1（开启状态）

• 开启实例

  

2.2 查看通用查询日志

• 找到你的通用查询日志文件。
• 普通文件文件直接双击打开查看。
• 命令行 cat 、 tail 等命令查看。
• 

2.3 删除或刷新通用查询日志

• 普通文件直接删除。
• 刷新的话, 看 第七节。

三、二进制日志（Binary log）

• 二进制日志记录保存了所有执行过的 DDL、DML 语句。
• 如果MySQL服务意外停止, 可通过二进制日志文件排查, 用户操作或表结构操作，从而来恢复数据库数据。
• 启动 二进制日志 功能, 会影响服务器性能, 但如果需要恢复或主从复制功能, 则好处则大于对服务器的影响。
• MySQL5.7 版本中, 二进制日志默认被禁用。

3.1 应用场景

• 主从复制
• 数据或表结构恢复

3.2 二进制日志文件配置 3.2.1 查看二进制日志配置示例

# 第一步: 终端输入连接 mysql $ mysql -u root -p # 第二步: 查看 二进制日志文件配置, 默认是关闭状态 mysql> show variables like "%log_bin%"; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-------+ 6 rows in set (0.01 sec) 

3.2.2 查看二进制日志文件内容

# 查看二进制日志文件 mysql> show binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000001 | 177 | | binlog.000002 | 177 | | binlog.000003 | 442 | +---------------+-----------+ 3 rows in set (0.00 sec) # 查看二进制日志文件内容, 终端命令操作下, 例:  $ mysqlbinlog binlog.000003 

3.2.3 二进制日志文件配置

• 文件名格式: 文件名.有序自增数字， 例: binlog.000001, binlog.000002 等
• 默认情况下, 还会生成一个 例:binlog.index二进制日志索引文件, 包含二进制日志文件的名称。
• 发生以下任何事件时, 会重新生成二进制日志文件
  • 服务器启动或重新启动
  • 服务器刷新日志
  • 日志文件大小达到max_binlog_size值, 默认值为1GB。

如果想要将开启二进制日志 :

1. 编辑MySQL配置文件my.cnf。

2. 在 [mysqld] 组中加入配置文件配置项。

   [mysqld] # 1. 改为自己的路径与日志文件名, 如果不写文件名, 则默认为 host_name.number (主机名.number) log_bin = dir/{filename.number} # 2. 或直接默认为基本名称并保存至数据库数据目录。 log_bin # 其他配置 binlog_format = row # 日志文件格式 expire_logs_days = 15 # 执行自动删除的天数, 默认为0, 表示不自动删除。 max_binlog_size = 200M # 单个日志文件的大小限制, 默认为 1GB 

3. 重启服务器, 使配置生效。

3.2.4 配置文件格式

• 通过 binlog-format=type 变量控制。

• MySQL5.7.7 之前, 默认格式为statement,MySQL5.7.7及以后默认为row。

• 例外: 在NDB Cluster中, 默认为mixed。

• 存在临时表时, 不建议切换复制格式。只有 基于语句 复制才会记录临时表, 基于行 的复制不会记录临时表, 基于混合复制时, 通常会记录临时表, 但可加载函数UUID()会发生异常。

• 复制的过程中, 不建议切换复制格式。

• InnoDB数据表且事务隔离级别为RC 或 R-UC时, 只能使用 基于行 的日志。

• 基于语句的日志记录（binlog-format=statement）
• 基于行的日志记录（binlog-format=row）
• 前两种的混合日志记录（binlog-format=mixed）

# 全局设置 - 之后的连接生效, 当前连接不生效 mysql> SET GLOBAL binlog_format = 'STATEMENT'; mysql> SET GLOBAL binlog_format = 'ROW'; mysql> SET GLOBAL binlog_format = 'MIXED'; # 范围设置 - 只在当前连接生效 mysql> SET SESSION binlog_format = 'STATEMENT'; mysql> SET SESSION binlog_format = 'ROW'; mysql> SET SESSION binlog_format = 'MIXED'; 

3.3 删除二进制日志文件

# 1. 删除所有二进制日志文件 mysql> reset master; # 2. 根据日期删除日期之前的所有日志文件 mysql> purge master logs before "20220815"; # 3. 根据文件名删除之前的所有日志 mysql> purge master logs to "binlog.000003"; 

3.4 mysqlbinlog 命令常用选项

# 终端命令, 可组合使用 # 文件名都是举例, 改为自己的文件名 # 1. 显示帮助信息 mysqlbinlog --help # 2. 从指定服务器上获取日志 mysqlbinlog binlog.000003 -h 127.0.0.1 # 3. 指定 TCP/IP 端口号 mysqlbinlog binlog.000003 -h 127.0.0.1 -p 123456 # 4. 指定数据库 mysqlbinlog -d 数据库名 # 5. 指定开始时间（>=） mysqlbinlog --start-datetime="2022-08-15 18:00:00" binlog.000003 # 6. 指定结束时间（<=） mysqlbinlog --stop-datetime="2022-08-15 18:30:00" binlog.000003 # 7. 指定开始版本（>=）, 在日志文件中以 # at 开头的为版本号 mysqlbinlog --start-position=4835 binlog.000003 # 8. 指定结束版本（<=） mysqlbinlog --stop-position=5057 binlog.000003

3.5 二进制日志恢复

# 终端命令, 可结合 mysqlbinlog 命令选项组合使用 # 例: 通过最晚版本号, 恢复数据 mysqlbinlog binlog.000003 --stop-position=5057 | mysql -u root -p

四、中继日志（Relay log）

• 中继日志与二进制文件一样, 由一组编号文件组成和一个包含所有使用中继日志文件名称的索引文件。

• 中继日志仅用于从服务器，通过 I/O 线程读取主服务器的二进制日志并保存至relay log文件中, 从服务器再读取relay log文件并执行应用至从服务器, 从而使主从服务器的数据保持一致。

• 默认情况下, 当复制SQL线程在执行完文件中的所有事件并且不再需要它后, 会自动删除中继日志文件。可通过relay_log_purge变量控制。

4.1 中继日志文件配置 4.1.1 查看中继日志配置示例

# 第一步: 终端输入连接 mysql $ mysql -u root -p # 第二步: 查看 中继日志配置 mysql> show variables like "%relay_log%"; +---------------------------+---------------------------------------------------------------------------------+ | Variable_name | Value | +---------------------------+---------------------------------------------------------------------------------+ | relay_log | | | relay_log_basename | /Library/Application Support/appsolute/MAMP PRO/db/mysql57/Chon-relay-bin | | relay_log_index | /Library/Application Support/appsolute/MAMP PRO/db/mysql57/Chon-relay-bin.index | | relay_log_info_file | relay-log.info | | relay_log_info_repository | FILE | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | +---------------------------+---------------------------------------------------------------------------------+ 8 rows in set (0.00 sec) 

4.1.2 中继日志文件配置

• 文件名格式: 文件名.有序自增数字， 例: 主机名-relay-bin.000001, 主机名-relay-bin.000002 等
• 索引文件格式: 例:主机名-relay-bin.index, 包含所有中继日志文件的名称。
• 发生以下任何事件时, 会重新生成中继日志文件
  • 每次复制 I/O 线程启动时
  • 服务器刷新日志（flush logs或mysqladmin flush-logs）
  • relay log文件过大时, 有两种情况:
    • 如果max_relay_log_size值大于0, 则表示中继日志文件的最大大小, 默认 0。
    • 如果max_relay_log_size值等于0, 则由max_binlog_size确定最大中继日志大小。

可以在配置文件中自定义名称 :

1. 编辑MySQL配置文件my.cnf。

2. 在 [mysqld] 组中加入配置文件配置项。

   [mysqld] # 改为自己的路径与日志文件名, 例: relay_log = dir/filename
   
   relay_log_index = relay-bin.index 

3. 重启服务器, 使配置生效。

4.2 主从复制案例

MySQL - 复制应用中继日志解析

MySQL主从复制读写分离，看这篇就够了！

五、慢查询日志（Slow query log）

• 慢查询日志是保存 执行时间 超过long_query_time且至少执行min_examined_row_limit行的 DQL 语句。
• 通过慢查询日志, 可以找出哪些 DQL 语句执行时间较长、执行效率低, 从而优化。
• 默认是关闭的。

5.1 变量

• long_query_time: 查询所超过的秒数, 超过后将记录慢查询日志文件中。最小值0, 默认值 10 。
• min_examined_row_limit: 少于此配置行数的查询不会记录到慢查询中。最小值0, 默认值 0 。

5.2 临时开启

通过slow_query_log全局变量临时开启或关闭, 重启 MySQL 服务后会初始化为关闭状态。

• slow_query_log = 0（关闭状态, 默认）

• slow_query_log = 1（开启状态）

• 开启实例

  

5.3 查看慢查询日志

• 找到你的慢查询日志文件。
  • 普通文件文件直接双击打开查看。
  • 命令行 cat 、 tail 等命令查看。
• 通过分析工具查看, MySQL Dump Slow（MySQL 官方）、MySQL Sla（强大、可定制）

5.4 删除或刷新慢查询日志

• 普通文件直接删除。
• 刷新的话, 看 第七节。

六、元数据日志

元数据日志记录了影响表分区的数据定义语句生成的元数据操作。

分区元数据操作的记录被写入MySQL 数据目录中的ddl_log.log文件。

ddl_log.log在实际需要记录元数据语句之前不会创建它，并在成功启动mysqld 后将其删除。因此，此文件可能不会出现在以完全正常方式运行的 MySQL 服务器上。

例如:drop table和alter table生成的元数据操作。

例:alter table user drop partition p2, 必须确保完全删除该分区，并确保从 table 的分区列表中删除其定义t3。

七、刷新日志（MetaData Log） 7.1mysqladmin刷新 所有日志文件

# 1. 终端进入自己想要刷新的 mysql 日志目录文件夹 $ cd ???/log/ # 2. 建议备份 mysql 日志文件, 根据自己的文件名来操作, 例:  $ mv mysql.log mysql.log-backup.起个版本号 # 3. 刷新日志, 会重新生成一个新的通用查询日志 $ mysqladmin -u root -p flush-logs

7.2flush logs刷新所有日志文件

# 连接 mysql , 例: $ mysql -u root -p # 刷新 mysql> flush logs; Query OK, 0 rows affected (0.02 sec)