面试题：MySQL 稳定设置之日志管理优化

Question

MySQL 中有多种日志类型，如二进制日志（binary log）、错误日志（error log）和慢查询日志（slow query log）。假设你正在负责一个高并发的 MySQL 数据库的稳定性维护，你会如何配置这些日志以平衡性能和故障排查需求？请阐述具体的配置思路及相关参数的作用。

Answer 1

二进制日志（binary log）

1. 配置思路：
   • 开启二进制日志用于数据备份和主从复制。在高并发场景下，合理控制日志写入频率和大小，避免频繁 I/O 操作影响性能。
2. 相关参数及作用：
   • log-bin：开启二进制日志功能，指定日志文件的前缀名。例如 log-bin = mysql - bin，这样会生成类似 mysql - bin.000001 等的日志文件。
   • binlog - format：设置二进制日志格式，有 STATEMENT（基于语句）、ROW（基于行）、MIXED（混合模式）。在高并发场景下，ROW 格式能更准确记录数据变化，避免一些主从复制的问题，但日志量相对较大；STATEMENT 格式日志量小，但在某些情况下可能导致主从复制不一致。一般建议在高并发且数据一致性要求高的场景使用 ROW 格式。
   • sync - binlog：控制二进制日志刷新到磁盘的频率。取值为 0 时，表示由操作系统决定何时将二进制日志缓冲区的内容刷新到磁盘，性能最高但故障时可能丢失部分日志；取值为 1 时，表示每次事务提交时都将二进制日志缓冲区的内容刷新到磁盘，数据安全性最高但 I/O 开销大；取值为 N（N > 1）时，表示每 N 次事务提交将二进制日志缓冲区的内容刷新到磁盘，在性能和数据安全之间有一定平衡，例如 sync - binlog = 100。

错误日志（error log）

1. 配置思路：
   • 错误日志记录 MySQL 运行过程中的错误信息，应确保其能够完整准确记录关键错误，同时避免日志文件过大影响磁盘空间。
2. 相关参数及作用：
   • log - error：指定错误日志文件的路径和文件名，例如 log - error = /var/log/mysql/error.log。通过明确路径，方便运维人员定位和查看错误信息。
   • log - errors - debug：可用于开启更详细的调试错误日志记录。在排查复杂问题时可临时开启，但在高并发生产环境默认不开启，因为详细的调试日志会产生大量数据，影响性能和磁盘空间。

慢查询日志（slow query log）

1. 配置思路：
   • 慢查询日志用于记录执行时间超过一定阈值的 SQL 语句，帮助定位性能瓶颈。在高并发场景下，合理设置阈值，避免记录过多非关键的慢查询，同时定期清理日志文件。
2. 相关参数及作用：
   • slow - query - log：开启慢查询日志功能，设置为 ON 表示开启，OFF 表示关闭。
   • long - query - time：设置慢查询的时间阈值，单位为秒。例如 long - query - time = 2，表示执行时间超过 2 秒的 SQL 语句将被记录到慢查询日志中。此值应根据系统实际情况调整，设置过小可能记录大量非性能瓶颈的查询，设置过大可能错过真正的慢查询。
   • log - slow - admin - statements：设置是否记录管理类的慢查询语句，如 ALTER TABLE 等。设置为 ON 可以记录这类语句，方便全面排查数据库性能问题。
   • log - slow - slaves：对于主从复制环境，设置是否记录从库上的慢查询。设置为 ON 有助于排查从库性能问题。
   • slow - query - log - file：指定慢查询日志文件的路径和文件名，如 slow - query - log - file = /var/log/mysql/slow - query.log，便于集中管理和分析慢查询。