# 1. 使用connection_control插件防止暴力破解
# 1.1 安装connection_control插件
mysql> INSTALL PLUGIN connection_control SONAME ‘connection_control.so’;
Query OK, 0 rows affected (0.01 sec)

mysql> INSTALL PLUGIN connection_control_failed_login_attempts SONAME ‘connection_control.so’;
Query OK, 0 rows affected (0.01 sec)

# 1.2 配置登录失败次数限制
mysql> SET GLOBAL connection_control_failed_connections_threshold = 3;
Query OK, 0 rows affected (0.00 sec)

# 1.3 配置锁定时间（单位：毫秒）
mysql> SET GLOBAL connection_control_min_connection_delay = 180000;
Query OK, 0 rows affected (0.00 sec)

# 1.4 查看配置
mysql> SHOW VARIABLES LIKE ‘connection_control%’;
+——————————————+——–+
| Variable_name | Value |
+——————————————+——–+
| connection_control_failed_connections_threshold | 3 |
| connection_control_max_connection_delay | 2147483647 |
| connection_control_min_connection_delay | 180000 |
+——————————————+——–+

# 2. 使用防火墙限制访问IP
# 2.1 配置iptables规则，只允许特定IP访问MySQL端口
$ iptables -A INPUT -p tcp –dport 3306 -s 192.168.1.0/24 -j ACCEPT
$ iptables -A INPUT -p tcp –dport 3306 -j DROP
$ service iptables save

# 2.2 配置firewalld规则（CentOS 7+）
$ firewall-cmd –permanent –add-rich-rule=’rule family=”ipv4″ source address=”192.168.1.0/24″ port
protocol=”tcp” port=”3306″ accept’
$ firewall-cmd –permanent –remove-port=3306/tcp
$ firewall-cmd –reload

# 3. 配置MySQL绑定特定IP
# 3.1 修改my.cnf配置文件
$ vi /etc/my.cnf
[mysqld]
bind-address = 192.168.1.100

# 3.2 重启MySQL服务
$ systemctl restart mysqld

# 4. 使用fail2ban防止暴力破解
# 4.1 安装fail2ban
$ yum install -y fail2ban

# 4.2 配置fail2ban
$ vi /etc/fail2ban/jail.d/mysql.conf
[mysqld-auth]
enabled = true
filter = mysqld-auth
port = 3306
action = iptables[name=mysql, port=3306, protocol=tcp]
logpath = /var/log/mysql/mysql-error.log
maxretry = 3
bantime = 1800

# 4.3 启动fail2ban服务
$ systemctl start fail2ban
$ systemctl enable fail2ban

