MySQL 常用命令与巡检:从登录到定性的一份清单

登录上去之后最常见的情况是:知道有几个命令,但不知道先敲哪个。

这一篇按「回答什么问题」来分组,不按字母表。每一组都写清三件事:命令是什么、它回答什么、读到什么数值该警觉。

所有命令都在 192.168.101.7:3306(MySQL 8.4.11)上实际跑过,数字是真的。

先登录

# 最常用:交互式,回车后输密码
mysql -h192.168.101.7 -P3306 -udba -p

# 直接进某个库,省一次 USE
mysql -h192.168.101.7 -P3306 -udba -p -D lab

# 不登录,跑一条就退出(写脚本、做监控都靠这个)
mysql -h192.168.101.7 -P3306 -udba -p -N -e "SELECT VERSION();"

# 本机 socket 登录(3306 上的 root 走这个)
mysql -uroot -p

-N 去掉表头,-B 用 tab 分隔,-e 直接执行,-E 等价于 SQL 末尾加 \G。这四个参数配齐,一行命令就能塞进 shell 脚本。

连不上先分清是哪种「连不上」:ERROR 1045 是账号密码或来源不对;ERROR 2003 是网络或端口不通;ERROR 1049 是库名不存在。三种错对应三个完全不同的排查方向。

按症状找命令

真出问题时不用从头读,直接查这张表:

现象 先跑哪一段 关键输出
连不上 / 权限报错 §5 账号 mysql.user 的 host 列、SHOW GRANTS
磁盘一直在涨 §2 库表体积 data_length、data_free(碎片)
突然变慢、改不动 §7 连接、§9 锁 processlist 的 State、innodb_trx、data_lock_waits
单条 SQL 慢 §12 执行计划 type、key、rows vs actual rows
参数改过又变回去了 §11 参数来源 VARIABLE_SOURCE = DYNAMIC
主从断了 §10 日志 SHOW REPLICA STATUS 的 Last_Error
不知道连的是哪个实例 §0 我在哪儿 @@port、@@datadir

先认清「看的东西」放在哪:四个系统库

这是本篇的地基。MySQL 把「关于自己的信息」也存成表,分散在四个库里。不认清这四个库,命令就只能靠背。

库 装什么 性质 什么时候查
information_schema 标准元数据:有哪些库表、字段类型、索引、权限、当前连接、事务、锁 大部分是视图,查它可能触发 IO 想知道「结构长什么样」
performance_schema 运行时计数:等待事件、锁等待、语句统计、参数来源、内存分配 内存表,开销可控 想知道「刚才发生了什么」
mysql 账号、权限、时区、插件、日志开关 真实表,可直接改(不建议手改) 想知道「谁能连、能干什么」
sys 把上面两个包装成人能读的视图 视图,底层还是前两个 想少写 join 就拿到结论

两个最常用的 sys 视图,记住能省很多 SQL:

-- 谁在堵谁,一条查清(本篇 §9 会用到)
SELECT * FROM sys.innodb_lock_waits\G

-- 哪些语句最耗时间(需要 statements_digest 打开)
SELECT * FROM sys.statement_analysis LIMIT 10;

sys 不是「额外功能」,它就是官方替你写好的 join。


§0 我在哪儿:先确认实例,再谈别的

一台机器上跑着 3306、3307、5432、5433 四个实例的时候,连错实例是最常见的事故来源。动手之前先跑这一段,五秒钟。

SELECT VERSION()               AS `版本`,
       @@hostname              AS `主机名`,
       @@port                  AS `端口`,
       @@datadir               AS `数据目录`,
       USER()                  AS `你声称的身份`,
       CURRENT_USER()          AS `服务端认定身份`,
       @@character_set_server  AS `服务端字符集`,
       @@collation_server      AS `排序规则`,
       @@time_zone             AS `时区`,
       @@read_only             AS `是否只读`
\G

四个字段值得单独说:

  • @@port + @@datadir:这两个一起看,才能确定「我改的是哪个实例的哪份数据」。
  • USER() vs CURRENT_USER():前者是你声称的账号,后者是服务端实际匹配到的账号。两个不一样,说明权限是按另一个身份给的——排查「为什么我权限不够」时,答案通常就在这个差里。
  • @@read_only:在从库上误操作写数据,就靠这个字段提前发现。
  • @@sql_mode:决定严格模式开没开。没开时插入超长字符串会被静默截断,不报错——这是数据悄悄变脏的常见原因。
