6.10.6 日志分析与根因定位

日志分析与根因定位遵循“先收集、后归因、再验证”，关键是把异常现象映射到具体组件与执行路径。必须统一日志规范与留存策略，确保时间戳、线程/会话ID、SQL指纹、主机与实例标识可跨组件关联。

日志与指标关联原理草图：

一、日志启用与规范（安装/配置示例）#

适用于 MySQL 8.x，配置路径 /etc/my.cnf，重启后生效。

[mysqld]
# 错误日志
log_error=/var/log/mysql/error.log

# 慢查询日志
slow_query_log=ON
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1
log_queries_not_using_indexes=ON

# 通用查询日志（建议仅排障时短期开启）
general_log=OFF
general_log_file=/var/log/mysql/general.log

# 二进制日志
log_bin=/var/lib/mysql/mysql-bin
binlog_format=ROW
server_id=101

重启并验证：

systemctl restart mysqld
mysql -uroot -p -e "SHOW VARIABLES LIKE 'slow_query_log%';"
mysql -uroot -p -e "SHOW VARIABLES LIKE 'log_error';"

预期输出：slow_query_log=ON，log_error=/var/log/mysql/error.log。

日志留存与权限：

mkdir -p /var/log/mysql
chown -R mysql:mysql /var/log/mysql
chmod 750 /var/log/mysql

二、关键日志字段与快速检索#

错误日志中常见关键词：InnoDB:, Deadlock, Replica, Access denied, Can't open file.

快速检索示例：

# 最近1小时关键错误
grep -E "ERROR|InnoDB|Deadlock|Replica" /var/log/mysql/error.log | tail -n 50

# 按时间窗过滤（需要日志带有时间戳）
awk '$1" "$2 >= "2024-08-01 10:00:00" && $1" "$2 <= "2024-08-01 11:00:00"' \
/var/log/mysql/error.log

慢查询日志定位高频SQL：

# 统计最慢/最频繁SQL指纹
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_report.txt
less /tmp/slow_report.txt

Binlog定位误操作：

# 解析指定时间窗binlog
mysqlbinlog --start-datetime="2024-08-01 10:00:00" \
            --stop-datetime="2024-08-01 10:30:00" \
            /var/lib/mysql/mysql-bin.000123 | head -n 50

三、典型场景排错（命令+步骤+预期）#

1）实例无法启动#

排查步骤：

# 查看错误日志
tail -n 100 /var/log/mysql/error.log

# 验证配置项
mysqld --verbose --help | grep -E "log_error|datadir|port"

# 检查端口占用
ss -lntp | grep 3306

# 检查数据目录权限
ls -ld /var/lib/mysql

常见原因：InnoDB: corrupt、端口占用、权限不足、参数拼写错误。
预期：修复后 systemctl start mysqld 成功且 error.log 无新错误。

2）性能突降#

流程：慢日志 → SQL指纹 → 执行计划 → 索引与统计信息

# 查最慢SQL
pt-query-digest /var/log/mysql/slow.log | head -n 50

# 获取执行计划
mysql -uroot -p -e "EXPLAIN FORMAT=JSON SELECT ...\G"

必要时更新统计信息：

ANALYZE TABLE db.table;

3）主从复制中断#

mysql -uroot -p -e "SHOW SLAVE STATUS\G"

关注字段：Last_IO_Error, Last_SQL_Error, Seconds_Behind_Master。
若报主键冲突，可跳过或重建：

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

预期：Slave_IO_Running=Yes 且 Slave_SQL_Running=Yes。

4）死锁与锁等待#

错误日志中 Deadlock found 片段：

grep -n "Deadlock found" -n /var/log/mysql/error.log

抓取当前锁：

SHOW ENGINE INNODB STATUS\G;

优化建议：统一访问顺序、补充索引、缩小事务范围。

四、日志集中化（示例：rsyslog转发）#

# /etc/rsyslog.d/mysql.conf
$FileCreateMode 0640
$InputFileName /var/log/mysql/error.log
$InputFileTag mysql-error:
$InputFileStateFile mysql-error-state
$InputFileSeverity error
$InputFileFacility local0
$InputRunFileMonitor

# 转发到日志平台
local0.*  @@logserver.example.com:514