# 4.4 查看fail2ban状态
$ fail2ban-client status mysqld-auth
Status for the jail: mysqld-auth
|- Filter
| |- Currently failed: 0
| |- Total failed: 0
| `- File list: /var/log/mysql/mysql-error.log
`- Actions
|- Currently banned: 0
|- Total banned: 0
`- Banned IP list:

# 5. 测试暴力破解防御
# 5.1 尝试多次错误登录
$ mysql -u root -pWrongPassword1
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

$ mysql -u root -pWrongPassword2
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

$ mysql -u root -pWrongPassword3
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

# 5.2 第四次登录将被延迟
$ time mysql -u root -pWrongPassword4
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

real 0m3.012s
user 0m0.004s
sys 0m0.008s

# 1. 使用参数化查询
# 参数化查询可以防止SQL注入攻击，因为参数值会被自动转义

# 1.1 PHP示例（使用PDO）
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 安全的参数化查询
$stmt = $pdo->prepare(‘SELECT * FROM users WHERE username = ?’);
$stmt->execute([$username]);
$user = $stmt->fetch();

// 不安全的字符串拼接（容易导致SQL注入）
// $stmt = $pdo->query(“SELECT * FROM users WHERE username = ‘$username'”);
?>

# 1.2 Java示例（使用PreparedStatement）
// 安全的参数化查询
String sql = “SELECT * FROM users WHERE username = ?”;
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, username);
ResultSet rs = pstmt.executeQuery();

// 不安全的字符串拼接（容易导致SQL注入）
// String sql = “SELECT * FROM users WHERE username = ‘” + username + “‘”;

# 2. 启用SQL模式限制
# 2.1 设置严格的SQL模式
mysql> SET GLOBAL sql_mode =
‘STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
Query OK, 0 rows affected (0.00 sec)

# 2.2 查看SQL模式
mysql> SELECT @@GLOBAL.sql_mode;
+————————————————————————————————————————+
| @@GLOBAL.sql_mode |
+————————————————————————————————————————+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+————————————————————————————————————————+

# 3. 使用最小权限原则
# 3.1 为应用程序创建专用用户
mysql> CREATE USER ‘app_user’@’%’ IDENTIFIED BY ‘SecurePassword123!’;
Query OK, 0 rows affected (0.01 sec)

# 3.2 授予必要的最小权限
mysql> GRANT SELECT, INSERT, UPDATE ON fgedudb.* TO ‘app_user’@’%’;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 4. 过滤用户输入
# 4.1 在应用程序层面过滤和转义用户输入

# 5. 监控SQL注入攻击
# 5.1 启用查询日志
$ vi /etc/my.cnf
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql-general.log

# 5.2 重启MySQL服务
$ systemctl restart mysqld

# 5.3 监控异常查询
$ tail -f /var/log/mysql/mysql-general.log | grep -i “union\|select.*from\|drop\|delete\|update”

# 6. 使用Web应用防火墙（WAF）
# 6.1 安装ModSecurity（Apache）
$ yum install -y mod_security mod_security_crs

# 6.2 启用ModSecurity
$ vi /etc/httpd/conf.d/mod_security.conf
SecRuleEngine On

# 6.3 重启Apache服务
$ systemctl restart httpd

# 6.4 配置OWASP Core Rule Set
$ cp /etc/httpd/modsecurity.d/crs/crs-setup.conf.example /etc/httpd/modsecurity.d/crs/crs-setup.conf
$ sed -i ‘s/SecDefaultAction “phase:1,log,auditlog,deny,status:403″/SecDefaultAction
“phase:1,log,auditlog,allow”/’ /etc/httpd/modsecurity.d/crs/crs-setup.conf
$ sed -i ‘s/SecDefaultAction “phase:2,log,auditlog,deny,status:403″/SecDefaultAction
“phase:2,log,auditlog,allow”/’ /etc/httpd/modsecurity.d/crs/crs-setup.conf

# 6.5 重启Apache服务
$ systemctl restart httpd

# 1. 配置连接限制
# 1.1 设置最大连接数
mysql> SET GLOBAL max_connections = 500;
Query OK, 0 rows affected (0.00 sec)

# 1.2 设置每个用户的最大连接数
mysql> GRANT USAGE ON *.* TO ‘app_user’@’%’ WITH MAX_USER_CONNECTIONS 50;
Query OK, 0 rows affected (0.01 sec)

# 1.3 查看连接限制
mysql> SHOW VARIABLES LIKE ‘max_connections’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 500 |
+—————–+——-+

mysql> SELECT User, Host, max_user_connections FROM mysql.user WHERE User = ‘app_user’;
+———-+——+———————-+
| User | Host | max_user_connections |
+———-+——+———————-+
| app_user | % | 50 |
+———-+——+———————-+

# 2. 配置连接超时
# 2.1 设置连接超时时间
mysql> SET GLOBAL wait_timeout = 600;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL interactive_timeout = 600;
Query OK, 0 rows affected (0.00 sec)

# 2.2 查看连接超时配置
mysql> SHOW VARIABLES LIKE ‘%timeout%’;
+—————————–+——-+
| Variable_name | Value |
+—————————–+——-+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| interactive_timeout | 600 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| wait_timeout | 600 |
+—————————–+——-+

# 3. 使用连接池
# 3.1 配置应用程序连接池（Java示例）
// HikariCP连接池配置
HikariConfig config = new HikariConfig();
config.setJdbcUrl(“jdbc:mysql://localhost:3306/fgedudb”);
config.setUsername(“app_user”);
config.setPassword(“SecurePassword123!”);
config.setMaximumPoolSize(50);
config.setMinimumIdle(10);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);

HikariDataSource dataSource = new HikariDataSource(config);

# 4. 使用防火墙限制连接速率
# 4.1 配置iptables连接速率限制
$ iptables -A INPUT -p tcp –dport 3306 -m state –state NEW -m limit –limit 60/minute –limit-burst 10 -j
ACCEPT
$ iptables -A INPUT -p tcp –dport 3306 -j DROP
$ service iptables save

# 4.2 配置firewalld连接速率限制（CentOS 7+）
$ firewall-cmd –permanent –add-rich-rule=’rule family=”ipv4″ source address=”0.0.0.0/0″ port
protocol=”tcp” port=”3306″ limit value=”60/m” accept’
$ firewall-cmd –permanent –add-rich-rule=’rule family=”ipv4″ source address=”0.0.0.0/0″ port
protocol=”tcp” port=”3306″ drop’
$ firewall-cmd –reload

# 5. 使用专业的DDoS防护服务
# 5.1 配置CDN加速和DDoS防护
# 例如：阿里云CDN、腾讯云CDN、Cloudflare等

# 5.2 配置负载均衡
# 使用负载均衡器分散流量，防止单点故障
$ vi /etc/haproxy/haproxy.cfg
frontend mysql_frontend
bind *:3306
mode tcp
default_backend mysql_backend

backend mysql_backend
mode tcp
balance roundrobin
server mysql1 192.168.1.100:3306 check
server mysql2 192.168.1.101:3306 check

# 6. 监控DDoS攻击
# 6.1 监控连接数
mysql> SHOW STATUS LIKE ‘Connections’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Connections | 1234 |
+—————+——-+

# 6.2 监控线程数
mysql> SHOW STATUS LIKE ‘Threads_connected’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_connected | 123 |
+——————-+——-+

# 6.3 使用脚本监控连接数
$ vi /usr/local/bin/monitor_mysql_connections.sh
#!/bin/bash
MAX_CONNECTIONS=500
ALERT_EMAIL=”admin@example.com”

CURRENT_CONNECTIONS=$(mysql -u root -p”password” -N -e “SHOW STATUS LIKE ‘Threads_connected’;” | awk ‘{print
$2}’)

if [ $CURRENT_CONNECTIONS -gt $MAX_CONNECTIONS ]; then
echo “MySQL connections exceed limit: $CURRENT_CONNECTIONS” | mail -s “MySQL DDoS Alert” $ALERT_EMAIL
fi

# 给脚本添加执行权限
$ chmod +x /usr/local/bin/monitor_mysql_connections.sh

# 配置定时任务
$ crontab -e
* * * * * /usr/local/bin/monitor_mysql_connections.sh

# 1. 遵循最小权限原则
# 1.1 为应用程序创建专用用户
mysql> CREATE USER ‘app_user’@’%’ IDENTIFIED BY ‘SecurePassword123!’;
Query OK, 0 rows affected (0.01 sec)

# 1.2 授予必要的最小权限
mysql> GRANT SELECT, INSERT, UPDATE ON fgedudb.customers TO ‘app_user’@’%’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ON fgedudb.products TO ‘app_user’@’%’;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 2. 使用角色管理权限
# 2.1 创建角色
mysql> CREATE ROLE ‘read_only’;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE ROLE ‘read_write’;
Query OK, 0 rows affected (0.01 sec)

# 2.2 给角色授予权限
mysql> GRANT SELECT ON *.* TO ‘read_only’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO ‘read_write’;
Query OK, 0 rows affected (0.01 sec)

# 2.3 将角色授予用户
mysql> GRANT ‘read_only’ TO ‘readonly_user’@’%’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ‘read_write’ TO ‘app_user’@’%’;
Query OK, 0 rows affected (0.01 sec)

# 2.4 设置默认角色
mysql> SET DEFAULT ROLE ‘read_write’ FOR ‘app_user’@’%’;
Query OK, 0 rows affected (0.01 sec)

# 3. 定期审计用户权限
# 3.1 查询用户权限
mysql> SHOW GRANTS FOR ‘app_user’@’%’;
+———————————————————————+
| Grants for app_user@% |
+———————————————————————+
| GRANT USAGE ON *.* TO `app_user`@`%` |
| GRANT SELECT, INSERT, UPDATE ON `fgedudb`.`customers` TO `app_user`@`%` |
| GRANT SELECT ON `fgedudb`.`products` TO `app_user`@`%` |
| GRANT `read_write`@`%` TO `app_user`@`%` |
+———————————————————————+

# 3.2 撤销不必要的权限
mysql> REVOKE UPDATE ON fgedudb.customers FROM ‘app_user’@’%’;
Query OK, 0 rows affected (0.01 sec)

# 4. 禁用危险的存储过程和函数
# 4.1 查看启用的存储过程和函数
mysql> SHOW VARIABLES LIKE ‘log_bin_trust_function_creators’;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| log_bin_trust_function_creators | OFF |
+———————————+——-+

# 4.2 禁用存储过程和函数的创建（生产环境建议）
$ vi /etc/my.cnf
[mysqld]
log_bin_trust_function_creators = 0

# 5. 限制管理权限的使用
# 5.1 创建受限的管理员用户
mysql> CREATE USER ‘limited_admin’@’localhost’ IDENTIFIED BY ‘SecureAdmin123!’;
Query OK, 0 rows affected (0.01 sec)

# 5.2 授予受限的管理权限
mysql> GRANT PROCESS, REPLICATION CLIENT ON *.* TO ‘limited_admin’@’localhost’;
Query OK, 0 rows affected (0.01 sec)

# 6. 启用权限变更审计
# 6.1 启用审计日志
$ vi /etc/my.cnf
[mysqld]
plugin-load-add = audit_log.so
audit_log_format = JSON
audit_log_policy = ALL

# 6.2 重启MySQL服务
$ systemctl restart mysqld

# 6.3 查看审计日志
$ tail -f /var/lib/mysql/audit.log

# 1. 数据加密存储
# 1.1 启用表空间加密
mysql> INSTALL COMPONENT ‘file://component_encryption’;
Query OK, 0 rows affected (0.01 sec)

# 1.2 设置加密密钥
mysql> SET PERSIST encryption_keyring_file_data = ‘/mysql/keys/keyring’;
Query OK, 0 rows affected (0.01 sec)

# 1.3 创建加密表
mysql> CREATE TABLE fgedudb.users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ENCRYPTION=’Y’;
Query OK, 0 rows affected (0.01 sec)

# 2. 数据加密传输
# 2.1 生成SSL证书
$ mkdir -p /mysql/ssl
$ cd /mysql/ssl
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca.pem
$ openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem
$ openssl rsa -in server-key.pem -out server-key.pem
$ openssl x509 -req -in server-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 >
server-cert.pem

# 2.2 配置MySQL使用SSL
$ vi /etc/my.cnf
[mysqld]
ssl-ca=/mysql/ssl/ca.pem
ssl-cert=/mysql/ssl/server-cert.pem
ssl-key=/mysql/ssl/server-key.pem
require_secure_transport=ON

# 2.3 重启MySQL服务
$ systemctl restart mysqld

# 2.4 验证SSL配置
mysql> SHOW VARIABLES LIKE ‘%ssl%’;
+————————————-+—————————-+
| Variable_name | Value |
+————————————-+—————————-+
| have_openssl | YES |
| have_ssl | YES |
| require_secure_transport | ON |
| ssl_ca | /mysql/ssl/ca.pem |
| ssl_cert | /mysql/ssl/server-cert.pem |
| ssl_cipher | |
| ssl_key | /mysql/ssl/server-key.pem |
+————————————-+—————————-+

# 3. 敏感数据脱敏
# 3.1 创建脱敏函数
DELIMITER //
CREATE FUNCTION fgedudb.mask_email(email VARCHAR(100))
RETURNS VARCHAR(100)
BEGIN
DECLARE at_pos INT;
DECLARE user_part VARCHAR(50);
DECLARE domain_part VARCHAR(50);

SET at_pos = INSTR(email, ‘@’);
IF at_pos = 0 THEN
RETURN email;
END IF;

SET user_part = LEFT(email, at_pos – 1);
SET domain_part = SUBSTRING(email, at_pos);

IF LENGTH(user_part) <= 2 THEN RETURN CONCAT(LEFT(user_part, 1), '***' , domain_part); ELSE RETURN CONCAT(LEFT(user_part, 2), '***' , domain_part); END IF; END // DELIMITER ; # 3.2 使用脱敏函数 mysql> SELECT
username, fgedudb.mask_email(email) AS masked_email FROM fgedudb.users;
+———-+——————+
| username | masked_email |
+———-+——————+
| john | jo***@example.com |
| alice | al***@test.com |
+———-+——————+

# 4. 访问控制与审计
# 4.1 限制数据访问
mysql> GRANT SELECT (id, username, masked_email) ON fgedudb.users TO ‘report_user’@’%’;
Query OK, 0 rows affected (0.01 sec)

# 4.2 启用访问审计
$ vi /etc/my.cnf
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql-general.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

# 5. 数据备份安全
# 5.1 加密备份数据
$ mysqldump -u root -p fgedudb | gzip | openssl enc -aes-256-cbc -salt -out fgedudb_backup.sql.gz.enc
enter aes-256-cbc encryption password:
Verifying – enter aes-256-cbc encryption password:

# 5.2 解密备份数据
$ openssl enc -d -aes-256-cbc -in fgedudb_backup.sql.gz.enc | gunzip | mysql -u root -p fgedudb
enter aes-256-cbc decryption password:

# 6. 数据销毁安全
# 6.1 安全删除敏感数据
mysql> UPDATE fgedudb.users SET password = ‘***REMOVED***’ WHERE id = 1;
Query OK, 1 row affected (0.01 sec)

# 6.2 永久删除数据（使用TRUNCATE或DROP）
mysql> TRUNCATE TABLE fgedudb.temp_sensitive_data;
Query OK, 0 rows affected (0.01 sec)

# 6.3 使用专用工具安全删除数据
$ shred -u /mysql/data/fgedudb/sensitive_table.ibd

# 1. 禁用危险的函数
# 1.1 查看当前禁用的函数
mysql> SHOW VARIABLES LIKE ‘log_bin_trust_function_creators’;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| log_bin_trust_function_creators | OFF |
+———————————+——-+

# 1.2 禁用危险函数
$ vi /etc/my.cnf
[mysqld]
disable_functions = ‘LOAD_FILE,INTO OUTFILE,EXECUTE,eval,system’

# 2. 限制存储过程和函数的权限
# 2.1 设置存储过程和函数的安全选项
mysql> SET GLOBAL log_bin_trust_function_creators = 0;
Query OK, 0 rows affected (0.00 sec)

# 2.2 创建存储过程时使用DEFINER
DELIMITER //
CREATE DEFINER=’root’@’localhost’ PROCEDURE fgedudb.get_user(IN user_id INT)
BEGIN
SELECT * FROM fgedudb.users WHERE id = user_id;
END //
DELIMITER ;

# 3. 审核存储过程和函数
# 3.1 查看所有存储过程
mysql> SHOW PROCEDURE STATUS WHERE Db = ‘fgedudb’;
+——–+—————-+———–+—————-+———————+———————+—————+———+———————-+———————-+——————–+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client |
collation_connection | Database Collation |
+——–+—————-+———–+—————-+———————+———————+—————+———+———————-+———————-+——————–+
| fgedudb | get_user | PROCEDURE | root@localhost | 2026-04-01 12:00:00 | 2026-04-01 12:00:00 | DEFINER | |
utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+——–+—————-+———–+—————-+———————+———————+—————+———+———————-+———————-+——————–+

# 3.2 查看存储过程代码
mysql> SHOW CREATE PROCEDURE fgedudb.get_user;
+—————-+——————————————+
| Procedure | sql_mode |
+—————-+——————————————+
| get_user | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+—————-+——————————————+

# 4. 限制用户执行系统命令
# 4.1 禁用SYSTEM函数
mysql> SELECT VERSION();
+———–+
| VERSION() |
+———–+
| 8.4.0 |
+———–+

# 注意：MySQL 8.0.16及以上版本已移除SYSTEM函数

# 5. 防止文件操作攻击
# 5.1 限制文件操作权限
mysql> SELECT @@global.secure_file_priv;
+—————————+
| @@global.secure_file_priv |
+—————————+
| /mysql/files/ |
+—————————+

# 5.2 配置secure_file_priv
$ vi /etc/my.cnf
[mysqld]
secure_file_priv = /mysql/files/

# 6. 启用安全审计
# 6.1 安装审计插件
mysql> INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;
Query OK, 0 rows affected (0.01 sec)

# 6.2 配置审计日志
$ vi /etc/my.cnf
[mysqld]
plugin-load-add = audit_log.so
audit_log_format = JSON
audit_log_policy = ALL
audit_log_include_accounts = ‘root@localhost,admin@%’

# 6.3 查看审计日志
$ tail -f /var/lib/mysql/audit.log

# 1. 环境准备
# 1.1 MySQL版本检查
$ mysql -V
mysql Ver 8.4.0 for Linux on x86_64 (MySQL Community Server – GPL)

# 1.2 操作系统版本检查
$ cat /etc/centos-release
CentOS Linux release 7.9.2009 (Core)

# 2. 安装和配置connection_control插件
# 2.1 安装插件
mysql> INSTALL PLUGIN connection_control SONAME ‘connection_control.so’;
Query OK, 0 rows affected (0.01 sec)

mysql> INSTALL PLUGIN connection_control_failed_login_attempts SONAME ‘connection_control.so’;
Query OK, 0 rows affected (0.01 sec)

# 2.2 配置插件
mysql> SET GLOBAL connection_control_failed_connections_threshold = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL connection_control_min_connection_delay = 180000;
Query OK, 0 rows affected (0.00 sec)

# 3. 配置防火墙
# 3.1 查看当前防火墙状态
$ systemctl status firewalld
Active: active (running) since Wed 2026-04-01 12:00:00 CST; 1h ago

# 3.2 配置防火墙规则
$ firewall-cmd –permanent –add-rich-rule=’rule family=”ipv4″ source address=”192.168.1.0/24″ port
protocol=”tcp” port=”3306″ accept’
success

$ firewall-cmd –permanent –remove-port=3306/tcp
success

$ firewall-cmd –reload
success

# 4. 安装和配置fail2ban
# 4.1 安装fail2ban
$ yum install -y epel-release
$ yum install -y fail2ban

# 4.2 配置fail2ban
$ vi /etc/fail2ban/jail.d/mysql.conf
[mysqld-auth]
enabled = true
filter = mysqld-auth
port = 3306
action = iptables[name=mysql, port=3306, protocol=tcp]
logpath = /var/log/mysql/mysql-error.log
maxretry = 3
bantime = 1800

# 4.3 配置MySQL错误日志
$ vi /etc/my.cnf
[mysqld]
log-error = /var/log/mysql/mysql-error.log

# 4.4 启动fail2ban服务
$ systemctl start fail2ban
$ systemctl enable fail2ban

# 4.5 查看fail2ban状态
$ fail2ban-client status mysqld-auth
Status for the jail: mysqld-auth
|- Filter
| |- Currently failed: 0
| |- Total failed: 0
| `- File list: /var/log/mysql/mysql-error.log
`- Actions
|- Currently banned: 0
|- Total banned: 0
`- Banned IP list:

