5分钟，轻松搞定MySQL数据库等保测评-每日运维

前言

作为数据库运维人员，需要理性看待数据库等保测评相关工作，完全按照测评结果对数据库进行整改，或完全忽略测评结果都是不可取的，过犹不及，物极必反。

比如MySQL和Oracle为了防止密码被暴力破解的连接错误密码延时功能，随着连续输错密码次数的增加，连接的延时也会逐渐增加，如果设置了此功能，可能未见其利，先见其害，开发能人员一般是不受运维人员约束的，如果某个程序使用错误的密码一直尝试连接数据库，并且启用了密码延时功能，势必会导致正常连接也出现延时的问题，所以要结合实际情况进行整改。

简介

本次主要讲解以下8部分内容。

1.MySQL 用户密码加密方式；
2.MySQL 用户密码策略；
3.MySQL 用户密码有效期；
4.MySQL 连接控制;
5.MySQL 连接超时;
6.MySQL 审计;
7.MySQL 传输加密SSL;
8.MySQL 数据加密。

环境说明

MySQL:5.7.44
OS:Redhat 7.6

1.MySQL 用户密码加密方式

5.7，默认使用插件mysql_native_password

create user cjc01@'localhost' identified with mysql_native_password by 'a';

也支持sha256_password

create user cjc02@'localhost' identified with sha256_password by 'a';

8.0开始，默认使用插件caching_sha2_password

create user cjc03@'localhost' identified with caching_sha2_password by 'a';

8.0之前，没有caching_sha2_password插件

ERROR 1524 (HY000): Plugin 'caching_sha2_password' is not loaded

其中，mysql_native_password 和 caching_sha2_password 插件默认使用什么加密算法：

mysql_native_password:使用的是SHA1加密算法。

链接如下：

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase_authentication_methods_native_password_authentication.html

