一、MYSQL设置账号密码复杂度

1、设置密码复杂度

[root@localhost tmp]# mysql -uroot -p

2、测试修改密码

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

3、查看一下当前修改后的密码策略,如果没有需要启动安全插件

mysql> SHOW VARIABLES LIKE "%password%";

image.png

4、启动安全插件

# find / -name validate_password.so
/usr/lib64/mysql/plugin/validate_password.so

首先打开/etc/my.cnf，然后在[mysqld]的下方加入如下代码：

plugin-load-add=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
重启mysqld服务
# systemctl restart mysqld.service

5、登录

# mysql -uroot -p
# mysql -hlocalhost -P3309 -uroot -ppass123

6、查看一下当前修改后的密码策略
mysql> SHOW VARIABLES LIKE "%password%";

image.png

7、设置强密码策略，启动插件默认是强密码，下面是说明参数不用设置
参数说明
validate_password_policy值

image.png

如果不想设置那么复杂，比如指向设置root密码为1234，设置方式：
首先，修改validate_password_policy参数的值
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec）
这样，判断密码的标准就基于密码的长度了。这个由validate_password_length参数来决定。
mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
|                          8 |
+----------------------------+
1 row in set (0.00 sec)

validate_password_length参数默认为8，它有最小值的限制，最小值为：
validate_password_number_count+ validate_password_special_char_count+ (2 * validate_password_mixed_case_count)
其中:

validate_password_number_count             #指定了密码中数字的长度，
validate_password_special_char_count       #指定了密码中特殊字符的长度，
validate_password_mixed_case_count         #指定了密码中大小字母的长度。

这些参数，默认值均为1，所以validate_password_length最小值为4，如果你显性指定validate_password_length的值小于4，尽管不会报错，但validate_password_length的值将设为4。

如下演示：

mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
|                          8 |
+----------------------------+
1 row in set (0.00 sec)


mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)


mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
|                          4 |
+----------------------------+
1 row in set (0.00 sec)

如果修改了validate_password_number_count，validate_password_special_char_count，validate_password_mixed_case_count中任何一个值，则validate_password_length将进行动态修改。

mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
|                          4 |
+----------------------------+
1 row in set (0.00 sec)

mysql>  select @@validate_password_mixed_case_count;
+--------------------------------------+
| @@validate_password_mixed_case_count |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> set global validate_password_mixed_case_count=2;
Query OK, 0 rows affected (0.00 sec)

mysql>  select @@validate_password_mixed_case_count;
+--------------------------------------+
| @@validate_password_mixed_case_count |
+--------------------------------------+
|                                    2 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
|                          6 |
+----------------------------+
1 row in set (0.00 sec)

二、设置密码90天过期

设置全局密码过期时间default_password_lifetime，单位为天

1、查看当前的密码过期时间：

mysql> SHOW VARIABLES LIKE "%password%";

image.png

2、首先打开/etc/my.cnf，然后在[mysqld]的下方加入如下代码：

default_password_lifetime=90

3、保存退出后，重启mysqld服务

[root@anolis8 ~]# systemctl restart mysqld.service

4、登录mysql查看

[root@anolis8 ]# mysql -hlocalhost -uroot -ppass123

mysql> SHOW VARIABLES LIKE "%password%";
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user 'root'@'localhost' identified by 'Pass123#';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW VARIABLES LIKE "%password%";
+----------------------------------------------+-----------------+
| Variable_name                                | Value           |
+----------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys | ON              |
| caching_sha2_password_digest_rounds          | 5000            |
| caching_sha2_password_private_key_path       | private_key.pem |
| caching_sha2_password_public_key_path        | public_key.pem  |
| default_password_lifetime                    | 90              |
| disconnect_on_expired_password               | ON              |
| generated_random_password_length             | 20              |
| mysql_native_password_proxy_users            | OFF             |
| password_history                             | 0               |
| password_require_current                     | OFF             |
| password_reuse_interval                      | 0               |
| report_password                              |                 |
| sha256_password_auto_generate_rsa_keys       | ON              |
| sha256_password_private_key_path             | private_key.pem |
| sha256_password_proxy_users                  | OFF             |
| sha256_password_public_key_path              | public_key.pem  |
| 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               |
+----------------------------------------------+-----------------+
23 rows in set (0.01 sec)

default_password_lifetime=0 时默认密码永不过期。

除全局配置外，也可以创建用户时指定密码过期时间
1、创建用户test_passwd并设置密码过期时间为90天

mysql> CREATE USER 'test_passwd'@'localhost' identified by 'Atest_passwd123' PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.10 sec)

mysql> select user,host,password_last_changed,password_lifetime from mysql.user where user='test_passwd';
+-------------+-----------+-----------------------+-------------------+
| user        | host      | password_last_changed | password_lifetime |
+-------------+-----------+-----------------------+-------------------+
| test_passwd | localhost | 2024-08-22 15:32:34   |                90 |
+-------------+-----------+-----------------------+-------------------+
1 row in set (0.00 sec)

2、创建用户test_passwd_never并设置密码永不过期

mysql> CREATE USER 'test_passwd2'@'localhost' identified by 'Atest_passwd123' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password_last_changed,password_lifetime from mysql.user where user='test_passwd2';
+--------------+-----------+-----------------------+-------------------+
| user         | host      | password_last_changed | password_lifetime |
+--------------+-----------+-----------------------+-------------------+
| test_passwd2 | localhost | 2024-08-22 15:34:08   |                 0 |
+--------------+-----------+-----------------------+-------------------+
1 row in set (0.00 sec)

