起源

MySQL 提供 mysql 命令行客户端作为用户与数据库交互的入口,覆盖从连接数据库、库表管理、数据增删改查到权限控制、导入导出、诊断排查的完整操作链路。掌握常用命令是数据库日常运维与开发的基础。

原理

一、客户端连接机制

  1. mysql 客户端通过两种通道连接服务器
  • 本地 socket:默认走 /var/lib/mysql/mysql.sock,速度快
  • 远程 TCP:通过 IP 加端口 3306 连接
  1. 连接参数由用户名、主机、密码、数据库四要素决定
  2. 认证方式受账号配置的认证插件影响(如 mysql_native_password、caching_sha2_password)

二、命令分类

按操作对象将命令归类:

类别 覆盖内容
连接类 登录、切换、退出
库级操作 创建、删除、切换、查看
表级操作 创建、结构、修改、删除
数据操作 增删改查 CRUD
查询分析 条件、排序、分组、聚合、连接
权限管理 用户、授权、回收
导入导出 mysqldump、LOAD DATA、SELECT INTO
诊断命令 SHOW 状态、EXPLAIN、日志

三、MySQL 四类日志定位

日志 作用 场景
错误日志 记录启动、运行、关闭的错误 故障排查
通用查询日志 记录所有 SQL 请求 审计、排错,生产一般关闭
慢查询日志 记录执行超时的 SQL SQL 性能优化
binlog 记录所有数据变更 主从复制、数据恢复

实现

一、连接与登录

# 本地 socket 连接(默认)
mysql -u root -p

# 指定主机与端口远程连接
mysql -h 192.168.1.10 -P 3306 -u user01 -p

# 指定数据库直接连接
mysql -u root -p mydb

# 使用 socket 文件连接
mysql -u root -p -S /var/lib/mysql/mysql.sock

# 免密配置文件(/etc/mysql/conf.d/ 下)
[client]
host=192.168.1.10
user=user01
password=你的密码

连接后在客户端内切换库、查看当前状态:

-- 查看当前连接的数据库和用户
SELECT DATABASE(), USER(), VERSION();

-- 切换数据库
USE mydb;

-- 退出客户端
exit;

二、库级操作

-- 查看所有数据库
SHOW DATABASES;

-- 创建数据库(指定字符集)
CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4;

-- 删除数据库
DROP DATABASE mydb;

-- 切换数据库
USE mydb;

-- 查看库大小占比
SHOW DATABASES LIKE 'my%';

三、表级操作

-- 查看当前库所有表
SHOW TABLES;

-- 创建表
CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  status TINYINT DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看表结构
DESC users;
SHOW CREATE TABLE users;

-- 修改表(增加、删除、修改列)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users MODIFY COLUMN name VARCHAR(80);

-- 删除表
DROP TABLE users;

-- 清空表(保留结构)
TRUNCATE TABLE users;

四、数据增删改查

-- 插入
INSERT INTO users (name, email) VALUES ('张三', 'zhang@example.com');

-- 批量插入
INSERT INTO users (name, email) VALUES
  ('李四', 'li@example.com'),
  ('王五', 'wang@example.com');

-- 更新
UPDATE users SET status = 2 WHERE id = 1;

-- 删除
DELETE FROM users WHERE id = 1;

-- 查询全部
SELECT * FROM users;

五、查询进阶

-- 条件查询
SELECT * FROM users WHERE status = 1 AND name LIKE '张%';

-- 字段筛选与去重
SELECT DISTINCT status FROM users;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 分页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 聚合统计
SELECT status, COUNT(*) AS cnt FROM users GROUP BY status;

-- 连接查询
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

-- 子查询
SELECT * FROM users WHERE id IN
  (SELECT user_id FROM orders WHERE total > 100);

六、常用函数

-- 字符串函数
SELECT UPPER(name), CONCAT(id, '-', name) FROM users;

-- 日期函数
SELECT NOW(), DATE_FORMAT(created_at, '%Y-%m-%d') FROM users;

-- 数值函数
SELECT ROUND(123.456, 2), ABS(-5), CEILING(1.2);

-- 条件函数
SELECT id, IF(status = 1, '启用', '禁用') FROM users;

-- 聚合函数(配合 GROUP BY)
SELECT COUNT(*), SUM(total), AVG(total), MAX(total), MIN(total) FROM orders;

七、权限管理

-- 创建用户
CREATE USER 'user01'@'%' IDENTIFIED WITH mysql_native_password BY 'User@123456';

-- 授权
GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO 'user01'@'%';

-- 授予全部权限
GRANT ALL PRIVILEGES ON *.* TO 'user01'@'%' WITH GRANT OPTION;

-- 刷新权限
FLUSH PRIVILEGES;

-- 回收权限
REVOKE SELECT ON mydb.* FROM 'user01'@'%';

-- 查看用户权限
SHOW GRANTS FOR 'user01'@'%';

-- 修改密码
ALTER USER 'user01'@'%' IDENTIFIED BY '新密码';

