MySQL笔记
- 报错信息
error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
环境 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)
MySQL5.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。
解决方法:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER; #修改加密规则
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; #更新一下用户的密码
mysql> FLUSH PRIVILEGES; #刷新权限
--创建新的用户：
mysql> create user root@'%' identified WITH mysql_native_password BY 'root';
mysql> grant all privileges on *.* to root@'%' with grant option;
mysql> 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>