MYSQL加固

### MySQL 安全加固完整实施步骤
#### 一、密码强度策略（validate_password）

```bash
-- 查看插件安装路径 及validate_password.so
SHOW VARIABLES LIKE 'plugin_dir';

ls /usr/lib64/mysql/plugin/validate_password.so

```

![](/media/202605/2026-05-11_092841_6235940.52531266105527.png)

编辑数据配置文件my.cnf [该实例是linux环境]
vim /etc/my.cnf
```bash
vim /etc/my.cnf
[mysqld]
……

#======================= anfu=============================
# 加载密码校验插件（Linux 用 .so，Windows 用 .dll）
plugin-load-add=validate_password.so
# 强制启用且运行时不允许卸载
validate-password=FORCE_PLUS_PERMANENT
# 密码策略参数
validate_password_length=10
validate_password_mixed_case_count=1
validate_password_number_count=1
validate_password_special_char_count=1
validate_password_dictionary_file = /var/lib/mysql/password_dictionary.txt

```

![](/media/202605/2026-05-11_092115_3016050.06553425759606735.png)

**弱密码规则**

![](/media/202605/2026-05-11_092317_1170420.24366166620626628.png)
#### 二、连接控制（防止暴力破解）
**步骤1：安装插件**
```bash
-- 安装连接控制插件
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';

-- 验证插件是否安装成功
SHOW PLUGINS;
```

![](/media/202605/2026-05-11_092624_1043340.7133748535918444.png)

![](/media/202605/2026-05-11_092645_0505940.5760911267503062.png)

**步骤2：配置文件添加**

```bash
[mysqld]

# 登录失败3次后锁定
connection-control-failed-connections-threshold=3

# 锁定后延迟1800秒（30分钟）才能重试，单位毫秒
connection-control-min-connection-delay=1800000

# 连接控制插件
plugin-load-add=connection_control.so

```

![](/media/202605/2026-05-11_093425_9138050.7811703615159569.png)

**验证配置**
```bash
 SHOW VARIABLES LIKE 'connection_control%';
```

![](/media/202605/2026-05-11_093541_3273460.8607098814325256.png)

### 三、开启 SSL 加密 [慎重]

#### 创建证书目录
```bash
mkdir -p /var/lib/mysql/ssl
cd /var/lib/mysql/ssl

# 设置权限
chmod 600 *.pem
chown mysql:mysql *.pem
```
#### 证书配置
```bash
[mysqld]
ssl-ca=/var/lib/mysql/ssl/ca-cert.pem
ssl-cert=/var/lib/mysql/ssl/server-cert.pem
ssl-key=/var/lib/mysql/ssl/server-key.pem

[client]
ssl-ca=/var/lib/mysql/ssl/ca-cert.pem
ssl-cert=/var/lib/mysql/ssl/client-cert.pem
ssl-key=/var/lib/mysql/ssl/client-key.pem

```

### 四、三权分立用户创建

```bash
-- ==================== 1. 管理员用户（admin） ====================
-- 权限：所有权限，但不直接操作业务数据
CREATE USER 'admin'@'10.233.%' IDENTIFIED BY 'Admin@2024!' REQUIRE SSL;
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'10.233.%' WITH GRANT OPTION;
GRANT SELECT ON *.* TO 'admin'@'10.233.%';  -- 审计需要
FLUSH PRIVILEGES;

-- ==================== 2. 业务员用户（salesman） ====================
-- 权限：仅能操作业务表，不能查看其他业务数据
CREATE USER 'salesman'@'10.233.%' IDENTIFIED BY 'Sales@2024!' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE ON business_db.* TO 'salesman'@'10.233.%';
GRANT SELECT ON company_db.departments TO 'salesman'@'10.233.%';  -- 只读参考表
FLUSH PRIVILEGES;

-- ==================== 3. 审计员用户（auditor） ====================
-- 权限：只读所有日志和数据，不能修改
CREATE USER 'auditor'@'10.233.%' IDENTIFIED BY 'Audit@2024!' REQUIRE SSL;
GRANT SELECT ON *.* TO 'auditor'@'10.233.%';
GRANT RELOAD, PROCESS, SHOW DATABASES, LOCK TABLES ON *.* TO 'auditor'@'10.233.%';
GRANT SELECT ON mysql.general_log TO 'auditor'@'10.233.%';
GRANT SELECT ON mysql.slow_log TO 'auditor'@'10.233.%';
FLUSH PRIVILEGES;

-- ==================== 4. 普通用户（user） ====================
-- 权限：仅查询自己的数据
CREATE USER 'user'@'10.233.%' IDENTIFIED BY 'User@2024!' REQUIRE SSL;
GRANT SELECT ON personal_db.* TO 'user'@'10.233.%';
FLUSH PRIVILEGES;

-- ==================== 删除默认危险用户 ====================
DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.user WHERE user='root' AND host NOT IN ('localhost', '127.0.0.1', '::1');
DROP USER IF EXISTS 'test'@'%';
FLUSH PRIVILEGES;

```

**限制默认用户远程登录**

```bash
-- 限制 root 只能本地登录
UPDATE mysql.user SET host='localhost' WHERE user='root' AND host='%';

-- 或者删除远程root
DELETE FROM mysql.user WHERE user='root' AND host='%';
FLUSH PRIVILEGES;

-- 禁止匿名用户
DELETE FROM mysql.user WHERE user='';
FLUSH PRIVILEGES;

```
### 五、日志审计功能

**编辑配置文件**

```bash

[mysqld]
# ========== 通用查询日志 ==========
general_log = 1
general_log_file = /var/lib/mysql/general.log

# ========== 慢查询日志 ==========
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2  # 超过2秒记录
log_queries_not_using_indexes = 1

# ========== 错误日志 ==========
log_error = /var/lib/mysql/error.log

# ========== 二进制日志（用于数据恢复和审计） ==========
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW  # ROW格式记录更详细
binlog_expire_logs_seconds = 604800  # 7天
max_binlog_size = 100M

# ========== 审计日志插件 ==========
plugin-load-add=audit_log.so
# 或使用企业版自带审计
# audit_log_policy=ALL
# audit_log_file=/var/lib/mysql/audit.log

```
**设置日志文件权限**

```bash
chown mysql:mysql /var/lib/mysql/*.log
chmod 640 /var/lib/mysql/general.log
chmod 640 /var/lib/mysql/slow.log
chmod 640 /var/lib/mysql/error.log
```

### 六、连接超时设置

**编辑配置文件**

```bash
[mysqld]
# 非交互式连接超时（程序API连接）
wait_timeout = 1800

# 交互式连接超时（命令行连接）
interactive_timeout = 1800

# 注：1800秒 = 30分钟
```