印刻数据库

外观

MySQL 故障诊断命令详解 ​

连接状态与进程管理命令 ​

SHOW PROCESSLIST ​

查看当前MySQL服务器上的所有连接和进程信息，是故障诊断的基础命令。

sql
-- 查看所有进程
SHOW PROCESSLIST;

-- 查看完整SQL语句（MySQL 5.7+）
SHOW FULL PROCESSLIST;

-- 筛选特定用户的连接
SELECT * FROM information_schema.processlist WHERE user = 'app_user';

版本差异：

  • MySQL 5.7+ 中，information_schema.processlist 提供了更丰富的筛选能力
  • MySQL 8.0 中，引入了 performance_schema.threads 表，提供更详细的线程信息

KILL ​

终止有问题的连接或查询进程。

sql
-- 终止指定ID的进程
KILL 123;

-- 强制终止（MySQL 5.7+）
KILL QUERY 123;

注意事项：

  • 谨慎使用KILL命令，可能导致事务回滚和数据不一致
  • 优先终止查询而非连接，减少对应用的影响

日志查看命令 ​

SHOW BINARY LOGS ​

查看二进制日志列表，用于故障恢复和复制问题诊断。

sql
SHOW BINARY LOGS;

-- MySQL 8.0+ 查看当前使用的二进制日志
SHOW MASTER STATUS;

SHOW ERROR LOGS ​

MySQL 5.7+ 新增的命令，用于查看错误日志列表。

sql
SHOW ERROR LOGS;

查询慢查询日志配置 ​

sql
-- 查看慢查询日志状态
SHOW VARIABLES LIKE '%slow_query%';

-- 查看慢查询阈值
SHOW VARIABLES LIKE 'long_query_time';

版本差异：

  • MySQL 5.6 中，默认关闭慢查询日志
  • MySQL 5.7+ 默认开启慢查询日志（long_query_time=10秒）
  • MySQL 8.0 增加了更多慢查询日志参数，如 log_slow_extra

存储引擎状态命令 ​

SHOW ENGINE INNODB STATUS ​

查看InnoDB存储引擎的详细状态信息，包括事务、锁、缓冲池等。

sql
SHOW ENGINE INNODB STATUS;

输出解读：

  • TRANSACTIONS：当前事务状态
  • SEMAPHORES：信号量等待情况
  • LOG：日志写入状态
  • BUFFER POOL AND MEMORY：缓冲池使用情况
  • ROW OPERATIONS：行操作统计

SHOW TABLE STATUS ​

查看表的状态信息，包括存储引擎、行数、碎片等。

sql
-- 查看特定表状态
SHOW TABLE STATUS LIKE 'users';

-- 查看所有表状态
SHOW TABLE STATUS;

性能监控命令 ​

SHOW GLOBAL STATUS ​

查看MySQL服务器的全局状态变量，用于性能监控和故障诊断。

sql
-- 查看连接相关状态
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Connections';

-- 查看查询相关状态
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Queries';

-- 查看InnoDB相关状态
SHOW GLOBAL STATUS LIKE 'Innodb_%';

常用状态变量：

  • Threads_connected：当前连接数
  • Threads_running：当前运行中的连接数
  • Questions：客户端执行的查询数
  • Innodb_buffer_pool_hit_rate：缓冲池命中率

SHOW GLOBAL VARIABLES ​

查看MySQL服务器的全局配置变量。

sql
-- 查看内存相关配置
SHOW GLOBAL VARIABLES LIKE '%buffer%';
SHOW GLOBAL VARIABLES LIKE '%cache%';

-- 查看连接相关配置
SHOW GLOBAL VARIABLES LIKE 'max_connections';

锁状态查询命令 ​

查看InnoDB锁信息 ​

sql
-- MySQL 5.6+ 查看锁等待信息
SELECT * FROM information_schema.innodb_lock_waits;
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_trx;

-- MySQL 8.0+ 推荐使用 performance_schema 视图
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

查看表锁信息 ​

sql
SHOW OPEN TABLES WHERE In_use > 0;

复制状态命令 ​

主从复制状态查询 ​

sql
-- 在从库上查看复制状态
SHOW SLAVE STATUS\G;

-- 在主库上查看复制连接
SHOW SLAVE HOSTS;

关键字段解读：

  • Slave_IO_Running：IO线程状态
  • Slave_SQL_Running：SQL线程状态
  • Seconds_Behind_Master：从库延迟时间
  • Last_Error：最近的错误信息

磁盘与存储相关命令 ​

查看表空间使用情况 ​

sql
-- MySQL 5.7+ 查看表空间使用
SELECT table_schema, table_name, data_length, index_length, data_free 
FROM information_schema.tables 
WHERE table_schema = 'your_database';

-- MySQL 8.0+ 查看共享表空间
SELECT * FROM information_schema.innodb_tablespaces;

查看临时表使用情况 ​

sql
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';

日志分析命令 ​

mysqlbinlog ​

用于查看和解析二进制日志，是复制故障和数据恢复的重要工具。

bash
# 查看二进制日志内容
mysqlbinlog mysql-bin.000001

# 按时间范围过滤
mysqlbinlog --start-datetime='2023-01-01 10:00:00' --stop-datetime='2023-01-01 11:00:00' mysql-bin.000001

# 按位置范围过滤
mysqlbinlog --start-position=107 --stop-position=1000 mysql-bin.000001

版本差异：

  • MySQL 5.7+ 中，mysqlbinlog 支持 --json 选项，输出JSON格式
  • MySQL 8.0 中，增强了对加密二进制日志的支持

mysqldumpslow ​

分析慢查询日志，识别性能瓶颈。

