Bo's Oracle Station › 博客首页 ›Oracle技术› Oracle云、Linux、MySQL和Java › 查看内容

【博客文章2026】MySQL 9.x数据库管理技巧13：MySQL数据库和服务器安全加固

2026-5-12 11:29| 发布者: admin| 查看: 441| 评论: 0|原作者: Bo Tang

摘要: 文章覆盖了MySQL安全加固的核心要点，特别是SSL强制和审计插件的配置。也关注了root远程登录和配置持久化这两个问题。

Author: Bo Tang

1. 密码复杂度插件：

MySQL 9.x数据库服务器默认已经安装了密码复杂度插件，而且也设置了中等级别复杂度策略。密码要求必需8位以上、必需至少包含1个大小写，必需至少包含1个数字和1个特殊字符：

[root@node0 log]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 15

Server version: 9.7.0

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user root@'localhost' identified by 'oracle_4U';

Query OK, 0 rows affected (0.017 sec)

mysql> show variables like 'validate_password%';

+-------------------------------------------------+--------+

| Variable_name | Value |

+-------------------------------------------------+--------+

| validate_password.changed_characters_percentage | 0 |

| 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 |

+-------------------------------------------------+--------+

8 rows in set (0.011 sec)

更改密码，进行测试（故意不满足复杂度要求，查看报错信息）：

mysql> alter user root@'localhost' identified by 'a123456';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

2. 密码周期插件：

mysql> show global variables like 'default_password_lifetime';

+---------------------------+-------+

| Variable_name | Value |

+---------------------------+-------+

| default_password_lifetime | 0 |

+---------------------------+-------+

1 row in set (0.010 sec)

配置密码生命周期为90天（如果要跨越重启保留配置，需要把“default_password_lifetime = 90”写进/etc/my.cnf的[mysqld]之下。如果需要跨越重启保留配置，那么以下所有的配置都需要如此例一样写进/etc/my.cnf）：

mysql> SET GLOBAL default_password_lifetime = 90;

Query OK, 0 rows affected (0.000 sec)

3. 登录失败锁定设置：

mysql> show variables like '%connection_control%';

Empty set (0.010 sec)

如果查询为空，则说明需要安装插件：

mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';

Query OK, 0 rows affected (0.006 sec)

mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';

Query OK, 0 rows affected (0.002 sec)

mysql> show variables like '%connection_control%';

+-------------------------------------------------+------------+

| Variable_name | Value |

+-------------------------------------------------+------------+

| connection_control_exempt_unknown_users | OFF |

| connection_control_failed_connections_threshold | 3 |

| connection_control_max_connection_delay | 2147483647 |

| connection_control_min_connection_delay | 1000 |

+-------------------------------------------------+------------+

4 rows in set (0.008 sec)

设置登录失败锁定次数为5次、设置最小锁定时间为15分钟（单位为毫秒）和设置最大锁定时间为30分钟（单位为毫秒）：

mysql> set global connection_control_failed_connections_threshold=5;

Query OK, 0 rows affected (0.001 sec)

mysql> set global connection_control_min_connection_delay=900000;

Query OK, 0 rows affected (0.000 sec)

mysql> set global connection_control_max_connection_delay=1800000;

Query OK, 0 rows affected (0.000 sec)

mysql> show variables like '%connection_control%';

+-------------------------------------------------+---------+

| Variable_name | Value |

+-------------------------------------------------+---------+

| connection_control_exempt_unknown_users | OFF |

| connection_control_failed_connections_threshold | 5 |

| connection_control_max_connection_delay | 1800000 |

| connection_control_min_connection_delay | 900000 |

+-------------------------------------------------+---------+

4 rows in set (0.008 sec)

4. 会话空闲超时设置：

interactive_timeout为交互的超时；wait_timeout为会话的超时；lock_wait_timeout为超时重置时间：

mysql> set global interactive_timeout=1800;

Query OK, 0 rows affected (0.000 sec)

mysql> set global wait_timeout=1800;

Query OK, 0 rows affected (0.000 sec)

mysql> set global lock_wait_timeout=1800;

Query OK, 0 rows affected (0.000 sec)

mysql> show variables like '%timeout';

+-----------------------------------+----------+

| Variable_name | Value |

+-----------------------------------+----------+

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| have_statement_timeout | YES |

| innodb_flush_log_at_timeout | 1 |

| innodb_lock_wait_timeout | 50 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 1800 |

| lock_wait_timeout | 1800 |

| mysqlx_connect_timeout | 30 |

