default_authentication_plugin 参数的设定

前言：最近做了个​

​MySQL​

​的压力测试，使用的是​

​sysbench​

​测试工具大家感兴趣可以看看  ​​使用这个工具的使用发现了一个之前没有遇到的问题，因为使用​

​sysbench​

​的时候需要远程登录​

​MySQL​

​，

使用​

​-u root -p​

​的时候弹出了错误​

​error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded​

去官网查了查，发现是版本的问题，原来​

​Mysql​

​在之前的版本的关于​

​password​

​的加密方法都是使用的  ​

​mysql_native_password​

不过到​

​MySQL8.0​

​的时候换成了​

​caching_sha2_password​

​，所以就会报错，需要我们安装额外的插件，下面我们就来演示一下如何不用装插件的方法来规避这个错误。

演示：

  运行环境：​

​Centos7.4​

​ +​

​MySQL 8.0.11​

​版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)

mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | caching_sha2_password |
| localhost | mysql.infoschema | mysql_native_password |
| localhost | mysql.session    | mysql_native_password |
| localhost | mysql.sys        | mysql_native_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)
--------------------- 
      

​Centos7.4​

​MySQL 5.7​

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.15-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)

mysql> select host,user,plugin from mysql.user;
+-----------+-----------+-----------------------+
| host      | user      | plugin                |
+-----------+-----------+-----------------------+
| localhost | root      | mysql_native_password |
| localhost | mysql.sys | mysql_native_password |
| %         | root      | mysql_native_password |
--------------------- 
      

  可以看到​

​MySQL8.0.11​

​版本默认的认证方式是​

​caching_sha2_password​

​，而在​

​MySQL5.7​

​版本则为​

​mysql_native_password​

​。若想在​

​MySQL8.0​

​版本中继续使用旧版本中的认证方式需要在​

​my.cnf​

​文件中配置并重启，因为此参数不可动态修改。

mysql> set global default_authentication_plugin='mysql_native_password';
ERROR 1238 (HY000): Variable 'default_authentication_plugin' is a read only variable
      

  写入​

​my.cnf​

​文件后重启​

​MySQL​

​：

vim my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
      

  另一种解决方法：兼容新老版本的认证方式。

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER; #修改加密规则 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; #更新一下用户的密码 
FLUSH PRIVILEGES; #刷新权限
--创建新的用户：
create user root@'%' identified WITH mysql_native_password BY 'root';
grant all privileges on *.* to root@'%' with grant option;
flush privileges;
--在MySQL8.0创建用户并授权的语句则不被支持：
mysql> grant all privileges on *.* to root@'%' identified by 'root' with grant option;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'root' with grant option' at line 1
mysql> 
      

总结：