MySQL 数据库核心操作：创建、修改、备份与连接排查

2. 查看所有支持的字符集和校验规则

根据需求选择，主流推荐 utf8：

SHOW CHARSET;
SHOW COLLATION;

3. 校验规则的实际影响

最典型的是 utf8_general_ci（不区分大小写）和 utf8_bin（区分大小写）的区别。

案例 1：不区分大小写 (utf8_general_ci)

CREATE DATABASE test1 COLLATE utf8_general_ci;
USE test1;
CREATE TABLE person(name VARCHAR(20));
INSERT INTO person VALUES('a'),('A'),('b'),('B');
SELECT * FROM person WHERE name='a';

查询 name='a' 时，会同时返回 a 和 A；排序也不区分大小写。

案例 2：区分大小写 (utf8_bin)

CREATE DATABASE test2 COLLATE utf8_bin;
USE test2;
CREATE TABLE person(name VARCHAR(20));
INSERT INTO person VALUES('a'),('A'),('b'),('B');
SELECT * FROM person WHERE name='a';

查询 name='a' 时，只返回 a；排序时大写字母排在前面。

结论：用户信息等需要区分大小写的场景用 utf8_bin，普通业务数据建议 utf8_general_ci。

三、操纵数据库：查看、修改、删除

日常操作主要包括查看信息、修改配置、删除无用库。

1. 查看数据库

• 查看所有数据库：

  SHOW DATABASES;
  

• 查看指定库的创建语句：了解详细配置（如字符集）。

  SHOW CREATE DATABASE mytest;
  

  结果示例中可见反引号 ` 防止关键字冲突，以及版本兼容标记 /*!40100 DEFAULT CHARACTER SET utf8 */。

2. 修改数据库

仅支持修改字符集和校验规则，不支持直接改库名（需迁移数据）。核心语法：

ALTER DATABASE db_name [alter_spacification [,alter_spacification]...];

实战：将 mytest 字符集从 utf8 改为 gbk。

ALTER DATABASE mytest CHARSET=gbk;
SHOW CREATE DATABASE mytest;

3. 删除数据库

高危操作，执行后数据无法恢复。核心语法：

DROP DATABASE [IF EXISTS] db_name;

提醒：生产环境禁止直接删除，需先备份并审批；开发环境删除前务必确认无用。

四、数据库的备份与恢复

数据是核心资产，备份是必备操作。MySQL 提供 mysqldump 工具备份，source 命令恢复。

1. 备份工具：mysqldump

基于命令行执行（需退出 MySQL 连接），核心语法：

# 备份单个数据库
mysqldump -P 端口号 -u 用户名 -p 密码 -B 数据库名 > 备份文件路径/文件名.sql

参数说明：

• -P：端口号，默认 3306。
• -u：用户名。
• -p：密码，与 -p 间无空格。
• -B：关键参数，备份包含数据库创建语句。
• >：重定向符号。

2. 多场景备份实战

• 备份单个完整数据库：

  mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql
  

• 备份指定表：

  mysqldump -u root -p123456 mytest person user > D:/mytest_tables.sql
  

• 批量备份多个数据库：

  mysqldump -u root -p123456 -B mytest db1 db2 > D:/multi_db.sql
  

3. 恢复实战：source 命令

需在 MySQL 连接中执行：

SOURCE 备份文件的绝对路径;

实战：恢复 mytest。

SOURCE D:/mytest.sql;

4. 注意事项

• -B 参数重要性：未加 -B 会导致缺少库创建语句，恢复时需手动建库。
• 路径问题：建议使用绝对路径。
• 权限问题：备份需查询权限，恢复需创建、插入权限。
• 编码问题：保持备份文件与数据库字符集一致。
• 生产策略：建议定时自动备份，并存于独立服务器。

五、查看数据库连接情况

遇到卡顿或疑似入侵时，可用 show processlist 实时查看连接。

1. 核心语法

SHOW PROCESSLIST;

2. 结果解析

3. 应用场景

• 排查卡顿：大量 Query 状态且 Time 很大，说明有慢 SQL。
• 排查入侵：发现陌生 User 或非内网 Host，需立即改密。
• 释放无效连接：大量 Sleep 状态可通过 kill 连接 Id; 释放。

六、避坑指南与最佳实践

最后总结一些实战经验：

• 创建必加 IF NOT EXISTS：避免重复报错。
• 禁止直接删除生产库：先备份，做权限管控。
• 备份必加 -B 参数：确保恢复顺利。
• 字符集优先 utf8：避免乱码。
• 关注陌生 IP：及时发现入侵。

最佳实践：

• 关键字大写，库名/表名小写。
• 库名/表名使用反引号。
• 生产环境采用本地 + 异地双重备份策略。
• 遵循最小权限原则。
• 定期查看连接，优化慢 SQL。