MySQL教程FG171-MySQL服务器日志管理

内容简介：MySQL服务器日志是数据库管理和故障排查的重要工具，包含了数据库运行的各种信息。本文风哥教程参考MySQL官方文档MySQL Server Administration部分，详细介绍MySQL各类日志的配置、管理和分析方法，帮助DBA高效管理MySQL日志系统。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 MySQL日志类型概述

MySQL提供多种类型的日志，用于记录数据库的不同方面活动，主要包括：

错误日志（Error Log）：记录MySQL服务器的启动、运行和停止过程中的错误信息
通用查询日志（General Query Log）：记录所有客户端的连接和查询操作
慢查询日志（Slow Query Log）：记录执行时间超过阈值的SQL语句
二进制日志（Binary Log）：记录数据修改操作，用于复制和恢复
中继日志（Relay Log）：从主服务器接收的二进制日志，用于复制
事务日志（Redo Log）：确保事务的持久性，用于崩溃恢复

1.2 MySQL日志配置基础

MySQL日志配置主要通过配置文件（my.cnf或my.ini）或动态修改系统变量实现。学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 日志存储规划

在生产环境中，日志存储需要单独规划，建议：

将日志文件与数据文件存储在不同的磁盘上，避免I/O竞争
使用高性能磁盘存储二进制日志，特别是在复制环境中
为日志文件预留足够的存储空间，定期监控空间使用情况

2.2 日志保留策略

日志保留策略需要根据业务需求和存储容量来确定：

错误日志：建议保留30-90天，用于故障追溯
通用查询日志：仅在诊断问题时开启，不建议长期开启
慢查询日志：建议保留30-180天，用于性能分析
二进制日志：根据复制和恢复需求，通常保留7-30天

Part03-生产环境项目实施方案

3.1 错误日志配置与管理

错误日志是MySQL服务器最重要的日志之一，记录了服务器的启动、运行和停止过程中的错误信息。

# 配置错误日志
# vi /etc/my.cnf
[mysqld]
# 错误日志文件路径
log_error = /mysql/logs/error.log
# 错误日志详细程度（1-3，3最详细）
log_error_verbosity = 2
# 日志时间戳格式（UTC或SYSTEM）
log_timestamps = SYSTEM

# 查看错误日志内容
# tail -n 50 /mysql/logs/error.log
2026-04-02T10:00:01.123456+08:00 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘8.4.0’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Server – GPL.
2026-04-02T10:05:30.123456+08:00 1234 [Warning] InnoDB: A long semaphore wait:
–Thread 139876543210987 has waited at row0purge.cc line 813 for 240.00 seconds the semaphore:
Mutex at 0x7f3e4c000000, Mutex_innodb_adaptive_hash_index, state: locked by thread 139876543211000
2026-04-02T10:10:20.123456+08:00 2345 [ERROR] Got an error reading communication packets

3.2 通用查询日志配置与管理

通用查询日志记录所有客户端的连接和查询操作，会产生大量日志，建议仅在诊断问题时开启。

# 动态开启通用查询日志
mysql> SET GLOBAL general_log = ON;
Query OK, 0 rows affected (0.00 sec)

# 设置通用查询日志文件
mysql> SET GLOBAL general_log_file = ‘/mysql/logs/general.log’;
Query OK, 0 rows affected (0.00 sec)

# 查看通用查询日志内容
# tail -n 20 /mysql/logs/general.log
2026-04-02T10:15:00.123456+08:00 5678 Connect root@localhost on fgedudb using Socket
2026-04-02T10:15:05.123456+08:00 5678 Query SHOW GLOBAL VARIABLES LIKE ‘general_log%’
2026-04-02T10:15:10.123456+08:00 5678 Query SELECT * FROM fgedu_student WHERE id = 1
2026-04-02T10:15:15.123456+08:00 5678 Quit

3.3 慢查询日志配置与管理

慢查询日志记录执行时间超过阈值的SQL语句，是性能优化的重要工具。

