Mysql--日志（错误日志、二进制日志、查询日志、慢查询日志）

四种日志对比总结

介绍：

MySQL 的这四种日志各司其职：

• 错误日志 用于系统级错误排查；
• 二进制日志 主要用于数据恢复、主从复制；
• 查询日志 记录所有 SQL 语句，适用于调试和审计；
• 慢查询日志 则专注于数据库性能优化。

1. 错误日志（Error Log）

作用：

记录 MySQL 服务器运行过程中出现的错误、警告、启动与关闭信息。
主要用于故障排查和系统监控。

特点：

• 包含 MySQL 启动、停止过程中发生的错误。
• 记录系统级错误（如磁盘空间不足）和 MySQL 内部错误。
• 记录服务器的启动时间、停止时间以及出现的严重错误。

日志文件位置：

可以通过以下命令查看错误日志位置：

SHOW VARIABLES LIKE 'log_error';

默认情况下，错误日志文件名称可能为 hostname.err，存放在 MySQL 数据目录中。

相关配置：

修改配置文件 my.cnf，指定错误日志的路径：

[mysqld]
log_error = /var/log/mysql/mysql-error.log

2. 二进制日志（Binary Log，Binlog）

作用：

记录所有更改数据库数据的操作（如 DML、DDL 操作），以及可能引发数据库变更的事务提交等信息。
主要用于：

• 数据恢复：通过 Binlog 恢复误删除或误修改的数据。
• 主从复制：主服务器将二进制日志传输到从服务器，实现主从同步。
• 审计：用于跟踪和分析谁在什么时间更改了数据库中的哪些数据。

特点：

• 不记录 SELECT 这类仅查询数据的语句。
• 记录事务的开始、提交和具体操作。
• 二进制日志是增量日志，可以配合备份日志进行增量恢复。

日志文件位置：

可以通过以下命令查看二进制日志文件名和存储位置：

SHOW VARIABLES LIKE 'log_bin';

相关配置：

修改 my.cnf 文件启用二进制日志：

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log

二进制日志格式

默认格式为 row

#可以使用mysqlbinlog命令查看二进制文件

补充

当 MySQL 的二进制日志文件编号达到最大值（999999）时，MySQL 会停止创建新的二进制日志文件并报错。这可能导致数据备份中断、主从复制故障等问题。

解决办法：

• 使用自动清理功能（如 expire_logs_days 或 binlog_expire_logs_seconds）来删除旧日志文件。
• 手动删除旧日志，或在必要时重置日志编号，从而避免编号耗尽的情况。

当文件编号达到上限时（编号耗尽的情况）

• 最大文件编号：
  MySQL 使用 6 位编号 管理二进制日志文件，编号范围是从 000001 到 999999。当文件编号递增到上限 999999 后，MySQL 无法再创建新的二进制日志文件，会导致以下几种行为或错误：

  1）MySQL 服务器停止写入二进制日志

  • 如果二进制日志的编号到达上限（即 binlog.999999 已存在），MySQL 无法创建新的日志文件。
  • 错误日志 中会出现如下报错：

    [ERROR] Error: binlog file number exceeds the max limit 999999.
    

    此时，MySQL 停止二进制日志功能，并且主从复制、恢复等基于二进制日志的功能可能会失效。

  2）导致主从复制故障（如果启用了复制）

  • 在主从复制场景中，如果主库停止写入二进制日志，将导致从库无法获取新的二进制日志，从而导致复制中断。
    这可能引发 数据不一致 问题。

解决办法–详细

为了避免二进制日志编号耗尽的问题，可以采取以下措施：

方法 1：手动清理旧的二进制日志文件

• 命令：

  PURGE BINARY LOGS TO 'binlog.000100';
  

  • 这将删除 binlog.000001 到 binlog.000099 的二进制日志文件，释放文件编号。

• 自动清理（推荐）：
  在 MySQL 配置文件中设置自动清理机制：

  [mysqld]`
  expire_logs_days = 7  # 自动删除 7 天前的二进制日志文件
  

  或者：

  binlog_expire_logs_seconds = 604800  # 设置日志自动清理时间为 7 天（MySQL 8.0+）
  

方法 2：重置二进制日志编号

如果二进制日志文件编号接近上限，可以重置日志编号，方法如下：

1. 停止 MySQL 服务：

   systemctl stop mysql
   

2. 删除旧的二进制日志文件（谨慎操作，确保数据安全）：

   rm -f /var/lib/mysql/binlog.*
   

3. 启动 MySQL 服务：

   systemctl start mysql
   

4. 重新启动二进制日志：

   FLUSH LOGS;
   

   这将重新生成新的二进制日志文件，从编号 000001 开始。

方法 3：调整存储配置

• 分片存储： 将日志分散到不同目录或磁盘。
• 分布式复制管理： 在多主复制架构中分担负载。

3. 查询日志（General Query Log）

作用：

记录 MySQL 服务器收到的所有 SQL 请求（包括查询语句和数据操作语句），以及客户端连接、断开等操作。

特点：

查询日志默认是关闭的

• 记录 SQL 语句执行过程中的所有细节，包括 SELECT、INSERT、UPDATE、DELETE 等。
• 通用日志通常用于调试和分析，但因为记录所有 SQL 语句，会占用较多磁盘空间，所以不建议长期开启。

日志文件位置：

可以通过以下命令查看查询日志是否开启及文件路径：

SHOW VARIABLES LIKE 'general_log%';

相关配置：

修改 my.cnf 文件开启查询日志：

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql-query.log

配置后发现general_log 的value为改为ON并且mysql-general.log成功产生日志

成功开启

4. 慢查询日志（Slow Query Log）

作用：

记录执行时间超过指定阈值（默认 10 秒）的 SQL 语句，主要用于优化数据库性能。

特点：

• 仅记录执行时间较长的 SQL 语句，帮助分析和优化数据库的性能瓶颈。
• 可以用来发现导致系统变慢的低效 SQL。

日志文件位置：

可以通过以下命令查看慢查询日志设置：

SHOW VARIABLES LIKE 'slow_query_log%';

慢查询日志默认是关闭的

相关配置：

修改 my.cnf 文件，开启慢查询日志并设置阈值：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 5  # 设置执行时间超过 5 秒的语句记录到日志