3、创建用户test_passwd_default并设置密码过期时间遵循系统默认值

mysql> CREATE USER 'test_passwd_default'@'localhost' identified by 'Atest_passwd123' PASSWORD EXPIRE DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password_last_changed,password_lifetime from mysql.user where user='test_passwd_default';
+---------------------+-----------+-----------------------+-------------------+
| user                | host      | password_last_changed | password_lifetime |
+---------------------+-----------+-----------------------+-------------------+
| test_passwd_default | localhost | 2024-08-22 15:35:25   |              NULL |
+---------------------+-----------+-----------------------+-------------------+
1 row in set (0.00 sec)

三、设置登录失败处理功能，失败登录5次锁定5分钟
1、登录

[root@localhost tmp]# mysql -uroot -p

2、安装插件

mysql> install plugin CONNECTION_CONTROL soname 'connection_control.so';
Query OK, 0 rows affected (0.16 sec)

mysql> install plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS soname 'connection_control.so';
Query OK, 0 rows affected (0.00 sec)

查看：
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         | 1000       |
+-------------------------------------------------+------------+
3 rows in set (0.01 sec)

3、修改my.cnf文件

vi /etc/my.cnf
在文件中，我们增加如下两行
connection-control-failed-connections-threshold=5   #登陆失败次数限制
connection-control-min-connection-delay=300000    #限制重试时间，此处为毫秒，注意按需求换算，此处为5分钟

4、重启mysqld服务
[root@anolis8 ~]# systemctl restart mysqld.service
5、重新登录数据库，查看是否生效

mysql> show variables like '%connection_control%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 5          |
| connection_control_max_connection_delay         | 2147483647 |
| connection_control_min_connection_delay         | 300000     |
+-------------------------------------------------+------------+
3 rows in set (0.01 sec)

四、mysql数据库设置超时断开连接
mysql数据库长时间未操作自动断开连接由参数：
interactive_timeout和wait_timeout控制，默认都是8小时（28800分钟）

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               | 28800    |
| lock_wait_timeout                 | 31536000 |
| 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       |
| wait_timeout                      | 28800    |
+-----------------------------------+----------+
22 rows in set (0.01 sec)

修改interactive_timeout和wait_timeout即可设置长时间未操作自动断开连接。
修改interactive_timeout和wait_timeout为15分钟（将参数添加到my.cnf参数文件）。

步骤：
1.修改配置文件my.cnf添加以下参数

vim /etc/my.cnf
[mysqld]
...
interactive_timeout=15
wait_timeout=15

2.保存退出后，重启数据库
[root@anolis8 ~]# systemctl restart mysqld.service

3.查看修改后的参数

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               | 15       |
| lock_wait_timeout                 | 31536000 |
| 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       |
| wait_timeout                      | 15       |
+-----------------------------------+----------+
22 rows in set (0.01 sec)

五、 mysql 开启 general_log 日志
在做等保评测时，会要求 mysql 开启 general_log 日志，该日志会记录所有的数据库动作，增长幅度非常大，因此适合于在出现问题时临时开启一段时间，待问题排查解决后再进行关闭，否则日志文件的增长速度会超出你的想象。

mysql> show variables like '%general_log%';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/lib/mysql/anolis8.log |
+------------------+----------------------------+
2 rows in set (0.01 sec)

可以发现，当前 general_log 参数为关闭状态（OFF）
日志输出位置是：/home/www/general_log/gen.log
注意：这个输出位置不支持目录，必须设置成文件。

2、开启 general_log：

[root@anolis8 ~]# ll /var/lib/mysql/anolis8.log
ls: 无法访问'/var/lib/mysql/anolis8.log': 没有那个文件或目录
[root@anolis8 ~]# touch /var/lib/mysql/anolis8.log
[root@anolis8 ~]# chown -R mysql:mysql /var/lib/mysql/anolis8.log
[root@anolis8 ~]# ll /var/lib/mysql/anolis8.log 
-rw-r--r-- 1 mysql mysql 0 8月  22 16:22 /var/lib/mysql/anolis8.log

mysql> set global general_log_file='/var/lib/mysql/anolis8.log';
Query OK, 0 rows affected (0.00 sec)

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

mysql> show variables like '%general_log%';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | ON                         |
| general_log_file | /var/lib/mysql/anolis8.log |
+------------------+----------------------------+
2 rows in set (0.00 sec)

3、查看日志文件
到设置的输出位置就可以看到对应的输出文件大小正在发生变化。
下一步就可以利用 logrotate 对输出文件进行滚动存储了。

[root@anolis8 ~]# ll /var/lib/mysql/anolis8.log 
-rw-r--r-- 1 mysql mysql 0 8月  22 16:22 /var/lib/mysql/anolis8.log
[root@anolis8 ~]# ll /var/lib/mysql/anolis8.log 
-rw-r--r-- 1 mysql mysql 249 8月  22 16:23 /var/lib/mysql/anolis8.log

参考文档
https://www.cnblogs.com/lewisat/p/17696410.html
https://www.cnblogs.com/orcl-2018/p/14103451.html
https://www.cnblogs.com/netWild/p/18062917