MySQL 故障排查信息收集

故障排查信息收集原则

全面性：收集与故障相关的所有可能信息
准确性：确保收集的信息真实反映系统状态
及时性：在故障发生时尽快收集信息
系统性：按照一定的逻辑顺序和分类收集信息
安全性：在收集信息过程中避免对系统造成进一步影响

系统层面信息

1. 操作系统信息

基本信息

bash

# 操作系统版本
cat /etc/os-release

# 内核版本
uname -a

# 主机名和IP地址
hostname
ifconfig -a  # 或 ip addr

# 系统负载
uptime

资源使用情况

bash

# CPU使用情况
top -b -n 1

# 内存使用情况
free -m

# 磁盘使用情况
df -h

# 磁盘I/O性能
iostat -x 1 5

# 网络连接状态
netstat -tuln
netstat -an | grep ESTABLISHED | wc -l

系统日志

bash

# 系统日志
tail -n 200 /var/log/messages

# 安全日志
tail -n 200 /var/log/secure

# 内核日志
dmesg | tail -n 100

2. 硬件信息

服务器硬件

bash

# CPU信息
lscpu

# 内存信息
dmidecode -t memory

# 磁盘信息
fdisk -l

# RAID状态（如果适用）
cat /proc/mdstat

存储子系统

bash

# 磁盘性能测试
iotop -b -n 1

# 存储设备信息
lsblk

# 文件系统类型
mount | grep -E '(ext|xfs|btrfs)'

MySQL 数据库信息

1. 实例状态

基本状态

bash

# MySQL进程状态
ps aux | grep mysql

# MySQL端口监听
netstat -tuln | grep 3306

# MySQL服务状态
systemctl status mysql  # 或 service mysql status

连接状态

sql

-- 查看当前连接数
SHOW GLOBAL STATUS LIKE 'Threads%';

-- 查看连接详情
SHOW PROCESSLIST;

-- 查看连接状态统计
SHOW GLOBAL STATUS LIKE 'Connection%';

2. 配置信息

配置文件

bash

# 查看MySQL配置文件位置
mysql --help | grep my.cnf

# 查看配置文件内容
cat /etc/my.cnf
cat /etc/mysql/my.cnf

运行时配置

sql

-- 查看所有运行时参数
SHOW GLOBAL VARIABLES;

-- 查看关键参数
SHOW GLOBAL VARIABLES LIKE '%buffer%';
SHOW GLOBAL VARIABLES LIKE '%cache%';
SHOW GLOBAL VARIABLES LIKE '%log%';
SHOW GLOBAL VARIABLES LIKE '%innodb%';

3. 性能指标

状态变量

sql

-- 查看关键状态变量
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Innodb_row%';
SHOW GLOBAL STATUS LIKE 'Com_%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

性能模式

sql

-- 启用性能模式（如果未启用）
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events%';

-- 查看慢查询
SELECT * FROM performance_schema.events_statements_history_long WHERE TIMER_WAIT > 10000000000 ORDER BY TIMER_WAIT DESC LIMIT 10;

-- 查看锁等待
SELECT * FROM performance_schema.data_locks ORDER BY ENGINE_LOCK_ID;

Sys Schema

sql

-- 查看慢查询
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile ORDER BY avg_timer_wait DESC LIMIT 10;

-- 查看表访问统计
SELECT * FROM sys.schema_table_statistics ORDER BY rows_changed DESC LIMIT 10;

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

日志信息

1. 错误日志

查找错误日志位置

sql

-- 查看错误日志位置
SHOW GLOBAL VARIABLES LIKE 'log_error';

分析错误日志

bash

# 查看错误日志最后200行
tail -n 200 /var/log/mysql/error.log

# 搜索错误信息
grep -i "error" /var/log/mysql/error.log | tail -n 100
grep -i "warning" /var/log/mysql/error.log | tail -n 100
grep -i "critical" /var/log/mysql/error.log | tail -n 100
grep -i "crash" /var/log/mysql/error.log | tail -n 100
grep -i "innodb" /var/log/mysql/error.log | tail -n 100

2. 慢查询日志

检查慢查询日志配置

sql

-- 查看慢查询日志配置
SHOW GLOBAL VARIABLES LIKE '%slow%';

分析慢查询日志

bash

# 查看慢查询日志内容
tail -n 200 /var/lib/mysql/slow-query.log

# 使用mysqldumpslow分析
mysqldumpslow -s t -t 10 /var/lib/mysql/slow-query.log

