MySQL 常用命令速查：库表、用户权限、备份导入全场景

数据库·2026-09-23·879 字·约 9 分钟

一、结论先给

MySQL 命令分两类：查询类（随便跑）和变更类（跑之前先想好回滚）。真正出事的从来不是不会写 SQL，而是没带 WHERE 的 UPDATE、没先备份的 DROP、没限流的大表 ALTER。

操作 危险等级 前置动作
SELECT / SHOW 安全 无
INSERT / UPDATE / DELETE 中 先 SELECT 用同样 WHERE 看影响行数
ALTER TABLE 高 先看表大小，大表用 pt-online-schema-change
DROP / TRUNCATE 极高 先备份，且二次确认库名
GRANT / REVOKE 中 MySQL 8 不再支持 GRANT 隐式建用户

保命配置：给自己开一个不带自动提交的会话习惯。

SET autocommit = 0;      -- 手动提交，改完先 SELECT 看结果再 COMMIT / ROLLBACK
-- 或者开启安全模式，禁止无 WHERE 的 UPDATE/DELETE（仅命令行客户端有效）
SET sql_safe_updates = 1;

👉 命令记不全查 Linux 命令速查；慢查询日志分析用 日志分析工具。

二、连接与基本信息

# 连接
mysql -h 127.0.0.1 -P 3306 -u root -p
mysql -u root -p -e "SELECT VERSION();"        # 非交互执行单条
mysql -u root -p --database=app < init.sql     # 执行脚本
mysql -u root -p --default-character-set=utf8mb4

SELECT VERSION();                 -- 版本
SELECT @@version_comment;         -- 发行版（MySQL / Percona / MariaDB）
SELECT DATABASE(), USER(), @@port, @@datadir;
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE '%time_zone%';
SELECT @@sql_mode;                -- 严不严格，迁移时经常打架
SHOW ENGINES;

字符集建议全链路 utf8mb4（注意不是 utf8，MySQL 的 utf8 是 3 字节的伪 UTF-8，存不了 emoji）：

-- 检查有没有踩坑
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_COLLATION LIKE 'utf8_%' AND TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema');

三、库与表