| mysqlx_idle_worker_thread_timeout | 60 |

| mysqlx_interactive_timeout | 28800 |

| mysqlx_port_open_timeout | 0 |

| mysqlx_read_timeout | 30 |

| mysqlx_wait_timeout | 28800 |

| mysqlx_write_timeout | 60 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| replica_net_timeout | 60 |

| rpl_stop_replica_timeout | 31536000 |

| rpl_stop_slave_timeout | 31536000 |

| slave_net_timeout | 60 |

| ssl_session_cache_timeout | 300 |

| wait_timeout | 1800 |

+-----------------------------------+----------+

23 rows in set (0.005 sec)

5. 打开各种日志：

general日志：

mysql> show global variables like 'general%log';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| general_log | OFF |

+---------------+-------+

1 row in set (0.006 sec)

mysql> set global general_log=on;

Query OK, 0 rows affected (0.004 sec)

mysql> show global variables like 'general%log';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| general_log | ON |

+---------------+-------+

1 row in set (0.005 sec)

slow query日志：

mysql> show global variables like 'slow%log';

+----------------+-------+

| Variable_name | Value |

+----------------+-------+

| slow_query_log | OFF |

+----------------+-------+

1 row in set (0.006 sec)

mysql> set global slow_query_log=on;

Query OK, 0 rows affected (0.002 sec)

mysql> show global variables like 'slow%log';

+----------------+-------+

| Variable_name | Value |

+----------------+-------+

| slow_query_log | ON |

+----------------+-------+

1 row in set (0.004 sec)

bin log日志：

mysql> show global variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

1 row in set (0.004 sec)

mysql> SHOW BINARY LOGS;

+---------------+-----------+-----------+

| Log_name | File_size | Encrypted |

+---------------+-----------+-----------+

| binlog.000001 | 500 | No |

| binlog.000002 | 221 | No |

| binlog.000003 | 198 | No |

+---------------+-----------+-----------+

3 rows in set (0.000 sec)

下载亚马逊RDS for MySQL的开发团队在GPL宣言的框架下对Mariadb的审计插件源代码进行了二次开发的插件：https://www.botangdb.com/portal.php?mod=attachment&id=678 将其放置于plugins目录中。

mysql> show variables like '%plugin%';

+-----------------------------------------------+--------------------------+

| Variable_name | Value |

+-----------------------------------------------+--------------------------+

| plugin_dir | /usr/lib64/mysql/plugin/ |

| replication_optimize_for_static_plugin_config | OFF |

+-----------------------------------------------+--------------------------+

2 rows in set (0.006 sec)

[root@node0 ~]# ls -l /usr/lib64/mysql/plugin/server_audit.so

-rw-r--r-- 1 root root 610736 May 12 17:15 /usr/lib64/mysql/plugin/server_audit.so

mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';

Query OK, 0 rows affected (0.00 sec)

mysql> set global server_audit_logging=on;

Query OK, 0 rows affected (0.001 sec)

mysql> set global server_audit_events='connect,query';

Query OK, 0 rows affected (0.000 sec)

mysql> show variables like '%audit%';

+-------------------------------+-----------------------+

| Variable_name | Value |

+-------------------------------+-----------------------+

| server_audit_events | CONNECT,QUERY |

| server_audit_excl_users | |

| server_audit_file_path | server_audit.log |

| server_audit_file_rotate_now | OFF |

| server_audit_file_rotate_size | 1000000 |

| server_audit_file_rotations | 9 |

| server_audit_incl_users | |

| server_audit_loc_info | |

| server_audit_logging | ON |

| server_audit_output_type | file |

| server_audit_query_log_limit | 1024 |

| server_audit_syslog_facility | LOG_USER |

| server_audit_syslog_ident | mysql-server_auditing |

| server_audit_syslog_info | |

| server_audit_syslog_priority | LOG_INFO |

+-------------------------------+-----------------------+

15 rows in set (0.006 sec)

6. 配置默认进行SSL连接：

mysql> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> update user set host='%' where user='root';

