MySQL命令实战：从连接到备份的高频操作解析

平时帮人处理MySQL线上问题，见多了“命令大全背得熟，上了生产就手抖”的情况。MySQL的命令看着多，但真正干活时，你高频用到的也就是连接、查表、写数据、看性能、做备份这几个闭环里的几十条。这篇文章不按字母表把命令拉一遍，而是按实际工作场景把命令串起来讲，每条都会带使用边界和坑。适合刚接手MySQL的开发者，也适合要独立维护数据库的兼职DBA。

1. 先别急着背命令：理解MySQL命令行的执行边界

刚接触MySQL的时候，我一度以为所有的“命令”都是在mysql客户端里敲的SQL。后来才发现，所谓“MySQL命令”至少要分成两类：一类是mysql客户端里的SQL语句，另一类是操作系统命令行下的客户端工具，比如mysqladmin、mysqldump、mysqlbinlog。两者混着用，是最容易乱的地方。

1.1 登录命令里藏着不少细节

最基础的连接命令是这样：

mysql -uroot -p -h 127.0.0.1 -P 3306

这行看着简单，实际上有几个容易被忽略的点：

• -h 127.0.0.1表示走TCP连接，而直接写mysql -uroot -p在Linux上默认走Unix socket。区别在于：TCP要经过网络层，受bind-address和权限表里的host字段约束；socket则完全走本地文件，速度更快，且不占网络端口。你要是发现本机连不上但socket能连上，先查权限表和bind-address，别急着改防火墙。
• -P是大写的端口号参数，小写的-p是密码参数。密码直接跟在-p后面容易被记进shell历史，我习惯只敲-p让它交互式输入。
• 登录后加-A可以关闭自动补全，尤其在库表数量多到上千的环境里，没加-A的客户端光是把库名表名加载进补全列表就要卡几秒。
• 定义登录后的提示符也很有用，避免在多个环境之间切昏了头：

mysql -uroot -p --prompt="\\u@\h [\d]> "

这样提示符会显示当前用户、主机名和当前所在数据库，多环境切换时不容易把测试库当生产库。

1.2 mysqladmin、mysqldump和mysql客户端的分工

这些工具经常被混为一谈，实际上各有各的定位：

工具	主要用途	常见场景
mysql	执行SQL语句、交互式操作	日常查询、建表、改数据
mysqladmin	服务端管理命令	ping存活、查看状态、刷新日志
mysqldump	逻辑备份	导出表结构和数据
mysqlbinlog	解析二进制日志	增量恢复、误操作排查

比如检查数据库是否活着，不需要登录进mysql执行SELECT 1，直接在系统命令行跑：

mysqladmin -uroot -p ping

返回mysqld is alive就说明实例存活。日常需要快速看关键状态值时，mysqladmin -uroot -p status比登录后敲一堆SHOW STATUS更省事。

1.3 非交互式执行：脚本化运维的基础

用-e参数可以在不进交互界面、不连终端的情况下直接执行SQL，这类命令最适合写脚本：

mysql -uroot -p -e "SHOW DATABASES;" mysql -uroot -p -e "SELECT user, host FROM mysql.user;"

如果SQL语句特别长，更推荐用heredoc方式：

mysql -uroot -p <<SQL USE app_db; SHOW TABLES; SQL

这种方式在自动化运维里非常常见，还避免了-e里引号嵌套的烦人问题。执行SQL文件则用source：

SOURCE /tmp/backup.sql;

.和source是等价的，但source更直观，别人看你的操作记录时也不会一脸问号。

2. 建库建表命令：字符集与表结构的前置决策

很多人在建库阶段就埋下了坑，而且这个坑往往要到上线跑了一段时间才爆出来。最典型的就是字符集选错、排序规则选错，导致查询结果和预期不一致，甚至报错。

2.1 字符集和排序规则为什么总在第一步翻车