-- 删除用户
DROP USER 'user01'@'%';

-- 查看所有用户
SELECT user, host, plugin FROM mysql.user;

八、数据导入导出

# 导出整个库
mysqldump -u root -p mydb > mydb.sql

# 导出指定表
mysqldump -u root -p mydb users > users.sql

# 导出多个库
mysqldump -u root -p --databases db1 db2 > all.sql

# 只导出结构不带数据
mysqldump -u root -p --no-data mydb > struct.sql

# InnoDB 热备不加锁
mysqldump -u root -p --single-transaction mydb > mydb.sql
# 导入 SQL 文件
mysql -u root -p mydb < mydb.sql

# 或用 source 导入
mysql -u root -p mydb
-- 客户端内 source 导入
SOURCE /path/mydb.sql;

-- 表级数据导入(LOAD DATA)
LOAD DATA LOCAL INFILE '/path/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

-- 导出查询结果为文件
SELECT * FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

九、诊断命令

-- 查看配置变量
SHOW VARIABLES;
SHOW VARIABLES LIKE '%buffer%';

-- 查看运行状态
SHOW STATUS;
SHOW ENGINE INNODB STATUS;

-- 查看当前连接
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 终止慢查询会话
KILL 12345;

-- 查看表状态
SHOW TABLE STATUS LIKE 'users';

查询分析:

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE email = 'zhang@example.com';
-- 关注 type、key、rows、Extra 字段

-- 打开耗时统计
SET profiling = 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

十、事务操作

-- 开启事务
START TRANSACTION;
COMMIT;
ROLLBACK;

-- 查看隔离级别与当前事务隔离
SHOW VARIABLES LIKE 'transaction_isolation';
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

十一、日志体系

1. 错误日志

记录启动、运行、关闭过程中的错误与警告,是故障排查的第一来源。

# 查看配置
SHOW VARIABLES LIKE 'log_error';

# 实时查看错误日志
sudo tail -f /var/log/mysql/error.log

2. 通用查询日志

记录所有客户端请求的 SQL,生产环境资源开销大,一般关闭,仅在审计或排错时临时开启。

-- 查看状态
SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'general_log_file';

-- 临时开启
SET GLOBAL general_log = ON;
SET GLOBAL general_log = OFF;

3. 慢查询日志

记录执行时间超过 long_query_time 的 SQL,用于定位效率低下的查询。

-- 查看慢日志配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
# my.cnf 开启慢查询
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
# 查看慢日志文件
sudo tail -f /var/log/mysql/mysql-slow.log

4. binlog 二进制日志

记录所有数据变更操作,是主从复制与数据恢复的载体。

-- 查看 binlog 是否开启及文件列表
SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;

-- 查看当前 binlog 位点
SHOW MASTER STATUS;

-- 查看 binlog 事件
SHOW BINLOG EVENTS IN 'binlog.000001';

binlog 文件内容使用 mysqlbinlog 解析:

# 解析 binlog 内容
mysqlbinlog /var/lib/mysql/binlog/bin.000001

# 指定起始位置解析
mysqlbinlog --start-position=154 /var/lib/mysql/binlog/bin.000001

# 按时间范围解析
mysqlbinlog --start-datetime="2025-12-02 10:00:00" --stop-datetime="2025-12-02 12:00:00" /var/lib/mysql/binlog/bin.000001

# 基于 binlog 恢复数据库
mysqlbinlog /var/lib/mysql/binlog/bin.000001 | mysql -u root -p

binlog 三种格式:

格式 含义 特点
STATEMENT 记录原始 SQL 语句 日志小,但有不确定性函数可能复现不一致
ROW 记录每行变更细节 一致性最好,日志较大,默认推荐
MIXED 自动混用两种 默认 STATEMENT,无法复现时自动切换 ROW

十二、常用组合命令速查表

场景 命令
本地登录 mysql -u root -p
远程连接 mysql -h IP -P 3306 -u user -p
查看所有库 SHOW DATABASES;
查看所有表 SHOW TABLES;
查看表结构 DESC 表名;
导出整个库 mysqldump -u root -p mydb > mydb.sql
导入 SQL mysql -u root -p mydb < mydb.sql
查看执行计划 EXPLAIN SELECT ...;
查看当前连接 SHOW FULL PROCESSLIST;
查看 binlog mysqlbinlog /var/lib/mysql/binlog/bin.000001
实时看慢日志 tail -f /var/log/mysql/mysql-slow.log

总结

  1. mysql 客户端通过本地 socket 或远程 TCP 连接服务器
  2. 库、表、数据三层操作覆盖基础管理,CRUD 与查询进阶支撑开发
  3. 权限管理基于账号加授权,遵循最小权限原则
  4. 导入导出使用 mysqldump 与 LOAD DATA,配合 source 实现数据迁移
  5. 诊断依赖 SHOW 系列、EXPLAIN、PROCESSLIST,慢查询通过日志定位
  6. 日志体系按错误、通用、慢查询、binlog 四类划分,binlog 是复制与恢复的核心载体