SHA1( password ) XOR SHA1( "20-bytes random data from server" SHA1( SHA1( password ) )

5分钟，轻松搞定MySQL数据库等保测评-1

caching_sha2_password:使用的是SHA256加密算法。

链接如下：

https://dev.mysql.com/doc/dev/mysql-server/latest/page_caching_sha2_authentication_exchanges.html

5分钟，轻松搞定MySQL数据库等保测评-2

2.MySQL 用户密码策略

默认没有安装密码策略插件

MySQL [(none)]> show variables like 'validate_password%';
Empty set (0.01 sec)

MySQL [(none)]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'validate_password';
Empty set (0.00 sec)

安装密码策略插件

MySQL [(none)]> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected (0.35 sec)

查看默认密码策略

MySQL [(none)]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'validate_password';
+-------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------------+---------------+
| validate_password | ACTIVE |
+-------------------+---------------+
1 row in set (0.00 sec)

MySQL [(none)]> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| 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 |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

默认策略说明：

validate_password_check_user_name :OFF
设置为ON的时候表示能将密码设置成当前用户名

validate_password_dictionary_file :
用于检查密码的字典文件的路径名，默认为空

validate_password_length :8
密码的最小长度，也就是说密码长度必须大于或等于8

validate_password_mixed_case_count :1
如果密码策略是中等或更强的，validate_password要求密码具有的小写和大写字符的最小数量。对于给定的这个值密码必须有那么多小写字符和那么多大写字符

validate_password_number_count :1
密码必须包含的数字个数

validate_password_policy :MEDIUM
用于验证密码强度的策略，该参数可以设定三种级别：0代表低，1代表中，2代表高。
validate_password_policy 主要影响密码的强度检查级别：
0/LOW：只检查密码长度。
1/MEDIUM：检查密码长度、数字、大小写字母、特殊字符，默认是MEDIUM。
2/STRONG：检查密码长度、数字、大小写字母、特殊字符和字典文件。

validate_password_special_char_count :1
密码必须包含的特殊字符个数

测试

新增用户，需要满足密码策略

create user cjc04@'localhost' identified with mysql_native_password by 'a';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

MySQL [(none)]> create user cjc04@'localhost' identified with mysql_native_password by 'cjcA12345!';
Query OK, 0 rows affected (0.01 sec)

修改密码策略

SET GLOBAL validate_password_policy=LOW;
SET GLOBAL validate_password_check_user_name=ON;
SET GLOBAL validate_password_length=5;
SET GLOBAL validate_password_mixed_case_count=1;
SET GLOBAL validate_password_number_count=1;
SET GLOBAL validate_password_special_char_count=1;

配置参数

[root@cjc-db-01 mysql]# vi etc/my.cnf
validate_password_policy=LOW
validate_password_check_user_name=ON
validate_password_length=5
validate_password_mixed_case_count=1
validate_password_number_count=1
validate_password_special_char_count=1

重启

动态参数，也可以不重启

[mysql@cjc-db-01 ~]$ mysqladmin -uroot -p shutdown
[mysql@cjc-db-01 ~]$ mysqld --defaults-file=/etc/my.cnf --user=mysql &

查看修改后的密码策略

[mysql@cjc-db-01 ~]$ mysql -uroot -p
MySQL [(none)]> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_check_user_name | ON |
| validate_password_dictionary_file | |
| validate_password_length | 5 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
7 rows in set (0.01 sec)

卸载密码策略插件

mysql> UNINSTALL PLUGIN validate_password;
Query OK, 0 rows affected (0.29 sec)

mysql> show variables like 'validate_password%';
Empty set (0.00 sec)

[root@cjc-db-01 ~]# vi mysqldata/3308/conf/my.cnf
#validate_password_policy=LOW
#validate_password_check_user_name=ON
#validate_password_length=5
#validate_password_mixed_case_count=1
#validate_password_number_count=1
#validate_password_special_char_count=1

3.MySQL 用户密码有效期

全局配置：

default_password_lifetime

用户级别配置：

ALTER USER 'your_username'@'your_host' PASSWORD EXPIRE INTERVAL N DAY;# N代表几天

当密码过期时，用户必须更改密码才能继续访问数据库。

default_password_lifetime密码有效期，单位为天。

该变量的默认值为0，表示密码没有有效期限制。

MySQL [(none)]> SELECT USER,HOST,PLUGIN,password_expired FROM mysql.user;
+---------------+-----------+-----------------------+------------------+
| USER | HOST | PLUGIN | password_expired |
+---------------+-----------+-----------------------+------------------+
| root | localhost | mysql_native_password | N |
| mysql.session | localhost | mysql_native_password | N |
| mysql.sys | localhost | mysql_native_password | N |
| cjc01 | localhost | mysql_native_password | N |
| cjc02 | localhost | sha256_password | N |
| cjc04 | localhost | mysql_native_password | N |
+---------------+-----------+-----------------------+------------------+
6 rows in set (0.00 sec)

全局配置，密码有效期100天。

MySQL [(none)]> set global default_password_lifetime=100;
Query OK, 0 rows affected (0.00 sec)

用户级别配置：设置cjc01用户密码2天后过期

MySQL [(none)]> ALTER USER 'cjc01'@'localhost' PASSWORD EXPIRE INTERVAL 2 DAY;
Query OK, 0 rows affected (0.29 sec)

查询密码剩余时间

MySQL [(none)]> SELECT USER,HOST,password_lifetime,password_last_changed,now() FROM mysql.user;
+---------------+-----------+-------------------+-----------------------+---------------------+
| USER | HOST | password_lifetime | password_last_changed | now() |
+---------------+-----------+-------------------+-----------------------+---------------------+
| root | localhost | NULL | 2024-05-18 09:52:12 | 2024-05-18 10:51:45 |
| mysql.session | localhost | NULL | 2024-05-18 09:51:46 | 2024-05-18 10:51:45 |
| mysql.sys | localhost | NULL | 2024-05-18 09:51:46 | 2024-05-18 10:51:45 |
| cjc01 | localhost | 2 | 2024-05-18 09:56:27 | 2024-05-18 10:51:45 |
| cjc02 | localhost | NULL | 2024-05-18 09:56:33 | 2024-05-18 10:51:45 |
| cjc04 | localhost | NULL | 2024-05-18 10:09:26 | 2024-05-18 10:51:45 |
+---------------+-----------+-------------------+-----------------------+---------------------+
6 rows in set (0.00 sec)

修改操作系统时间，为当前时间+1月

[root@cjc-db-01 ~]# date
Sat May 18 15:59:55 CST 2024
[root@cjc-db-01 ~]# date -s "2024-06-18 16:00:30"
Tue Jun 18 16:00:30 CST 2024

cjc01用户密码已过期，无法登录

MySQL [(none)]> SELECT USER,HOST,password_expired,password_lifetime,password_last_changed,now() FROM mysql.user;
+---------------+-----------+------------------+-------------------+-----------------------+---------------------+
| USER | HOST | password_expired | password_lifetime | password_last_changed | now() |
+---------------+-----------+------------------+-------------------+-----------------------+---------------------+
| root | localhost | N | NULL | 2024-05-18 09:52:12 | 2024-06-18 16:02:00 |
| mysql.session | localhost | N | NULL | 2024-05-18 09:51:46 | 2024-06-18 16:02:00 |
| mysql.sys | localhost | N | NULL | 2024-05-18 09:51:46 | 2024-06-18 16:02:00 |
| cjc01 | localhost | N | 2 | 2024-05-18 09:56:27 | 2024-06-18 16:02:00 |
| cjc02 | localhost | N | NULL | 2024-05-18 09:56:33 | 2024-06-18 16:02:00 |
| cjc04 | localhost | N | NULL | 2024-05-18 10:09:26 | 2024-06-18 16:02:00 |
+---------------+-----------+------------------+-------------------+-----------------------+---------------------+
6 rows in set (0.00 sec)

[mysql@cjc-db-01 ~]$ mysql -ucjc01 -p
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

其他用户可以正常登录

[mysql@cjc-db-01 ~]$ mysql -ucjc04 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]>

