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