我见过太多因为默认字符集导致的中文乱码问题，根源往往不在程序，而在建库时就用了latin1或者老旧的utf8。MySQL的utf8最多支持3字节，而emoji和很多生僻字需要4字节，必须用utf8mb4。所以建库我基本固定用这条：

CREATE DATABASE IF NOT EXISTS app_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

utf8mb4_0900_ai_ci是8.0里默认的排序规则，支持Unicode 9.0，不区分大小写。如果你用的是5.7，那常见的搭配是utf8mb4_general_ci。排序规则不一致会导致查询范围、排序结果都不同，团队成员在各自机器上建库时还容易各建各的。所以生产环境我都是直接把建库语句固化到发布脚本里，不允许手工创建。

SHOW COLLATION LIKE 'utf8mb4%';可以查看当前支持的所有排序规则字符串，选错了随时能看。

2.2 表结构定义与ALTER的正确用法

建表的习惯直接决定后面写查询的体验。我一般这样建核心表：

CREATE TABLE `user` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `username` VARCHAR(64) NOT NULL COMMENT '用户名', `mobile` CHAR(11) DEFAULT NULL COMMENT '手机号', `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0禁用 1启用', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_mobile` (`mobile`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

几个值得注意的细节：

• INT后面加UNSIGNED，主键可取值范围翻倍，自增不容易提前撞上限。
• 手机号用CHAR(11)固定长度比VARCHAR(11)更合适，固定长度不意味着一定省空间，但语义更清楚。
• DATETIME直接用DEFAULT CURRENT_TIMESTAMP，插入时不用手动给时间字段赋值，省掉一个容易踩的坑。
• 字段和表名都带COMMENT，半年后再看这个表，后人能少骂几句。

查看建表语句最推荐这条：

SHOW CREATE TABLE user\G

\G让输出变成纵向展示，字段多的时候可读性比默认表格强得多。

线上改表结构是另一种风险。早期MySQL的ALTER TABLE常常要重建整张表，锁住DDL期间的所有读写。5.6之后引入了在线DDL，但很多东西仍取决于版本和操作类型，我一般显式声明算法：

ALTER TABLE user ADD COLUMN age TINYINT UNSIGNED DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE;

LOCK=NONE的意思是尽量不阻塞读写，数据库如果判断当前操作无法在线完成，会直接报错而不是默默锁表。这个错误反而是好事，能逼你先审视大表变更的风险。

2.3 清空数据的命令别混用：DELETE、TRUNCATE、DROP

这三个命令看着都和数据删除有关，风险等级完全不同。

• DELETE FROM t;：逐行删除，会产生binlog，支持事务回滚，但大表删除极慢，还容易让主从延迟飙升。
• TRUNCATE TABLE t;：快速清空，释放空间，但会触发隐式提交，一旦执行，即使后面在事务里回滚也救不回来。
• DROP TABLE t;：把表和表结构一起删掉，除非有备份，否则等于一切归零。

我一般这么区分：保留表结构只要清数据就用TRUNCATE，但要先确认环境，别在生产上开会时顺手清错表；删除少量数据用DELETE并加LIMIT分批；连结构都不要才用DROP。

2.4 用户与权限命令：别只在root下面裸奔

权限命令的大全里必然有这几个：

CREATE USER 'app_user'@'%' IDENTIFIED BY '密码'; GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'%'; FLUSH PRIVILEGES;

MySQL 8.0里GRANT不能直接创建用户了，必须先CREATE USER，再用GRANT授权，那套旧习惯别再带过来了。FLUSH PRIVILEGES大多数时候都不用执行，但有人篡改过系统表或直接从文件拷贝账号时，它是生效的关键一步。

查看用户权限和当前账号权限常用：

SHOW GRANTS FOR 'app_user'@'%'; SHOW GRANTS FOR CURRENT_USER();

给业务方账号权限时，我坚持最少权限原则：只要能增删改查，就绝不给DDL权限。业务线误用管理账号把表结构改了的事情，已经听过不止一次。

3. SELECT背后的执行顺序：查询命令不是按书写顺序跑的

很多查询写得很别扭，是因为人以为SQL是“从上到下按书写顺序执行”的。其实真正执行时有一套固定的逻辑顺序，理解了它，很多慢查询和报错就豁然开朗了。

3.1 书写顺序和执行顺序是两回事

先看一个典型聚合查询：

SELECT department_id, COUNT(*) AS cnt FROM employee WHERE status = 'active' GROUP BY department_id HAVING cnt > 5 ORDER BY cnt DESC LIMIT 10;

这条语句的逻辑执行顺序其实是：

步骤	子句	作用
1	FROM/JOIN	先确定数据来源，组装虚拟表
2	WHERE	对每一行做条件过滤，此时还没分组
3	GROUP BY	按字段分组
4	HAVING	对聚合后的分组结果进行过滤
5	SELECT	投影字段，计算表达式、别名
6	ORDER BY	对最终结果排序
7	LIMIT	截取行数

因为WHERE在分组之前执行，所以不能在WHERE里用cnt这种聚合别名，只能用HAVING。反过来说，WHERE先过滤掉大量无效行再分组，性能会好很多，这是写聚合查询时要放在最前面思考的事。

3.2 JOIN命令的语义别搞反

两表关联的常见形式：

SELECT o.order_id, u.username FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.status = 'paid';

INNER JOIN只保留两边都能匹配上的行，LEFT JOIN则以左表为主，右表没有匹配就补NULL。很多人以为LEFT JOIN一定比INNER JOIN快，错。LEFT JOIN的语义决定了它可能保留更多行，优化器的执行路径也可能更复杂，不该凭感觉选JOIN类型，而要按业务语义选。

此外有个常用技巧，想查“不出现在右表中的记录”，可以直接：

SELECT u.id FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL;

这是典型的“反连接”写法，比用NOT IN通常更稳定，尤其避免了下层子查询里返回NULL值的特殊坑。

3.3 条件聚合和高级写法

用CASE WHEN在聚合函数里做多维度统计，是命令从及格到实用的分水岭：

SELECT department_id, SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_cnt, SUM(CASE WHEN status = 'disabled' THEN 1 ELSE 0 END) AS disabled_cnt FROM employee GROUP BY department_id;

这样一条SQL就能把同一个分组里的多状态数量统计出来，比写三条子查询再UNION要快很多。

GROUP_CONCAT也值得单独说：

SELECT department_id, GROUP_CONCAT(username ORDER BY created_at SEPARATOR ',') FROM employee GROUP BY department_id;

它可以按分组把字符串拼接起来，常用于生成“某个用户的多角色名列表”。但注意GROUP_CONCAT默认长度上限是1024字节，结果被截断时，你会看到数据突然少了，这时候用SHOW VARIABLES LIKE 'group_concat_max_len';查一下，并根据需求临时调大会话变量。

3.4 UNION还是UNION ALL

UNION默认会去重，UNION ALL直接合并，后者性能更好但会保留重复行。如果业务上已经确定两个查询结果不会重叠，或者重复不影响统计，直接用UNION ALL，我带过的新人几乎都会在这上面多写一遍UNION，然后对着这个很简单的大查询发呆半天。

4. 索引命令与EXPLAIN：慢SQL诊断的实操入口

索引命令本身不难，难在“建完之后为什么还是慢”这句话背后一连串的判断。

4.1 建索引命令的正确姿势

给已有表补索引时，风格各不相同。下面几种写法效果基本等价：

ALTER TABLE user ADD INDEX idx_username (username); CREATE INDEX idx_username ON user (username);

如果要建唯一索引：

CREATE UNIQUE INDEX uk_mobile ON user (mobile);

组合索引最需要动脑：

CREATE INDEX idx_dep_status ON employee (department_id, status);

组合索引遵循最左前缀法则，(department_id, status)这个索引可以高效支持按department_id查询，以及同时按department_id和status查询，但如果只按status查询，这个索引基本用不上。所以我日常劝别人：别一上来给每个字段都建独立索引，那会让写入变慢、占用空间，优化器也可能挑错执行计划。

还有一种前缀索引，在前缀字段做索引，比如对长文本只需要前20个字符做索引：

ALTER TABLE article ADD INDEX idx_content_prefix (content(20));

这种索引最典型的场景是URL、长描述等大字段，整体建索引会非常大，前缀索引能大幅节省空间，代价是没法精确匹配该字段的全部内容。

4.2 EXPLAIN怎么读才不白看

慢SQL排查第一件事就是看执行计划：

EXPLAIN SELECT u.username, o.amount FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'paid';

输出里最值得关注的是type、key、rows和Extra。

type从好到差大致是：

type值	含义	常见场景
const	主键或唯一索引等值匹配，最多一行	WHERE id = 1
eq_ref	被驱动表通过唯一索引关联	JOIN的主键关联
ref	非唯一索引等值匹配	WHERE status = 'paid'
range	索引范围扫描	BETWEEN、IN、大于小于
index	全索引扫描	覆盖索引或者扫描整棵索引树
ALL	全表扫描	没有可用索引

看到ALL的时候，先别急着骂优化器，先看rows估算的行数。如果这个表本身就几千行，全表扫描未必比走索引慢。数据量小的时候，优化器选择全表扫描完全合理。

Extra里出现Using index是好信号，表示查询没回表，只扫索引就拿到了数据，这叫覆盖索引。出现Using filesort则代表排序没法用索引完成，多见于ORDER BY字段不在索引里或者排序方向和索引不一致。

4.3 看起来在用索引，其实索引失效的常见写法

索引失效的命令写法，我列几个高频的：

-- 对索引列做函数运算，索引失效 WHERE DATE(created_at) = '2025-01-01'; -- 隐式类型转换，比如mobile是varchar，却拿数字去比较 WHERE mobile = 13800001111; -- LIKE以%开头，前缀不确定，索引无法定位 WHERE username LIKE '%admin%'; -- OR连接的条件里，只要有一个字段没索引，另一个索引也不一定能走 WHERE username = 'admin' OR mobile = '13800001111';

函数运算的典型例子是把时间字段外面套了DATE()，这种写法看着很自然，却让索引失效。正确的思路是改成范围条件：

WHERE created_at >= '2025-01-01 00:00:00' AND created_at < '2025-01-02 00:00:00';

EXPLAIN之后发现走了索引但rows还是很大，也可能是因为统计信息太旧。8.0里执行：

ANALYZE TABLE employee;

让优化器重新收集统计信息，有概率纠正离谱的执行计划。

5. 写入、事务与锁命令：白天写数据，晚上查阻塞

查询命令用得最多，真正让数据库出问题的往往是写入和事务。白天写入时一切正常，到了晚上一查锁等待，发现是下午某个没提交的事务一直占着行锁不放。这类场景，命令要成套地用。

5.1 INSERT/UPDATE/DELETE的高阶用法

批量插入时尽量用一条INSERT带多组VALUES，减少网络往返：

INSERT INTO user (username, mobile) VALUES ('u1', '13800000001'), ('u2', '13800000002');

需要“有则更新，无则插入”的场景，用ON DUPLICATE KEY UPDATE：

INSERT INTO user_point (user_id, point) VALUES (1, 100) ON DUPLICATE KEY UPDATE point = point + 100;

这条命令在主键或唯一键冲突时会执行后面的更新逻辑。和REPLACE INTO相比，ON DUPLICATE KEY UPDATE不会删除原有行再插入新行，主键不会变，外键关系和自增ID都不容易乱。REPLACE INTO的原理是“删除旧行再插入新行”，副作用比较多，我已经基本不用它了。

UPDATE也支持多表关联：

UPDATE orders o JOIN users u ON o.user_id = u.id SET o.owner_name = u.username WHERE u.status = 'active';

这种写法比先SELECT再逐条UPDATE高效，但要特别注意WHERE条件别漏，一旦漏了就是全表更新，那可是事故级别的问题。我的习惯是UPDATE前先用同样的WHERE跑一条SELECT COUNT看影响范围。

5.2 事务控制命令和隐式提交的坑

事务的基本命令就这么几个：

START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE id = 1; UPDATE account SET balance = balance + 100 WHERE id = 2; COMMIT; -- 如果中途发现某一步失败，执行 ROLLBACK;

也可以用SAVEPOINT做部分回滚：

SAVEPOINT sp1; UPDATE ...; ROLLBACK TO SAVEPOINT sp1;

但很多新人不知道的是，下面的命令会在事务内“隐式提交”，把前面的操作全部落盘：

• ALTER TABLE、CREATE INDEX、DROP TABLE这类DDL
• GRANT、CREATE USER权限变更
• TRUNCATE TABLE

所以别以为开了事务保护就能随便执行DDL，一旦执行，之前想回滚的数据就全都回滚不了了。我遇到过在事务里先插了几千条数据，然后顺手加了条索引，结果插入的数据被隐式提交，再想ROLLBACK已经没用了。

autocommit也是老生常谈。MySQL默认是1，意味着每个SQL都会自动提交。有些环境为了统一控制事务，会SET SESSION autocommit=0，这时候如果忘记COMMIT，事务就一直挂着，行锁一直不释放，后面所有等这把锁的会话都会积压。

5.3 锁等待排查命令要成套使用

生产环境锁等待发生时，第一条命令永远是看当前都有哪些会话在跑：

SHOW FULL PROCESSLIST;

重点关注Time很长、State为Updating、Waiting for table metadata lock或处于Sending data的会话。找到可疑ID后，确认多几次再动手，用：

KILL 123456;

终止会话。注意KILL之前一定要看Info列里的SQL内容，确认它不是一个大查询正在正常跑，而是确实卡住或占用了锁。

如果锁等待来自事务没提交，SHOW PROCESSLIST往往看不出锁，因为事务连接可能正在空闲。这时查事务表更准：

SELECT * FROM information_schema.innodb_trx\G

重点看trx_started时间，空转了半个小时以上的事务，大概率是要找的元凶。更直接的锁信息可以从performance_schema.data_lock_waits或者sys.innodb_lock_waits查出来。在sys库里执行：

SELECT * FROM sys.innodb_lock_waits\G

它会列出谁在等哪把锁、谁持有锁、等待多久，指向相当明确。

锁等待超时时间由参数控制：

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

默认50秒太长了，在业务应用里，一个等锁的请求挂50秒才报错，对用户来说体验已经不可接受。线上我一般调整到5到10秒，让应用快速失败，而不是把所有线程都堆在锁上。

6. 备份恢复与日志命令：平时不练，出事就慌

备份恢复命令是命令大全里存在感最低、但真正决定生死的一部分。很多MySQL命令你可以在网上现查，唯独恢复流程，现查真的会出事。

6.1 mysqldump常用参数组合

逻辑备份最常用的工具是mysqldump，一条比较完整的备份命令长这样：

mysqldump -uroot -p \ --single-transaction \ --default-character-set=utf8mb4 \ --set-gtid-purged=ON \ -q \ app_db > /data/backup/app_db_$(date +%F).sql

解释一下关键参数：

参数	作用	注意事项
--single-transaction	用InnoDB的一致性快照备份，不锁表	对MyISAM无效，MyISAM会锁表
--default-character-set=utf8mb4	导出文件字符集	不设容易导出一堆utf8乱码
--set-gtid-purged=ON	记录GTID信息，便于后续增量恢复	8.0在某些场景下必须显式指定
-q	快速模式，不逐行缓存	适合大库，但会加大IO压力
--no-data	只导表结构	新环境想建表不想带数据时用
--no-create-info	只导数据	已有表结构时恢复数据用

备份多个库加--databases：

mysqldump -uroot -p --databases app_db test_db > multi_db.sql

单库备份不加--databases，生成的SQL里默认不带CREATE DATABASE语句，恢复时你得先手动建库。加了--databases则会自带建库语句。这个区别经常让不熟悉的人恢复时不断报“Unknown database”。

6.2 恢复命令与常见报错

导入备份文件最常用的两种方式：

mysql -uroot -p app_db < /data/backup/app_db.sql

或者进入mysql客户端后：

SOURCE /data/backup/app_db.sql;

恢复时报ERROR 1418、ERROR 1064这类错，先回去查备份文件的字符集和版本兼容性。尤其从8.0备份恢复到5.7，默认字符集、认证插件都可能不一致，轻则报错，重则导入一半中断。我建议恢复前先在测试库用source跑一遍全量备份，别拿生产库试错。

数据中包含二进制字段时，导出备份建议加--hex-blob，否则二进制内容会被解释成乱码，恢复出来的数据直接坏掉。

6.3 日志命令：定位误操作和增量恢复的关键

出事最常见的问题是“某张表被删了几行，能不能找回来”。这时候要靠binlog。

先确认当前实例是否开了binlog：

SHOW VARIABLES LIKE 'log_bin';

如果返回ON，再看当前正在写哪个日志文件：

SHOW MASTER STATUS;

输出里的File列就是当前活跃的binlog文件名，Position是当前写入位置。要列出所有binlog文件：

SHOW BINARY LOGS;

误操作恢复的思路是：先用全量备份恢复到一个基础时间点，再通过binlog把从备份时间点到误操作之前的增量部分补回来。实际操作时用mysqlbinlog把日志解析成SQL：

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v mysql-bin.000010

--base64-output=DECODE-ROWS -v会把binlog中的行事件解码成可读的SQL伪语句。想找某条具体的误操作语句，可以配合grep：

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v mysql-bin.000010 | grep -A 20 'DELETE FROM `app_db`.`user`'

定位到大致位置后，用--start-position和--stop-position精确截取需要重放的日志段：

mysqlbinlog --no-defaults --start-position=2197 --stop-position=4520 mysql-bin.000010 | mysql -uroot -p

整个过程看着不难，但真到恢复现场，手忙脚乱是常态，所以务必在测试机上完整演练过一遍。

6.4 用命令临时开日志调试

实在查不到问题时，可以临时打开通用查询日志，看所有进到MySQL的SQL：

SET GLOBAL general_log = 'ON'; SET GLOBAL general_log_file = '/tmp/mysql_general.log';

用完马上关掉：

SET GLOBAL general_log = 'OFF';

这个日志会记录所有客户端请求，生产环境开着它I/O压力会明显加大，我只在短时间定位问题时用，而且用必关。

命令行的批处理技巧也值得一提。用-N跳过列名，用-B批处理模式输出，方便脚本解析：

mysql -uroot -p -N -B -e "SELECT user, host FROM mysql.user;"

结果可以直接交给awk、while循环处理，比如对一组库做同样的操作：

mysql -uroot -p -N -e "SHOW DATABASES" | while read db; do mysqldump -uroot -p --databases "$db" > "/backup/$db.sql" done

这些命令组合起来，效果远超一个单独的SQL。

命令行这东西，不需要背全，需要练熟。你先把连接、建表、查询、索引、事务、备份这个链路跑上几遍，比对着大全抄十遍都管用。真遇到拿不准的，HELP命令在手边就是手册；但恢复流程这种高危操作，临场再翻可来不及。我的建议很朴素：每个月找一台测试机器，完整执行一遍备份和恢复，体验过一次手抖恢复的过程，你对MySQL命令的信任度和概念都会跟现在完全不同。