# 配置慢查询日志
# vi /etc/my.cnf
[mysqld]
# 开启慢查询日志
slow_query_log = ON
# 慢查询日志文件路径
slow_query_log_file = /mysql/logs/slow.log
# 慢查询阈值（秒）
long_query_time = 2
# 记录未使用索引的查询
log_queries_not_using_indexes = ON
# 记录管理语句
log_slow_admin_statements = ON
# 记录慢查询的最小行数
min_examined_row_limit = 1000

# 使用pt-query-digest分析慢查询日志
# pt-query-digest /mysql/logs/slow.log > slow_query_analysis.txt

# 查看分析结果摘要
# head -n 50 slow_query_analysis.txt
# 301ms user time, 10ms system time, 10.00M rss, 40.00M vsz
# Current date: Tue Apr 2 10:20:00 2026
# Hostname: fgedu.net.cn
# Files: /mysql/logs/slow.log
# Overall: 1,234 total, 56 unique, 0.1 QPS, 0.2x concurrency ________________
# Time range: 2026-04-01T00:00:00 to 2026-04-02T10:20:00
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 3456s 2ms 123s 3s 10s 8s 2s
# Lock time 123s 0 45s 100ms 500ms 500ms 10ms
# Rows sent 456789 0 12345 371 1234 2345 0
# Rows examine 12345678 0 123456 9999 56789 234567 100
# Query size 123456 10 1234 100 500 200 50

# Profile
summed exec time count pct stddev median avg 95% max query
================= ====== ==== ======= ======= =========== =========== =========== =====
3210.0s (93%) 1024 83% 8s 2s 3.1s 10s 123s SELECT * FROM fgedu_orders WHERE customer_id = ?
123.0s (4%) 128 10% 2s 1s 0.96s 2s 10s UPDATE fgedu_users SET last_login = NOW() WHERE id = ?
45.0s (1%) 64 5% 0.5s 0.3s 0.7s 1s 2s DELETE FROM fgedu_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY) 12.0s (0.3%) 18 1% 0.2s 0.1s 0.7s 1s 2s SELECT * FROM fgedu_products WHERE category_id = ? AND status = ?

3.4 二进制日志配置与管理

二进制日志记录数据修改操作，用于复制和基于时间点的恢复。

# 查看二进制日志文件
mysql> SHOW BINARY LOGS;
+—————+———–+
| Log_name | File_size |
+—————+———–+
| binlog.000001 | 1073741824|
| binlog.000002 | 1073741824|
| binlog.000003 | 567890123|
+—————+———–+

# 配置二进制日志
# vi /etc/my.cnf
[mysqld]
# 开启二进制日志
log_bin = /mysql/binlog/binlog
# 二进制日志格式（ROW, STATEMENT, MIXED）
binlog_format = ROW
# 二进制日志最大大小
max_binlog_size = 1G
# 二进制日志过期时间（天）
expire_logs_days = 14
# 同步二进制日志到磁盘
sync_binlog = 1

# 查看二进制日志内容
# mysqlbinlog /mysql/binlog/binlog.000003 | head -n 50
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#260402 10:25:00 server id 1 end_log_pos 123 CRC32 0x12345678 Start: binlog v 4, server v 8.4.0 created 260402 10:25:00
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#260402 10:25:30 server id 1 end_log_pos 234 CRC32 0x87654321 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= ‘12345678-1234-1234-1234-1234567890ab:12345’/*!*/;
# at 234
#260402 10:25:30 server id 1 end_log_pos 345 CRC32 0xabcdef12 Query thread_id=5678 exec_time=0 error_code=0
SET TIMESTAMP=1712024730/*!*/;
SET @@session.pseudo_thread_id=5678/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1134118272/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*! utf8mb4 *//*!/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!/;
BEGIN
/*!*/;
# at 345
#260402 10:25:30 server id 1 end_log_pos 456 CRC32 0x12ab34cd Table_map: `fgedudb`.`fgedu_student` mapped to number 123
# at 456
#260402 10:25:30 server id 1 end_log_pos 567 CRC32 0x56ef78ab Write_rows: table id 123 flags: STMT_END_F
### INSERT INTO `fgedudb`.`fgedu_student`
### SET
### @1=1000 /* INT meta=0 nullable=0 is_null=0 */
### @2=’张三’ /* VARSTRING(100) meta=100 nullable=0 is_null=0 */
### @3=20 /* INT meta=0 nullable=1 is_null=0 */
### @4=’2026-04-02′ /* DATE meta=0 nullable=1 is_null=0 */
# at 567
#260402 10:25:30 server id 1 end_log_pos 598 CRC32 0x90ab12cd Xid = 123456
COMMIT/*!*/;