# 5. 测试暴力破解防御
# 5.1 尝试多次错误登录
$ for i in {1..5}; do mysql -u root -pWrongPassword -e “SELECT 1;” 2>&1 | grep “ERROR” || break; done
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) # 这里会有延迟
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES) # 延迟更长

# 5.2 查看fail2ban状态
$ fail2ban-client status mysqld-auth
Status for the jail: mysqld-auth
|- Filter
| |- Currently failed: 5
| |- Total failed: 5
| `- File list: /var/log/mysql/mysql-error.log
`- Actions
|- Currently banned: 1
|- Total banned: 1
`- Banned IP list: 192.168.1.100

# 6. 恢复被封禁的IP
# 6.1 解锁被封禁的IP
$ fail2ban-client set mysqld-auth unbanip 192.168.1.100

# 6.2 验证IP已解锁
$ fail2ban-client status mysqld-auth
Status for the jail: mysqld-auth
|- Filter
| |- Currently failed: 5
| |- Total failed: 5
| `- File list: /var/log/mysql/mysql-error.log
`- Actions
|- Currently banned: 0
|- Total banned: 1
`- Banned IP list:

# 1. 环境准备
# 1.1 创建测试数据库和表
mysql> CREATE DATABASE fgedudb;
Query OK, 1 row affected (0.01 sec)

