MySQL命令行实战指南：从日常管理到性能优化

我一直觉得MySQL的命令行是一个被很多人低估的武器。平时用Navicat、DBeaver这类图形工具点来点去确实方便可真到了线上环境、服务器裸奔、没有图形界面的时候你能不能稳住局面全靠手里那几条命令。特别是接手别人的数据库、排查慢查询、做数据迁移、恢复误删记录那种“手边只有终端”的场景命令的熟练度直接决定你解决问题的速度。这篇东西与其叫“命令大全”不如说是我这些年摸爬滚打攒下来的MySQL命令实战笔记。我不会只扔给你一串命令列表就完事而是把每个命令背后“为什么要这么写”“什么时候用”“有哪些坑”都讲清楚。无论你是刚接触数据库的学生、准备课程设计的开发者还是正在维护生产库的工程师这篇文章都值得你花二十分钟完整读一遍然后收藏起来当工具书翻。1. 连接与日常管理从登录到切换库的一串基本功1.1 登录、退出与连接状态命令行登录MySQL最基础的一条就是mysql -u root -p-u指定用户名-p表示需要输入密码回车之后系统会提示你输入密码这时候输入的密码是隐藏的不会明文显示在终端里。很多人图省事会写成mysql -u root -p123456直接把密码跟在-p后面。我不建议这么干因为这样密码会留在shell的历史记录里比如~/.bash_history别人一翻就能看到这是实打实的安全隐患。如果你要远程连接数据库服务器需要加上主机地址和端口mysql -h 192.168.1.100 -P 3306 -u root -p注意这里的端口是大写的-P用户名是小写的-u。大小写搞反的话小写的-p后面会被当成密码去解析大概率直接报Access denied这种低级错误在很多新手身上反复出现过。登录之后想要退出直接输入exit;或者quit;也可以直接用快捷键Ctrl D退出。exit和quit本质上是一回事都是正常断开连接。别用Ctrl C去强杀进程虽然也能退但有时候会导致连接没有正常释放在旧版客户端里容易留下残留的socket文件或者把终端搞进奇怪的状态。登录之后我习惯先看一眼当前连接的是谁、在哪个库SELECT USER(), CURRENT_USER(), DATABASE();USER()返回你当前登录时使用的用户名和主机CURRENT_USER()返回MySQL实际匹配到的账户DATABASE()返回当前所在的数据库如果没有选库则为NULL。这句命令在排查权限问题的时候特别好用比如你明明用root登录了但建表时报权限不足这时候用CURRENT_USER()一看很可能实际匹配的是rootlocalhost和root192.168.%的不同账户权限自然不一样。1.2 查看实例信息登录进去第一件事很多人会摸不清自己连的到底是MySQL哪个版本、什么发行版。这时候用SELECT VERSION();或者更详细一点SHOW VARIABLES LIKE %version%;前者直接输出版本号比如8.0.32后者会列出一堆版本相关的系统变量包括version_comment能看出是社区版还是企业版。为什么要确认版本因为MySQL 5.7和8.0在认证插件、字符集默认值、窗口函数支持上都有明显差异同一个SQL在这两个版本上跑出来的结果可能不一样。比如8.0默认认证插件是caching_sha2_password而5.7默认是mysql_native_password你用老版本客户端去连8.0的库就经常会报认证失败。想看当前数据库的连接数、运行时间、线程情况SHOW STATUS LIKE Threads_connected; SHOW STATUS LIKE Uptime; SHOW PROCESSLIST;Threads_connected表示当前有多少个客户端连着Uptime是数据库启动到现在跑了多少秒SHOW PROCESSLIST能列出当前所有正在执行的连接和SQL是排查死锁、慢查询的起点。生产环境上一旦发现连接数飙高第一步就是把SHOW PROCESSLIST拉出来看看是哪个应用、哪条SQL在捣乱经常能找到没有提交的事务或者锁等待的会话。MySQL 8.0还提供了更现代的查询方式SELECT * FROM performance_schema.processlist;效果类似但可以用SQL语法去过滤比如只看某个用户的连接、排除自己这在连接数特别多的时候比SHOW PROCESSLIST好用得多。1.3 数据库与表的查看切换查看服务器上一共有哪些数据库SHOW DATABASES;这个命令会列出所有你有权限看到的库。刚安装完的MySQL默认有几个系统库information_schema、mysql、performance_schema、sys。新手看着不要慌这些是MySQL自带的元数据管理库不是被入侵了。其中information_schema保存了所有表、列、索引的元数据信息mysql保存了用户、权限等核心系统表performance_schema是性能监控用的sys是DBA为了方便查看性能数据封装的一堆视图。选库和查看表USE database_name; SHOW TABLES;USE切换当前默认数据库执行之后后续不带库名的SQL都会在这个库里执行SHOW TABLES列出当前库里的所有表。如果表很多可以用LIKE过滤SHOW TABLES LIKE user%;只想看某一张表的结构DESC table_name;或者SHOW CREATE TABLE table_name;这两个有本质区别。DESC简明扼要列出字段名、类型、是否为空、键类型、默认值适合快速浏览SHOW CREATE TABLE输出完整建表语句包括ENGINE、CHARSET、AUTO_INCREMENT等所有细节在排查表字符集问题、复制表结构、导出DDL的时候是首选。2. 库和表的结构操作建库建表改字段2.1 创建与删除数据库创建数据库的完整语法CREATE DATABASE IF NOT EXISTS db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;这里面有几个关键点要说明白。IF NOT EXISTS是一种保护性写法如果库已经存在命令不会报错只是产生一个警告。不加这个重复执行会直接报ERROR 1007 (HY000): Cant create database db_name; database exists。脚本里批量执行时建议都加上。同样删除数据库用DROP DATABASE IF EXISTS db_name这个命令非常危险一旦执行整个库连同所有表数据全部消失MySQL不会给你二次确认的机会。CHARACTER SET utf8mb4是指定字符集。注意现在强烈建议一律使用utf8mb4而不是老的utf8。因为utf8在MySQL里最多只支持3个字节像emoji这种4字节字符会存不进去插入直接报Incorrect string value。而utf8mb4是完整的UTF-8编码能覆盖所有Unicode字符。2010年之前的老库用latin1、gbk的另说latin1碰到中文会变成乱码gbk在跨平台传输时编码很容易错位新库直接无脑utf8mb4。COLLATE utf8mb4_general_ci是排序规则。_ci结尾表示大小写不敏感的比较规则_bin结尾表示二进制比较。对大部分业务场景来说默认的utf8mb4_general_ci或8.0的utf8mb4_0900_ai_ci够用但如果你的业务对大小写敏感或者需要精确区分字符的二进制编码就要选utf8mb4_bin。查询时是否区分大小写、排序顺序都跟这个设置直接相关所以建库之前想清楚建完之后再改编码是一件非常痛苦的事。2.2 表的创建与修改建表时我习惯把ENGINE、AUTO_INCREMENT、COMMENT都写清楚方便后面维护的人一眼看懂CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(50) NOT NULL COMMENT 用户名, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT用户表;几个细节展开说一下。INT UNSIGNED表示无符号整数主键ID一般用无符号这样正数的上限能翻一倍从21亿多变成42亿多对大部分业务够用。真要存海量数据可以用BIGINT UNSIGNED但一般用户表、订单表到不了那个量级。AUTO_INCREMENT是自增列必须配合索引使用通常就是主键。自增列有个小坑如果你删除了某行数据自增的计数器不会回退。比如插入了1、2、3删掉3再插入的时候ID是4而不是3。MySQL不会复用已删除的自增值这是设计如此别想着手动把计数器调回去。DEFAULT CURRENT_TIMESTAMP是一个很实用的默认值插入数据时不需要手动填created_atMySQL会自动取当前时间。MySQL 5.6.5之后支持在DATETIME类型上使用这个默认值之前版本只能用TIMESTAMP。UNIQUE KEY uk_username (username)给username加唯一索引。加唯一索引之后重复插入相同的用户名会直接报Duplicate entry错误。你用代码做校验是挡不住并发场景下两个请求同时插入相同用户名这种情况的数据库层面的唯一约束才是最后防线。修改表结构的常用操作-- 添加字段 ALTER TABLE users ADD COLUMN age INT DEFAULT 0 COMMENT 年龄; -- 删除字段 ALTER TABLE users DROP COLUMN age; -- 修改字段类型 ALTER TABLE users MODIFY COLUMN email VARCHAR(200) DEFAULT NULL COMMENT 邮箱; -- 重命名字段 ALTER TABLE users CHANGE COLUMN email mail VARCHAR(100) DEFAULT NULL; -- 修改表名 ALTER TABLE users RENAME TO member_users;ADD COLUMN默认添加到表的最后位置如果要把新字段加在某个字段后面可以用AFTERALTER TABLE users ADD COLUMN age INT DEFAULT 0 COMMENT 年龄 AFTER email;CHANGE COLUMN和MODIFY COLUMN的区别在于CHANGE可以同时改字段名和字段类型MODIFY只能改字段类型。两者在修改类型时都需要完整写出字段定义。注意MODIFY COLUMN改字段类型的时候如果不写原有的NOT NULL或DEFAULT这些属性可能会丢失这是很多人在改字段时遇到“字段莫名变成允许NULL”的原因。生产环境修改大表结构是非常谨慎的操作。ALTER TABLE在MySQL 5.6之前的版本会锁表期间所有读写都会被阻塞5.6之后有了Online DDL大部分操作支持在线执行但在数据量很大的表上仍然会产生大量IO耗时可能很长。稳妥的做法是用pt-online-schema-change这类工具在业务低峰期执行或者至少在变更前先确认表大小、当前连接数和锁等待情况。2.3 索引的创建与维护索引是MySQL查询性能的核心。创建索引-- 普通索引 CREATE INDEX idx_email ON users(email); -- 唯一索引 CREATE UNIQUE INDEX uk_email ON users(email); -- 联合索引 CREATE INDEX idx_name_age ON users(username, age); -- 在ALTER TABLE里加索引 ALTER TABLE users ADD INDEX idx_created_at (created_at);关于索引这里面有几个关键的认知要建立起来。第一联合索引(username, age)遵循最左前缀原则。也就是说查询条件里只有username的时候能用到这个索引只有age的时候用不到。如果两条查询分别以username和age为条件就需要建两个独立的索引。建联合索引时最常查询的字段放最左边区分度高的字段也更适合放前面。第二索引不是越多越好。每多一个索引INSERT、UPDATE、DELETE时都要同步维护索引数据写入性能会下降而且会占用更多磁盘空间。一个几万行的表全表扫描的代价本来就可控建一堆索引纯属浪费。真正需要索引的是那种有几百万行、查询条件能过滤掉绝大多数数据的场景。第三查看索引用SHOW INDEX FROM users;这个命令会列出表上所有索引包括索引名、字段、顺序、唯一性、基数Cardinality等。Cardinality是一个很有参考价值的数字它表示索引中不同值的数量如果相对于总行数很低说明这个索引区分度差。删除索引相对少见但有时候也逃不掉DROP INDEX idx_email ON users;3. 用户与权限权限控制是一门精细活3.1 创建用户与授权创建一个用户并授予权限最常见的写法是CREATE USER app_userlocalhost IDENTIFIED BY StrongPssw0rd; GRANT SELECT, INSERT, UPDATE, DELETE ON db_name.* TO app_userlocalhost;CREATE USER创建用户app_userlocalhost中的localhost限定了这个用户只能从本机连接。如果要允许某个网段访问写成app_user192.168.%如果要允许任意主机写成app_user%。生产环境不建议用%能用IP段限制就尽量用IP段限制这是很基础的一层防护。密码引号内的字符串被当成密码。MySQL 8.0默认使用caching_sha2_password认证要求密码强度足够。如果你的程序用的老版本驱动连8.0数据库可能报Authentication plugin caching_sha2_password cannot be loaded这时候可以创建用户时指定老认证插件CREATE USER app_userlocalhost IDENTIFIED WITH mysql_native_password BY StrongPssw0rd;或者改已有用户的认证插件ALTER USER app_userlocalhost IDENTIFIED WITH mysql_native_password BY StrongPssw0rd;但要注意这是兼容性处理从安全角度讲长期还是应该升级客户端驱动以支持新版认证。GRANT授予权限时db_name.*表示db_name库下的所有表*.*表示所有库所有表。权限粒度可以有表级、列级但日常用得最多的是库级和表级。给应用账号授权时遵循最小权限原则只需要读写的就不要给CREATE、DROP、ALTER只需要查询的就只给SELECT。常用权限一览权限作用范围说明SELECT表/视图查询数据INSERT表插入数据UPDATE表更新数据DELETE表删除数据CREATE库/表创建数据库和表DROP库/表删除数据库和表ALTER表修改表结构INDEX表创建和删除索引REFERENCES表外键引用ALL PRIVILEGES全局除了GRANT之外的所有权限如果把GRANT和CREATE USER合并成一条也是可以的GRANT SELECT, INSERT, UPDATE, DELETE ON db_name.* TO app_userlocalhost IDENTIFIED BY password;这种旧式写法在MySQL 8.0中已经不支持了会报语法错误所以还是建议分开写。3.2 回收权限与删除用户回收权限REVOKE DELETE ON db_name.* FROM app_userlocalhost;删除用户DROP USER app_userlocalhost;如果用户已经不存在执行DROP USER会报错可以先查一下用户列表再操作。查看所有用户SELECT user, host FROM mysql.user;这个查询直接查系统库mysql里的user表。注意修改用户权限相关操作是写系统表的过程所以执行之后需要让MySQL重新加载权限数据。大部分情况下GRANT、REVOKE、DROP USER会自动刷新但如果你直接通过INSERT INTO mysql.user这种暴力方式修改了系统表强烈不建议这么做就必须手动执行FLUSH PRIVILEGES。修改密码-- MySQL 5.7及之前 SET PASSWORD FOR app_userlocalhost PASSWORD(NewPassword); -- MySQL 8.0 ALTER USER app_userlocalhost IDENTIFIED BY NewPassword;8.0中PASSWORD()函数已被移除用ALTER USER是标准做法。自己改自己密码的时候可以简写为ALTER USER USER() IDENTIFIED BY NewPassword;USER()函数返回当前登录用户这样就不用敲用户名和主机了。3.3 权限表的刷新与查看上面提过FLUSH PRIVILEGES这里展开说一下。它的作用是重新加载权限表。正常通过CREATE USER、GRANT、REVOKE操作后不需要手动执行MySQL已经自动记住了但如果出现权限看起来改了却没生效的诡异现象或者有DBA直接改过mysql.user表这种情况真有人干执行一下FLUSH PRIVILEGES;这是一个治标的方法。与其出了问题再刷新不如从来不要绕过规范命令去直接改系统表。查看当前用户的权限SHOW GRANTS;查看指定用户的权限SHOW GRANTS FOR app_userlocalhost;SHOW GRANTS输出的结果是授予该用户的GRANT语句能直观看到这个用户有哪些权限。接手别人的项目第一步就是用SHOW GRANTS FOR确认账号的实际权限如果发现权限给了ALL PRIVILEGES ON *.*而业务只需要读写某个库建议立刻收紧防止应用被拖库后影响整个数据库实例。4. 数据增删改查最常用的四类命令4.1 插入数据插入单行数据INSERT INTO users (username, email, age) VALUES (zhangsan, zhangsanexample.com, 25);插入多行INSERT INTO users (username, email, age) VALUES (lisi, lisiexample.com, 30), (wangwu, wangwuexample.com, 28);一次性插入多行的性能远好于逐行插入因为减少了客户端与服务器之间的交互次数。如果你有上万条数据要批量写入甚至可以拆成每批500-1000条这种方式比循环单条插入快一个数量级。有一类特殊插入是“有则更新无则插入”INSERT INTO users (id, username, email) VALUES (1, zhangsan, newemailexample.com) ON DUPLICATE KEY UPDATE email VALUES(email);这条语句在遇到主键或唯一键冲突时会转而执行UPDATE操作而不是报错。VALUES(email)在这里指代VALUES子句中准备插入的email字段值。不过要注意MySQL 8.0.20开始官方建议用别名语法替代VALUES()函数INSERT INTO users (id, username, email) VALUES (1, zhangsan, newemailexample.com) AS new ON DUPLICATE KEY UPDATE email new.email;两者的效果一致新语法主要是为了兼容未来版本。还有一种“忽略式插入”INSERT IGNORE INTO users (id, username, email) VALUES (100, zhangsan, xx.com);遇到主键或唯一键冲突时直接跳过不报错、不更新只产生一个警告。适合批量同步数据的场景比如从外部文件导入里面可能有重复ID你希望把能插的插进去重复的跳过用INSERT IGNORE很合适。4.2 更新与删除更新数据UPDATE users SET age 26 WHERE username zhangsan;删除数据DELETE FROM users WHERE username lisi;这两个命令在开发环境随便跑无所谓但在生产环境UPDATE和DELETE不带WHERE条件就是事故级别的操作。执行之前务必先确认两件事一是WHERE条件是不是真的能筛选出你预期的那部分数据二是是否有事务可以回滚。习惯性做法是先在副本库或测试库跑一遍SELECT COUNT(*)确认影响行数再在主库执行UPDATE或DELETE。如果你想清空整张表有两条命令DELETE FROM users; TRUNCATE TABLE users;DELETE FROM逐行删除速度慢但支持WHERE而且会记录每一行的删除日志可以通过事务回滚TRUNCATE是直接重建表速度极快但无法回滚也不能带WHERE条件。生产环境清空大表时用TRUNCATE更高效但前提是你百分百确定这些数据不要了。另外TRUNCATE会重置自增ID计数器而DELETE不会。4.3 SELECT 查询的各种姿势查询是使用频率最高的操作也是最值得花时间精通的。基础的查询SELECT * FROM users; SELECT id, username, email FROM users WHERE age 20;SELECT *在开发调试时无所谓但在生产环境、写业务代码时不建议因为多余的字段会增加网络传输和内存压力。只查询需要用到的列是基本素养。排序与限制SELECT * FROM users ORDER BY created_at DESC LIMIT 10;ORDER BY默认升序ASCDESC是降序。LIMIT限制返回行数。这里有个实战小技巧分页查询大数据量时LIMIT 100000, 20这种写法会扫描前面10万行然后丢弃效率很低。可以改成基于上次最大ID的查询方式SELECT * FROM users WHERE id 100000 ORDER BY id ASC LIMIT 20;这样能利用主键索引快速定位性能提升非常明显。聚合查询SELECT COUNT(*), AVG(age), MAX(created_at), MIN(created_at) FROM users; SELECT age, COUNT(*) FROM users GROUP BY age;GROUP BY分组统计是最常用的聚合方式。GROUP BY配合HAVING可以对分组后的结果再做筛选SELECT age, COUNT(*) as cnt FROM users GROUP BY age HAVING cnt 1;注意HAVING和WHERE的区别WHERE是在分组之前对原始行做过滤HAVING是在分组之后对聚合结果做过滤。写了聚合条件却放在WHERE里SQL直接报错或者得不到预期结果这是新手经常踩的坑。模糊查询SELECT * FROM users WHERE username LIKE zhang%;LIKE中的%匹配任意长度的任意字符_匹配单个字符。需要注意LIKE %xx%这种以通配符开头的写法索引会失效全表扫描没跑。如果业务上有频繁的模糊搜索需求应该考虑用全文索引或者单独的搜索引擎比如Elasticsearch而不是靠LIKE硬扛。子查询和联表查询-- 子查询 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 100); -- 联表查询 SELECT u.username, o.order_no, o.amount FROM users u INNER JOIN orders o ON u.id o.user_id WHERE o.amount 100;INNER JOIN只返回两边都匹配的行LEFT JOIN返回左表所有行右表没有匹配时用NULL填充。对于订单、用户这类典型关联业务LEFT JOIN用得非常普遍。我见过很多人在写连接查询时不加表别名整个SQL长到几百个字符可读性极差。建议每个表都起一个简短的别名查询条件里所有字段都带别名前缀既清楚又不容易发生字段歧义。EXPLAIN是查询性能分析的核心工具放在后面的性能章节详细讲。5. 存储过程与视图把逻辑放进数据库5.1 存储过程的创建与调用存储过程就是预编译的SQL语句集合适合封装复杂的业务逻辑。一个简单的存储过程DELIMITER // CREATE PROCEDURE GetUserByEmail(IN input_email VARCHAR(100)) BEGIN SELECT id, username, email FROM users WHERE email input_email; END // DELIMITER ;调用方式CALL GetUserByEmail(zhangsanexample.com);DELIMITER //是一条客户端指令告诉MySQL客户端暂时用//作为语句分隔符因为存储过程内部有多条SQL需要用分号分隔如果不用自定义分隔符客户端会按分号提前结束命令。存储过程还可以带OUT输出参数DELIMITER // CREATE PROCEDURE CountUsersByAge(IN input_age INT, OUT user_count INT) BEGIN SELECT COUNT(*) INTO user_count FROM users WHERE age input_age; END // DELIMITER ; CALL CountUsersByAge(20, cnt); SELECT cnt;cnt是用户变量CALL之后可以用SELECT cnt查看输出值。要不要在业务里用存储过程业界一直有争议。我的态度是对于确实需要数据库事务内完成多步操作的场景存储过程可以减少网络往返简化应用代码但对于复杂业务逻辑存储过程的调试困难、版本不好管理、性能调优不方便这些缺点会随着项目变大越来越明显。一般业务团队我更倾向于把逻辑写在应用层数据库只负责数据存储和简单约束。查看和删除存储过程SHOW PROCEDURE STATUS; DROP PROCEDURE IF EXISTS GetUserByEmail;5.2 视图的使用视图是一张虚拟表本质上是保存了一条SQL查询不存储数据。创建视图CREATE VIEW v_user_order AS SELECT u.id, u.username, o.order_no, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id;创建之后就可以像查询普通表一样查询视图SELECT * FROM v_user_order WHERE amount 100;视图有几个实际价值。第一可以隐藏敏感字段比如给第三方提供只读的v_user_public视图不暴露email、phone等隐私列第二可以简化复杂查询把经常要用的联表逻辑封装成一个视图应用层查询语句变得简单第三当底层表结构调整时只要视图的定义跟着调整上层应用可能不需要改。但视图也有局限。性能上视图在执行时会被MySQL展开成底层SQL再执行如果你在视图上加了过滤条件优化器不一定能把这个条件下推到内部查询中有时候会扫描大量数据。所以视图适合逻辑复用不适合用来大幅度优化性能。修改视图ALTER VIEW v_user_order AS SELECT ...;删除视图DROP VIEW IF EXISTS v_user_order;6. 备份与迁移数据安全的关键操作6.1 mysqldump 逻辑备份mysqldump是最常用的逻辑备份工具输出的是SQL语句集合可以把数据重建出来。基础备份命令mysqldump -u root -p db_name db_name.sql备份多个库mysqldump -u root -p --databases db1 db2 dbs.sql备份所有库mysqldump -u root -p --all-databases all.sql生产环境执行备份我建议加上几个关键参数mysqldump -u root -p --single-transaction --routines --triggers --events db_name db_name.sql--single-transaction是InnoDB表在线备份的关键。它的原理是开启一个可重复读级别的事务在事务里做一致性快照备份过程中不会锁表业务可以继续写入。注意这个参数对MyISAM表无效MyISAM表还是会被锁住。--routines连带备份存储过程和函数--triggers连带备份触发器--events连带备份事件调度器。这三个不带上恢复之后你会发现自己辛辛苦苦写的存储过程全没了。只备份表结构不备份数据mysqldump -u root -p --no-data db_name db_name_structure.sql只备份数据不备份表结构mysqldump -u root -p --no-create-info db_name db_name_data.sql这几种方式在对现有库补数据、迁移结构的时候很常用。备份单张表mysqldump -u root -p db_name table_name table_name.sql备份压缩mysqldump -u root -p db_name | gzip db_name.sql.gz大库备份时压缩几乎是必须的文本形式的SQL文件压缩率很可观往往能压到原来的十分之一。6.2 导入恢复恢复备份文件mysql -u root -p db_name db_name.sql如果SQL文件里已经有CREATE DATABASE和USE语句用--databases参数备份的就有可以不用指定库名mysql -u root -p db_name.sql导入压缩包gunzip db_name.sql.gz | mysql -u root -p db_name导入大文件时除了用mysql客户端也可以进入MySQL命令行后执行source命令SOURCE /path/to/db_name.sql;这两种方式本质上是一样的source是客户端内执行的命令而已。恢复数据之前一定要想清楚几件事目标库的数据能否被覆盖备份文件的字符集和目标库是否一致备份的版本和当前版本是否兼容跨大版本迁移比如5.7到8.0时5.7的备份文件导入8.0偶尔会碰到语法不兼容的问题反过来也是一样。稳妥的做法是先在一个测试实例上做一次完整导入演练确认没问题再对生产环境操作。7. 性能排查与常见问题生产环境里的救命工具7.1 EXPLAIN 与慢查询一条SQL跑得慢第一件事就是看执行计划EXPLAIN SELECT u.username, o.order_no, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.age 20;EXPLAIN的输出关键看几个字段type、key、rows、Extra。type表示访问类型从好到差大致是systemconsteq_refrefrangeindexALL。看到ALL说明是全表扫描如果表很大且查询频繁这就是性能隐患。key表示实际用到的索引如果是NULL说明没有使用索引。rows是一个估算值表示MySQL认为需要扫描多少行才能得到结果。这个数字越大查询越慢。优化SQL的目标很大程度上就是让rows降下来。Extra字段最值得注意的是一些“坏味道”比如Using filesort文件排序和Using temporary使用临时表。出现这两种情况说明SQL的ORDER BY或GROUP BY没有利用到索引MySQL不得不额外做排序或建临时表数据量大时性能会急剧下降。慢查询日志是另一个重要工具。先看是否开启SHOW VARIABLES LIKE slow_query_log;没开启就开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2;long_query_time 2表示超过2秒的SQL会被记录到慢查询日志。生产环境一般设置在1-5秒之间日志里能看到Query_time、Rows_examined、Rows_sent这三项能帮你快速定位“扫描了海量行却只返回几行”这种低效查询。Rows_examined远大于Rows_sent是典型的索引失效SQL特征。查看当前正在执行的长查询SELECT * FROM information_schema.processlist WHERE time 10 AND command ! Sleep;这条SQL能找出执行超过10秒、且不是空闲状态的连接。找到了进程ID如果确实需要终止某个查询用KILL 12345;12345是processlist里的ID。生产环境杀查询要非常谨慎确认这条SQL不是关键业务正在执行的再动手。等锁的查询杀不杀得掉也要看锁的状态有时候杀了会话后事务已经持有的锁要等事务回滚才能释放。7.2 常见错误速查表命令用得多各种报错也见得多。我把最常见的几种整理成一张表方便查询。错误信息原因解决方案ERROR 1045 (28000): Access denied for user用户密码错误或该用户不允许从当前主机连接检查用户名、密码、用户对应的host范围ERROR 1049 (42000): Unknown database数据库不存在或拼写错误用SHOW DATABASES确认库名ERROR 1142 (42000): SELECT command denied用户没有相应权限用GRANT授权ERROR 1062 (23000): Duplicate entry主键或唯一键冲突检查数据是否重复或用INSERT IGNORE、ON DUPLICATE KEY UPDATEERROR 1264: Out of range value数值超出字段范围修改字段类型为更大范围ERROR 1366: Incorrect string value插入的字符与表字符集不兼容统一使用utf8mb4字符集ERROR 1415: Not allowed to return a result set from a trigger触发器内不能返回结果集移除触发器中的SELECTERROR 2002: Cant connect to local MySQL serverMySQL服务未启动或socket文件路径不对检查服务状态确认mysql.sock路径ERROR 2013: Lost connection to MySQL server连接超时或网络不稳定检查wait_timeout、max_allowed_packet排查网络问题Authentication plugin caching_sha2_password cannot be loaded客户端工具太老不支持8.0新版认证升级客户端驱动或将用户改为mysql_native_passwordERROR 1366这个报错在中文环境下极其常见尤其是老项目里表字符集还是latin1或utf8插入emoji或者生僻字就直接报错。解决思路分三步确认表的字符集确认连接的字符集然后统一调整为utf8mb4。查看连接字符集用SHOW VARIABLES LIKE character_set%;7.3 几个实操心得与避坑心得一改变量影响了什么要想清楚。SET GLOBAL设置的变量是全局的只对新连接生效不会影响已经存在的连接。你开了一个终端改了SET GLOBAL long_query_time2再用另一个已经打开的终端去查可能发现根本没变因为那个终端用的是旧的会话值。这会让你误以为设置没生效。改完全局变量要么重开连接要么用SET SESSION针对当前会话设置。**心得二命令行里写密码不是好习惯。**我见过有人在脚本里直接写mysql -u root -p123456这种脚本如果传到代码仓库、或者被其他人看到等于把数据库密码直接交出去了。稍微好一点的做法是用配置文件[client] userroot password123456放到~/.my.cnf同时设置文件权限为600chmod 600 ~/.my.cnf这样MySQL客户端会自动读取该配置命令行里就不用暴露密码了。**心得三批量更新数据时多用事务控制。**如果你要在命令行里执行大量UPDATE或者在一个脚本里循环更新几千条数据一定要显式开启事务START TRANSACTION; UPDATE users SET age age 1 WHERE id 1000; -- 执行完检查一下影响行数、数据对不对 COMMIT;这样如果中途发现更新错了还能用ROLLBACK回滚避免酿成不可挽回的事故。我在实际工作中不止一次遇到过“跑完UPDATE才发现条件写错”的情况要不是有事务包裹后果很难收拾。**心得四善用扩展插入语法。**批量插入数据时在INSERT语句后面加VALUES多行写法以及INSERT IGNORE、ON DUPLICATE KEY UPDATE这类扩展语法能极大提升数据初始化和同步的效率。特别是把A库的数据往B库导的场景你写一段SQL就能完成去重和更新不用在应用层写一堆循环判断。心得五执行结构变更之前先备份。ALTER TABLE改字段类型、删索引、重建表这类操作一旦执行就没有后悔药。就算你在本地数据库验证过生产环境的表数据和本地也未必一样。正规的做法是操作前先用mysqldump把表备份出来操作后再跑一遍关键查询验证。这套流程看着笨重但出事故的时候就知道它值多少钱了。我个人在实际操作中还有一个习惯凡是手写命令去操作生产库先用EXPLAIN或SELECT COUNT(*)验证条件把影响范围确认清楚再在事务里执行并立即检查结果确认无误后才提交。这套流程帮我避开了好几次差点酿成事故的操作。命令行这东西用熟了之后就像身体的一部分MySQL的各种运维场景基本都能轻松应对。