3.5 中继日志配置与管理

中继日志是从主服务器接收的二进制日志，用于复制环境中的从服务器。

# 配置中继日志
# vi /etc/my.cnf
[mysqld]
# 中继日志文件路径
relay_log = /mysql/logs/relay-bin
# 开启中继日志自动恢复
relay_log_recovery = ON
# 中继日志空间限制（0表示无限制）
relay_log_space_limit = 10G

Part04-生产案例与实战讲解

4.1 日志监控实战

使用脚本监控MySQL日志文件的增长和异常：

#!/bin/bash
# log_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

LOG_DIR=”/mysql/logs”
ALERT_FILE=”/tmp/mysql_log_alert.txt”
MAX_LOG_SIZE=10737418240 # 10GB

# 检查日志文件大小
check_log_size() {
echo “检查日志文件大小…”
for log_file in $(find $LOG_DIR -name “*.log” -o -name “*.err”); do
file_size=$(stat -c %s $log_file)
if [ $file_size -gt $MAX_LOG_SIZE ]; then
echo “警告: 日志文件 $log_file 超过10GB，当前大小: $(du -h $log_file | cut -f1)” >> $ALERT_FILE
fi
done
}

# 检查错误日志中的严重错误
check_error_log() {
echo “检查错误日志…”
error_log=”$LOG_DIR/error.log”
if [ -f $error_log ]; then
# 查找最近24小时的严重错误
grep “ERROR” $error_log | grep -E “$(date +%Y-%m-%d -d ‘yesterday’)|$(date +%Y-%m-%d)” | head -n 20 >> $ALERT_FILE
fi
}

# 检查慢查询日志
check_slow_log() {
echo “检查慢查询日志…”
slow_log=”$LOG_DIR/slow.log”
if [ -f $slow_log ]; then
# 计算最近24小时的慢查询数量
slow_count=$(grep -c “Query_time:” $slow_log | grep -E “$(date +%Y-%m-%d -d ‘yesterday’)|$(date +%Y-%m-%d)”)
if [ $slow_count -gt 100 ]; then
echo “警告: 最近24小时慢查询数量过多: $slow_count” >> $ALERT_FILE
fi
fi
}

# 主函数
main() {
echo “MySQL日志监控脚本执行开始: $(date)” > $ALERT_FILE
check_log_size
check_error_log
check_slow_log

# 如果有告警，发送邮件
if [ $(wc -l < $ALERT_FILE) -gt 1 ]; then # 这里可以添加邮件发送逻辑 echo "检测到日志异常，请查看: $ALERT_FILE" else echo "日志监控正常" rm $ALERT_FILE fi } main

# 运行日志监控脚本
# chmod +x /mysql/scripts/log_monitor.sh
# /mysql/scripts/log_monitor.sh
MySQL日志监控脚本执行开始: Tue Apr 2 10:30:00 2026
检查日志文件大小…
检查错误日志…
检查慢查询日志…
日志监控正常

4.2 日志分析实战

使用sys模式分析慢查询：

# 使用sys模式查看慢查询
mysql> USE sys;
Database changed

mysql> SELECT * FROM statements_with_runtimes_in_95th_percentile ORDER BY avg_timer_wait DESC LIMIT 10;
+————————————–+——–+—————+————-+—————-+—————-+—————-+—————-+————-+
| query | calls | total_latency | avg_latency | max_latency | lock_latency | rows_sent | rows_examined | full_scan |
+————————————–+——–+—————+————-+—————-+—————-+—————-+—————-+————-+
| SELECT * FROM fgedu_orders WHERE customer_id = ? | 1024 | 3210.00 s | 3.13 s | 123.00 s | 10.00 s | 123456 | 12345678 | YES |
| UPDATE fgedu_users SET last_login = NOW() WHERE id = ? | 128 | 123.00 s | 960.00 ms | 10.00 s | 5.00 s | 0 | 128 | NO |
| DELETE FROM fgedu_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY) | 64 | 45.00 s | 703.00 ms | 2.00 s | 2.00 s | 0 | 1024567 | YES | +--------------------------------------+--------+---------------+-------------+----------------+----------------+----------------+----------------+-------------+ 3 rows in set (0.02 sec)