SELECT @@sql_mode\G

§1 有哪些库、各占多大

不用逐个 USE 进去看,information_schema.tables 就是所有表的元数据总表。

SELECT table_schema AS `库名`,
       COUNT(*)     AS `表数量`,
       ROUND(SUM(data_length)  / 1024 / 1024, 1) AS `数据MB`,
       ROUND(SUM(index_length) / 1024 / 1024, 1) AS `索引MB`,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS `合计MB`
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

单位是字节,所以要除两次 1024 才是 MB。看到某个库的 索引MB 比 数据MB 还大,多半是索引建多了——索引不是免费的,它在磁盘上和 Buffer Pool 里都占位置。

§2 哪张表在占空间、有没有碎片

SELECT table_name                          AS `表`,
       engine                              AS `引擎`,
       table_rows                          AS `估算行数`,
       ROUND(data_length  / 1024 / 1024, 1) AS `数据MB`,
       ROUND(index_length / 1024 / 1024, 1) AS `索引MB`,
       ROUND(data_free    / 1024 / 1024, 1) AS `碎片MB`,
       ROUND(avg_row_length, 0)            AS `平均行字节`,
       table_comment                       AS `注释`
FROM information_schema.tables
WHERE table_schema = 'lab'
ORDER BY data_length DESC;

这一段有两个必须知道的坑:

坑一:table_rows 是估算值。 InnoDB 不会为了告诉你行数去数一遍,它拿的是采样估算。lab.emp 实际 5 万行,table_rows 通常显示 4.9 万上下。要对数必须用 COUNT(*):

SELECT COUNT(*) FROM lab.emp;

坑二:data_free 大于 0 说明有碎片。 大量 DELETE 之后,页空出来了但没还给操作系统,表文件不会变小。看到碎片大了,回收方式是 OPTIMIZE TABLE(本质是重建表,线上要评估 IO 和锁)。

§3 一张表的结构:三件套

看表结构永远只用这三条,按顺序:

DESC lab.emp;                  -- 字段:类型、是否 NULL、默认值、key
SHOW INDEX FROM lab.emp;       -- 索引:哪几列、顺序、唯一性、cardinality
SHOW CREATE TABLE lab.emp\G    -- 全部:引擎、字符集、排序规则、外键、分区

必须用 SHOW CREATE TABLE 的场合:想知道引擎(InnoDB 还是 MyISAM)、字符集(utf8mb4 还是 latin1)、外键约束名、分区定义。这些 DESC 都看不到。

SHOW INDEX 里有三列要会读:

列 含义 怎么用
Seq_in_index 该列在联合索引里排第几 判断最左前缀:查 (dept_id, hired_at) 时只带 hired_at 用不上
Cardinality 这一列有多少个不同值(估算) 算选择度,见 §4
Index_type BTREE / FULLTEXT / HASH 确认索引类型对不对

§4 索引好不好用:算选择度

选择度 = 不同值数 / 总行数。 越接近 1 越能区分,越接近 0 越没用。

SELECT s.table_name AS `表`,
       s.index_name AS `索引`,
       s.seq_in_index AS `第几列`,
       s.column_name AS `列`,
       s.cardinality AS `不同值数`,
       ROUND(s.cardinality / NULLIF(t.table_rows, 0), 5) AS `选择度`
FROM information_schema.statistics s
JOIN information_schema.tables t
  ON t.table_schema = s.table_schema AND t.table_name = s.table_name
WHERE s.table_schema = 'lab'
ORDER BY s.table_name, s.index_name, s.seq_in_index;

lab.emp 的实测结果:

列 不同值数 选择度 结论
id(主键) 50000 1.00000 唯一,最好
name 48206 0.96412 很好
hired_at 2950 0.05900 一般,适合范围查询
dept_id 5 0.00010 单独查没用,扫它不如全表

这张表最值得看的是最后一行。dept_id 选择度 0.0001,单独用 WHERE dept_id = 3 要扫一万行,优化器会直接放弃索引。但把它放在联合索引第一列,(dept_id, hired_at) 就能同时撑住「等值 + 范围」——这就是为什么低选择度的列也能进联合索引,只是不能单独当索引用。

