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()