MySQL 日常管理命令

连接与断开连接

1. 连接 MySQL 服务器

bash

# 基本连接方式
mysql -u username -p

# 指定主机和端口
mysql -u username -p -h hostname -P port

# 使用套接字文件连接（本地连接）
mysql -u username -p --socket=/path/to/mysql.sock

# 连接特定数据库
mysql -u username -p database_name

# 连接时执行命令
mysql -u username -p -e "SELECT * FROM table_name"

# 使用密码文件连接
mysql --defaults-extra-file=/path/to/password.cnf

2. 断开连接

在 MySQL 命令行中，可以使用以下命令断开连接：

sql

EXIT;
-- 或
QUIT;
-- 或使用快捷键 Ctrl+D

数据库管理

1. 查看数据库列表

sql

SHOW DATABASES;

2. 创建数据库

sql

CREATE DATABASE database_name;

-- 指定字符集和校对规则
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 如果数据库不存在则创建
CREATE DATABASE IF NOT EXISTS database_name;

3. 选择数据库

sql

USE database_name;

4. 删除数据库

sql

DROP DATABASE database_name;

-- 如果数据库存在则删除
DROP DATABASE IF EXISTS database_name;

5. 查看当前数据库

sql

SELECT DATABASE();

6. 查看数据库创建语句

sql

SHOW CREATE DATABASE database_name;

表管理

1. 查看表列表

sql

-- 查看当前数据库中的表
SHOW TABLES;

-- 查看指定数据库中的表
SHOW TABLES FROM database_name;

2. 创建表

sql