mysql> USE fgedudb;
Database changed

mysql> CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

# 1.2 插入测试数据
mysql> INSERT INTO users (username, password, email) VALUES
(‘john’, ‘password123’, ‘john@example.com’),
(‘alice’, ‘password456’, ‘alice@test.com’);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

# 2. 演示SQL注入攻击
# 2.1 不安全的查询（字符串拼接）
# 假设应用程序使用以下代码查询用户：
# $sql = “SELECT * FROM users WHERE username = ‘$username'”;

# 攻击者输入：’ OR ‘1’=’1
# 实际执行的SQL：SELECT * FROM users WHERE username = ” OR ‘1’=’1′

# 测试这个攻击
mysql> SELECT * FROM users WHERE username = ” OR ‘1’=’1′;
+—-+———-+————-+——————+———————+
| id | username | password | email | created_at |
+—-+———-+————-+——————+———————+
| 1 | john | password123 | john@example.com | 2026-04-01 12:00:00 |
| 2 | alice | password456 | alice@test.com | 2026-04-01 12:00:00 |
+—-+———-+————-+——————+———————+

# 3. 实施SQL注入防御
# 3.1 使用参数化查询
# PHP PDO示例：
# $stmt = $pdo->prepare(‘SELECT * FROM users WHERE username = ?’);
# $stmt->execute([$username]);