-- 建库（务必显式指定字符集）
CREATE DATABASE app DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 建表模板
CREATE TABLE `user` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`   VARCHAR(64)  NOT NULL,
  `mobile`     CHAR(11)     NOT NULL DEFAULT '',
  `status`     TINYINT      NOT NULL DEFAULT 1,
  `balance`    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看
SHOW DATABASES;
SHOW TABLES;
SHOW CREATE TABLE `user`\G          -- \G 竖排显示，看建表语句必用
DESC `user`;
SHOW TABLE STATUS LIKE 'user'\G     -- 行数（近似）、数据大小、引擎

-- 改表
ALTER TABLE `user` ADD COLUMN `email` VARCHAR(128) NOT NULL DEFAULT '' AFTER `mobile`;
ALTER TABLE `user` MODIFY COLUMN `mobile` VARCHAR(20) NOT NULL DEFAULT '';
ALTER TABLE `user` DROP COLUMN `email`;
RENAME TABLE `user` TO `t_user`;

大表加字段的代价要先估：

-- 看表有多大（Data_length + Index_length）
SELECT
  TABLE_NAME,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 1) AS 'Size(MB)',
  TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC LIMIT 10;

超过几 GB 的表直接 ALTER 会锁表很久，MySQL 8 支持 ALGORITHM=INSTANT（加列到末尾等有限场景），否则用 pt-online-schema-change 或 gh-ost。

四、用户与权限（MySQL 8 语法变了）

最大的坑：MySQL 8.0 之后 GRANT 不再隐式创建用户，必须先 CREATE USER。

-- ✅ MySQL 8 正确写法
CREATE USER 'app'@'10.0.%' IDENTIFIED BY 'Str0ng_Pass!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'10.0.%';

-- ❌ 这会报错（8.0 不再支持）
GRANT ALL ON app.* TO 'app'@'%' IDENTIFIED BY 'xxx';

-- 建只读账号
CREATE USER 'reader'@'%' IDENTIFIED BY 'Read_0nly!';
GRANT SELECT ON app.* TO 'reader'@'%';

-- 查看与回收
SHOW GRANTS FOR 'app'@'10.0.%';
REVOKE DELETE ON app.* FROM 'app'@'10.0.%';
DROP USER 'app'@'10.0.%';

密码与认证插件：

-- 8.0 默认 caching_sha2_password，老客户端（PHP 5.x、老 JDBC）连不上
ALTER USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY 'Str0ng_Pass!';
-- 或全局改回（my.cnf）
-- default_authentication_plugin=mysql_native_password

-- 改密码
ALTER USER 'app'@'%' IDENTIFIED BY 'New_Pass!';
-- root 忘记密码时（需重启）：mysqld --skip-grant-tables --skip-networking &

账号三原则：最小权限、限定来源网段、不同应用不同账号（出问题时能立刻定位是哪个服务）。

五、增删改查（带安全习惯）

-- 危险操作先预演：把 UPDATE 的 WHERE 拿去 SELECT 数一遍
SELECT COUNT(*) FROM `user` WHERE status = 0;      -- 先确认影响范围
UPDATE `user` SET status = 9 WHERE status = 0;     -- 再改

-- 带 LIMIT 的删除，避免一次锁太多行
DELETE FROM log WHERE created_at < '2026-01-01' LIMIT 5000;

-- 大批量删除用循环（每次 5000，间隔 0.5 秒）
-- 见下方脚本

-- 插入与冲突处理
INSERT INTO `user` (username, mobile) VALUES ('afei', '13800138000')
  ON DUPLICATE KEY UPDATE mobile = VALUES(mobile);

-- 批量插入
INSERT INTO `user` (username, mobile) VALUES ('a','1'), ('b','2'), ('c','3');

-- 只改一行
UPDATE `user` SET balance = balance - 100 WHERE id = 1 LIMIT 1;

分批删除脚本（避免长事务和主从延迟）：

#!/usr/bin/env bash
# 用法：./del.sh "created_at < '2026-01-01'"
where="${1:?用法: $0 \"条件\"}"
while : ; do
  n=$(mysql -u root -p"$PW" -N -B -e "DELETE FROM app.log WHERE $where LIMIT 5000; SELECT ROW_COUNT();")
  echo "删除 $n 行"
  [ "$n" -eq 0 ] && break
  sleep 0.5
done

六、导入导出

# 导出整个库（含建表语句，最常用）
mysqldump -u root -p --single-transaction --routines --triggers --events app > app.sql

# 只导结构
mysqldump -u root -p --no-data app > schema.sql

# 只导数据（tab 分隔，导入极快）
mysqldump -u root -p -t --tab=/tmp/app_data app

# 带条件导出
mysqldump -u root -p app `user` --where="id > 10000" > user_part.sql

# 压缩导出
mysqldump -u root -p --single-transaction app | gzip > app.sql.gz

# 导入
mysql -u root -p app < app.sql
gunzip < app.sql.gz | mysql -u root -p app

--single-transaction 是 InnoDB 不锁表导出的关键参数（一致性快照），没有它，导出期间会锁表。

导出成 CSV（给运营/财务看）：

SELECT id, username, mobile, created_at
INTO OUTFILE '/tmp/user.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM `user`;
-- 若报 secure_file_priv 限制，查：SHOW VARIABLES LIKE 'secure_file_priv';

导入 CSV：

LOAD DATA INFILE '/tmp/user.csv'
INTO TABLE `user`
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
(id, username, mobile, created_at);

七、连接、进程与锁（排障必查）

SHOW PROCESSLIST;                    -- 前 100 条，看谁在跑
SHOW FULL PROCESSLIST;               -- 完整 SQL
SELECT * FROM information_schema.PROCESSLIST
  WHERE COMMAND != 'Sleep' AND TIME > 10 ORDER BY TIME DESC;

-- 连接数水位（超过 max_connections 的 80% 就该告警）
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 按用户统计连接
SELECT USER, COUNT(*) FROM information_schema.PROCESSLIST GROUP BY USER;

-- 杀掉某个查询（只杀查询不杀连接用 KILL QUERY）
KILL QUERY 12345;
KILL 12345;                          -- 连连接一起杀

-- 当前锁等待
SELECT * FROM performance_schema.data_lock_waits;
SHOW ENGINE INNODB STATUS\G          -- 看 LATEST DETECTED DEADLOCK 段

批量生成 kill 语句（连接打满时救命）：

-- 杀掉 Sleep 超过 300 秒的连接
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Sleep' AND TIME > 300 AND USER NOT IN ('root','system user');

八、运行状态速查

-- QPS / TPS（间隔 1 秒取差值）
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';
-- 简化版：
SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Questions','Com_select','Com_insert','Com_update','Com_delete','Slow_queries','Threads_connected');

-- InnoDB 缓冲池命中率（应 > 99%）
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

-- 临时表落盘比例（太高要调 tmp_table_size）
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- 表缓存与打开表数
SHOW GLOBAL STATUS LIKE 'Open_tables';
SHOW GLOBAL VARIABLES LIKE 'table_open_cache';

一条汇总查询：

SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Uptime') AS 运行秒数,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Questions') AS 总查询数,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Slow_queries') AS 慢查询数,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_connected') AS 当前连接;

👉 慢查询日志暴涨会连带把磁盘写满，排查思路见 Linux 磁盘满了怎么排查；容量规划用 磁盘容量计算器。

九、常见误区

  1. 用 utf8 而不是 utf8mb4：存 emoji 直接 Incorrect string value 报错。
  2. MySQL 8 里用 GRANT ... IDENTIFIED BY 建用户：语法直接报错，必须先 CREATE USER。
  3. mysqldump 不加 --single-transaction：导出期间锁表，业务全卡。
  4. DELETE 一次删几百万行：长事务 + 大 undo + 主从延迟，应该分批。
  5. ALTER 大表直接跑：锁表几十分钟，应该在低峰期或用在线改表工具。
  6. 给应用账号 ALL PRIVILEGES 且 host='%'：一次脱库就是全库沦陷。

十、几条纪律

  1. 任何 UPDATE/DELETE 先写 SELECT COUNT(*) 用同一 WHERE 验证范围。
  2. 命令行客户端开 sql_safe_updates，或者显式 BEGIN; … COMMIT;。
  3. 库表字符集统一 utf8mb4，sql_mode 在环境间保持一致。
  4. 每个应用独立账号、限定网段、最小权限。
  5. 大表结构变更走低峰期 + 在线改表工具，先在从库演练。
  6. 导出必须带 --single-transaction，导入前确认目标库名。

十一、延伸阅读

👉 相关在线工具：Linux 命令速查 · 日志分析 · 磁盘容量计算，免登录、纯前端。

文中提到的工具

🐬
MySQL 命令速查MySQL 8.0 常用命令速查：增删改查、索引、权限、锁、备份与主从
→🐧
Linux 命令速查Linux 常用命令速查手册，含用法解析和示例
→📋
日志分析粘贴 Java/Nginx/MySQL/Redis/Docker 日志，自动找出错误点、原因与修复建议
→💾
存储容量计算器计算磁盘/存储容量，支持 RAID 配置估算
→

相关阅读

数据库MySQL 备份与恢复：mysqldump、XtraBackup 与 binlog 时间点恢复→数据库MySQL 慢查询与索引优化：EXPLAIN 怎么看、索引为什么会失效→数据库MySQL 主从复制配置与延迟排查：从搭建到 Seconds_Behind_Master 不准→

还有 65 个免费在线工具

纯前端实现，不用注册，数据不上传服务器。

浏览全部工具