1. 未授权访问
– 弱密码
– 权限过大
– 网络暴露

2. 数据泄露
– 明文存储敏感数据
– 不安全的连接
– 备份文件泄露

3. 注入攻击
– SQL注入
– 代码注入

4. 拒绝服务
– 资源耗尽
– 连接攻击

5. 内部威胁
– 恶意员工
– 权限滥用

1. 创建用户
mysql> CREATE USER ‘app_user’@’localhost’ IDENTIFIED BY ‘StrongPassword123!’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 授权
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO ‘app_user’@’localhost’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 查看用户权限
mysql> SHOW GRANTS FOR ‘app_user’@’localhost’;

输出示例：
+————————————————————————————————————————+
| Grants for app_user@localhost |
+————————————————————————————————————————+
| GRANT USAGE ON *.* TO `app_user`@`localhost` IDENTIFIED BY PASSWORD ‘*A1B2C3D4E5F6G7H8I9J0’ |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `app_user`@`localhost` |
+————————————————————————————————————————+

4. 回收权限
mysql> REVOKE DELETE ON app_db.* FROM ‘app_user’@’localhost’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 删除用户
mysql> DROP USER ‘app_user’@’localhost’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 创建角色
mysql> CREATE ROLE ‘app_read’, ‘app_write’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 为角色授权
mysql> GRANT SELECT ON app_db.* TO ‘app_read’;
mysql> GRANT INSERT, UPDATE, DELETE ON app_db.* TO ‘app_write’;

输出示例：
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

3. 将角色授予用户
mysql> GRANT ‘app_read’, ‘app_write’ TO ‘app_user’@’localhost’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 激活角色
mysql> SET DEFAULT ROLE ALL TO ‘app_user’@’localhost’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 查看角色
mysql> SELECT * FROM mysql.role_edges;

输出示例：
+———–+—————-+——————+——————+——————-+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+———–+—————-+——————+——————+——————-+
| % | app_read | localhost | app_user | N |
| % | app_write | localhost | app_user | N |
+———–+—————-+——————+——————+——————-+

1. 查看密码验证插件
mysql> SHOW VARIABLES LIKE ‘validate_password%’;

输出示例：
+————————————–+——–+
| Variable_name | Value |
+————————————–+——–+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+————————————–+——–+

2. 修改密码策略
mysql> SET GLOBAL validate_password.length = 12;
mysql> SET GLOBAL validate_password.policy = ‘STRONG’;

输出示例：
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

3. 修改用户密码
mysql> ALTER USER ‘app_user’@’localhost’ IDENTIFIED BY ‘NewStrongPassword123!’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 设置密码过期
mysql> ALTER USER ‘app_user’@’localhost’ PASSWORD EXPIRE INTERVAL 90 DAY;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 查看密码过期设置
mysql> SELECT user, host, password_expired, password_last_changed, password_lifetime FROM mysql.user WHERE user = ‘app_user’;

输出示例：
+———-+———–+——————+———————–+——————-+
| user | host | password_expired | password_last_changed | password_lifetime |
+———-+———–+——————+———————–+——————-+
| app_user | localhost | N | 2026-04-01 10:00:00 | 90 |
+———-+———–+——————+———————–+——————-+

1. 启用密码轮换
mysql> SET GLOBAL default_password_lifetime = 90;

输出示例：
Query OK, 0 rows affected (0.00 sec)

2. 检查密码过期状态
mysql> SELECT user, host, password_expired, password_last_changed FROM mysql.user;

输出示例：
+—————+———–+——————+———————–+
| user | host | password_expired | password_last_changed |
+—————+———–+——————+———————–+
| root | localhost | N | 2026-01-01 00:00:00 |
| app_user | localhost | N | 2026-04-01 10:00:00 |
| replication | % | N | 2026-03-01 00:00:00 |
+—————+———–+——————+———————–+

3. 强制用户修改密码
mysql> ALTER USER ‘app_user’@’localhost’ PASSWORD EXPIRE;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 用户登录时修改密码
mysql> SET PASSWORD = ‘NewPassword123!’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

1. 修改my.cnf配置
vim /etc/my.cnf

[mysqld]
bind-address = 127.0.0.1

2. 重启MySQL
systemctl restart mysqld

3. 检查监听地址
netstat -tuln | grep 3306

输出示例：
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

4. 允许远程访问（如需）
# 修改为特定IP或0.0.0.0（不推荐）
bind-address = 192.168.1.100

1. 检查SSL状态
mysql> SHOW VARIABLES LIKE ‘%ssl%’;