调到1年

[root@cjc-db-01 ~]# date -s "2025-06-18 16:00:30"
Wed Jun 18 16:00:30 CST 2025

所有用户密码都到期，无法登录

[mysql@cjc-db-01 ~]$ mysql -uroot -p
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

[mysql@cjc-db-01 ~]$ mysql -ucjc01 -p
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

[mysql@cjc-db-01 ~]$ mysql -ucjc04 -p
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

4.MySQL 连接控制

默认没有安装连接控制的插件，该插件用来控制客户端在登录操作连续失败一定次数后的响应的延迟，可有效的防止客户端暴力登录的风险。

该插件包含以下两个组件：

CONNECTION_CONTROL：
用来控制登录失败的次数及延迟响应时间。

CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS：
将登录失败的操作记录至 information_schema 系统库中。

查看

MySQL [(none)]> show variables like 'connection%';
Empty set (0.00 sec)
MySQL [(none)]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME like 'connection%';
Empty set (0.00 sec)

安装插件

MySQL [(none)]> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
Query OK, 0 rows affected (0.33 sec)

MySQL [(none)]> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
Query OK, 0 rows affected (0.01 sec)

查看插件

MySQL [(none)]> show variables like 'connection%';
+-------------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 3 |
| connection_control_max_connection_delay | 2147483647 |
| connection_control_min_connection_delay | 1000 |
+-------------------------------------------------+------------+
3 rows in set (0.00 sec)

参数说明：

connection_control_failed_connections_threshold
允许帐户进行的连续失败尝试的次数。
默认为 3 ，表示当连接失败 3 次后启用连接控制，0 表示不开启。

connection_control_max_connection_delay
超出阈值的连接失败的最大延迟(以毫秒为单位)，默认 2147483647 毫秒，约 25 天。

connection_control_min_connection_delay
超过阈值的连接失败的最小延迟(以毫秒为单位)，默认 1000 毫秒，即 1 秒。

当客户端连接数据库连续失败到达一定次数后，服务端会进行一段时间的响应延迟，连续失败尝试的次数越多，响应延迟时间越长。

MySQL [(none)]> SELECT * FROM information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
Empty set (0.00 sec)

测试如下：连续输错10次密码

[mysql@cjc-db-01 ~]$ cat conn.sh
date
mysql -ucjc-1 -p1
date
mysql -ucjc-1 -p1
date
mysql -ucjc-1 -p1
date
mysql -ucjc-1 -p1
date
mysql -ucjc-1 -p1
date
mysql -ucjc-1 -p1
date
mysql -ucjc-1 -p1
date
mysql -ucjc-1 -p1
date
mysql -ucjc-1 -p1
date
mysql -ucjc-1 -p1
date
mysql -ucjc-1 -p1

通过时间可以看到，前三次连接没有延时，第四次开始延时越来越长，第十次连接需要等待7秒，才会出现密码错误的提示。

[mysql@cjc-db-01 ~]$ sh conn.sh
Sat May 18 17:26:09 CST 2024
ERROR 1045 (28000): Access denied for user 'cjc-1'@'localhost' (using password: YES)
Sat May 18 17:26:09 CST 2024
ERROR 1045 (28000): Access denied for user 'cjc-1'@'localhost' (using password: YES)
Sat May 18 17:26:09 CST 2024
ERROR 1045 (28000): Access denied for user 'cjc-1'@'localhost' (using password: YES)
Sat May 18 17:26:09 CST 2024
ERROR 1045 (28000): Access denied for user 'cjc-1'@'localhost' (using password: YES)
Sat May 18 17:26:10 CST 2024
ERROR 1045 (28000): Access denied for user 'cjc-1'@'localhost' (using password: YES)
Sat May 18 17:26:12 CST 2024
ERROR 1045 (28000): Access denied for user 'cjc-1'@'localhost' (using password: YES)
Sat May 18 17:26:15 CST 2024
ERROR 1045 (28000): Access denied for user 'cjc-1'@'localhost' (using password: YES)
Sat May 18 17:26:19 CST 2024
ERROR 1045 (28000): Access denied for user 'cjc-1'@'localhost' (using password: YES)
Sat May 18 17:26:24 CST 2024
ERROR 1045 (28000): Access denied for user 'cjc-1'@'localhost' (using password: YES)
Sat May 18 17:26:30 CST 2024
ERROR 1045 (28000): Access denied for user 'cjc-1'@'localhost' (using password: YES)
Sat May 18 17:26:37 CST 2024
ERROR 1045 (28000): Access denied for user 'cjc-1'@'localhost' (using password: YES)

