起源
MySQL 提供 mysql 命令行客户端作为用户与数据库交互的入口,覆盖从连接数据库、库表管理、数据增删改查到权限控制、导入导出、诊断排查的完整操作链路。掌握常用命令是数据库日常运维与开发的基础。
原理
一、客户端连接机制
- mysql 客户端通过两种通道连接服务器
- 本地 socket:默认走
/var/lib/mysql/mysql.sock,速度快 - 远程 TCP:通过 IP 加端口 3306 连接
- 连接参数由用户名、主机、密码、数据库四要素决定
- 认证方式受账号配置的认证插件影响(如 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 |
总结
- mysql 客户端通过本地 socket 或远程 TCP 连接服务器
- 库、表、数据三层操作覆盖基础管理,CRUD 与查询进阶支撑开发
- 权限管理基于账号加授权,遵循最小权限原则
- 导入导出使用 mysqldump 与 LOAD DATA,配合 source 实现数据迁移
- 诊断依赖 SHOW 系列、EXPLAIN、PROCESSLIST,慢查询通过日志定位
- 日志体系按错误、通用、慢查询、binlog 四类划分,binlog 是复制与恢复的核心载体