输出示例：
+—————+—————–+
| Variable_name | Value |
+—————+—————–+
| have_ssl | YES |
| ssl_ca | |
| ssl_cert | |
| ssl_key | |
+—————+—————–+

2. 生成SSL证书
openssl req -x509 -nodes -days 3650 -newkey rsa:2048 -keyout /etc/mysql/ssl/server-key.pem -out /etc/mysql/ssl/server-cert.pem

3. 修改my.cnf配置
[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

4. 重启MySQL
systemctl restart mysqld

5. 创建需要SSL的用户
mysql> CREATE USER ‘ssl_user’@’localhost’ IDENTIFIED BY ‘Password123!’ REQUIRE SSL;

输出示例：
Query OK, 0 rows affected (0.01 sec)

6. 测试SSL连接
mysql -u ssl_user -p –ssl-mode=REQUIRED

输出示例：
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.36 MySQL Community Server – GPL

mysql> \s
…
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
…

1. 检查TDE状态
mysql> SHOW VARIABLES LIKE ‘innodb_encrypt_tables’;

输出示例：
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| innodb_encrypt_tables | OFF |
+———————-+——-+

2. 启用TDE
mysql> SET GLOBAL innodb_encrypt_tables = ON;

输出示例：
Query OK, 0 rows affected (0.00 sec)

3. 创建加密表
mysql> CREATE TABLE encrypted_table (
id INT PRIMARY KEY,
data VARCHAR(255)
) ENCRYPTION=’Y’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 检查表加密状态
mysql> SELECT table_name, create_options FROM information_schema.tables WHERE table_schema = ‘test’ AND table_name = ‘encrypted_table’;

输出示例：
+—————+—————-+————————+
| table_schema | table_name | create_options |
+—————+—————-+————————+
| test | encrypted_table| ENCRYPTION=”Y” |
+—————+—————-+————————+

1. 创建加密函数
mysql> DELIMITER //
mysql> CREATE FUNCTION encrypt_data(data VARCHAR(255), key_str VARCHAR(255)) RETURNS VARBINARY(255)
BEGIN
RETURN AES_ENCRYPT(data, key_str);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 创建解密函数
mysql> DELIMITER //
mysql> CREATE FUNCTION decrypt_data(encrypted_data VARBINARY(255), key_str VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
RETURN AES_DECRYPT(encrypted_data, key_str);
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 创建带加密列的表
mysql> CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARBINARY(255),
phone VARBINARY(255)
);

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 插入加密数据
mysql> INSERT INTO users VALUES (1, ‘John Doe’, encrypt_data(‘john@fgedu.net.cn’, ‘secret_key’), encrypt_data(‘1234567890’, ‘secret_key’));

输出示例：
Query OK, 1 row affected (0.01 sec)

5. 查询解密数据
mysql> SELECT id, name, decrypt_data(email, ‘secret_key’) AS email, decrypt_data(phone, ‘secret_key’) AS phone FROM users;

输出示例：
+—-+———-+——————+————-+
| id | name | email | phone |
+—-+———-+——————+————-+
| 1 | John Doe | john@fgedu.net.cn | 1234567890 |
+—-+———-+——————+————-+

1. 检查审计日志状态
mysql> SHOW VARIABLES LIKE ‘audit_log%’;

输出示例：
+———————+———————–+
| Variable_name | Value |
+———————+———————–+
| audit_log_enabled | OFF |
| audit_log_file | audit.log |
| audit_log_format | JSON |
| audit_log_handler | FILE |
+———————+———————–+

2. 启用审计日志
mysql> SET GLOBAL audit_log_enabled = ON;

输出示例：
Query OK, 0 rows affected (0.00 sec)

3. 查看审计日志
cat /var/lib/mysql/audit.log

输出示例：
{“timestamp”: “2026-04-01 10:00:00”, “record_id”: 1, “event_time”: “2026-04-01 10:00:00”, “event_type”: “QUERY”, “server_id”: 1, “command_class”: “select”, “connection_id”: 10, “host”: “localhost”, “user”: “app_user”, “query”: “SELECT * FROM users”}

1. 检查通用查询日志状态
mysql> SHOW VARIABLES LIKE ‘general_log%’;

输出示例：
+——————+———————-+
| Variable_name | Value |
+——————+———————-+
| general_log | OFF |
| general_log_file | /var/lib/mysql/host.log |
+——————+———————-+

2. 启用通用查询日志
mysql> SET GLOBAL general_log = ON;

输出示例：
Query OK, 0 rows affected (0.00 sec)

3. 查看通用查询日志
tail -f /var/lib/mysql/host.log

输出示例：
2026-04-01T10:00:00.000000Z 10 Query SELECT * FROM users
2026-04-01T10:00:01.000000Z 10 Query INSERT INTO users VALUES (2, ‘Jane Smith’, ‘jane@fgedu.net.cn’)

1. 限制MySQL用户权限
chown -R mysql:mysql /var/lib/mysql
chmod 700 /var/lib/mysql

2. 禁用不必要的服务
systemctl stop telnet
systemctl disable telnet

3. 配置防火墙
ufw allow 3306/tcp
ufw enable

4. 定期更新系统
apt-get update
apt-get upgrade

5. 启用SELinux或AppArmor
# 配置SELinux策略
semanage fcontext -a -t mysqld_db_t “/var/lib/mysql(/.*)?”
restorecon -R /var/lib/mysql

1. 移除匿名用户
mysql> DELETE FROM mysql.user WHERE User=”;

输出示例：
Query OK, 1 row affected (0.01 sec)

2. 移除测试数据库
mysql> DROP DATABASE IF EXISTS test;

输出示例：
Query OK, 0 rows affected (0.00 sec)

3. 限制root用户访问
mysql> DELETE FROM mysql.user WHERE User=’root’ AND Host NOT IN (‘localhost’, ‘127.0.0.1’, ‘::1’);

输出示例：
Query OK, 0 rows affected (0.00 sec)

4. 刷新权限
mysql> FLUSH PRIVILEGES;

输出示例：
Query OK, 0 rows affected (0.00 sec)

5. 配置my.cnf安全选项
vim /etc/my.cnf

[mysqld]
# 禁用符号链接
symbolic-links=0

# 禁用本地文件导入
local-infile=0

# 启用二进制日志
log-bin=mysql-bin

# 启用慢查询日志
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log

# 设置连接超时
wait_timeout=300
interactive_timeout=300

1. 检查用户权限
mysql> SELECT user, host, plugin FROM mysql.user;

输出示例：
+—————+———–+———————–+
| user | host | plugin |
+—————+———–+———————–+
| root | localhost | caching_sha2_password |
| app_user | localhost | caching_sha2_password |
| replication | % | mysql_native_password |
| anonymous | localhost | ” |
+—————+———–+———————–+

2. 发现问题
– 存在匿名用户
– replication用户使用弱密码插件
– replication用户允许从任何主机访问

3. 修复问题
– 删除匿名用户
mysql> DELETE FROM mysql.user WHERE User=”;

– 修改replication用户密码插件
mysql> ALTER USER ‘replication’@’%’ IDENTIFIED WITH caching_sha2_password BY ‘StrongReplPassword123!’;

– 限制replication用户访问主机
mysql> DROP USER ‘replication’@’%’;
mysql> CREATE USER ‘replication’@’192.168.1.%’ IDENTIFIED WITH caching_sha2_password BY ‘StrongReplPassword123!’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’192.168.1.%’;

4. 刷新权限
mysql> FLUSH PRIVILEGES;

5. 验证修复
mysql> SELECT user, host, plugin FROM mysql.user;

输出示例：
+—————+————-+———————–+
| user | host | plugin |
+—————+————-+———————–+
| root | localhost | caching_sha2_password |
| app_user | localhost | caching_sha2_password |
| replication | 192.168.1.% | caching_sha2_password |
+—————+————-+———————–+

1. 使用参数化查询

# 不安全的查询
query = “SELECT * FROM users WHERE username = ‘” + username + “‘ AND password = ‘” + password + “‘”

# 安全的参数化查询
query = “SELECT * FROM users WHERE username = ? AND password = ?”
params = (username, password)
cursor.execute(query, params)

2. 输入验证

# 验证用户名和密码
if not re.match(r’^[a-zA-Z0-9_]+$’, username):
raise ValueError(“Invalid username”)

if len(password) < 8:
raise ValueError(“Password too short”)

3. 最小权限原则

# 为应用创建专用用户
mysql> CREATE USER ‘app_user’@’localhost’ IDENTIFIED BY ‘StrongPassword123!’;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO ‘app_user’@’localhost’;

4. 使用预处理语句

# Python示例
import mysql.connector

cnx = mysql.connector.connect(user=’app_user’, password=’StrongPassword123!’, host=’localhost’, database=’app_db’)
cursor = cnx.cursor(prepared=True)

query = “INSERT INTO users (name, email) VALUES (%s, %s)”
data = (‘John Doe’, ‘john@fgedu.net.cn’)
cursor.execute(query, data)

cnx.commit()
cursor.close()
cnx.close()