4.3 日志轮转实战

配置日志轮转，避免日志文件过大：

# 创建日志轮转配置文件
# vi /etc/logrotate.d/mysql
/mysql/logs/error.log {
daily
rotate 30
size 1G
compress
delaycompress
missingok
notifempty
create 640 mysql mysql
postrotate
/bin/systemctl reload mysqld > /dev/null 2>/dev/null || true
endscript
}

/mysql/logs/general.log {
daily
rotate 7
size 500M
compress
delaycompress
missingok
notifempty
create 640 mysql mysql
postrotate
/bin/systemctl reload mysqld > /dev/null 2>/dev/null || true
endscript
}

/mysql/logs/slow.log {
daily
rotate 30
size 2G
compress
delaycompress
missingok
notifempty
create 640 mysql mysql
postrotate
/bin/systemctl reload mysqld > /dev/null 2>/dev/null || true
endscript
}

# 测试日志轮转配置
# logrotate -d /etc/logrotate.d/mysql
reading config file /etc/logrotate.d/mysql
reading config info for /mysql/logs/error.log
reading config info for /mysql/logs/general.log
reading config info for /mysql/logs/slow.log

Handling 3 logs

rotating pattern: /mysql/logs/error.log daily (30 rotations)
empty log files are not rotated, old logs are removed
considering log /mysql/logs/error.log
log does not need rotating (log size 524288 bytes < 1073741824 bytes) rotating pattern: /mysql/logs/general.log daily (7 rotations) empty log files are not rotated, old logs are removed considering log /mysql/logs/general.log log does not need rotating (log size 1048576 bytes < 524288000 bytes) rotating pattern: /mysql/logs/slow.log daily (30 rotations) empty log files are not rotated, old logs are removed considering log /mysql/logs/slow.log log does not need rotating (log size 2097152 bytes < 2147483648 bytes)

Part05-风哥经验总结与分享

5.1 日志管理最佳实践

风哥提示：在生产环境中，日志管理是数据库运维的重要组成部分，需要制定合理的策略和流程。

定期备份日志文件，避免磁盘空间不足
仅在必要时开启通用查询日志，减少性能影响
根据业务需求调整慢查询阈值，避免过多或过少的慢查询记录
使用二进制日志进行复制和恢复时，确保日志的完整性和可用性
建立日志监控和告警机制，及时发现和处理问题
定期分析日志内容，优化数据库性能和配置

5.2 日志相关故障排查

常见日志相关故障及排查方法：

日志文件过大：检查日志配置，调整日志级别，配置日志轮转
日志丢失：检查日志路径权限，查看服务器错误日志，恢复备份
慢查询过多：分析慢查询日志，优化SQL语句和索引，调整阈值
二进制日志损坏：使用mysqlbinlog工具检查，从备份恢复，重新搭建复制

生产环境建议：建立完善的日志管理体系，包括配置、监控、分析和归档等环节，确保数据库的稳定运行和快速故障排查。更多学习教程公众号风哥教程itpux_com

from MySQL:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html