Query OK, 1 row affected (0.002 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> alter user root@'%' require ssl;

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> alter user root@'%' identified by 'Zkydag@zkydag1';

Query OK, 0 rows affected (0.017 sec)

mysql> alter user root@'%' require ssl;

Query OK, 0 rows affected (0.001 sec)

mysql> set global require_secure_transport= on;

Query OK, 0 rows affected (0.000 sec)

MySQL已经强制使用ssl连接，所以连接时一定要写-h，即使本地连接也要写-h 127.0.0.1，重新连接：

[root@node0 ~]# mysql -u root -p -h 127.0.0.1

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 128

Server version: 9.7.0 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%ssl_cert%';

+-----------------+-----------------+

| Variable_name | Value |

+-----------------+-----------------+

| admin_ssl_cert | |

| mysqlx_ssl_cert | |

| ssl_cert | server-cert.pem |

+-----------------+-----------------+

3 rows in set (0.005 sec)

mysql> show variables like '%ssl_key%';

+----------------+----------------+

| Variable_name | Value |

+----------------+----------------+

| admin_ssl_key | |

| mysqlx_ssl_key | |

| ssl_key | server-key.pem |

+----------------+----------------+

3 rows in set (0.005 sec)

mysql> show variables like '%require_secure_transport%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| require_secure_transport | ON |

+--------------------------+-------+

1 row in set (0.006 sec)

mysql> show status like 'ssl_cipher';

+---------------+------------------------+

| Variable_name | Value |

+---------------+------------------------+

| Ssl_cipher | TLS_AES_128_GCM_SHA256 |

+---------------+------------------------+

1 row in set (0.003 sec)

7. MySQL数据库所在服务器的Linux操作系统加固：

7.1 密码复杂度和周期：

配置复杂度和周期为90：

[root@node0 ~]# vim /etc/login.defs

[root@node0 ~]# cat /etc/login.defs

# Please note that the parameters in this configuration file control the

# behavior of the tools from the shadow-utils component. None of these

# tools uses the PAM mechanism, and the utilities that use PAM (such as the

# passwd command) should therefore be configured elsewhere. Refer to

# /etc/pam.d/system-auth for more information.

# *REQUIRED*

# Directory where mailboxes reside, _or_ name of file, relative to the

# home directory. If you _do_ define both, MAIL_DIR takes precedence.

# QMAIL_DIR is for Qmail

#QMAIL_DIR Maildir

MAIL_DIR /var/spool/mail

#MAIL_FILE .mail

# Default initial "umask" value used by login(1) on non-PAM enabled systems.

# Default "umask" value for pam_umask(8) on PAM enabled systems.

# UMASK is also used by useradd(8) and newusers(8) to set the mode for new

# home directories if HOME_MODE is not set.

# 022 is the default value, but 027, or even 077, could be considered

# for increased privacy. There is no One True Answer here: each sysadmin

# must make up their mind.

UMASK 022

# HOME_MODE is used by useradd(8) and newusers(8) to set the mode for new

# home directories.

# If HOME_MODE is not set, the value of UMASK is used to create the mode.

HOME_MODE 0700

# Password aging controls:

# PASS_MAX_DAYS Maximum number of days a password may be used.

# PASS_MIN_DAYS Minimum number of days allowed between password changes.

# PASS_MIN_LEN Minimum acceptable password length.

# PASS_WARN_AGE Number of days warning given before a password expires.

PASS_MAX_DAYS 90

PASS_MIN_DAYS 7

PASS_MIN_LEN 8

PASS_WARN_AGE 7

# Min/max values for automatic uid selection in useradd

UID_MIN 1000

UID_MAX 60000

# System accounts

SYS_UID_MIN 201

SYS_UID_MAX 999

# Min/max values for automatic gid selection in groupadd

GID_MIN 1000

GID_MAX 60000

# System accounts

SYS_GID_MIN 201

SYS_GID_MAX 999

# If defined, this command is run when removing a user.

# It should remove any at/cron/print jobs etc. owned by

# the user to be removed (passed as the first argument).

#USERDEL_CMD /usr/sbin/userdel_local

# If useradd should create home directories for users by default

# On RH systems, we do. This option is overridden with the -m flag on

# useradd command line.

CREATE_HOME yes

# This enables userdel to remove user groups if no members exist.

USERGROUPS_ENAB yes

# Use SHA512 to encrypt password.

ENCRYPT_METHOD SHA512

[root@node0 ~]# useradd apps

[root@node0 ~]# passwd apps

Changing password for user apps.

New password:

BAD PASSWORD: The password is shorter than 8 characters

[root@node0 ~]# passwd apps

Changing password for user apps.

New password:

Retype new password:

passwd: all authentication tokens updated successfully.

[root@node0 ~]# chage -l apps

Last password change : May 14, 2026

Password expires : Aug 12, 2026

Password inactive : never

Account expires : never

Minimum number of days between password change : 7

Maximum number of days between password change : 90

Number of days of warning before password expires : 7

[root@node0 ~]# chage -l root

Last password change : Nov 27, 2025

Password expires : never

Password inactive : never

Account expires : never

Minimum number of days between password change : 0

Maximum number of days between password change : 99999

Number of days of warning before password expires : 7

7.2 PAM配置：

/etc/pam.d/system-auth：

[root@node0 ~]# vim /etc/pam.d/system-auth

[root@node0 ~]# cat /etc/pam.d/system-auth

# Generated by authselect on Tue Nov 25 09:43:11 2025

# Do not modify this file manually.

auth required pam_tally2.so onerr=fail deny=5 unlock_time=300 even_deny_root root_unlock_time=300

auth required pam_env.so

auth required pam_faildelay.so delay=2000000

auth [default=1 ignore=ignore success=ok] pam_usertype.so isregular

auth [default=1 ignore=ignore success=ok] pam_localuser.so

auth sufficient pam_unix.so nullok try_first_pass

auth [default=1 ignore=ignore success=ok] pam_usertype.so isregular

auth sufficient pam_sss.so forward_pass

auth required pam_deny.so

account required pam_unix.so

account sufficient pam_localuser.so

account sufficient pam_usertype.so issystem

account [default=bad success=ok user_unknown=ignore] pam_sss.so

account required pam_permit.so

password requisite pam_pwquality.so try_first_pass local_users_only retry=3 minlen=8 ucredit=-1 lcredit=-1 dcredit=-1 ocredit=-1 enforce_for_root

password sufficient pam_unix.so sha512 shadow nullok try_first_pass use_authtok

password sufficient pam_sss.so use_authtok

password required pam_deny.so

session optional pam_keyinit.so revoke

session required pam_limits.so

-session optional pam_systemd.so

session [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid

session required pam_unix.so

session optional pam_sss.so

/etc/profile（在末尾配置添加超时退出）：

[root@node0 ~]# vim /etc/profile

[root@node0 ~]# tail -n 40 /etc/profile

# /usr/share/doc/setup-*/uidgid file

if [ $UID -gt 199 ] && [ "`/usr/bin/id -gn`" = "`/usr/bin/id -un`" ]; then

umask 002

else

umask 022

for i in /etc/profile.d/*.sh /etc/profile.d/sh.local ; do

if [ -r "$i" ]; then

if [ "${-#*i}" != "$-" ]; then

. "$i"

else

. "$i" >/dev/null

done

unset i

unset -f pathmunge

if [ -n "${BASH_VERSION-}" ] ; then

if [ -f /etc/bashrc ] ; then

# Bash login shells run only /etc/profile

# Bash non-login shells run only /etc/bashrc

# Check for double sourcing is done in /etc/bashrc.

. /etc/bashrc

if [ $USER = "oracle" ] || [ $USER = "grid" ] ; then

if [ $SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

export TMOUT=900

readonly TMOUT

防火墙的配置，只允许172.25.250.98进行sshd访问：

[root@node0 ~]# firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="172.25.250.98" port port="22" protocol="tcp" accept"

success

[root@node0 ~]# firewall-cmd --permanent --remove-service=ssh

success

[root@node0 ~]# firewall-cmd --reload

success

防火墙的配置，只允许172.25.250.0/24进行mysql访问：

[root@node0 ~]# firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="172.25.250.0/24" port port="3306" protocol="tcp" accept"

success

[root@node0 ~]# firewall-cmd --reload

success

防火墙的配置，只允许172.25.250.0/24进行https访问：

[root@node0 ~]# firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="172.25.250.0/24" port port="443" protocol="tcp" accept"

success

[root@node0 ~]# firewall-cmd --reload

success

防火墙生效的规则列表：

[root@node0 ~]# firewall-cmd --list-all

public (active)

target: default

icmp-block-inversion: no

interfaces: br1 eno1 eno2

sources:

services: cockpit dhcpv6-client

ports:

protocols:

masquerade: no

forward-ports:

source-ports:

icmp-blocks:

rich rules:

rule family="ipv4" source address="172.25.250.98" port port="22" protocol="tcp" accept

rule family="ipv4" source address="172.25.250.0/24" port port="3306" protocol="tcp" accept

rule family="ipv4" source address="172.25.250.0/24" port port="443" protocol="tcp" accept

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：【博客文章2026】MySQL 9.x数据库管理技巧12：MySQL数据库的SQL调优

【博客文章2026】MySQL 9.x数据库管理技巧13：MySQL数据库和服务器安全加固

相关分类