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()vsCURRENT_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\GLOCK_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脚本每段开头都有一个 SELECT '===== N. 标题 =====' 作分隔,输出虽然长,但能对着编号找回来。
顺序很重要
最后把这十四段压成一条线,这是真正该记住的东西:
- 先确认身份(§0)——连错实例,后面全白做
- 再看体量(§1 §2)——知道数据有多大,才有「这算快还是慢」的基准
- 然后看现场(§6 §7 §9)——连接、水位、锁,回答「现在卡在哪」
- 最后看计划(§12)——定位到具体哪条 SQL、为什么慢
前三步都是定性,只有第四步是定量。绝大多数线上问题的答案在前三步就已经出现了,EXPLAIN 只是最后确认一遍。