§5 谁能连、有什么权限

SELECT user AS `账号`, host AS `允许来源`, plugin AS `认证插件`, account_locked AS `已锁`
FROM mysql.user ORDER BY user, host;

SHOW GRANTS FOR CURRENT_USER();

host 列决定「从哪来」,它做的是字符串匹配,不是网段计算:

  • dba@192.168.101.% → 从 192.168.101.7 连能匹配上
  • 但 dba@192.168.101.% 匹配不到 127.0.0.1,因为 127.0.0.1 不以 192.168.101. 开头

这就是「远程能连、本机回环连不上」的原因。它不是 bug,是匹配规则。

plugin 列在 8.x 通常是 caching_sha2_password。老客户端连不上,往往是因为它只认 mysql_native_password。

§6 现在谁连着、在跑什么

现场排障第一站。

SELECT id AS `线程ID`, user AS `账号`, host AS `来源`, db AS `库`,
       command AS `命令`, time AS `持续秒`, state AS `状态`,
       LEFT(info, 60) AS `在跑什么`
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;

-- 连接数水位:超过 80% 就该查连接池或连接泄漏
SELECT @@max_connections AS `上限`,
       (SELECT COUNT(*) FROM information_schema.processlist) AS `当前`,
       ROUND((SELECT COUNT(*) FROM information_schema.processlist)
             / @@max_connections * 100, 1) AS `使用率%`;

读法:Command = Sleep 的空闲连接不用管;重点是 Time 很长 + State 停在非正常状态的那种。State 是 Waiting for table metadata lock 说明被 DDL 堵了,Sending data 说明正在扫数据。

§7 一行健康快照:QPS / TPS / 命中率

SELECT CAST(MAX(IF(VARIABLE_NAME='Uptime', VARIABLE_VALUE, 0)) AS UNSIGNED) AS `运行秒数`,
       ROUND(CAST(MAX(IF(VARIABLE_NAME='Questions', VARIABLE_VALUE, 0)) AS UNSIGNED)
             / CAST(MAX(IF(VARIABLE_NAME='Uptime', VARIABLE_VALUE, 1)) AS UNSIGNED), 2) AS `QPS均值`,
       CAST(MAX(IF(VARIABLE_NAME='Threads_running', VARIABLE_VALUE, 0)) AS UNSIGNED) AS `活跃线程`,
       ROUND((1 - CAST(MAX(IF(VARIABLE_NAME='Innodb_buffer_pool_reads', VARIABLE_VALUE, 0)) AS UNSIGNED)
                / NULLIF(CAST(MAX(IF(VARIABLE_NAME='Innodb_buffer_pool_read_requests', VARIABLE_VALUE, 1)) AS UNSIGNED), 0)) * 100, 3) AS `缓冲池命中率%`,
       CAST(MAX(IF(VARIABLE_NAME='Slow_queries', VARIABLE_VALUE, 0)) AS UNSIGNED) AS `慢查询累计`,
       CAST(MAX(IF(VARIABLE_NAME='Handler_read_rnd_next', VARIABLE_VALUE, 0)) AS UNSIGNED) AS `全表扫读次数`
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ('Uptime','Questions','Threads_running',
                        'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',
                        'Slow_queries','Handler_read_rnd_next');

这些值全是开机以来的累计量,所以要除以 Uptime 才是「每秒」。刚启动的实例数字会虚高——Uptime 只有几十秒时,别拿这个 QPS 当基准。

判断标准,记三条就够:

指标 红线 说明
缓冲池命中率 < 99% 内存不够,或有大表全扫在刷缓存
连接使用率 > 80% 查连接池配置,或看有没有 Sleep 几十小时的泄漏连接
Handler_read_rnd_next 增速 持续快速上涨 有 SQL 在走全表扫

§8 InnoDB 状态:120 行里只看三段

SHOW ENGINE INNODB STATUS\G

输出一百多行,第一次看会懵。其实只需要三段:

段 回答什么 看什么
TRANSACTIONS 有没有长事务、锁等待 ACTIVE 秒数很大的事务、LOCK WAIT
BUFFER POOL AND MEMORY 缓存够不够 Buffer pool hit rate、Modified DB pages(脏页比例)
LOG 写入压力 Log sequence number 与 Last checkpoint 的差值

