MySQL 日志配置规范

二进制日志（Binary Log）配置

二进制日志的作用

记录所有数据更改事件
用于复制和恢复
用于审计和监控

二进制日志配置参数

1. 启用二进制日志

ini

# 启用二进制日志
log_bin = /var/lib/mysql/mysql-bin

2. 二进制日志格式

ini

# 设置二进制日志格式为 ROW
# 可选值：STATEMENT、ROW、MIXED
binlog_format = ROW

3. 二进制日志文件大小

ini

# 设置单个二进制日志文件的最大大小
max_binlog_size = 1G

4. 二进制日志过期时间

ini

# 设置二进制日志的过期时间（天）
# MySQL 8.0 之前版本使用 expire_logs_days
# MySQL 8.0 及之后版本推荐使用 binlog_expire_logs_seconds
expire_logs_days = 7

# MySQL 8.0 及之后版本使用
# binlog_expire_logs_seconds = 604800  # 7天

5. 二进制日志缓存

ini

# 设置二进制日志缓存大小
binlog_cache_size = 32M

# 设置最大二进制日志缓存大小
max_binlog_cache_size = 1G

6. 二进制日志检查点

ini

# 设置二进制日志检查点频率
# 可选值：1（每次事务）、0（不自动）、N（每N秒）
sync_binlog = 1

7. 二进制日志事务压缩

ini

# 启用二进制日志事务压缩（MySQL 8.0.20+）
binlog_transaction_compression = ON

# 设置二进制日志事务压缩级别
binlog_transaction_compression_level_zstd = 3

二进制日志配置最佳实践

使用 ROW 格式的二进制日志，确保主从数据一致性
设置合理的二进制日志大小和过期时间，避免占用过多磁盘空间
对于生产环境，将 sync_binlog 设置为 1，确保二进制日志的安全性
对于高并发环境，适当增大 binlog_cache_size
定期备份二进制日志，用于灾难恢复

错误日志（Error Log）配置

错误日志的作用

记录 MySQL 服务器的启动、关闭和错误信息
用于故障诊断和排查

错误日志配置参数

1. 启用错误日志

ini

# 设置错误日志文件路径
log_error = /var/log/mysql/error.log

2. 错误日志级别

ini

# 设置错误日志级别
# 可选值：1（仅错误）、2（错误和警告）、3（错误、警告和注意）
log_error_verbosity = 3

3. 错误日志格式

ini

# 设置错误日志格式
# 可选值：FILE、TABLE
# MySQL 8.0.13+ 支持将错误日志写入表
# log_error_services = 'log_filter_internal; log_sink_internal'

4. 错误日志大小限制

ini

# 设置错误日志的最大大小（MySQL 8.0+）
error_log_max_size = 100M

# 设置错误日志的轮换次数（MySQL 8.0+）
error_log_rotate = ON

错误日志配置最佳实践

将错误日志与数据文件分开存储，便于管理和备份
设置合理的错误日志级别，避免日志文件过大
对于生产环境，推荐使用 log_error_verbosity = 3
定期检查和清理错误日志
对于 MySQL 8.0+，启用错误日志轮换

查询日志（General Query Log）配置

查询日志的作用

记录所有 MySQL 服务器接收到的查询语句
用于调试和审计

查询日志配置参数

1. 启用查询日志

ini

# 启用查询日志
general_log = OFF

# 设置查询日志文件路径
general_log_file = /var/log/mysql/general.log

2. 查询日志输出格式

ini

# 设置查询日志输出格式
# 可选值：FILE、TABLE
general_log_output = FILE

查询日志配置最佳实践

生产环境不建议启用查询日志，会影响性能
仅在调试和审计时临时启用
对于需要审计的场景，考虑使用其他审计工具替代

慢查询日志（Slow Query Log）配置

慢查询日志的作用

记录执行时间超过指定阈值的查询语句
用于性能分析和优化

慢查询日志配置参数

