建议根据需要对系统用户口令设置复杂度限制，口令的复杂度（8位以上、至少字母、数字、符号等混合组成）并更换周期进行合理设置（90天以内），不允许新设定的口令与前次旧口令相同。

 查询现有用户密码有效期

mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user;
+------------------+-----------+------------------+-----------------------+-------------------+
| user             | host      | password_expired | password_last_changed | password_lifetime |
+------------------+-----------+------------------+-----------------------+-------------------+
| test             | %         | N                | 2023-12-26 18:58:59   |              NULL |
| mysql.infoschema | localhost | N                | 2023-12-26 18:58:49   |              NULL |
| mysql.session    | localhost | N                | 2023-12-26 18:58:49   |              NULL |
| mysql.sys        | localhost | N                | 2023-12-26 18:58:49   |              NULL |
| root             | localhost | N                | 2023-12-26 18:58:59   |                90 |
+------------------+-----------+------------------+-----------------------+-------------------+
5 rows in set (0.00 sec)

设置root密码有效期90天

mysql> alter user root@'localhost' password expire interval 90 day;
Query OK, 0 rows affected (0.01 sec)

设置test密码有效期90天

mysql> alter user test@'%' password expire interval 90 day;
Query OK, 0 rows affected (0.00 sec)

查看test和root的密码有效期 均为90天

mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user;
+------------------+-----------+------------------+-----------------------+-------------------+
| user             | host      | password_expired | password_last_changed | password_lifetime |
+------------------+-----------+------------------+-----------------------+-------------------+
| test             | %         | N                | 2023-12-26 18:58:59   |                90 |
| mysql.infoschema | localhost | N                | 2023-12-26 18:58:49   |              NULL |
| mysql.session    | localhost | N                | 2023-12-26 18:58:49   |              NULL |
| mysql.sys        | localhost | N                | 2023-12-26 18:58:49   |              NULL |
| root             | localhost | N                | 2023-12-26 18:58:59   |                90 |
+------------------+-----------+------------------+-----------------------+-------------------+
5 rows in set (0.00 sec)

本次不涉及.设置密码永不过期

alter user test@'%' password expire never;

查询密码策略

SHOW VARIABLES LIKE "%password%";

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


添加密码复杂度插件

INSTALL PLUGIN validate_password SONAME 'validate_password.so';

显示已安装插件

show plugins;

修改my.cnf配置，#服务器在启动时加载插件，并防止在服务器运行时删除插件。并配置密码复杂度，修改后重启生效

[mysqld]

plugin-load-add=validate_password.so

validate-password=FORCE_PLUS_PERMANENT

validate_password_policy = MEDIUM

validate_password_length = 10

validate_password_number_count = 1

validate_password_special_char_count = 1

validate_password_mixed_case_count = 1

备注：临时修改复杂度策略，重启后失效

SET GLOBAL validate_password_policy = MEDIUM; -- 密码策略级别：LOW, MEDIUM, STRONG

SET GLOBAL validate_password_length = 10; -- 密码最小长度

SET GLOBAL validate_password_number_count = 1; -- 密码中至少包含的数字个数

SET GLOBAL validate_password_special_char_count = 1; -- 密码中至少包含的特殊字符个数

SET GLOBAL validate_password_mixed_case_count = 1; -- 密码中至少包含的大小写字母个数

SET GLOBAL validate_password_check_user_name = OFF; -- 是否禁止使用用户名作为密码的一部分（建议设置为OFF）