MySQL [(none)]> SELECT * FROM information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+---------------------+-----------------+
| USERHOST | FAILED_ATTEMPTS |
+---------------------+-----------------+
| 'cjc-1'@'localhost' | 11 |
+---------------------+-----------------+
1 row in set (0.00 sec)

首次输入正确密码，也会继续延时，后面延时计数会重新计算。

[mysql@cjc-db-01 ~]$ mysql -ucjc01 -pa
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]>

5.MySQL 连接超时

查看超时参数

MySQL [(none)]> show global 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 | 300 |
| innodb_rollback_on_timeout | ON |
| interactive_timeout | 172800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 172800 |
+-----------------------------+----------+
13 rows in set (0.00 sec)

连接参数又关的参数：

wait_timeout/interactive_timeout
mysql关闭交互/非交互连接前等待的最大时限。默认28800秒。
wait_timeout 非交互时断开连接前等待时间。
interactive_timeout 交互断开连接的等待时间。

其他参数说明:

connect_timeout
mysql客户端在尝试与mysql服务器建立连接时，mysql服务器返回错误握手协议前等待客户端数据包的最大时限。默认10秒。

lock_wait_timeout
sql语句请求元数据锁的最长等待时间，默认为一年。
此锁超时对于隐式访问MySQL库中系统表的sql语句无效，但是对于使用select，update语句直接访问mysql库中标的sql语句有效。

net_read_timeout/net_write_timeout
mysql服务器端等待从客户端读取数据向客户端写入数据的最大时限，默认30秒。

delayed_insert_timeout
insert delay操作延迟的秒数
innodb_rollback_on_timeout
在innodb中，当事务中的最后一个请求超时的时候，就会回滚这个事务

rpl_stop_slave_timeout
控制stop slave 的执行时间，在重放一个大的事务的时候,突然执行stop slave,命令 stop slave会执行很久,这个时候可能产生死锁或阻塞,严重影响性能，mysql 5.6可以通过rpl_stop_slave_timeout参数控制stop slave 的执行时间

slave_net_timeout
mysql从复制连结等待读取数据的最大时限，默认3600秒。

172800=48小时=2天

[mysql@cjc-db-01 log]$ cat etc/my.cnf|grep -E "interactive_timeout|wait_timeout"
wait_timeout=172800
interactive_timeout=172800
innodb_lock_wait_timeout=300

测试，修改参数

MySQL [(none)]> set global wait_timeout=60;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> set global interactive_timeout=60;
Query OK, 0 rows affected (0.00 sec)

Your MySQL connection id is 35

查看error.log
2024-05-18T18:41:45.281243+08:00 35 [Note] Aborted connection 35 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets)

连接重连

MySQL [(none)]> select user,host,plugin from mysql.user where user like 'cjc%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 36
Current database: *** NONE ***

6.MySQL 审计

MySQL专用审计功能 MySQL Enterprise Audit 在MySQL商业产品（commercial product）的企业版数据库（ MySQL Enterprise Edition）中提供，在社区版数据库（MySQL Community Edition）中不提供Audit审计功能。

https://dev.mysql.com/doc/refman/8.0/en/audit-log.html

社区版启用审计有两种选择，1是开启general_log，2是使用mysql分支审计插件。

1.general_log

先看看general_log:默认没有开启general_log。

mysql> set global general_log=on;
Query OK, 0 rows affected (0.04 sec)

测试，创建一个20列的表t1。

mysql> use cjc
create table t1(
id01 int comment 'xxxxxxxxxxxxxxxxxxxx',
id02 int comment 'xxxxxxxxxxxxxxxxxxxx',
id03 int comment 'xxxxxxxxxxxxxxxxxxxx',
id04 int comment 'xxxxxxxxxxxxxxxxxxxx',
id05 int comment 'xxxxxxxxxxxxxxxxxxxx',
id06 int comment 'xxxxxxxxxxxxxxxxxxxx',
id07 int comment 'xxxxxxxxxxxxxxxxxxxx',
id08 int comment 'xxxxxxxxxxxxxxxxxxxx',
id09 int comment 'xxxxxxxxxxxxxxxxxxxx',
id10 int comment 'xxxxxxxxxxxxxxxxxxxx',
id11 int comment 'xxxxxxxxxxxxxxxxxxxx',
id12 int comment 'xxxxxxxxxxxxxxxxxxxx',
id13 int comment 'xxxxxxxxxxxxxxxxxxxx',
id14 int comment 'xxxxxxxxxxxxxxxxxxxx',
id15 int comment 'xxxxxxxxxxxxxxxxxxxx',
id16 int comment 'xxxxxxxxxxxxxxxxxxxx',
id17 int comment 'xxxxxxxxxxxxxxxxxxxx',
id18 int comment 'xxxxxxxxxxxxxxxxxxxx',
id19 int comment 'xxxxxxxxxxxxxxxxxxxx',
id20 int comment 'xxxxxxxxxxxxxxxxxxxx'
);