# 测试参数化查询（MySQL客户端）
mysql> PREPARE stmt FROM ‘SELECT * FROM users WHERE username = ?’;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @username = ” OR ‘1’=’1′;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE stmt USING @username;
Empty set (0.00 sec)

# 3.2 设置严格的SQL模式
mysql> SET GLOBAL sql_mode =
‘STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
Query OK, 0 rows affected (0.00 sec)

# 3.3 使用最小权限原则
# 创建应用程序专用用户
mysql> CREATE USER ‘app_user’@’%’ IDENTIFIED BY ‘SecurePassword123!’;
Query OK, 0 rows affected (0.01 sec)

# 授予最小权限
mysql> GRANT SELECT ON fgedudb.users TO ‘app_user’@’%’;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 3.4 启用查询日志监控
mysql> SET GLOBAL general_log = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL general_log_file = ‘/var/log/mysql/mysql-general.log’;
Query OK, 0 rows affected (0.00 sec)

# 4. 测试SQL注入防御
# 4.1 尝试使用参数化查询防御SQL注入
# 应用程序现在使用参数化查询，攻击者的输入会被当作普通字符串处理

# 4.2 尝试使用最小权限限制攻击范围
# 即使发生SQL注入，攻击者也只能执行SELECT操作

# 4.3 监控异常查询
$ tail -f /var/log/mysql/mysql-general.log | grep -i “union\|select.*from\|drop\|delete\|update”

