FATAL error 2059 Authentication plugin caching_sha2_password cannot be loaded

1、报错

# 执行sysbench
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=127.0.0.1  --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=sbtest --db-driver=mysql --tables=5 --table-size=500000 --report-interval=3 --time=3000 --threads=6  --max-requests=0  prepare

# 报错
FATAL: unable to connect to MySQL server on host '127.0.0.1', port 3306, aborting...
(last message repeated 1 times)
FATAL: 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版本检查
> mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: **8.0.20** MySQL Community Server - GPL
           

2、原因

找原因

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-4.html 8.0.4的官方release

注意2点：第一不兼容；第二默认插件改了，从 mysql_native_password 到 caching_sha2_password了

在这里插入图片描述

3、解决

https://github.com/akopytov/sysbench/issues/244

https://lefred.be/content/sysbench-for-mysql-8-0/

• 修改使用原来的的mysql_native_password方式[为了兼容原版，这个操作最快]
• 编译sysbench linking it to mysql-community-libs-8.0.x (/usr/lib64/mysql/libmysqlclient.so.21)
• wget sysbench文件[这里还是需要新版的MySQL的]

4、疑问：为啥直接MySQL可以，而使用sysbench才报错呢

/usr/share/sysbench/oltp_common.lua:83 行报错

81 function cmd_prepare()
 82    local drv = sysbench.sql.driver()
 83    local con = drv:connect()