CREATE TABLE table_name (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 指定字符集和校对规则
CREATE TABLE table_name (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 如果表不存在则创建
CREATE TABLE IF NOT EXISTS table_name (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
);

3. 查看表结构

sql

-- 查看表结构
DESCRIBE table_name;
-- 或
DESC table_name;

-- 查看表完整结构，包括索引、约束等
SHOW CREATE TABLE table_name;

-- 查看表的列信息
SHOW COLUMNS FROM table_name;

4. 修改表

sql

-- 添加列
ALTER TABLE table_name ADD column_name VARCHAR(50);

-- 添加列并指定位置
ALTER TABLE table_name ADD column_name VARCHAR(50) AFTER existing_column;

-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE child_table ADD FOREIGN KEY (parent_id) REFERENCES parent_table(id);

-- 修改列类型
ALTER TABLE table_name MODIFY column_name INT;

-- 修改列名
ALTER TABLE table_name CHANGE old_column_name new_column_name VARCHAR(100);

-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;

-- 修改表名
ALTER TABLE old_table_name RENAME TO new_table_name;

5. 删除表

sql

DROP TABLE table_name;

-- 如果表存在则删除
DROP TABLE IF EXISTS table_name;

-- 删除多个表
DROP TABLE table1, table2;

用户和权限管理

1. 查看用户列表

sql

-- MySQL 5.7+
SELECT user, host FROM mysql.user;

-- 查看用户权限
SHOW GRANTS FOR 'username'@'hostname';

2. 创建用户

sql

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

-- MySQL 8.0+ 使用 caching_sha2_password 认证插件
CREATE USER 'username'@'hostname' IDENTIFIED WITH caching_sha2_password BY 'password';

-- 创建用户并限制连接数
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS 10;

3. 修改用户密码

sql

-- MySQL 5.7+
ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';

-- MySQL 5.6 及更早版本
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('new_password');

4. 授权用户

sql

-- 授予所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'hostname';

-- 授予表级权限
GRANT SELECT ON database_name.table_name TO 'username'@'hostname';

-- 授予列级权限
GRANT SELECT (column1, column2) ON database_name.table_name TO 'username'@'hostname';

-- 授予权限并允许用户授权给其他用户
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname' WITH GRANT OPTION;

5. 撤销用户权限

sql

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'hostname';

-- 撤销特定权限
REVOKE SELECT, INSERT ON database_name.* FROM 'username'@'hostname';

6. 删除用户

sql

DROP USER 'username'@'hostname';

-- 如果用户存在则删除
DROP USER IF EXISTS 'username'@'hostname';

7. 刷新权限

在修改用户权限后，需要刷新权限使更改生效：

sql

FLUSH PRIVILEGES;

数据操作

1. 插入数据

sql

-- 插入单行数据
INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');

-- 插入多行数据
INSERT INTO table_name (column1, column2) VALUES 
  ('value1', 'value2'),
  ('value3', 'value4'),
  ('value5', 'value6');

-- 插入查询结果
INSERT INTO table_name (column1, column2) SELECT column1, column2 FROM another_table;

2. 查询数据

sql

-- 查询所有列
SELECT * FROM table_name;

-- 查询特定列
SELECT column1, column2 FROM table_name;

-- 带条件查询
SELECT * FROM table_name WHERE column1 = 'value';

-- 带排序
SELECT * FROM table_name ORDER BY column1 ASC;
-- 降序
SELECT * FROM table_name ORDER BY column1 DESC;

-- 带分页
SELECT * FROM table_name LIMIT 10;
-- 偏移量
SELECT * FROM table_name LIMIT 10 OFFSET 20;

-- 带分组
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

-- 带过滤分组
SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 5;

3. 更新数据

sql

-- 更新所有行
UPDATE table_name SET column1 = 'new_value';

-- 带条件更新
UPDATE table_name SET column1 = 'new_value' WHERE column2 = 'condition';

-- 更新多个列
UPDATE table_name SET column1 = 'new_value1', column2 = 'new_value2' WHERE column3 = 'condition';

4. 删除数据

sql

-- 删除所有行
DELETE FROM table_name;

-- 带条件删除
DELETE FROM table_name WHERE column = 'condition';

-- 删除前 N 行
DELETE FROM table_name LIMIT 10;

索引管理

1. 查看表索引

sql

SHOW INDEX FROM table_name;
-- 或
SHOW KEYS FROM table_name;

2. 创建索引

sql

-- 创建普通索引
CREATE INDEX index_name ON table_name (column1);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column1);

-- 创建复合索引
CREATE INDEX index_name ON table_name (column1, column2);

-- 创建全文索引
CREATE FULLTEXT INDEX index_name ON table_name (column1);

3. 删除索引

sql

DROP INDEX index_name ON table_name;

4. 查看索引创建语句

sql

SHOW CREATE TABLE table_name;

存储引擎管理

1. 查看支持的存储引擎

sql

SHOW ENGINES;

2. 查看表的存储引擎

sql

SHOW TABLE STATUS LIKE 'table_name';

-- 或
SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

3. 修改表的存储引擎

sql

ALTER TABLE table_name ENGINE = InnoDB;

配置管理

1. 查看系统变量

sql

-- 查看所有系统变量
SHOW VARIABLES;

-- 查看特定系统变量
SHOW VARIABLES LIKE 'variable_name';

-- 查看全局变量
SHOW GLOBAL VARIABLES LIKE 'variable_name';

-- 查看会话变量
SHOW SESSION VARIABLES LIKE 'variable_name';

2. 修改系统变量

sql

-- 修改全局变量
SET GLOBAL variable_name = 'value';

-- 修改会话变量
SET SESSION variable_name = 'value';
-- 或
SET variable_name = 'value';

3. 查看状态变量

sql

-- 查看所有状态变量
SHOW STATUS;

-- 查看特定状态变量
SHOW STATUS LIKE 'status_name';

-- 查看全局状态变量
SHOW GLOBAL STATUS LIKE 'status_name';

-- 查看会话状态变量
SHOW SESSION STATUS LIKE 'status_name';

日志管理

1. 查看日志配置

sql

-- 查看错误日志配置
SHOW VARIABLES LIKE 'log_error';

-- 查看慢查询日志配置
SHOW VARIABLES LIKE '%slow_query%';

-- 查看二进制日志配置
SHOW VARIABLES LIKE '%log_bin%';

-- 查看查询日志配置
SHOW VARIABLES LIKE 'general_log%';

2. 查看二进制日志列表

sql

SHOW BINARY LOGS;

3. 查看当前二进制日志

sql

SHOW MASTER STATUS;

4. 刷新日志

sql

-- 刷新二进制日志
FLUSH BINARY LOGS;

-- 刷新所有日志
FLUSH LOGS;

5. 清理二进制日志

sql

-- 删除特定日志之前的所有日志
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 删除指定时间之前的所有日志
PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

-- 删除7天前的所有日志
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

复制管理

1. 查看复制状态

sql

-- 在从服务器上查看复制状态
SHOW SLAVE STATUS\G;

-- 在主服务器上查看复制连接
SHOW PROCESSLIST;

2. 启动复制

sql

START SLAVE;

-- 只启动 IO 线程
START SLAVE IO_THREAD;

-- 只启动 SQL 线程
START SLAVE SQL_THREAD;

3. 停止复制

sql

STOP SLAVE;

-- 只停止 IO 线程
STOP SLAVE IO_THREAD;

-- 只停止 SQL 线程
STOP SLAVE SQL_THREAD;

4. 重置复制

sql

-- 重置从服务器复制信息
RESET SLAVE;

-- 重置主服务器二进制日志
RESET MASTER;

备份与恢复

1. 使用 mysqldump 备份

bash

-- 备份单个数据库
mysqldump -u username -p database_name > database_backup.sql

-- 备份多个数据库
mysqldump -u username -p --databases db1 db2 > databases_backup.sql

-- 备份所有数据库
mysqldump -u username -p --all-databases > all_databases_backup.sql

-- 备份表结构
mysqldump -u username -p --no-data database_name > schema_backup.sql

-- 备份数据
mysqldump -u username -p --no-create-info database_name > data_backup.sql

-- 压缩备份
mysqldump -u username -p database_name | gzip > database_backup.sql.gz

2. 使用 mysql 恢复

bash

-- 恢复数据库
mysql -u username -p database_name < database_backup.sql

-- 恢复压缩备份
gunzip < database_backup.sql.gz | mysql -u username -p database_name

3. 使用 SELECT ... INTO OUTFILE 导出数据

sql

SELECT * INTO OUTFILE '/path/to/output.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table_name;

4. 使用 LOAD DATA INFILE 导入数据

sql

LOAD DATA INFILE '/path/to/input.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

性能监控与优化

1. 查看进程列表

sql

SHOW PROCESSLIST;

-- 查看完整进程列表
SHOW FULL PROCESSLIST;

2. 查看慢查询

sql

-- 查看慢查询日志配置
SHOW VARIABLES LIKE '%slow_query%';

-- 查看慢查询阈值
SHOW VARIABLES LIKE 'long_query_time';

-- 查看慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';

3. 使用 EXPLAIN 分析查询

sql

EXPLAIN SELECT * FROM table_name WHERE column = 'value';

-- 查看扩展信息
EXPLAIN EXTENDED SELECT * FROM table_name WHERE column = 'value';

-- 查看格式化输出
EXPLAIN FORMAT=JSON SELECT * FROM table_name WHERE column = 'value';

4. 查看服务器状态

sql

-- 查看服务器状态摘要
SHOW GLOBAL STATUS;

-- 查看连接数
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- 查看查询数量
SHOW GLOBAL STATUS LIKE 'Queries';

-- 查看慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 查看 Innodb 状态
SHOW ENGINE INNODB STATUS\G;

5. 查看缓存状态

sql

-- 查看查询缓存状态
SHOW VARIABLES LIKE 'query_cache%';
SHOW GLOBAL STATUS LIKE 'Qcache%';

-- 查看 Innodb 缓冲池状态
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

服务器管理

1. 查看服务器版本

sql

SELECT VERSION();

-- 或在命令行中
mysql --version

2. 查看服务器状态

sql

SHOW STATUS;

-- 查看服务器启动时间
SHOW GLOBAL STATUS LIKE 'Uptime';

3. 重新加载权限表

sql

FLUSH PRIVILEGES;

4. 刷新表缓存

sql

FLUSH TABLES;

5. 锁定和解锁表

sql

-- 锁定表
LOCK TABLES table_name WRITE;

-- 解锁表
UNLOCK TABLES;

6. 关闭服务器

sql

SHUTDOWN;

常用工具命令

1. mysqladmin

mysqladmin 是一个用于管理 MySQL 服务器的命令行工具：

bash

-- 查看服务器状态
mysqladmin -u username -p status

-- 查看服务器变量
mysqladmin -u username -p variables

-- 查看进程列表
mysqladmin -u username -p processlist

-- 关闭服务器
mysqladmin -u username -p shutdown

-- 刷新日志
mysqladmin -u username -p flush-logs

-- 重新加载权限
mysqladmin -u username -p reload

-- 检查服务器是否存活
mysqladmin -u username -p ping

2. mysqlshow

mysqlshow 用于显示数据库、表和列信息：

bash

-- 查看数据库列表
mysqlshow -u username -p

-- 查看数据库中的表
mysqlshow -u username -p database_name

-- 查看表结构
mysqlshow -u username -p database_name table_name

-- 查看表的列信息
mysqlshow -u username -p database_name table_name -v

3. mysqldumpslow

mysqldumpslow 用于分析慢查询日志：

bash

-- 查看慢查询日志
mysqldumpslow /path/to/slow-query.log

-- 按查询次数排序
mysqldumpslow -s c /path/to/slow-query.log

-- 按查询时间排序
mysqldumpslow -s t /path/to/slow-query.log

-- 显示前 10 条
mysqldumpslow -t 10 /path/to/slow-query.log

常见问题（FAQ）

Q1: 如何快速查看当前连接的 MySQL 版本？

A1: 使用以下命令：

sql

SELECT VERSION();
-- 或在命令行中
mysql --version

Q2: 如何查看当前数据库中的所有表？

A2: 使用以下命令：

sql

SHOW TABLES;

Q3: 如何查看表的详细结构？

A3: 使用以下命令：

sql

DESCRIBE table_name;
-- 或更详细的信息
SHOW CREATE TABLE table_name;

Q4: 如何备份单个数据库？

A4: 使用 mysqldump 命令：

bash

mysqldump -u username -p database_name > database_backup.sql

Q5: 如何恢复数据库？

A5: 使用 mysql 命令：

bash

mysql -u username -p database_name < database_backup.sql

Q6: 如何查看当前用户的权限？

A6: 使用以下命令：

sql

SHOW GRANTS;

Q7: 如何查看当前服务器的状态？

A7: 使用以下命令：

sql

SHOW GLOBAL STATUS;

Q8: 如何优化查询性能？

A8: 可以使用 EXPLAIN 分析查询执行计划，然后根据结果优化索引、重写查询或调整配置：

sql

EXPLAIN SELECT * FROM table_name WHERE column = 'value';

Q9: 如何查看慢查询日志？

A9: 首先确保慢查询日志已启用，然后查看慢查询日志文件：

sql

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';

然后使用 mysqldumpslow 工具分析日志：

bash

mysqldumpslow /path/to/slow-query.log

Q10: 如何安全地删除数据库？

A10: 使用 DROP DATABASE IF EXISTS 命令，这样如果数据库不存在也不会报错：

sql

DROP DATABASE IF EXISTS database_name;

最佳实践

1. 命令行使用最佳实践

使用密码文件：避免在命令行中直接输入密码，使用密码文件或环境变量
使用别名：为常用命令创建别名，提高效率
使用命令历史：使用上下箭头键查看和重复之前的命令
使用快捷键：掌握常用快捷键，如 Ctrl+A（行首）、Ctrl+E（行尾）、Ctrl+L（清屏）
使用管道和重定向：结合使用管道（|）和重定向（>、>>）处理命令输出

2. 数据库管理最佳实践

定期备份：制定定期备份策略，确保数据安全
使用事务：在修改数据时使用事务，确保数据一致性
使用参数化查询：避免 SQL 注入攻击
定期优化表：定期使用 OPTIMIZE TABLE 优化表结构
监控性能：定期监控数据库性能，及时发现和解决问题

3. 安全性最佳实践

使用强密码：为 MySQL 用户设置强密码
限制用户权限：遵循最小权限原则，只授予必要的权限
限制远程访问：只允许必要的 IP 地址访问 MySQL 服务器
定期更新：定期更新 MySQL 到最新版本，修复已知漏洞
启用 SSL/TLS：配置 MySQL 使用 SSL/TLS 加密连接

4. 性能最佳实践

使用索引：为经常查询的列创建索引
优化查询：避免使用 SELECT *，只查询必要的列
避免大事务：将大事务拆分为多个小事务
优化配置：根据服务器硬件和负载调整 MySQL 配置
使用连接池：使用连接池管理数据库连接，减少连接开销

案例分析

案例1：日常数据库备份

问题：需要每天自动备份 MySQL 数据库。

解决方案：

创建备份脚本：

bash

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mysql"
MYSQL_USER="backup_user"
MYSQL_PASSWORD="backup_password"

mkdir -p $BACKUP_DIR

# 备份所有数据库
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases | gzip > $BACKUP_DIR/all_databases_$DATE.sql.gz

# 清理7天前的备份
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +7 -exec rm -f {} \;

添加到 crontab，每天凌晨 2 点执行：
bash
```
0 2 * * * /path/to/backup_script.sh
```

结果：

实现了每天自动备份所有数据库
自动清理旧备份，节省磁盘空间
备份文件使用日期时间命名，便于管理和恢复

案例2：查询性能优化

问题：一个查询执行缓慢，需要优化。

解决方案：

使用 EXPLAIN 分析查询执行计划：

sql

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

发现没有合适的索引，创建复合索引：

sql

CREATE INDEX idx_customer_order_date ON orders (customer_id, order_date);

再次分析查询执行计划，确认使用了新创建的索引。

结果：

查询执行时间从 5 秒降低到 0.1 秒
减少了全表扫描，提高了查询效率
提高了应用程序的响应速度

案例3：用户权限管理

问题：需要为一个新应用创建 MySQL 用户，并授予适当的权限。

解决方案：

创建应用用户：

sql

CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'secure_password';

授予应用所需的最小权限：

sql

GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'app_user'@'192.168.1.%';

刷新权限：
sql
```
FLUSH PRIVILEGES;
```

结果：

应用可以正常访问数据库
遵循了最小权限原则，提高了安全性
限制了用户的访问 IP，进一步提高了安全性

MySQL 日常管理命令 ​

连接与断开连接 ​

1. 连接 MySQL 服务器 ​

2. 断开连接 ​

数据库管理 ​

1. 查看数据库列表 ​

2. 创建数据库 ​

3. 选择数据库 ​

4. 删除数据库 ​

5. 查看当前数据库 ​

6. 查看数据库创建语句 ​

表管理 ​

1. 查看表列表 ​

2. 创建表 ​

3. 查看表结构 ​

4. 修改表 ​

5. 删除表 ​

用户和权限管理 ​

1. 查看用户列表 ​

2. 创建用户 ​

3. 修改用户密码 ​

4. 授权用户 ​

5. 撤销用户权限 ​

6. 删除用户 ​

7. 刷新权限 ​

数据操作 ​

1. 插入数据 ​

2. 查询数据 ​

3. 更新数据 ​

4. 删除数据 ​

索引管理 ​

1. 查看表索引 ​

2. 创建索引 ​

3. 删除索引 ​

4. 查看索引创建语句 ​

存储引擎管理 ​

1. 查看支持的存储引擎 ​

2. 查看表的存储引擎 ​

3. 修改表的存储引擎 ​

配置管理 ​

1. 查看系统变量 ​

2. 修改系统变量 ​

3. 查看状态变量 ​

日志管理 ​

1. 查看日志配置 ​

2. 查看二进制日志列表 ​

3. 查看当前二进制日志 ​

4. 刷新日志 ​

5. 清理二进制日志 ​

复制管理 ​

1. 查看复制状态 ​

2. 启动复制 ​

3. 停止复制 ​

MySQL 日常管理命令

连接与断开连接

1. 连接 MySQL 服务器

2. 断开连接

数据库管理

1. 查看数据库列表

2. 创建数据库

3. 选择数据库

4. 删除数据库

5. 查看当前数据库

6. 查看数据库创建语句

表管理

1. 查看表列表

2. 创建表

3. 查看表结构

4. 修改表

5. 删除表

用户和权限管理

1. 查看用户列表

2. 创建用户

3. 修改用户密码

4. 授权用户

5. 撤销用户权限

6. 删除用户

7. 刷新权限

数据操作

1. 插入数据

2. 查询数据

3. 更新数据

4. 删除数据

索引管理

1. 查看表索引

2. 创建索引

3. 删除索引

4. 查看索引创建语句

存储引擎管理

1. 查看支持的存储引擎

2. 查看表的存储引擎

3. 修改表的存储引擎

配置管理

1. 查看系统变量

2. 修改系统变量

3. 查看状态变量

日志管理

1. 查看日志配置

2. 查看二进制日志列表

3. 查看当前二进制日志

4. 刷新日志

5. 清理二进制日志

复制管理

1. 查看复制状态

2. 启动复制

3. 停止复制