mysql> select * from t1;
Empty set (0.01 sec)

查看生成的general.log日志大小1.3KB，对性能有一定影响。

[mysql@cjc-db-01 log]$ ls -lrth general.log
-rw-r----- 1 mysql mysql 1.3K May 19 11:21 general.log

2.审计插件

默认没有审计插件

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%';
Empty set (0.00 sec)

在MySQL社区版中审计插件说明：

数据库审计功能主要将用户对数据库的各类操作行为记录审计日志，以便日后进行跟踪、查询、分析，以实现对用户操作的监控和审计。

审计是一项非常重要的工作，也是企业数据安全体系的重要组成部分，等保测评中也要求有审计日志。

对于 DBA 而言，数据库审计也极其重要，特别是发生人为事故后，审计日志便于进行责任追溯，问题查找。

当前 MySQL 社区版本并没有提供相关的插件使用，虽然 MySQL 提供有 binlog 及 general log ，这二者虽然具备部分审计功能，但一般不当做审计日志来看待。

常见的审计插件有 MariaDB Audit Plugin、Percona Audit Log Plugin、McAfee MySQL Audit Plugin 三种，MariaDB 自带的审计插件比较适合用于 MySQL 社区版，下面我们来学习下如何使用审计插件来实现审计功能。

MariaDB Auditing Plugin的安装

MariaDB 审计插件的名称是 server_audit.so（Windows系统下是 server_audit.dll ），要注意的是，审计插件一直在更新，不同版本的审计插件功能也不同，推荐使用 >= 1.4.4 版本的插件。

由于 MariaDB Auditing Plugin 集成在MariaDB里面，没有单独提供，所以我们需要先下载一个MariaDB。

MariaDB版本的选择，

最开始下载的是mariadb-10.11.8-linux-systemd-x86_64.tar.gz，安装插件报如下错误，不兼容。

mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
ERROR 1126 (HY000): Can't open shared library '/mysqldata/app/5.7.44/lib/plugin/server_audit.so' (errno: 2 mysqldata/app/5.7.44/lib/plugin/server_audit.so: undefined symbol: psi_prlock_wrlock)

重新下载mariadb-10.5.25-linux-systemd-x86_64.tar.gz版本

https://mariadb.org/download/

5分钟，轻松搞定MySQL数据库等保测评-3

进入plugin子目录，查看下是否要另外安装依赖

mysql> show global variables like 'plugin_dir';
+---------------+-----------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------+
| plugin_dir | mysqldata/app/5.7.44/lib/plugin/ |
+---------------+-----------------------------------+
1 row in set (0.01 sec)

[root@cjc-db-01 plugin]# pwd
/soft/mysql/mariadb/mariadb-10.5.25-linux-systemd-x86_64/lib/plugin
[root@cjc-db-01 plugin]# cp server_audit.so mysqldata/app/5.7.44/lib/plugin/
[root@cjc-db-01 plugin]# chmod +x mysqldata/app/5.7.44/lib/plugin/server_audit.so
[root@cjc-db-01 plugin]# chown mysql:mysql mysqldata/app/5.7.44/lib/plugin/server_audit.so

可以正常安装插件

mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
Query OK, 0 rows affected (0.50 sec)

但后面使用审计时，引起了MySQL宕机，兼容应该还有问题，谨慎使用！

查看审计插件

mysql> show variables like '%server_audit%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| server_audit_events | |
| 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 | OFF |
| server_audit_mode | 1 |
| 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 |
+-------------------------------+-----------------------+
16 rows in set (0.41 sec)

参数说明

server_audit_events：
指定记录事件的类型，可以用逗号分隔的多个值(connect,query,table)，默认为空代表审计所有事件

server_audit_excl_users：
用户白名单，该列表中的用户行为将不记录

server_audit_file_path：
存储日志的文件，默认在数据目录的 server_audit.log 文件中

server_audit_file_rotate_now：
强制日志文件轮转

server_audit_file_rotate_size：
限制日志文件的大小

server_audit_file_rotations：
指定日志文件的数量，如果为0日志将从不轮转

