1. 查看密码校验插件validate_password是否启用

插件文件名称用

for linux or mac:

validate_password.so

for windows:

validate_password.dll

mysql命令连接mysql shell:

不使用ssl连接示例:

mysql -uroot -p

使用ssl连接示例:

mysql -uroot -p --ssl-ca=/usr/local/mysql/data/ca-cert.pem --ssl-cert=/usr/local/mysql/data/client-cert.pem --ssl-key=/usr/local/mysql/data/client-key.pem

查看所有插件及查看密码校验插件是否启用:

mysql> show plugins;
mysql> 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及mysql环境变量没有以上信息, 则说明没有启用validate_password插件。

2. 查看mysql插件目录路径

查看插件目录路径:

mysql> show variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.02 sec)

可知我的mysql插件目录路径为/usr/local/mysql/lib/plugin/

查看mysql插件目录下内容:

cd /usr/local/mysql/lib/plugin/
ls -la

发现mysql密码校验插件validate_password.so, 显示插件是存在的, 只是尚未启用。

3. 启用密码校验插件validate_password

修改mysql配置文件:

vim /etc/my.cnf

追加以下内容:

[mysqld]
plugin-load-add = validate_password.so
# 服务器在启动时加载插件,并防止在服务器运行时删除插件。
validate-password = FORCE_PLUS_PERMANENT

本人当前的/etc/my.cnf文件内容参考示例:

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
ssl-ca = ca-cert.pem
ssl-cert = server-cert.pem
ssl-key = server-key.pem
plugin-load-add = validate_password.so
# 服务器在启动时加载插件,并防止在服务器运行时删除插件。
validate-password = FORCE_PLUS_PERMANENT

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

修改my.cnf后,重启mysql服务以使设置生效。
或者,要在运行时注册插件,可以使用以下语句(根据系统需要调整.so或.dll后缀):

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

4. 查看mysql密码校验插件validate_password状态

mysql> show plugins;

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

mysql> SHOW STATUS LIKE 'validate_password%';
+-----------------------------------------------+---------------------+
| Variable_name | Value |
+-----------------------------------------------+---------------------+
| validate_password_dictionary_file_last_parsed | 2021-02-28 05:49:23 |
| validate_password_dictionary_file_words_count | 0 |
+-----------------------------------------------+---------------------+
2 rows in set (0.01 sec)

5. 测试密码校验策略

测试, (当前默认策略:validate_password_policy=MEDIUM):

mysql> create user test@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user test@'localhost' identified by 'Mysql_123456';
Query OK, 0 rows affected (0.00 sec)

经检测发现, 对已存在账号的密码没有限制。