# 5. 额外的防御措施
# 5.1 过滤用户输入
# 在应用程序层面过滤和验证用户输入

# 5.2 使用Web应用防火墙（WAF）
# 配置ModSecurity或其他WAF产品防御SQL注入

# 5.3 定期审计代码
# 定期检查应用程序代码，发现并修复潜在的SQL注入漏洞

# 1. 环境准备
# 1.1 MySQL版本检查
$ mysql -V
mysql Ver 8.4.0 for Linux on x86_64 (MySQL Community Server – GPL)

# 1.2 查看当前连接数
mysql> SHOW STATUS LIKE ‘Threads_connected’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_connected | 12 |
+——————-+——-+

# 2. 配置连接限制
# 2.1 设置最大连接数
mysql> SET GLOBAL max_connections = 500;
Query OK, 0 rows affected (0.00 sec)

# 2.2 设置每个用户的最大连接数
mysql> GRANT USAGE ON *.* TO ‘app_user’@’%’ WITH MAX_USER_CONNECTIONS 50;
Query OK, 0 rows affected (0.01 sec)

# 2.3 查看连接限制配置
mysql> SHOW VARIABLES LIKE ‘max_connections’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 500 |
+—————–+——-+

mysql> SELECT User, Host, max_user_connections FROM mysql.user WHERE User = ‘app_user’;
+———-+——+———————-+
| User | Host | max_user_connections |
+———-+——+———————-+
| app_user | % | 50 |
+———-+——+———————-+