server_audit_incl_users：
指定哪些用户的活动将记录，connect将不受此变量影响，该变量比 server_audit_excl_users 优先级高

server_audit_loc_info:
内部参数，用不到

server_audit_logging：
启动或关闭审计，默认OFF，启动 ON

server_audit_mode：
标识版本，用于开发测试

server_audit_output_type：
指定日志输出类型，可为SYSLOG或FILE

server_audit_query_log_limit:
记录中查询字符串的长度限制。默认为1024

server_audit_syslog_facility：
默认为LOG_USER，指定facility

server_audit_syslog_ident：
设置ident，作为每个syslog记录的一部分

server_audit_syslog_info：
指定的info字符串将添加到syslog记录

server_audit_syslog_priority：
定义记录日志优先级

启用审计功能

set global server_audit_logging=on;
set global server_audit_events='connect,query,table,query_ddl,query_dcl,query_dml_no_select';
set global server_audit_file_path='/mysqldata/3308/log/mysql_audit.log';
set global server_audit_file_rotate_size=104857600;

添加以下配置，使得配置永久生效：

[mysql@cjc-db-01 log]$ vi mysqldata/3308/conf/my.cnf
server_audit_logging=on
server_audit_events=connect,query,table,query_ddl,query_dcl,query_dml_no_select
server_audit_file_path=/mysqldata/3308/log/mysql_audit.log
server_audit_file_rotate_size=104857600

测试：MySQL直接宕机？

mysql> use cjc;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> use cjc;
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/mysqldata/3308/socket/mysql.sock' (111)
ERROR:
Can't connect to the server

mysql> exit
Bye
[1]+ Exit 2 mysqld --defaults-file=/etc/my.cnf --user=mysql

查看审计日志：

[mysql@cjc-db-01 log]$ tail -100f mysql_audit.log
20240519 13:37:42,cjc-db-01,root,localhost,4,8,QUERY,,'show databases',0
20240519 13:37:44,cjc-db-01,root,localhost,4,9,QUERY,,'SELECT DATABASE()',0

查看error.log

[mysql@cjc-db-01 log]$ tail -500f error.log
240519 13:02:43 server_audit: MariaDB Audit Plugin version 1.4.14 STARTED.
240519 13:34:43 server_audit: logging started to the file server_audit.log.
240519 13:35:08 server_audit: Log file name was changed to '/mysqldata/3308/log/mysql_audit.log'.
240519 13:35:08 server_audit: logging was stopped.
240519 13:35:08 server_audit: logging started to the file mysqldata/3308/log/mysql_audit.log.
240519 13:35:59 server_audit: Log file rotate size was changed to '104857600'.
05:37:44 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=1048576
max_used_connections=1
max_threads=214
thread_count=2
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1983283 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fff6c0008c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fffa3dabe40 thread_stack 0x40000
mysqld(my_print_stacktrace+0x2c)[0xf4338c]
mysqld(handle_fatal_signal+0x476)[0x80d276]
/lib64/libpthread.so.0(+0xf8c0)[0x7ffff7bce8c0]
/lib64/libc.so.6(+0x154d89)[0x7ffff66d5d89]
/mysqldata/app/5.7.44/lib/plugin/server_audit.so(auditing+0xf94)[0x7fff905f22f4]
/mysqldata/app/5.7.44/lib/plugin/server_audit.so(+0xbf4b)[0x7fff905f2f4b]
mysqld[0x80df64]
mysqld(_Z18mysql_audit_notifyP3THD30mysql_event_general_subclass_tPKciS3_m+0x23d)[0x80e4bd]
mysqld(_ZN12Query_logger17general_log_writeEP3THD19enum_server_commandPKcm+0x58)[0xc632f8]
mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x9ed)[0xd18aad]
mysqld(_Z10do_commandP3THD+0x177)[0xd1a697]
mysqld(handle_connection+0x278)[0xdd9568]
mysqld(pfs_spawn_thread+0x1b4)[0x1534d04]
/lib64/libpthread.so.0(+0x8105)[0x7ffff7bc7105]
/lib64/libc.so.6(clone+0x6d)[0x7ffff667fb2d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): Connection ID (thread ID): 4
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

卸载审计

[root@cjc-db-01 plugin]# rm -r /mysqldata/app/5.7.44/lib/plugin/server_audit.so

7.MySQL 传输加密SSL

MySQL数据库5.7 、8.0以后默认都开启SSL，如需关闭SSL，可以把have_ssl 设为off。

MYSQL版本5.7.44，默认开启了ssl，同时 datadir 目录下自动生成证书文件。