另外:输出最上面如果出现 LATEST DETECTED DEADLOCK,说明刚死锁过,这一段必须逐行看,它会告诉你哪两条 SQL 互相等。

§9 锁与事务:三张表定位阻塞源

-- 谁开着事务没提交(长事务会拖住 purge,撑大 undo)
SELECT trx_id AS `事务ID`, trx_state AS `状态`, trx_started AS `开始时间`,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS `已持续秒`,
       trx_rows_locked AS `锁定行数`, trx_rows_modified AS `修改行数`,
       LEFT(trx_query, 50) AS `语句`
FROM information_schema.innodb_trx\G

-- 当前持有/等待的锁
SELECT ENGINE_TRANSACTION_ID AS `事务`, OBJECT_NAME AS `表`, INDEX_NAME AS `索引`,
       LOCK_TYPE AS `类型`, LOCK_MODE AS `模式`, LOCK_STATUS AS `状态`,
       LOCK_DATA AS `锁住的值`
FROM performance_schema.data_locks LIMIT 20;

-- 谁在等谁
SELECT * FROM performance_schema.data_lock_waits\G

-- 已经 join 好的版本(最常用)
SELECT wait_age, waiting_pid, waiting_query, blocking_pid, blocking_query
FROM sys.innodb_lock_waits\G

LOCK_MODE 怎么看:

值 含义
X / S 排他锁 / 共享锁
X,REC_NOT_GAP 只锁这一行,不锁间隙
X,GAP 只锁间隙(锁住「不存在的那段」)
X,INSERT_INTENTION 插入意向锁,抢间隙时用

自己造一次锁等待,比看十遍文档有用。 开两个窗口:

-- 窗口 A
BEGIN;
UPDATE lab.emp SET salary = 1 WHERE id = 1;   -- 不提交,锁一直持有

-- 窗口 B(会卡住,等 A)
UPDATE lab.emp SET salary = 2 WHERE id = 1;

这时候开第三个窗口跑上面的三条查询,就能看到完整等待链:谁持有、谁等待、等了多久。收尾在窗口 A 执行 ROLLBACK,窗口 B 立刻放行。

§10 日志:慢查询、binlog、错误日志

SELECT @@slow_query_log      AS `慢日志是否开启`,
       @@slow_query_log_file AS `慢日志文件`,
       @@long_query_time     AS `慢查询阈值秒`,
       @@log_output          AS `输出到`,
       @@log_error           AS `错误日志路径`;

SELECT @@log_bin AS `binlog开启`, @@binlog_format AS `格式`,
       @@gtid_mode AS `GTID模式`, @@binlog_expire_logs_seconds AS `保留秒数`;

SHOW BINARY LOG STATUS;   -- 8.4 必须用这条
SHOW BINARY LOGS;

8.4 的语法变化要特别注意:SHOW MASTER STATUS 已被删除,直接报 ERROR 1064。对应地:

旧写法(8.0) 新写法(8.4)
SHOW MASTER STATUS SHOW BINARY LOG STATUS
SHOW SLAVE STATUS SHOW REPLICA STATUS
CHANGE MASTER TO CHANGE REPLICATION SOURCE TO
START SLAVE START REPLICA

慢查询练手,三步:

SET GLOBAL long_query_time = 1;   -- 临时降到 1 秒
SELECT SLEEP(2);                  -- 造一条慢 SQL
-- 然后看文件:sudo tail -20 /var/lib/mysql/db01-slow.log

别忘了 long_query_time 用 SET GLOBAL 改只在内存里,重启就回去了。要永久生效得用 SET PERSIST(见 §11)。

§11 参数:值是多少、能不能改、谁改的

这是最容易被忽略、又最容易踩坑的一段。

SELECT VARIABLE_NAME AS `参数`, VARIABLE_SOURCE AS `来源`,
       SET_TIME AS `被修改时间`, SET_USER AS `被谁改`
FROM performance_schema.variables_info
WHERE VARIABLE_NAME IN ('innodb_buffer_pool_size','slow_query_log',
                        'max_connections','long_query_time','sql_mode');