# 3. 配置连接超时
# 3.1 设置连接超时时间
mysql> SET GLOBAL wait_timeout = 600;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL interactive_timeout = 600;
Query OK, 0 rows affected (0.00 sec)

# 4. 配置防火墙连接速率限制
# 4.1 查看当前防火墙规则
$ firewall-cmd –list-rich-rules
rule family=”ipv4″ source address=”192.168.1.0/24″ port port=”3306″ protocol=”tcp” accept

# 4.2 添加连接速率限制规则
$ firewall-cmd –permanent –add-rich-rule=’rule family=”ipv4″ source address=”192.168.1.0/24″ port
protocol=”tcp” port=”3306″ limit value=”60/m” accept’
$ firewall-cmd –permanent –add-rich-rule=’rule family=”ipv4″ source address=”192.168.1.0/24″ port
protocol=”tcp” port=”3306″ drop’
$ firewall-cmd –reload

# 5. 配置连接池
# 5.1 应用程序连接池配置示例（Java HikariCP）
// HikariCP连接池配置
HikariConfig config = new HikariConfig();
config.setJdbcUrl(“jdbc:mysql://localhost:3306/fgedudb”);
config.setUsername(“app_user”);
config.setPassword(“SecurePassword123!”);
config.setMaximumPoolSize(50);
config.setMinimumIdle(10);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);