[root@cjc-db-01 ~]# ls -lrth mysqldata/3308/data/*.pem
-rw------- 1 mysql mysql 1.7K May 18 09:51 mysqldata/3308/data/ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K May 18 09:51 mysqldata/3308/data/ca.pem
-rw------- 1 mysql mysql 1.7K May 18 09:51 mysqldata/3308/data/server-key.pem
-rw-r--r-- 1 mysql mysql 1.1K May 18 09:51 mysqldata/3308/data/server-cert.pem
-rw------- 1 mysql mysql 1.7K May 18 09:51 mysqldata/3308/data/client-key.pem
-rw-r--r-- 1 mysql mysql 1.1K May 18 09:51 mysqldata/3308/data/client-cert.pem
-rw------- 1 mysql mysql 1.7K May 18 09:51 mysqldata/3308/data/private_key.pem
-rw-r--r-- 1 mysql mysql 452 May 18 09:51 mysqldata/3308/data/public_key.pem

登录测试

[mysql@cjc-db-01 ~]$ mysql -ucjc -p -h192.168.1.6 -P3308
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> \s
--------------
mysql Ver 14.14 Distrib 5.7.44, for el7 (x86_64) using EditLine wrapper

Connection id:10
Current database:
Current user:cjc@192.168.1.6
SSL:Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.7.44-log MySQL Community Server (GPL)
Protocol version:10
Connection:192.168.1.6 via TCP/IP
Server characterset:utf8mb4
Db characterset:utf8mb4
Client characterset:utf8mb4
Conn. characterset:utf8mb4
TCP port:3308
Uptime:11 min 48 sec

Threads: 2 Questions: 26 Slow queries: 0 Opens: 120 Flush tables: 1 Open tables: 34 Queries per second avg: 0.036
--------------

查看参数

mysql> show variables like '%ssl%';
+-------------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------------+-----------------+
| have_openssl | YES |
| have_ssl | YES |
| performance_schema_show_processlist | OFF |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem |
+-------------------------------------+-----------------+
10 rows in set (0.00 sec

mysql> select user,host,plugin,ssl_type from mysql.user where user like 'cjc%';
+-------+-----------+-----------------------+----------+
| user | host | plugin | ssl_type |
+-------+-----------+-----------------------+----------+
| cjc01 | localhost | mysql_native_password | |
| cjc02 | localhost | sha256_password | |
| cjc04 | localhost | mysql_native_password | |
| cjc | % | mysql_native_password | |
+-------+-----------+-----------------------+----------+
4 rows in set (0.00 sec)

对cjc用户启用SSL

mysql> ALTER USER 'cjc'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.30 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.29 sec)

mysql> select user,host,plugin,ssl_type from mysql.user where user like 'cjc%';
+-------+-----------+-----------------------+----------+
| user | host | plugin | ssl_type |
+-------+-----------+-----------------------+----------+
| cjc01 | localhost | mysql_native_password | |
| cjc02 | localhost | sha256_password | |
| cjc04 | localhost | mysql_native_password | |
| cjc | % | mysql_native_password | ANY |
+-------+-----------+-----------------------+----------+
4 rows in set (0.00 sec)

登录测试

[mysql@cjc-db-01 ~]$ mysql -ucjc -p -h192.168.1.6 -P3308
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> \s
--------------
mysql Ver 14.14 Distrib 5.7.44, for el7 (x86_64) using EditLine wrapper

Connection id:16
Current database:
Current user:cjc@192.168.1.6
SSL:Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.7.44-log MySQL Community Server (GPL)
Protocol version:10
Connection:192.168.1.6 via TCP/IP
Server characterset:utf8mb4
Db characterset:utf8mb4
Client characterset:utf8mb4
Conn. characterset:utf8mb4
TCP port:3308
Uptime:20 min 17 sec

Threads: 2 Questions: 45 Slow queries: 0 Opens: 128 Flush tables: 1 Open tables: 42 Queries per second avg: 0.036

使用SSL登录

[mysql@cjc-db-01 ~]$ mysql --ssl-ca=/mysqldata/3308/data/ca.pem --ssl-cert=/mysqldata/3308/data/client-cert.pem --ssl-key=/mysqldata/3308/data/client-key.pem --ssl-cipher=AES128-SHA -u cjc -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.44-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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.

You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.44, for el7 (x86_64) using EditLine wrapper

Connection id:17
Current database:
Current user:cjc@localhost
SSL:Cipher in use is AES128-SHA
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.7.44-log MySQL Community Server (GPL)
Protocol version:10
Connection:Localhost via UNIX socket
Server characterset:utf8mb4
Db characterset:utf8mb4
Client characterset:utf8mb4
Conn. characterset:utf8mb4
UNIX socket:/mysqldata/3308/socket/mysql.sock
Uptime:22 min 40 sec

Threads: 2 Questions: 50 Slow queries: 0 Opens: 128 Flush tables: 1 Open tables: 42 Queries per second avg: 0.036
--------------

