MySQL8 修改密码验证插件
查看当前用户使用的密码验证插件
mysql> show variables like '%auth%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
查看 MySQL8 支持的密码验证插件
mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
修改用户的密码验证插件
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'root';
Query OK, 0 rows affected (0.45 sec)
修改系统默认的密码验证插件
- 配置参数方式
default-authentication-plugin
# 设置默认密码验证插件
default-authentication-plugin=caching_sha2_password
- 启动参数方式
--default-authentication-plugin
C:\Users\jie>D:\chengxu\MySQL\mysql-8.0.12-winx64\bin\mysqld --default-authentication-plugin=mysql_native_password
bugs
如果只指定用户名不指定主机，则表示的用户是任意主机，即 ‘username’@’%’，这个和本地主机即 ‘username’@’localhost’ 的含义是不同的。
# 'root' 更改失败，因为表示的是 'root'@'%'，而此处用户只有'root'@'localhost'
mysql> alter user 'root' identified with mysql_native_password by 'root';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'mysql> select host, user, plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| localhost | caocao | caching_sha2_password |
| localhost | liubei | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
+-----------+------------------+-----------------------+
无法通过命令行方式设置默认的密码验证插件
mysql> set default_authentication_plugin=mysql_native_password;
ERROR 1238 (HY000): Variable 'default_authentication_plugin' is a read only variable
mysql> set global default_authentication_plugin=mysql_native_password;
ERROR 1238 (HY000): Variable 'default_authentication_plugin' is a read only variable
