从第一次在服务器上敲mysql -u root -p手心冒汗到现在带新人时让他们先背熟几十条命令我对“命令大全”这四个字的理解一直在变。刚入行那会儿我把命令当成字典查遇到一个场景翻一条做久之后才发现真正值钱的不是记住多少条而是理解每条命令背后的执行逻辑、适用边界以及那些只有踩过坑才知道的细节。这篇内容我会把日常从连接、建库建表、增删改查到索引、事务、存储过程、备份恢复、巡检排错的命令按使用频率和重要度重新梳理一遍该给参数的地方给参数该讲原理的地方讲原理。它适合刚接触 MySQL 的新手当作实操手册也适合做了一两年但命令体系比较零散的朋友拿来补全认知。文中涉及 MySQL 架构、安装后的初始密码处理、索引与排序、JOIN 语义、更新子查询等高频关注点我都会结合真实场景讲清楚。1. 连接、账号与权限先把入口这一关管住命令行的第一步永远是怎么连上去。很多人装完 MySQL 之后用图形化客户端连得很顺一到纯命令行环境就卡壳问题往往出在对连接参数和认证方式不够熟。这一节把入口相关的命令讲透后面所有操作都建立在能稳定连上去的前提上。1.1 连接命令的几种姿势与选择依据最基础的一条命令长这样mysql -h 127.0.0.1 -P 3306 -u root -p-h是主机地址-P大写是端口-u是用户名-p小写表示密码在回车后交互输入不写在命令里。这个大小写区分是新手最容易搞混的地方小写-p是密码大写-P是端口敲错了会得到“Unknown MySQL server host”之类的提示实际上根本不是主机的问题。为什么不建议把密码直接跟在-p后面写成-proot123因为这样密码会进入 shell 的 history 记录history一敲就暴露了而且在多用户机器上通过ps aux有可能被同机其他用户看到命令行参数。我见过太多测试环境因为这一条被拖库的案例生产环境务必用交互输入或者用配置文件。如果你本地有个固定的测试库每次敲一长串参数很烦可以写一个客户端配置文件。在用户目录下建~/.my.cnf[client] host127.0.0.1 port3306 userroot password你的密码 default-character-setutf8mb4然后直接mysql就能连上。需要注意的是这个文件权限要设成600否则 MySQL 客户端会报“World-writable config file is ignored”并拒绝读取这是它保护你的方式。chmod 600 ~/.my.cnf记得加上。还有一个高频场景是远程连接。如果连不上先别急着改配置按顺序排查网络是否通ping主机、端口是否开用telnet ip 端口或nc -zv测试、账号是否允许远程来源这个在下一节讲、防火墙是否放行。MySQL 默认只监听127.0.0.1想接受远程连接要改bind-address这个改动属于安全敏感项改动前务必确认是不是真的需要对外暴露。提示连接时如果报Access denied for user九成是密码或主机来源不匹配不是网络问题。而报Cant connect to MySQL server才是网络、端口、服务没起来这类问题。两类报错对应两套排查方向分清楚能省很多时间。进入命令行后还有几个“元命令”值得记住。这些命令不以分号结尾是客户端层面的指令不是 SQLstatus或\s查看当前连接的版本、字符集、端口等信息show databases;列出所有库use 库名;切换当前库source /path/file.sql执行一个 SQL 脚本文件做数据导入时特别常用exit或quit或\q退出\G把查询结果竖着显示字段特别多的宽表用它可以避免换行乱成一团\G这个技巧我要多提一句。当你查一张有几十个字段的表时默认表格输出会挤成一团根本看不清改成select * from 表名\G注意末尾不要分号每一行会按“字段名: 值”的格式纵向排列可读性直接翻倍。1.2 用户管理命令与权限模型MySQL 的权限是“用户主机”的二维模型这一点极其关键。同样的用户名appuserappuserlocalhost和appuser%是两个完全不同的账号权限互不影响。创建用户的命令现在推荐这种写法CREATE USER appuser% IDENTIFIED BY 强密码;老写法GRANT ... IDENTIFIED BY在 8.0 之后已经被移除必须先用CREATE USER建账号再单独授权。这是 8.0 升级时最常见的兼容性问题之一。授权GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO appuser%; FLUSH PRIVILEGES;这里shop.*表示只给shop这个库下所有表的增删改查权限不给 DROP、不给 GRANT。最小权限原则在实际运维里不是口号应用账号绝不能给ALL PRIVILEGES更不能给*.*。我处理过一次线上误删事故根源就是某个应用连的是 root 账号一个拼错的 DELETE 语句直接清掉了一张大表。哪怕图省事也应给应用单独建账号。FLUSH PRIVILEGES这个命令经常被滥用。实际上直接用GRANT、REVOKE、CREATE USER这类语句修改权限时MySQL 会自动刷新内存中的权限表不需要手动执行。只有当你直接用INSERT、UPDATE去改mysql.user等系统表时才需要FLUSH PRIVILEGES让改动生效。日常用授权语句的情况下这条命令可以省掉。查看权限SHOW GRANTS FOR appuser%;回收权限REVOKE DELETE ON shop.* FROM appuser%;修改密码8.0 语法ALTER USER appuser% IDENTIFIED BY 新密码;删除用户DROP USER appuser%;这里有个坑%代表任意主机但它不匹配localhost。在 MySQL 的匹配逻辑里localhost走的是 socket 连接用的是另一套匹配规则。所以经常出现“用%建了账号却在本地连不上”的情况解决办法是额外建一个appuserlocalhost或者把应用配置里的localhost换成127.0.0.1强制走 TCP。1.3 初始密码处理与安全加固命令很多人安装完 MySQL 8.0第一件事就是问“初始密码是什么”。如果是通过包管理器安装且开启过临时密码它通常在错误日志里可以用grep temporary password /var/log/mysqld.log找到后用mysql -u root -p登录系统会强制你先改密码不改任何命令都执行不了。这是 8.0 的强制策略不是 bug。改完密码之后建议顺手把几个安全项过一遍。查看密码策略SHOW VARIABLES LIKE validate_password%;如果策略太严导致测试环境设置简单密码失败可以调整SET GLOBAL validate_password.policy LOW; SET GLOBAL validate_password.length 6;注意这条命令是全局、临时的重启后失效。想永久生效要写进配置文件。生产环境我强烈建议保持MEDIUM以上测试环境才放宽。查看当前认证插件SELECT user, host, plugin FROM mysql.user;8.0 默认用caching_sha2_password老版本的一些客户端连不上就是这个原因临时把某个账号改成mysql_native_password可以应急但长期看应该升级客户端而不是降级认证方式。2. 库表结构命令设计阶段就决定后面好不好过结构命令是那种“平时用得不多一出错就要命”的类型。改表在生产环境是有风险的所以每条 DDL 命令我都建议先搞清楚它会不会锁表、锁多久。这一节把库表操作命令和索引命令一起讲因为它们经常在同一个优化场景里出现。2.1 库级操作命令清单查看所有库SHOW DATABASES;只看自己关心的可以用模糊匹配SHOW DATABASES LIKE shop%;建库时明确字符集和排序规则不要依赖默认值CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;为什么强调utf8mb4因为 MySQL 里有个历史遗留的坑老版本所谓的utf8实际最多只存 3 字节存不了 emoji 和一些生僻字真正的完整 UTF-8 是utf8mb4。这个坑在涉及用户昵称、评论内容的业务里必然触发。所以从建库开始就用utf8mb4别等到线上报“Incorrect string value”才回头改。排序规则_ci表示大小写不敏感case insensitive_bin表示按二进制比较大小写敏感。一般业务用_ci需要精确区分的字段比如激活码、token再单独指定_bin。查看建库语句SHOW CREATE DATABASE shop;这个命令的价值在于当你需要在新环境复制一个库的结构时它输出的就是可以直接执行的完整语句比手写靠谱。删库这件事我不想多说只留一句DROP DATABASE shop;没有确认提示执行即生效。养成习惯删之前先SHOW TABLES;看一眼确认库名没敲错。2.2 建表与改表命令详解建表命令的核心不是把字段列出来而是把类型、长度、约束、默认值一次想清楚CREATE TABLE order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_status_created (status, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;几个细节值得展开。金额字段用DECIMAL而不是FLOAT浮点数在金融场景会有精度误差这是硬性要求。status设为NOT NULL DEFAULT 0避免大量 NULL 值干扰索引和统计。created_at和updated_at用自动时间戳能省掉应用层每次手动赋值的麻烦。“mysql 设置默认值为 0”这个需求很常见写法就是DEFAULT 0。但要注意NOT NULL和DEFAULT是两回事NOT NULL约束不能插 NULLDEFAULT是没给值时用啥。只有DEFAULT没有NOT NULL时显式插入 NULL 依然会写入 NULL。改表命令ALTER TABLE在生产环境要格外小心-- 加字段指定位置和不指定位置效果不同 ALTER TABLE order ADD COLUMN remark VARCHAR(255) DEFAULT NULL; -- 改字段类型可能重建表大表风险高 ALTER TABLE order MODIFY COLUMN remark VARCHAR(500) DEFAULT NULL; -- 改字段名 ALTER TABLE order CHANGE COLUMN remark note VARCHAR(255) DEFAULT NULL; -- 加索引 ALTER TABLE order ADD INDEX idx_created (created_at); -- 删索引 ALTER TABLE order DROP INDEX idx_created;MODIFY和CHANGE的区别记牢MODIFY只改类型和属性不改名字CHANGE既能改名也能改类型但必须把新旧名字都写上。很多人第一次用CHANGE少写了新名字直接语法报错。线上大表加字段、加索引直接ALTER可能造成长时间锁表甚至拖垮业务。常规做法是用在线 DDL 工具比如 pt-online-schema-change 或 gh-ost或者利用 MySQL 8.0 对部分ALGORITHMINPLACE操作的支持。执行前先用SHOW CREATE TABLE看清表大小和结构评估影响。查看表结构有三条命令各有用处DESC order; -- 简洁字段、类型、是否可空、键、默认值 SHOW CREATE TABLE order; -- 完整含引擎、字符集、索引定义 SHOW FULL COLUMNS FROM order; -- 带注释看字段注释最方便2.3 索引命令与创建时机的判断“mysql 创建索引”是搜索量极高的词但真正难的不是语法而是判断该不该建。语法先给全-- 建表时建 KEY idx_name (name) -- 表建好后加 ALTER TABLE user ADD INDEX idx_name (name); -- 或者 CREATE INDEX idx_name ON user (name); -- 唯一索引 CREATE UNIQUE INDEX uk_phone ON user (phone); -- 复合索引 CREATE INDEX idx_status_time ON order (status, created_at); -- 前缀索引长字符串字段 CREATE INDEX idx_title_prefix ON article (title(20)); -- 删除 DROP INDEX idx_name ON user;复合索引的“最左前缀”原则必须理解透。idx_status_time (status, created_at)能加速WHERE status ?、WHERE status ? AND created_at ?但单独用created_at作为条件时用不上这个索引因为最左的status没出现在条件里。这就是为什么复合索引的字段顺序至关重要把区分度高、又经常单独作为查询条件的字段放左边。前缀索引适用于长字符串比如给VARCHAR(255)的 URL 字段建索引用前 20 个字符往往已经足够区分能显著减小索引体积。但前缀索引不能用于覆盖索引和排序用的时候要权衡。那什么时候不该建索引写多读少的表要克制区分度低的字段比如性别只有两三个值单独建索引意义不大频繁更新的字段建索引会增加写开销。我个人的判断顺序是先看这条 SQL 在慢查询日志里出现频率高不高再看EXPLAIN显示扫了多少行最后才决定加什么索引。不加思考见字段就加索引是另一种性能灾难。查看表上的索引SHOW INDEX FROM order;看执行计划EXPLAIN SELECT * FROM order WHERE status 1;EXPLAIN输出的type列最值得关注从好到坏大致是system、const、eq_ref、ref、range、index、ALL。看到ALL就是全表扫描大表上出现基本要优化。key列显示实际用了哪个索引rows是预估扫描行数Extra里出现Using filesort或Using temporary也是优化信号。3. 数据操作与查询命令每天真刀真枪在用的部分DML 和查询命令是日常使用频率最高的。这一节我按“写”和“读”分开讲重点放在那些看起来简单、实际处处是坑的地方。3.1 增删改命令的边界与坑插入单行INSERT INTO user (name, age) VALUES (张三, 28);批量插入INSERT INTO user (name, age) VALUES (李四, 30), (王五, 25), (赵六, 33);批量插入比循环单条插入快得多因为减少了网络往返和事务提交次数。数据量大时务必批处理一般每次几百到几千行比较合适太大可能撞上max_allowed_packet限制。有一个容易忽略的坑批量插入时如果其中一条违反唯一约束整个语句默认全部失败。想跳过错误继续插用INSERT IGNORE INTO user (phone, name) VALUES (13800000000, 张三);INSERT IGNORE会把错误降级为警告重复的直接跳过。但要注意它也会吞掉其他错误比如类型转换失败用之前想清楚是否真的想忽略所有异常。还有个高频需求是“存在则更新不存在则插入”INSERT INTO user (phone, name) VALUES (13800000000, 张三) ON DUPLICATE KEY UPDATE name VALUES(name);这条命令依赖唯一索引或主键冲突来触发更新分支做计数、去重同步时特别好用。VALUES(name)取的是插入时提供的值8.0 之后官方推荐改成别名写法AS new ON DUPLICATE KEY UPDATE name new.name避免和其他用法混淆。更新命令UPDATE user SET age 29 WHERE name 张三;更新最大的风险是漏写WHERE直接全表更新。我给自己立的规矩是任何时候写UPDATE先写WHERE把条件想好再补SET最后才敲回车。另外生产环境执行前先SELECT COUNT(*)用同样的条件数一遍影响行数心里有底才动手。删除命令同理DELETE FROM user WHERE id 100;大批量删除大表数据时直接DELETE会锁住大量行、产生巨大 binlog、还可能撑爆 undo 日志。常规做法是分批删比如每次删 1000 行循环执行中间留一点间隔让主从和存储有个喘息空间。3.2 JOIN 的含义与写法选择“mysql 数据库 join 含义”这个问题问得特别多我用一个图书馆的类比来解释。把用户表想成读者名册订单表想成借阅记录。INNER JOIN内连接只保留两边都能对上的记录也就是“有借阅记录的读者和对应的借阅记录”没借过书的读者不出现。LEFT JOIN左连接保留左表全部右表没有匹配的用 NULL 填充。“所有读者以及他们的借阅记录没借过书的也列出来借阅部分为空”。RIGHT JOIN右连接和左连接相反保留右表全部。实际写代码时我基本不用 RIGHT JOIN把表顺序调换来写 LEFT JOIN 更符合阅读习惯。CROSS JOIN笛卡尔积两边行数相乘除非明确需要组合否则别用很容易炸出几百万行。-- 内连接查有订单的用户 SELECT u.name, o.amount FROM user u INNER JOIN order o ON u.id o.user_id; -- 左连接查所有用户包括没下过单的 SELECT u.name, o.amount FROM user u LEFT JOIN order o ON u.id o.user_id;这里有个经典坑在LEFT JOIN的WHERE里写右表字段的过滤条件会把左连接“悄悄”变成内连接。比如-- 这样写没下过单的用户会消失 SELECT u.name, o.amount FROM user u LEFT JOIN order o ON u.id o.user_id WHERE o.status 1;原因是右表匹配不上时o.status是 NULLNULL 不等于 1条件过滤掉了这些行。如果本意是“所有用户有已支付订单的显示出来”正确写法是把条件下移到ON里SELECT u.name, o.amount FROM user u LEFT JOIN order o ON u.id o.user_id AND o.status 1;这个ON和WHERE位置差异是面试里区分度很高的一道题也是实际排错时经常踩的点。JOIN 的性能关键在连接字段有没有索引。ON u.id o.user_id这条o.user_id上必须有索引否则每匹配一个用户就要扫一遍订单表几万用户直接把数据库拖垮。所以设计表时所有外键关联字段默认都建索引这是习惯。3.3 排序、分页与聚合命令“mysql 排序”涉及ORDER BYSELECT * FROM order ORDER BY created_at DESC LIMIT 20;单字段排序简单多字段要分清楚优先级SELECT * FROM order ORDER BY status ASC, created_at DESC;先按status升序status相同时再按时间倒序。想让ORDER BY走索引而不是 filesort排序字段最好和过滤字段组成一个合适的复合索引并且方向一致。比如索引是(status, created_at)那ORDER BY status, created_at能利用索引如果写成ORDER BY status, created_at DESC在某些版本和场景下就可能退化成 filesort。这类细节用EXPLAIN一看便知。分页是另一个重灾区SELECT * FROM order ORDER BY id LIMIT 0, 20; -- 第一页 SELECT * FROM order ORDER BY id LIMIT 100000, 20; -- 翻到很后面深分页LIMIT 100000, 20会先扫描并丢弃前 100000 行越翻越慢。优化思路是用“游标”的方式记住上一页最后一个 idSELECT * FROM order WHERE id 100000 ORDER BY id LIMIT 20;这样每次只扫需要的 20 行无论翻到多深都很快。前提是 id 单调递增且没有空洞依赖适用于按主键翻页的场景。聚合命令SELECT status, COUNT(*) AS cnt, SUM(amount) AS total FROM order WHERE created_at 2024-01-01 GROUP BY status;GROUP BY的字段和SELECT里非聚合字段要对应否则在ONLY_FULL_GROUP_BY模式下会直接报错。这个模式是 5.7 之后默认开启的好处是避免返回不确定的结果坏处是很多老代码迁移过来会报错。遇到报错先别急着关这个模式多数情况下是查询本身写得不够严谨。HAVING和WHERE的区别也要清楚WHERE在分组前过滤行HAVING在分组后过滤组。想筛掉订单数少于 5 的状态用HAVING cnt 5。3.4 更新子查询的经典报错与解法“mysql 中更新子查询”是一个高频问题因为 MySQL 不允许在UPDATE的WHERE子句里直接查同一张表-- 这样写会报错You cant specify target table user for update in FROM clause UPDATE user SET age age 1 WHERE id IN (SELECT id FROM user WHERE city 北京);报错原因是 MySQL 不能在更新一张表的同时从同一张表里读取数据用于定位这会造成不确定性和潜在的冲突。标准解法是用一层派生表把子查询“包起来”让中间的临时结果先物化UPDATE user SET age age 1 WHERE id IN ( SELECT id FROM ( SELECT id FROM user WHERE city 北京 ) AS tmp );多包一层SELECT再取别名MySQL 就会先把子查询结果算出来再拿去做更新条件问题解决。另一个解法是用JOIN改写UPDATE user u JOIN ( SELECT id FROM user WHERE city 北京 ) t ON u.id t.id SET u.age u.age 1;两种写法都行实际经验里数据量大时JOIN改写往往效率更好因为它能更好利用索引。但要注意更新涉及的记录多时务必先事务包起来或者先在测试库验证确认影响范围再上生产。4. 事务、存储过程与高级命令前面三节覆盖了 80% 的日常操作剩下 20% 属于“不常用但关键时刻能救场”的命令。事务保数据一致性存储过程封装复杂逻辑这两个是进阶路上绕不开的。4.1 事务控制命令MySQL 的 InnoDB 引擎支持事务核心就是几条命令START TRANSACTION; -- 或 BEGIN; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT; -- 提交 -- 或者 ROLLBACK; -- 回滚转账是最经典的例子扣款和加款必须同生共死中间任何一步失败都要整体回滚。不包事务的话扣款成功加款失败钱就凭空消失了。事务的隔离级别决定了并发下能看到什么SELECT transaction_isolation; -- 查看当前隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;默认是REPEATABLE READ。很多互联网业务会改成READ COMMITTED因为它在高并发下锁的范围更小、死锁概率更低。但改隔离级别有代价涉及一致性读取的逻辑要重新评估。切换前一定在测试环境充分验证。事务还有两个概念要记住AUTOCOMMIT和隐式提交。默认AUTOCOMMIT 1也就是每条 SQL 自动提交你不显式START TRANSACTION就没有事务保护。而像CREATE TABLE、ALTER TABLE这些 DDL 语句会隐式提交当前事务所以别在长事务中间夹 DDL否则前面的操作会被提前提交回滚就回不去了。SET autocommit 0; -- 关闭自动提交谨慎使用关闭自动提交之后每条语句都要手动COMMIT或ROLLBACK长事务会占用大量资源、阻塞其他会话生产环境不建议这么干。4.2 存储过程与函数命令“mysql 存储过程”经常出现在面试题里。它本质是一组预编译的 SQL 逻辑存在数据库里可以像函数一样调用。创建存储过程DELIMITER // CREATE PROCEDURE get_user_orders(IN uid BIGINT) BEGIN SELECT * FROM order WHERE user_id uid; END // DELIMITER ;DELIMITER这一步是新手最容易迷惑的地方。命令行默认用分号作为语句结束符但存储过程内部本身就有分号所以必须先临时把结束符改成//或$$写完整个过程再改回分号。不改的话客户端会在过程内部第一个分号处就认为语句结束了。调用CALL get_user_orders(100);查看和删除SHOW PROCEDURE STATUS WHERE Db shop; SHOW CREATE PROCEDURE get_user_orders; DROP PROCEDURE get_user_orders;存储过程内部还能写变量、条件判断、循环DELIMITER // CREATE PROCEDURE stat_order() BEGIN DECLARE total INT DEFAULT 0; SELECT COUNT(*) INTO total FROM order; IF total 1000 THEN SELECT 订单量很大 AS msg; ELSE SELECT 订单量正常 AS msg; END IF; END // DELIMITER ;我的个人态度是业务逻辑尽量放应用层存储过程不要滥用。它的可维护性、调试体验、版本管理都不如代码团队里会写的人少出问题排查也难。但它在批量数据处理、定时统计这类场景确实高效能减少网络往返该用的时候别排斥。4.3 视图、触发器与其他实用命令视图是把复杂查询存起来当表用CREATE VIEW v_user_order AS SELECT u.name, o.amount, o.created_at FROM user u JOIN order o ON u.id o.user_id; SELECT * FROM v_user_order WHERE amount 100;视图不存数据每次查都实时执行背后的 SQL适合封装频繁使用的复杂查询让上层代码干净。但它对性能没有直接提升用多了还可能掩盖底层 SQL 的问题。触发器是在增删改时自动执行的逻辑DELIMITER // CREATE TRIGGER trg_order_insert AFTER INSERT ON order FOR EACH ROW BEGIN UPDATE user_stat SET order_count order_count 1 WHERE user_id NEW.user_id; END // DELIMITER ;NEW代表新插入的行OLD代表被删改前的旧行。触发器很强大但同样不建议重度使用因为它把逻辑藏在数据库里应用开发者看不到容易造成“数据莫名其妙变了”的困扰。统计类的需求用异步任务或定时统计更可控。5. 备份、导入导出与日常巡检命令数据安全是底线备份命令和巡检命令看着不起眼真出事的时候就是救命稻草。5.1 备份与恢复命令实操逻辑备份的主力是mysqldump它是在命令行执行的工具不在 MySQL 交互界面里mysqldump -u root -p shop shop_backup.sql备份单个库。备份多个库用--databasesmysqldump -u root -p --databases shop user_center multi_backup.sql备份所有库mysqldump -u root -p --all-databases all_backup.sql只备份表结构不备份数据做环境搭建时常用mysqldump -u root -p --no-data shop shop_schema.sqlmysqldump有几个参数直接影响备份可用性务必记住参数作用建议--single-transaction备份期间不锁表保证一致性InnoDB 必加--routines导出存储过程和函数用到了就加--triggers导出触发器默认导出确认一下--events导出事件调度用到了就加--set-gtid-purgedOFF避免 GTID 信息导致的导入报错主从环境注意--default-character-setutf8mb4防止中文乱码强烈建议加一条我常用的生产备份命令mysqldump -u root -p \ --single-transaction \ --routines --triggers --events \ --default-character-setutf8mb4 \ shop shop_backup.sql恢复就是把文件导回去mysql -u root -p shop shop_backup.sql或者在 MySQL 交互界面里USE shop; SOURCE /path/shop_backup.sql;关于备份我踩过最深的坑是“备份文件其实没法恢复”。有次线上要恢复发现备份命令没加--single-transaction备份期间表在变导出来的一致性有问题还有一次没检查备份文件大小结果是空的。现在我给自己立了一条铁律备份完必须做恢复演练哪怕只恢复到一个测试库确认能跑通。没验证过的备份等于没有备份。5.2 导入导出与数据迁移命令除了mysqldump导出数据还有大杀器SELECT ... INTO OUTFILESELECT * FROM order INTO OUTFILE /tmp/order.csv FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n;注意这个命令导出的是服务器上的文件不是本地而且需要FILE权限和secure_file_priv配置允许的目录否则会报权限错误。这也是它比mysqldump麻烦的地方。反过来导入 CSVLOAD DATA INFILE /tmp/order.csv INTO TABLE order FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n;这个在大批量导入时比逐条INSERT快一个数量级做数据迁移时非常好用。但要注意字符集和字段顺序的对应导入前最好先建好表结构导入时用IGNORE或先清空表。数据在不同 MySQL 版本间迁移时版本兼容是难点。用mysqldump从低版本往高版本导通常没问题反过来从 8.0 往 5.7 导常常因为字符集默认规则、认证插件、保留字变化而报错。稳妥做法是导出后先用文本工具搜一遍把不兼容的语句处理掉再导。5.3 巡检命令与性能观察想了解数据库当前状态这几条命令每天看一眼SHOW STATUS; -- 全局状态变量几百项 SHOW STATUS LIKE Threads_connected; -- 当前连接数 SHOW STATUS LIKE Slow_queries; -- 慢查询次数 SHOW VARIABLES LIKE max_connections; -- 最大连接数上限 SHOW PROCESSLIST; -- 当前所有连接在干什么SHOW PROCESSLIST是排障神器。当数据库突然变慢用它可以看有哪些会话在跑、跑了多久、在等什么。如果发现某个会话Time列很大、State是Sending data或Locked基本就是问题所在。-- 当前执行时间超过 10 秒的语句 SELECT id, user, time, state, info FROM information_schema.processlist WHERE command ! Sleep AND time 10 ORDER BY time DESC;慢查询日志是另一个重点先看开没开SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time;没开的话在配置里打开重启生效或者临时开SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time是阈值超过它的 SQL 会被记录。生产环境一般设 1 秒甚至更低测试阶段可以设 0.1 秒来抓那些“不够慢但也不快”的语句。抓出来的慢 SQL 用EXPLAIN分析加索引、改写法、拆查询这是性能优化的主战场。查看表的大小和数据量评估存储压力SELECT table_name, table_rows, ROUND((data_length index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables WHERE table_schema shop ORDER BY size_mb DESC;这条挺实用能一眼看出哪个表最大、索引比数据还大的是不是索引建多了。6. 常见报错速查与命令使用心得前面按功能把命令梳理了一遍最后这一节我把平时遇到最多的报错整理成速查表再补几条命令使用上的经验。6.1 高频报错与对应解法报错信息常见原因解决方向Access denied for user密码错、主机来源不匹配、权限不足核对密码检查用户主机SHOW GRANTSCant connect to MySQL server服务没起、端口不通、防火墙拦截查服务状态、测端口、看防火墙Unknown column in field list字段名拼错、表别名没带对核对字段名和别名You cant specify target table ... in FROM clause更新时子查询查了同一张表用派生表包一层或用 JOIN 改写Incorrect string value字符集不是 utf8mb4存了 emoji改库表字段字符集为 utf8mb4Duplicate entry for key唯一索引冲突检查重复数据或用 INSERT IGNORELock wait timeout exceeded事务锁等待超时查 processlist 找持锁会话缩短事务Table is marked as crashed表损坏用REPAIR TABLE或从备份恢复Too many connections连接数打满查漏连接调max_connectionsData too long for column数据超字段长度改字段长度或截断数据Lock wait timeout exceeded这一条我单独说两句。它本质是某个事务长时间不提交导致其他事务等锁等到超时。排查步骤是先SHOW PROCESSLIST或查information_schema.innodb_trx找到运行时间最长的事务确认它是不是卡住了或者忘了提交必要时KILL掉。根治办法是让应用尽量缩短事务别在事务里做远程调用、别在事务里等用户输入。Too many connections的排查重点是区分“真的是业务量大”还是“连接泄漏”。如果是应用没用连接池每次请求新建连接又不关那加多少max_connections都没用迟早再打满。看Threads_connected是不是持续爬升、SHOW PROCESSLIST里Sleep状态的是不是特别多基本能判断出来。6.2 命令使用上我个人的几条经验第一条任何危险命令先在测试库跑一遍。UPDATE、DELETE、DROP、ALTER这四类操作无论多熟我都坚持先在测试环境验证语句和影响行数。生产环境的“手滑”代价太大一分钟的验证能省掉一周的补救。第二条善用LIMIT保护自己。写UPDATE和DELETE时实在没底可以先加LIMIT 1试跑确认逻辑对了再放大范围。这个习惯救过我好几次尤其是在写多表关联更新的时候。第三条命令行的补全和快捷键能省很多事。MySQL 客户端支持 Tab 补全表名和字段名部分配置下需要--auto-rehash输入长的库名表名时特别高效。还有Ctrl R反向搜索历史命令比按上箭头找快多了。第四条把常用命令整理成自己的脚本片段。比如备份、巡检、导入导出这些重复性操作写成带参数的 shell 脚本比每次手敲可靠。我自己的脚本库里备份脚本就包含了备份、压缩、校验文件大小、删除过期备份几个步骤一次写好长期受益。第五条理解命令背后的执行方式比记住命令本身更重要。同样是查数据SELECT走没走索引、JOIN用了什么算法、ORDER BY会不会 filesort这些决定了命令快不快。EXPLAIN是连接命令和性能之间的桥梁建议养成写复杂查询先看执行计划的习惯。最后分享一个我调试 SQL 的小方法。遇到复杂查询报错或结果不对我习惯把语句拆开逐步执行先单独跑子查询看结果对不对再加 JOIN最后加 WHERE 和 GROUP BY。这样一旦哪一步出问题范围立刻锁定比对着一条几百行的 SQL 干瞪眼高效得多。这个笨办法用久了反而成了我最快定位问题的路径。