??前言：最近做了個(gè)MySQL的壓力測(cè)試碌识，使用的是sysbench測(cè)試工具大家感興趣可以看看sysbench的github峭梳，使用這個(gè)工具的使用發(fā)現(xiàn)了一個(gè)之前沒(méi)有遇到的問(wèn)題舰绘，因?yàn)槭褂?code>sysbench的時(shí)候需要遠(yuǎn)程登錄MySQL，使用-u root -p的時(shí)候彈出了錯(cuò)誤error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded，去官網(wǎng)查了查除盏，發(fā)現(xiàn)是版本的問(wèn)題叉橱，原來(lái)Mysql在之前的版本的關(guān)于password的加密方法都是使用的mysql_native_password，不過(guò)到MySQL8.0的時(shí)候換成了caching_sha2_password者蠕，所以就會(huì)報(bào)錯(cuò)窃祝，需要我們安裝額外的插件，下面我們就來(lái)演示一下如何不用裝插件的方法來(lái)規(guī)避這個(gè)錯(cuò)誤踱侣。

演示：

??運(yùn)行環(huán)境：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)
--------------------- 

??運(yùn)行環(huán)境：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版本默認(rèn)的認(rèn)證方式是caching_sha2_password粪小，而在MySQL5.7版本則為mysql_native_password。若想在MySQL8.0版本中繼續(xù)使用舊版本中的認(rèn)證方式需要在my.cnf文件中配置并重啟抡句，因?yàn)榇藚?shù)不可動(dòng)態(tài)修改探膊。

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

??寫(xiě)入my.cnf文件后重啟MySQL：

vim my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password

??另一種解決方法：兼容新老版本的認(rèn)證方式。

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER; #修改加密規(guī)則 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; #更新一下用戶的密碼 
FLUSH PRIVILEGES; #刷新權(quán)限
    --創(chuàng)建新的用戶：
    create user root@'%' identified WITH mysql_native_password BY 'root';
    grant all privileges on *.* to root@'%' with grant option;
    flush privileges;
    --在MySQL8.0創(chuàng)建用戶并授權(quán)的語(yǔ)句則不被支持：
    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> 

總結(jié)：

??以上就是解決這個(gè)問(wèn)題的兩種方法了待榔，大家還有新的方法可以留言來(lái)告訴我哦逞壁。