8.MySQL 数据加密

TDE( Transparent Data Encryption,透明数据加密) 指的是无需修改应用就可以实现数据的加解密，在数据写磁盘的时候加密，读的时候自动解密。

加密后其他人即使能够访问数据库文件，没有key也无法读取数据，从而达到防止非法访问的目的。

对于不同的版本，支持的文件加密也是有区别的，8.0.23或以上，支持的加密包括有:独立表空间、通用表空间、DoubleWrite文件、mysql系统表空间、Redo log和UndoLog文件的加密。

5分钟，轻松搞定MySQL数据库等保测评-4

启用加密后，对于备份策略也需要调整，需要考虑到master键的保存，并不时作备份恢复测试，确保即使生产数据完全丢失也可以恢复数据。

安装插件

[mysql@cjc-db-01 3308]$ mkdir /mysqldata/3308/tde

检查

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';
Empty set (0.00 sec)

mysql> INSTALL PLUGIN keyring_udf SONAME'keyring_udf.so';
Query OK, 0 rows affected (0.37 sec)

修改配置文件

[mysql@cjc-db-01 3308]$ vi /mysqldata/3308/conf/my.cnf
early-plugin-load=keyring_file.so
keyring_file_data=/mysqldata/3308/tde

重启

[mysql@cjc-db-01 ~]$ mysqladmin -uroot -p shutdown
[mysql@cjc-db-01 ~]$ mysqld --defaults-file=/etc/my.cnf --user=mysql &

创建加密表

mysql> create table t2(id int) ENCRYPTION='Y';
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t2 values(1),(2),(3);
Query OK, 3 rows affected (0.34 sec)
Records: 3 Duplicates: 0 Warnings: 0

创建普通表

mysql> create table t3(id int);
Query OK, 0 rows affected (0.38 sec)

mysql> insert into t3 values(1),(2),(3);
Query OK, 3 rows affected (0.33 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

查看

[mysql@cjc-db-01 cjc]$ ls -lrth
total 328K
-rw-r----- 1 mysql mysql 67 May 18 09:54 db.opt
-rw-r----- 1 mysql mysql 9.4K May 19 11:20 t1.frm
-rw-r----- 1 mysql mysql 96K May 19 11:20 t1.ibd
-rw-r----- 1 mysql mysql 8.4K May 19 14:30 t2.frm
-rw-r----- 1 mysql mysql 96K May 19 14:30 t2.ibd
-rw-r----- 1 mysql mysql 8.4K May 19 14:37 t3.frm
-rw-r----- 1 mysql mysql 96K May 19 14:37 t3.ibd

对比加密表和普通表

[mysql@cjc-db-01 cjc]$ strings t3.ibd
infimum
supremum

[mysql@cjc-db-01 cjc]$ strings t2.ibd
2aa8eaa7-14b9-11ef-bb3c-080027d7d45e
t))7
'aX|
LpBvA3
~JXjfA
*2"JG`
3# m
......

查看tde文件

[mysql@cjc-db-01 3308]$ strings tde
Keyring file version:1.0
INNODBKey-2aa8eaa7-14b9-11ef-bb3c-080027d7d45e-1AES]08

mysqldump导出加密表，无限制

[mysql@cjc-db-01 back]$ mysqldump -uroot -p --databases cjc --tables t2 > t2.sql
[mysql@cjc-db-01 back]$ mysqldump -uroot -p --databases cjc --tables t3 > t3.sql

无加密

[mysql@cjc-db-01 3308]$ cat /mysqldata/back/t2.sql |grep INSERT
INSERT INTO `t2` VALUES (1),(2),(3);
[mysql@cjc-db-01 3308]$ cat /mysqldata/back/t3.sql |grep INSERT
INSERT INTO `t3` VALUES (1),(2),(3);

参考：

https://dev.mysql.com/doc/refman/8.0/en/innodb-data-encryption.html
https://dev.mysql.com/doc/refman/8.0/en/audit-log.html
https://dev.mysql.com/doc/refman/8.0/en/query-log.html
https://cloud.tencent.com/developer/article/2321905
https://www.modb.pro/db/1739191308676325376
https://www.modb.pro/db/332264

欢迎关注我的公众号"IT小Chen"

###chenjuchao 20240519###

5分钟，轻松搞定MySQL数据库等保测评-5

5分钟，轻松搞定MySQL数据库等保测评

相关文章

发布评论取消回复

剑圣无痕

百度搜索：蓝易云 – centos7系统手动安装ceph教程

出行365：用国产数据库为国人出行“护航”

tidb8.1的磁盘选择，关于网络ssd，和本地ssd的选择对性能影响很大，差距60倍。