1. 启用慢查询日志

ini

# 启用慢查询日志
slow_query_log = ON

# 设置慢查询日志文件路径
slow_query_log_file = /var/log/mysql/slow.log

2. 慢查询阈值

ini

# 设置慢查询阈值（秒）
long_query_time = 2

# 设置慢查询的最小扫描行数
min_examined_row_limit = 1000

3. 记录没有使用索引的查询

ini

# 记录没有使用索引的查询
log_queries_not_using_indexes = ON

# 记录管理语句
log_slow_admin_statements = ON

4. 慢查询日志输出格式

ini

# 设置慢查询日志输出格式
# 可选值：FILE、TABLE
slow_query_log_output = FILE

5. 慢查询日志时间精度

ini

# 设置慢查询日志时间精度（MySQL 5.6.4+）
# 可选值：0（秒）、1-6（毫秒）
log_output = FILE
log_timestamps = SYSTEM

慢查询日志配置最佳实践

生产环境建议启用慢查询日志
设置合理的慢查询阈值，通常为 2-5 秒
结合 pt-query-digest 等工具分析慢查询日志
定期清理慢查询日志，避免占用过多磁盘空间
对于高并发环境，考虑使用 Performance Schema 替代慢查询日志

中继日志（Relay Log）配置

中继日志的作用

从库上临时存储主库的二进制日志
用于复制过程

中继日志配置参数

1. 中继日志文件前缀

ini

# 设置中继日志文件前缀
relay_log = /var/lib/mysql/relay-bin

2. 中继日志索引文件

ini

# 设置中继日志索引文件
relay_log_index = /var/lib/mysql/relay-bin.index

3. 中继日志大小限制

ini

# 设置单个中继日志文件的最大大小
max_relay_log_size = 1G

4. 自动清理中继日志

ini

# 自动清理中继日志
relay_log_purge = ON

5. 中继日志恢复

ini

# 启用中继日志恢复
relay_log_recovery = ON

中继日志配置最佳实践

启用 relay_log_recovery，确保从库重启后复制可以正确恢复
设置合理的中继日志大小，避免占用过多磁盘空间
定期检查中继日志，确保复制正常运行

事务日志（InnoDB Redo Log）配置

事务日志的作用

确保事务的持久性
用于崩溃恢复

事务日志配置参数

1. 事务日志文件路径

ini

# 设置事务日志文件路径
innodb_log_group_home_dir = /var/lib/mysql/

2. 单个事务日志文件大小

ini

# 设置单个事务日志文件大小
innodb_log_file_size = 512M

3. 事务日志文件数量

ini

# 设置事务日志文件数量
innodb_log_files_in_group = 2

4. 事务日志缓冲区大小

ini

# 设置事务日志缓冲区大小
innodb_log_buffer_size = 16M

5. 事务日志刷新策略

ini

# 设置事务日志刷新策略
# 可选值：0（每秒刷新）、1（每次事务刷新）、2（每次事务刷新到OS缓存，每秒刷新到磁盘）
innodb_flush_log_at_trx_commit = 1

事务日志配置最佳实践

对于生产环境，将 innodb_flush_log_at_trx_commit 设置为 1，确保事务的持久性
设置合理的事务日志大小，通常为缓冲池大小的 25%-50%
事务日志文件数量建议为 2-4 个
对于高并发环境，适当增大 innodb_log_buffer_size

审计日志（Audit Log）配置

审计日志的作用

记录数据库的访问和操作
用于合规和安全审计

审计日志配置参数

1. 启用审计日志插件

ini

# 加载审计日志插件（MySQL 企业版）
plugin-load-add = audit_log.so
audit_log = FORCE_PLUS_PERMANENT

# 设置审计日志格式
audit_log_format = JSON

# 设置审计日志文件路径
audit_log_file = /var/log/mysql/audit.log

# 设置审计日志的最大大小
audit_log_rotate_on_size = 100M