# 使用pt-query-digest分析（Percona Toolkit）
pt-query-digest /var/lib/mysql/slow-query.log | head -n 100

3. 二进制日志

检查二进制日志配置

sql

-- 查看二进制日志配置
SHOW GLOBAL VARIABLES LIKE '%binlog%';

-- 查看二进制日志文件
SHOW BINARY LOGS;

分析二进制日志

bash

# 查看二进制日志内容
mysqlbinlog /var/lib/mysql/binlog.000001 | head -n 100

# 查看特定时间范围的二进制日志
mysqlbinlog --start-datetime="2023-01-01 00:00:00" --stop-datetime="2023-01-01 23:59:59" /var/lib/mysql/binlog.000001

4. 通用查询日志

检查通用查询日志配置

sql

-- 查看通用查询日志配置
SHOW GLOBAL VARIABLES LIKE '%general%';

分析通用查询日志

bash

# 查看通用查询日志内容
tail -n 200 /var/lib/mysql/general-query.log

数据库结构信息

1. 数据库和表

sql

-- 查看所有数据库
SHOW DATABASES;

-- 查看数据库大小
SELECT table_schema AS 'Database', 
       SUM(data_length + index_length) / 1024 / 1024 AS 'Size (MB)'
FROM information_schema.tables 
GROUP BY table_schema;

-- 查看表结构
SHOW CREATE TABLE db_name.table_name;

-- 查看表状态
SHOW TABLE STATUS FROM db_name LIKE 'table_name';

2. 索引信息

sql

-- 查看表索引
SHOW INDEX FROM db_name.table_name;

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引统计
SELECT * FROM information_schema.statistics WHERE table_schema = 'db_name' AND table_name = 'table_name';

3. 存储引擎信息

sql

-- 查看表存储引擎
SELECT table_schema, table_name, engine 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

-- 查看InnoDB表空间
SELECT * FROM information_schema.innodb_sys_tablespaces;

-- 查看InnoDB缓冲池状态
SHOW ENGINE INNODB STATUS\G

性能问题信息

1. 查询性能

sql

-- 查看执行中的查询
SHOW PROCESSLIST;

-- 查看长时间运行的查询
SELECT id, user, host, db, command, time, state, info 
FROM information_schema.processlist 
WHERE time > 60 
ORDER BY time DESC;

-- 查看查询执行计划
EXPLAIN SELECT * FROM db_name.table_name WHERE condition;

2. 锁和死锁

sql

-- 查看锁等待
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

-- 查看InnoDB锁信息
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;

-- 查看死锁日志
SHOW ENGINE INNODB STATUS\G

3. 复制状态

sql

-- 查看复制状态
SHOW SLAVE STATUS\G;

-- 查看主库二进制日志状态
SHOW MASTER STATUS;

-- 查看复制连接状态
SHOW GLOBAL STATUS LIKE 'Slave%';

自动化信息收集

1. 脚本自动化

基础信息收集脚本

bash

#!/bin/bash

# 定义输出目录
OUTPUT_DIR="/tmp/mysql_diagnostic_$(date +%Y%m%d_%H%M%S)"
mkdir -p $OUTPUT_DIR

# 系统信息
echo "=== 系统信息 ===" > $OUTPUT_DIR/system_info.txt
uname -a >> $OUTPUT_DIR/system_info.txt
cat /etc/os-release >> $OUTPUT_DIR/system_info.txt
uptime >> $OUTPUT_DIR/system_info.txt

# 资源使用情况
echo "=== 资源使用情况 ===" > $OUTPUT_DIR/resources.txt
free -m >> $OUTPUT_DIR/resources.txt
df -h >> $OUTPUT_DIR/resources.txt
top -b -n 1 >> $OUTPUT_DIR/resources.txt
iostat -x 1 3 >> $OUTPUT_DIR/resources.txt

# MySQL状态信息
echo "=== MySQL状态信息 ===" > $OUTPUT_DIR/mysql_status.txt
mysql -u root -p -e "SHOW GLOBAL STATUS;" >> $OUTPUT_DIR/mysql_status.txt

# MySQL变量信息
echo "=== MySQL变量信息 ===" > $OUTPUT_DIR/mysql_variables.txt
mysql -u root -p -e "SHOW GLOBAL VARIABLES;" >> $OUTPUT_DIR/mysql_variables.txt

