这一篇的定位:你的 MySQL 日常工具箱
环境装好了(MySQL 安装与初始配置)、账号分好了(MySQL 用户权限与安全管理),剩下的就是天天要敲的命令。这一篇不讲原理,只讲操作:按「连接 → 看状态 → 管结构 → 导数据 → 控事务」的顺序,把高频命令一次理清。SQL 语法本身不熟的,先补 SQL 基础速成 再回来看。
连接与退出:先把姿势摆对
# 本机 socket 连接(同机运维最常用)mysql -uroot -p
# TCP 连接远程或 Docker 映射端口mysql -h 127.0.0.1 -P 3306 -uapp_rw -p appdb
# 连接后直接执行一条 SQL 就退出,适合写脚本mysql -h 127.0.0.1 -uroot -p -e "SHOW PROCESSLIST;"几个容易忽略的点:
-p后面不要直接跟密码写在命令行里,会留在 shell 历史和进程列表中;交互输入或使用登录路径(mysql_config_editor)更稳妥。- 进错库不用重连,客户端内
USE appdb;即可切换。 - 退出用
exit或quit,Ctrl+C 在新版客户端里是中断当前输入行,别误以为卡死。
连接后第一件事,看清自己是谁、连的哪台:
SELECT USER(), CURRENT_USER(), @@hostname, @@port, VERSION();USER() 是你声称的身份,CURRENT_USER() 是实际命中的账号——排权限问题时看后者,这个区别在上一篇已经强调过。
看状态:SHOW 家族速查
MySQL 运维的一半信息来自 SHOW 命令,先给一张速查表:
| 命令 | 看什么 | 高频场景 |
|---|---|---|
SHOW DATABASES; | 全部库 | 接手新实例先盘点 |
SHOW TABLES; | 当前库的表 | 确认表是否存在 |
SHOW CREATE TABLE orders\G | 完整建表语句 | 对比结构、复制表定义 |
SHOW FULL PROCESSLIST; | 正在执行的连接与 SQL | 排查卡顿、锁等待第一现场 |
SHOW STATUS LIKE 'Threads%'; | 连接与线程计数 | 判断是否快打满连接数 |
SHOW VARIABLES LIKE 'max_connections'; | 参数当前值 | 确认配置是否生效 |
SHOW ENGINE INNODB STATUS\G | InnoDB 全景:事务、锁、死锁 | 死锁与锁等待分析 |
SHOW MASTER STATUS; / SHOW REPLICA STATUS\G | 主从复制状态 | 复制中断排查 |
SHOW GRANTS FOR 'u'@'h'; | 某账号权限 | 授权后复核 |
SHOW WARNINGS; | 上一条语句的警告 | SQL 执行「成功但有警告」时必看 |
输出太长时记住两个技巧:语句结尾用 \G 代替 ; 可以纵向排版(看长行字段必备);客户端内 pager less 可以分页翻看。
管理库表结构
-- 建库建表(字符集显式写明,原因见安装篇)CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;USE shop;
CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10, 2) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user (user_id)) ENGINE = InnoDB;
-- 看结构DESC orders;SHOW INDEX FROM orders;
-- 改结构ALTER TABLE orders ADD COLUMN remark VARCHAR(255) NULL AFTER status;ALTER TABLE orders MODIFY COLUMN remark VARCHAR(500) NULL;ALTER TABLE orders ADD INDEX idx_created (created_at);WARNING
ALTER TABLE在大表上可能是分钟级甚至小时级操作,且老版本会锁表。生产环境改结构前先确认表行数与业务低峰期,千万行级的大表优先考虑在线 DDL 工具(如 gh-ost、pt-online-schema-change)或ALGORITHM=INSTANT是否适用,不要在业务高峰直接敲回车。
删除操作单独提醒一句:DROP TABLE、TRUNCATE TABLE 都不可回滚,执行前先确认当前库:
SELECT DATABASE();在错误的库里执行 DROP 是运维事故的经典开场,养成先 SELECT DATABASE() 再动手的肌肉记忆。
数据导入与导出
逻辑导出:mysqldump
# 导出整个库(含建表与数据)mysqldump -h 127.0.0.1 -uroot -p --single-transaction --routines --triggers shop > shop_backup.sql
# 只导出结构不要数据mysqldump -h 127.0.0.1 -uroot -p --no-data shop > shop_schema.sql
# 只导出某几张表的数据mysqldump -h 127.0.0.1 -uroot -p --single-transaction shop orders order_items > orders.sql--single-transaction 让 InnoDB 在一致性快照下导出,不锁表,是线上导出的标配参数。导入就是反向灌入:
mysql -h 127.0.0.1 -uroot -p shop < shop_backup.sql完整的备份策略(全量、增量、binlog 时间点恢复)不在这里展开,系列里有专篇 数据库备份与恢复全攻略。
表格数据:CSV 互通
-- 导出为 CSV(需要 FILE 权限,且受 secure_file_priv 目录限制)SELECT id, user_id, amount FROM ordersINTO OUTFILE '/var/lib/mysql-files/orders.csv'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY '\n';
-- 导入 CSVLOAD DATA INFILE '/var/lib/mysql-files/orders.csv'INTO TABLE ordersFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY '\n'(id, user_id, amount);客户端本地文件版本是 LOAD DATA LOCAL INFILE,需要服务端和客户端同时允许,安全上更敏感,用前确认开关状态。
事务与会话控制
-- 手动事务四件套START TRANSACTION;UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;COMMIT; -- 或 ROLLBACK;
-- 查看当前会话的事务隔离级别SELECT @@transaction_isolation;
-- 找出长事务(超过 60 秒还没提交的)SELECT trx_id, trx_started, trx_mysql_thread_id, trx_queryFROM information_schema.innodb_trxWHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;
-- 必要时终止某个连接(需要相应权限)KILL 12345;长事务会拖住 Undo 日志回收、放大锁等待,发现长事务先联系业务确认再 KILL,不要看到就杀——杀掉一个正在跑批的事务,回滚本身可能更耗时。
用 information_schema 做盘点查询
SHOW 适合看单个对象,批量盘点则要用系统库 information_schema 写 SQL 查。最常用的是这条「全库表体积排行」:
SELECT table_schema AS db, table_name AS tbl, table_rows AS est_rows, ROUND(data_length / 1024 / 1024, 1) AS data_mb, ROUND(index_length / 1024 / 1024, 1) AS index_mb, ROUND((data_length + index_length) / 1024 / 1024, 1) AS total_mbFROM information_schema.tablesWHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')ORDER BY total_mb DESCLIMIT 20;注意 table_rows 对 InnoDB 是估算值不是精确行数,要精确值得 SELECT COUNT(*)(大表慎用,正好可以用上面这条先挑出大表再逐个确认)。其它高频盘点:
-- 哪些表还没有主键(应该全部有,这条结果最好为 0 行)SELECT t.table_schema, t.table_nameFROM information_schema.tables tLEFT JOIN information_schema.table_constraints c ON c.table_schema = t.table_schema AND c.table_name = t.table_name AND c.constraint_type = 'PRIMARY KEY'WHERE t.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') AND t.table_type = 'BASE TABLE' AND c.constraint_name IS NULL;
-- 每个库占多大空间SELECT table_schema, COUNT(*) AS tables_count, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS total_mbFROM information_schema.tablesGROUP BY table_schemaORDER BY total_mb DESC;这类盘点 SQL 建议存成自己的运维脚本库,接手任何一台新实例先跑一遍,库表规模、缺主键的表、体积大户立刻心里有数。
客户端里的小效率工具
\s:查看连接状态(字符集、已运行时长等)。tee /tmp/session.log:把当前会话输出留档,排障复盘很好用。--auto-rehash默认开启,表名列名可以 Tab 补全;连大库嫌慢可加-A关掉。- 批量跑脚本文件:
source /path/to/init.sql(客户端内)或 shell 里mysql db < file.sql。 - 结果竖排查看宽表:查询后加
\G,例如SELECT * FROM orders LIMIT 1\G。
常见问题
Q:中文存进去变问号或乱码?
连接、库、表、列四层字符集都要 utf8mb4。连接层可在登录后 SET NAMES utf8mb4; 临时修正,根治是按安装篇把服务端与建表默认值都定死。
Q:SHOW PROCESSLIST 里看到大量 Sleep 连接正常吗?
Sleep 是空闲连接,应用连接池常驻就会有。数量稳定、无堆积增长就正常;持续逼近 max_connections 才需要处理。
Q:执行 SQL 报 ERROR 1820 (HY000): You must reset your password?
账号密码已过期。用管理员账号执行 ALTER USER ... IDENTIFIED BY ... 重置,或按策略调整该账号的过期设置,细节见 MySQL 用户权限与安全管理。
Q:大查询把客户端卡住了怎么办?
另开一个连接 SHOW FULL PROCESSLIST 找到对应 Id 再 KILL QUERY <id>(只杀查询不断连接)或 KILL <id>(断连接)。
小结
- 连接先分清
USER()与CURRENT_USER(),排障少走弯路。 SHOW家族是运维的主力:PROCESSLIST 看现场、INNODB STATUS 看锁、CREATE TABLE 看定义。- mysqldump 加
--single-transaction是线上导出的标准姿势;结构变更与 DROP 类操作先确认库、再挑时间。 - 事务与长事务查询要会看,KILL 之前先确认。
命令顺手之后,下一个话题自然是性能:MySQL 索引与慢查询优化 把慢查询日志和 EXPLAIN 串起来用。