# 设置审计日志的过期时间
audit_log_policy = ALL

2. 社区版审计日志替代方案

对于社区版 MySQL，可以使用以下替代方案：

MariaDB Audit Plugin：MariaDB 提供的审计插件，可用于 MySQL
Percona Audit Log Plugin：Percona 提供的审计插件
sys schema：结合 Performance Schema 和 sys schema 实现审计功能

审计日志配置最佳实践

根据合规要求设置审计日志策略
定期清理审计日志，避免占用过多磁盘空间
将审计日志与数据文件分开存储
加密敏感的审计日志

日志文件的存储和管理

1. 日志文件存储位置

将日志文件与数据文件分开存储，便于管理和备份
使用独立的磁盘存储日志文件，避免影响数据文件的性能

2. 日志文件权限

bash

# 设置日志文件权限
chown mysql:mysql /var/log/mysql/
chmod 755 /var/log/mysql/
chmod 640 /var/log/mysql/*.log

3. 日志文件的备份

定期备份重要的日志文件，如二进制日志和错误日志
使用 rsync 或其他工具将日志文件备份到远程服务器
对于二进制日志，考虑使用 mysqlbinlog 工具进行备份和压缩

4. 日志文件的清理

配置合理的日志过期时间，自动清理过期日志
对于手动管理的日志文件，定期清理旧日志
使用 logrotate 等工具自动管理日志文件

logrotate 配置示例：

/var/log/mysql/*.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql mysql
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

不同版本的日志配置差异

MySQL 5.5 及之前

二进制日志默认格式为 STATEMENT
不支持慢查询日志的时间精度
不支持错误日志轮换
不支持审计日志插件

MySQL 5.6

支持慢查询日志的时间精度
支持二进制日志的事务压缩
支持在线修改二进制日志格式
增强了错误日志的详细程度

MySQL 5.7

支持将错误日志写入表
增强了慢查询日志的功能
支持更多的日志配置选项
增强了 Performance Schema 的日志监控功能

MySQL 8.0

支持错误日志轮换
支持错误日志大小限制
增强了二进制日志的安全性
支持更多的审计日志选项
支持将日志写入多个目标

日志配置的性能影响

1. 二进制日志的性能影响

启用二进制日志会增加主库的写入延迟
ROW 格式的二进制日志比 STATEMENT 格式的性能开销更大
sync_binlog = 1 会增加磁盘 I/O 开销

2. 慢查询日志的性能影响

启用慢查询日志会增加一定的性能开销
log_queries_not_using_indexes = ON 会增加更多的性能开销
结合 pt-query-digest 等工具分析慢查询日志，优化查询性能

3. 查询日志的性能影响

启用查询日志会显著影响性能
生产环境不建议启用查询日志

日志配置的安全考虑

1. 日志文件的访问控制

限制日志文件的访问权限，避免敏感信息泄露
定期检查日志文件的权限设置

2. 敏感信息的处理

避免在日志文件中记录敏感信息，如密码
对于包含敏感信息的日志，进行加密或脱敏处理

3. 日志的传输安全

对于远程传输的日志，使用加密传输
定期检查日志传输的安全性

常见问题（FAQ）

Q1: 如何优化二进制日志的性能？

A1: 优化二进制日志性能的方法包括：

使用 ROW 格式的二进制日志，确保主从数据一致性
适当增大 binlog_cache_size，减少磁盘 I/O
对于高并发环境，考虑将 sync_binlog 设置为大于 1 的值
使用 SSD 存储二进制日志，提高写入性能
启用二进制日志事务压缩（MySQL 8.0.20+）

Q2: 如何处理日志文件过大的问题？

A2: 处理日志文件过大的方法包括：

配置合理的日志过期时间，自动清理过期日志
设置合理的单个日志文件大小
定期手动清理旧日志文件
使用 logrotate 等工具自动管理日志文件
将日志文件与数据文件分开存储，使用独立的磁盘

Q3: 生产环境是否应该启用查询日志？

A3: 生产环境不建议启用查询日志，因为：

查询日志会记录所有查询语句，导致日志文件迅速增长
启用查询日志会显著影响 MySQL 服务器的性能
查询日志可能包含敏感信息，如密码
对于调试和审计，可以使用慢查询日志或 Performance Schema 替代

Q4: 如何分析慢查询日志？

A4: 分析慢查询日志的方法包括：

使用 mysqldumpslow 工具分析慢查询日志
使用 pt-query-digest 工具分析慢查询日志，生成详细的报告
使用 MySQL Enterprise Monitor 等商业工具分析慢查询日志
结合 EXPLAIN 命令分析慢查询的执行计划
优化慢查询的 SQL 语句和索引

Q5: 如何确保日志文件的安全性？

A5: 确保日志文件安全性的方法包括：

限制日志文件的访问权限，使用 chmod 640 和 chown mysql:mysql
将日志文件与数据文件分开存储，便于管理和备份
对于包含敏感信息的日志，进行加密或脱敏处理
定期备份日志文件，防止日志丢失
对于远程传输的日志，使用加密传输

Q6: MySQL 8.0 的错误日志有哪些改进？

A6: MySQL 8.0 对错误日志的改进包括：

支持错误日志轮换
支持设置错误日志的最大大小
增强了错误日志的详细程度
支持将错误日志写入表
支持自定义错误日志格式
增强了错误日志的安全性

Q7: 如何配置日志文件的自动备份？

A7: 配置日志文件自动备份的方法包括：

使用 cron 作业定期备份日志文件
使用 rsync 或其他工具将日志文件备份到远程服务器
对于二进制日志，使用 mysqlbinlog 工具进行备份和压缩
使用第三方备份工具，如 Percona XtraBackup
配置合理的日志过期时间，自动清理旧日志

MySQL 日志配置规范 ​

二进制日志（Binary Log）配置 ​

二进制日志的作用 ​

二进制日志配置参数 ​

1. 启用二进制日志 ​

2. 二进制日志格式 ​

3. 二进制日志文件大小 ​

4. 二进制日志过期时间 ​

5. 二进制日志缓存 ​

6. 二进制日志检查点 ​

7. 二进制日志事务压缩 ​

二进制日志配置最佳实践 ​

错误日志（Error Log）配置 ​

错误日志的作用 ​

错误日志配置参数 ​

1. 启用错误日志 ​

2. 错误日志级别 ​

3. 错误日志格式 ​

4. 错误日志大小限制 ​

错误日志配置最佳实践 ​

查询日志（General Query Log）配置 ​

查询日志的作用 ​

查询日志配置参数 ​

1. 启用查询日志 ​

2. 查询日志输出格式 ​

查询日志配置最佳实践 ​

慢查询日志（Slow Query Log）配置 ​

慢查询日志的作用 ​

慢查询日志配置参数 ​

1. 启用慢查询日志 ​

2. 慢查询阈值 ​

3. 记录没有使用索引的查询 ​

4. 慢查询日志输出格式 ​

5. 慢查询日志时间精度 ​

慢查询日志配置最佳实践 ​

中继日志（Relay Log）配置 ​

中继日志的作用 ​

中继日志配置参数 ​

1. 中继日志文件前缀 ​

2. 中继日志索引文件 ​

3. 中继日志大小限制 ​

4. 自动清理中继日志 ​

5. 中继日志恢复 ​

中继日志配置最佳实践 ​

事务日志（InnoDB Redo Log）配置 ​

事务日志的作用 ​

事务日志配置参数 ​

1. 事务日志文件路径 ​

2. 单个事务日志文件大小 ​

3. 事务日志文件数量 ​

4. 事务日志缓冲区大小 ​

5. 事务日志刷新策略 ​

事务日志配置最佳实践 ​