# MySQL进程信息
echo "=== MySQL进程信息 ===" > $OUTPUT_DIR/mysql_processlist.txt
mysql -u root -p -e "SHOW PROCESSLIST;" >> $OUTPUT_DIR/mysql_processlist.txt

# MySQL错误日志
echo "=== MySQL错误日志 ===" > $OUTPUT_DIR/mysql_error.log
ERROR_LOG=$(mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'log_error';" | grep log_error | awk '{print $2}')
tail -n 200 $ERROR_LOG >> $OUTPUT_DIR/mysql_error.log

# 打包输出
cd /tmp
tar -czf mysql_diagnostic_$(date +%Y%m%d_%H%M%S).tar.gz mysql_diagnostic_$(date +%Y%m%d_%H%M%S)
echo "诊断信息已保存到: /tmp/mysql_diagnostic_$(date +%Y%m%d_%H%M%S).tar.gz"

2. Percona Toolkit 工具

pt-mysql-summary

bash

# 安装Percona Toolkit
yum install percona-toolkit  # 或 apt-get install percona-toolkit

# 生成MySQL摘要报告
pt-mysql-summary --user=root --password=your_password > mysql_summary.txt

pt-stalk

bash

# 监控并收集性能问题信息
pt-stalk --function=status --variable=Threads_connected --threshold=100 --user=root --password=your_password

信息分析与故障定位

1. 分析方法

日志分析

错误日志：重点关注错误、警告和崩溃信息
慢查询日志：识别性能瓶颈和低效查询
二进制日志：分析数据变更和复制问题

性能分析

状态变量：监控关键性能指标的变化
执行计划：分析查询执行效率
锁分析：识别锁竞争和死锁问题

系统分析

资源使用：检查CPU、内存、磁盘和网络使用情况
系统日志：查找系统级别的问题
硬件状态：检查硬件故障和性能问题

2. 常见故障模式

连接问题

症状：连接拒绝、连接超时、连接数满
信息收集：网络状态、MySQL连接参数、防火墙设置
分析重点：max_connections参数、网络配置、连接池设置

性能问题

症状：查询缓慢、系统负载高、响应时间长
信息收集：慢查询日志、执行计划、系统资源使用
分析重点：索引使用、查询优化、系统资源瓶颈

复制问题

症状：复制延迟、复制中断、数据不一致
信息收集：复制状态、二进制日志、错误日志
分析重点：网络延迟、主从配置、SQL语句兼容性

崩溃问题

症状：MySQL进程崩溃、服务无法启动
信息收集：错误日志、系统日志、核心转储
分析重点：内存问题、磁盘空间、硬件故障、Bug

最佳实践

1. 信息收集规范

建立标准化流程：制定统一的信息收集步骤和模板
定期收集基线数据：在系统正常时收集基准信息，便于故障时对比
自动化收集：使用脚本和工具自动化信息收集过程
存储历史数据：保存历史信息，便于趋势分析和问题追踪

2. 工具使用建议

Percona Toolkit：使用专业工具提高信息收集效率
监控系统：配置监控系统自动收集和分析信息
日志管理：使用日志管理系统集中存储和分析日志
性能分析工具：使用专业性能分析工具深入分析性能问题

3. 信息安全

敏感信息保护：在收集和共享信息时，避免包含密码等敏感信息
权限控制：使用最小权限原则执行信息收集操作
数据脱敏：在分享诊断信息时，对敏感数据进行脱敏处理
安全传输：使用安全方式传输诊断信息

4. 文档管理

记录收集过程：详细记录信息收集的时间、方法和结果
整理分析报告：将收集的信息整理成结构化的分析报告
建立知识库：将常见问题的诊断信息和解决方案建立知识库
定期更新：根据MySQL版本和环境变化，更新信息收集方法

常见问题（FAQ）

Q1: 信息收集过程中如何避免对系统造成进一步影响？

A1: 信息收集的安全措施：

使用只读查询和命令
避免在高负载时执行资源密集型操作
限制查询结果集大小
使用后台执行方式
合理设置采样频率

Q2: 如何快速定位性能问题的根源？

A2: 性能问题快速定位方法：

检查慢查询日志，识别耗时最长的查询
分析执行计划，查看是否使用了索引
监控系统资源使用情况，识别瓶颈
检查MySQL状态变量，寻找异常指标
使用性能分析工具进行深入分析

Q3: 复制问题的信息收集重点是什么？

A3: 复制问题信息收集重点：

SHOW SLAVE STATUS输出
主库和从库的错误日志
主库的二进制日志状态
网络连接状态和延迟
主从配置参数对比

Q4: 如何收集InnoDB相关的问题信息？

A4: InnoDB问题信息收集：

SHOW ENGINE INNODB STATUS输出
InnoDB相关的状态变量
InnoDB表空间信息
InnoDB缓冲池状态
InnoDB错误日志信息

Q5: 信息收集后如何有效分析和分享？

A5: 信息分析和分享建议：

按照逻辑顺序整理信息
突出显示关键指标和异常值
使用图表可视化性能数据
提供详细的分析结论和建议
对敏感信息进行脱敏处理

Q6: 如何建立信息收集的自动化机制？

A6: 自动化信息收集方案：

使用脚本定期收集系统和MySQL信息
配置监控系统自动收集和分析指标
建立故障触发的信息收集机制
使用容器化工具统一信息收集环境
实现信息的自动分类和存储

Q7: 不同MySQL版本的信息收集有何差异？

A7: 版本差异处理：

了解不同版本的状态变量和配置参数差异
使用与版本匹配的工具和命令
关注新版本的特性和改进
参考官方文档的版本特定信息

Q8: 如何处理海量日志的分析？

A8: 海量日志分析策略：

使用日志分析工具（如ELK Stack）
设置合理的日志轮转和保留策略
重点关注关键时间段的日志
使用过滤和搜索功能定位问题
考虑使用分布式日志处理方案

Q9: 信息收集过程中遇到权限不足怎么办？

A9: 权限问题处理：

使用具有适当权限的用户
申请临时提升权限
使用只读权限收集基本信息
与系统管理员协作收集系统级信息

Q10: 如何验证收集的信息准确性？

A10: 信息验证方法：

多次收集对比结果一致性
与系统监控数据交叉验证
使用不同工具收集同一信息
参考历史数据和基准值
请同事协助验证分析结果

MySQL 故障排查信息收集 ​

故障排查信息收集原则 ​

系统层面信息 ​

1. 操作系统信息 ​

基本信息 ​

资源使用情况 ​

系统日志 ​

2. 硬件信息 ​

服务器硬件 ​

存储子系统 ​

MySQL 数据库信息 ​

1. 实例状态 ​

基本状态 ​

连接状态 ​

2. 配置信息 ​

配置文件 ​

运行时配置 ​

3. 性能指标 ​

状态变量 ​

性能模式 ​

Sys Schema ​

日志信息 ​

1. 错误日志 ​

查找错误日志位置 ​

分析错误日志 ​

2. 慢查询日志 ​

检查慢查询日志配置 ​

分析慢查询日志 ​

3. 二进制日志 ​

检查二进制日志配置 ​

分析二进制日志 ​

4. 通用查询日志 ​

检查通用查询日志配置 ​

分析通用查询日志 ​

数据库结构信息 ​

1. 数据库和表 ​

2. 索引信息 ​

3. 存储引擎信息 ​

性能问题信息 ​

1. 查询性能 ​

2. 锁和死锁 ​

3. 复制状态 ​

自动化信息收集 ​

1. 脚本自动化 ​

基础信息收集脚本 ​

2. Percona Toolkit 工具 ​

pt-mysql-summary ​

pt-stalk ​

信息分析与故障定位 ​

1. 分析方法 ​

日志分析 ​

性能分析 ​

系统分析 ​

MySQL 故障排查信息收集

故障排查信息收集原则

系统层面信息

1. 操作系统信息

基本信息

资源使用情况

系统日志

2. 硬件信息

服务器硬件

存储子系统

MySQL 数据库信息

1. 实例状态

基本状态

连接状态

2. 配置信息

配置文件

运行时配置

3. 性能指标

状态变量

性能模式

Sys Schema

日志信息

1. 错误日志

查找错误日志位置

分析错误日志

2. 慢查询日志

检查慢查询日志配置

分析慢查询日志

3. 二进制日志

检查二进制日志配置

分析二进制日志

4. 通用查询日志

检查通用查询日志配置

分析通用查询日志

数据库结构信息

1. 数据库和表

2. 索引信息

3. 存储引擎信息

性能问题信息

1. 查询性能

2. 锁和死锁

3. 复制状态

自动化信息收集

1. 脚本自动化

基础信息收集脚本

2. Percona Toolkit 工具

pt-mysql-summary

pt-stalk

信息分析与故障定位

1. 分析方法

日志分析

性能分析

系统分析