# 6. 监控DDoS攻击
# 6.1 创建连接数监控脚本
$ vi /usr/local/bin/monitor_mysql_connections.sh
#!/bin/bash
MAX_CONNECTIONS=500
ALERT_EMAIL=”admin@example.com”
LOG_FILE=”/var/log/mysql/connection_monitor.log”

# 日志函数
log() {
echo “$(date ‘+%Y-%m-%d %H:%M:%S’) – $1″ >> $LOG_FILE
}

# 获取当前连接数
CURRENT_CONNECTIONS=$(mysql -u root -p”password” -N -e “SHOW STATUS LIKE ‘Threads_connected’;” | awk ‘{print
$2}’)

log “Current MySQL connections: $CURRENT_CONNECTIONS”

# 检查连接数是否超过阈值
if [ $CURRENT_CONNECTIONS -gt $MAX_CONNECTIONS ]; then
ALERT_MESSAGE=”MySQL connections exceed limit: $CURRENT_CONNECTIONS (max: $MAX_CONNECTIONS)”
log “ALERT: $ALERT_MESSAGE”
echo $ALERT_MESSAGE | mail -s “MySQL DDoS Alert” $ALERT_EMAIL
fi

# 6.2 给脚本添加执行权限
$ chmod +x /usr/local/bin/monitor_mysql_connections.sh

# 6.3 配置定时任务
$ crontab -e
* * * * * /usr/local/bin/monitor_mysql_connections.sh

# 7. 测试DDoS攻击防御
# 7.1 使用ab工具模拟DDoS攻击
$ ab -n 1000 -c 100 http://localhost:8080/app/query.php

# 7.2 监控连接数变化
$ watch -n 1 “mysql -u root -p’password’ -N -e ‘SHOW STATUS LIKE “Threads_connected”;'”

# 7.3 查看防火墙日志
$ tail -f /var/log/firewalld

# 7.4 查看监控脚本日志
$ tail -f /var/log/mysql/connection_monitor.log

# 1. 环境准备
# 1.1 安装必要的工具
$ yum install -y mysql-connector-python python3-pip
$ pip3 install pymysql

# 2. 配置审计日志
# 2.1 安装审计插件
mysql> INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;
Query OK, 0 rows affected (0.01 sec)

# 2.2 配置审计日志
$ vi /etc/my.cnf
[mysqld]
plugin-load-add = audit_log.so
audit_log_format = JSON
audit_log_policy = ALL
audit_log_include_accounts = ‘root@localhost,admin@%’
audit_log_file = /var/log/mysql/audit.log

# 2.3 重启MySQL服务
$ systemctl restart mysqld

# 2.4 查看审计日志
$ tail -f /var/log/mysql/audit.log

# 3. 配置性能监控
# 3.1 启用性能模式
mysql> SET GLOBAL performance_schema = ON;
Query OK, 0 rows affected (0.00 sec)

# 3.2 配置性能监控
$ vi /etc/my.cnf
[mysqld]
performance_schema = ON
performance_schema_max_thread_classes = 200
performance_schema_max_thread_instances = 400

# 4. 创建安全审计脚本
# 4.1 创建审计脚本
$ vi /usr/local/bin/mysql_security_audit.py
#!/usr/bin/env python3
import pymysql
import json
import date