mysql8 修改密码插件_MySQL8 修改密码验证插件

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