VARIABLE_SOURCE 的五个值,决定了「重启后会不会变回去」:

来源 含义 重启后
COMPILED 编译默认值,没人动过 不变
GLOBAL 来自配置文件 my.cnf 保留
DYNAMIC 运行时 SET GLOBAL 改的 丢失
PERSISTED SET PERSIST 改的,写进了 mysqld-auto.cnf 保留
COMMAND_LINE 启动参数传进来的 看启动脚本

实测在 lab 环境里,slow_query_log 的来源就是 DYNAMIC——意味着它现在开着,但重启后就关了。

看到 DYNAMIC 就要追问一句:这是临时调的,还是本来就该写进配置? 线上「昨天还好好的,今天重启后监控全红」,一半是这个问题。

§12 执行计划:EXPLAIN 的三种用法

USE lab;

-- ① 表格:看 type / key / rows / Extra 四列
EXPLAIN SELECT * FROM emp WHERE dept_id = 3 AND hired_at > '2020-01-01' LIMIT 5;

-- ② 树形:看优化器实际选的访问路径,比表格直观
EXPLAIN FORMAT=TREE
SELECT * FROM emp WHERE dept_id = 3 AND hired_at > '2020-01-01' LIMIT 5\G

-- ③ 真跑一遍:真实的耗时和行数(8.0.18+)
EXPLAIN ANALYZE
SELECT * FROM emp WHERE dept_id = 3 AND hired_at > '2020-01-01' LIMIT 5\G

为什么一定要用第三种:EXPLAIN 里的 rows 是估算,EXPLAIN ANALYZE 里的 actual rows 才是真相。两者差一个数量级,说明统计信息过期,先 ANALYZE TABLE。

一个实测对照,看「索引失效」有多贵:

-- 索引有效
EXPLAIN ANALYZE SELECT * FROM emp WHERE hired_at = '2020-01-01';
-- → 走 idx_dept_hired,0.146 ms

-- 函数包住列,索引直接失效
EXPLAIN ANALYZE SELECT * FROM emp WHERE DATE(hired_at) = '2020-01-01';
-- → 全表扫 49582 行,12.8 ms

同一个查询条件,慢了 88 倍。 唯一区别是列被函数包住了——DATE(hired_at) 让索引里存的 hired_at 值没法直接比较。改成范围写法就恢复了:

WHERE hired_at >= '2020-01-01' AND hired_at < '2020-01-02'

§13 常用运维动作

-- 杀连接(先在 processlist 里确认 id 和 SQL,别杀错)
KILL 123;          -- 断连接
KILL QUERY 123;    -- 只取消当前语句,连接保留(更安全)

-- 更新统计信息(执行计划选错索引时先试这个,代价小)
ANALYZE TABLE lab.emp;

-- 回收碎片 / 重建表(会占大量 IO,线上要评估)
OPTIMIZE TABLE lab.emp;

-- 永久修改参数(写进 mysqld-auto.cnf,重启保留)
SET PERSIST slow_query_log = ON;

-- 撤销某条持久化设置
RESET PERSIST slow_query_log;

DDL 卡住时的元凶通常是元数据锁,查这个:

SELECT * FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING' LIMIT 10;

附:一键巡检脚本

上面 §0 – §13 的全部查询已经打包成一个脚本,可以在登录后一次跑完:

source /root/mysql-inspect.sql

或者不登录,一条命令出全部结果:

mysql -h192.168.101.7 -P3306 -udba -p < mysql-inspect.sql

↓ 下载 mysql-inspect.sql

脚本每段开头都有一个 SELECT '===== N. 标题 =====' 作分隔,输出虽然长,但能对着编号找回来。

顺序很重要

最后把这十四段压成一条线,这是真正该记住的东西:

  1. 先确认身份(§0)——连错实例,后面全白做
  2. 再看体量(§1 §2)——知道数据有多大,才有「这算快还是慢」的基准
  3. 然后看现场(§6 §7 §9)——连接、水位、锁,回答「现在卡在哪」
  4. 最后看计划(§12)——定位到具体哪条 SQL、为什么慢

前三步都是定性,只有第四步是定量。绝大多数线上问题的答案在前三步就已经出现了,EXPLAIN 只是最后确认一遍。