bash
# 查看慢查询摘要
mysqldumpslow slow.log

# 按查询次数排序
mysqldumpslow -s c slow.log

# 按锁定时间排序
mysqldumpslow -s l slow.log

系统状态命令 ​

查看MySQL服务器状态 ​

bash
# 检查MySQL服务是否运行
systemctl status mysql  #  systemd系统
/etc/init.d/mysql status  # SysV系统

# 查看MySQL端口监听情况
netstat -tlnp | grep mysql
ss -tlnp | grep mysql

查看磁盘空间使用 ​

bash
# 查看MySQL数据目录磁盘空间
df -h /var/lib/mysql

# 查看具体文件大小
du -sh /var/lib/mysql/*

故障诊断命令组合 ​

快速检查MySQL健康状态 ​

sql
-- 1. 检查连接数
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- 2. 检查运行中的查询
SHOW FULL PROCESSLIST;

-- 3. 检查InnoDB状态
SHOW ENGINE INNODB STATUS\G;

-- 4. 检查锁等待
SELECT * FROM information_schema.innodb_lock_waits;

-- 5. 检查复制状态（从库）
SHOW SLAVE STATUS\G;

性能问题诊断流程 ​

  1. 使用 SHOW FULL PROCESSLIST 查看是否有慢查询或阻塞
  2. 使用 SHOW GLOBAL STATUS 分析关键性能指标
  3. 查看慢查询日志，定位具体慢SQL
  4. 使用 EXPLAIN 分析慢SQL执行计划
  5. 检查 SHOW ENGINE INNODB STATUS 中的缓冲池和锁信息

常见问题（FAQ） ​

Q: SHOW PROCESSLIST 显示大量 Sleep 状态的连接，正常吗？ ​

A: Sleep 状态表示连接处于空闲状态。如果数量过多，可能是连接池配置不当或应用未正确关闭连接。建议：

  • 检查应用连接池配置，调整最大连接数和空闲超时时间
  • 启用 MySQL 的 wait_timeout 和 interactive_timeout 参数
  • 定期监控连接状态，及时清理无效连接

Q: 如何快速定位导致锁等待的查询？ ​

A: 可以通过以下组合查询定位：

sql
SELECT 
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Q: MySQL 8.0 中，为什么有些命令输出与之前版本不同？ ​

A: MySQL 8.0 对很多系统表和状态变量进行了重构，特别是将很多 information_schema 表的数据来源切换到 performance_schema，提供了更准确和详细的信息。建议：

  • 熟悉 performance_schema 相关表的使用
  • 参考官方文档了解版本间的命令差异
  • 逐步迁移到新版本推荐的命令和视图

Q: 使用 mysqlbinlog 查看二进制日志时，出现 "unknown variable 'default-character-set=utf8'" 错误怎么办？ ​

A: 这是因为 mysqlbinlog 工具不识别 my.cnf 中的某些客户端选项。解决方案：

  • 在 mysqlbinlog 命令后加上 --no-defaults 选项
  • 或修改 my.cnf，将字符集配置放在 [mysqld] 部分而非 [client] 部分

Q: 如何监控 MySQL 的实时性能指标？ ​

A: 除了手动执行命令外，还可以：

  • 使用 MySQL 自带的 Performance Schema 和 Sys Schema
  • 部署第三方监控工具，如 Prometheus + Grafana、Zabbix 等
  • 使用 MySQL Enterprise Monitor 等商业监控解决方案

Q: SHOW ENGINE INNODB STATUS 输出中，"BUFFER POOL AND MEMORY" 部分的 "Hit rate" 很低，怎么办？ ​

A: 缓冲池命中率低通常表示内存不足，建议：

  • 增加 innodb_buffer_pool_size 参数值（如果服务器内存充足）
  • 优化查询，减少全表扫描
  • 考虑使用 SSD 存储，减少磁盘 I/O 压力
  • 检查是否有大量临时表或大表扫描操作

Q: 如何查看 MySQL 服务器的负载情况？ ​

A: 可以通过以下方式：

  • 使用 SHOW GLOBAL STATUS LIKE 'Threads_running' 查看当前活跃线程数
  • 监控服务器 CPU、内存和磁盘 I/O 使用率
  • 查看 SHOW GLOBAL STATUS LIKE 'Queries' 了解查询吞吐量
  • 使用 vmstat、iostat 等系统命令查看系统级负载

Q: 复制故障时，如何快速定位问题？ ​

A: 复制故障诊断步骤：

  1. 在从库执行 SHOW SLAVE STATUS\G，查看错误信息
  2. 检查主库二进制日志是否存在且完整
  3. 验证从库复制账号权限
  4. 检查网络连接和防火墙设置
  5. 查看主从库的服务器 ID 是否冲突
  6. 检查主从库的 MySQL 版本兼容性

Q: 如何识别 MySQL 中的死锁？ ​

A: 可以通过以下方式：

  • 查看 SHOW ENGINE INNODB STATUS 中的 LATEST DETECTED DEADLOCK 部分
  • 启用 innodb_print_all_deadlocks 参数，将所有死锁信息记录到错误日志
  • 使用 information_schema.innodb_lock_waits 表监控锁等待情况

Q: 执行 KILL 命令后，进程为什么还在？ ​

A: KILL 命令只是向目标线程发送一个终止信号，线程需要完成当前操作后才能终止。如果线程正在执行长时间的事务回滚或磁盘 I/O 操作，可能需要较长时间才能终止。建议：

  • 耐心等待一段时间，不要重复执行 KILL 命令
  • 监控进程状态变化
  • 如长时间无响应，可考虑重启 MySQL 服务（仅作为最后的手段）