Percona Toolkit工具連線MySQL 8報錯的解決方案

瀟湘隱者發表於2020-10-28
MySql

使用Percona Toolkit的工具連線MySQL 8.x資料庫時，會遇到類似”failed: Plugin caching_sha2_password could not be loaded: lib64/mariadb/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at /usr/bin/pt-show-grants line 1367“這樣的錯誤，如下案例所示：

 

 

#pt-find --ctime +2 --engine InnoDB -u monitor --ask-pass 
Enter password: 
DBI connect(';;mysql_read_default_group=client','monitor',...) failed: Plugin caching_sha2_password could not be loaded: lib64/mariadb/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at /usr/bin/pt-find line 323.

 

遇到這個錯誤是因為MySQL 8.0（8.0.4）引入了caching_sha2_password外掛並將其作為預設的身份驗證外掛，而當前Percona Toolkit（Percona Toolkit 3.2.1是當前最新版本）的工具依賴的MySQL驅動程式尚不支援caching_sha2_password這這種身份驗證，所以報這個錯誤。例如pt-find使用的驅動為MySQL driver for Perl (DBD::mysql)（perl-DBD-MySQL）

 

 

解決方案：

 

 

1：更改使用者的密碼外掛為mysql_native_password

 

mysql> select user,host ,plugin from mysql.user where user='monitor';
+---------+-----------+-----------------------+
| user    | host      | plugin                |
+---------+-----------+-----------------------+
| monitor | localhost | caching_sha2_password |
+---------+-----------+-----------------------+
1 row in set (0.00 sec)
 
 
mysql> alter user monitor@'localhost' identified  with mysql_native_password by '************';
Query OK, 0 rows affected (0.07 sec)

 

這種方法就是讓MySQL使用mysql_native_password外掛認證密碼，這些驅動自然支援這種方式連線MySQL資料庫。

 

 

2：修改MySQL的全域性配置

 

    在my.cnf配置檔案中設定default_authentication_plugin=mysql_native_password 後重啟MySQL資料庫例項。這是一種不太安全的做法，按理說只需修改Percona Toolkit的工具使用的賬號即可。但是這種方法修改的是MySQL的全域性配置。影響範圍擴大，不推薦使用

 

 

[mysqld]

default_authentication_plugin=mysql_native_password

 

 

3：更新驅動

 

MySQL 8.0.4開始預設使用caching_sha2_password作為身份驗證外掛. caching_sha2_password和 sha256_password認證外掛提供比mysql_native_password外掛更安全的密碼加密方式 ，並且使用caching_sha2_password的效能比sha256_password更好。如果我們修改使用者的密碼外掛為mysql_native_password，似乎有點開倒車的感覺。其實這個問題也好解決，如果我們想用新的密碼驗證方式，那麼我們可以安裝高版本的驅動。這些驅動支援這種方式。

 

如下所示，當前測試環境，perl-DBD-MySQL模組依賴的類庫為/usr/lib64/mysql/libmysqlclient.so.18。在CentOS/RHEL/OL 7上，預設情況下，perl-DBD-MySQL要麼連結到舊版本的mariadb-libs（5.5），或連結mysql-community-libs-compat下的類庫。

 

[root@KerryDB ~]# more /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 
[root@KerryDB ~]# ldd /usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so | grep  'mysql\|maria'
        libmysqlclient.so.18 => /usr/lib64/mysql/libmysqlclient.so.18 (0x00007f48b0724000)
[root@KerryDB ~]# rpm -qf /usr/lib64/mysql/libmysqlclient.so.18
mysql-community-libs-compat-8.0.18-1.el7.x86_64

 

clip_image001

 

此版本（libmysqlclient.so.18）不支援新的身份驗證外掛。至少需要有libmysqlclient.so.21或libmariadb.so.3 或以上版本的類庫才支援caching_sha2_password認證方式。 所以，我可以下載部落格Perl & MySQL 8.0中的perl-DBD-MySQL-4.044-1.el7_.x86_64.zip 或perl-DBD-MySQL-4.050-1.el7.x86_64.rpm元件。

 

https://lefred.be/wp-content/uploads/2019/06/perl-DBD-MySQL-4.044-1.el7_.x86_64.zip

https://lefred.be/wp-content/uploads/2020/08/perl-DBD-MySQL-4.050-1.el7.x86_64.rpm

 

具體安裝過程如下， 安裝完成後，Percona Tookkit就能正常連線MySQL 8了

 

[root@KerryDB tmp]#  rpm -ivh perl-DBD-MySQL-4.044-1.el7.x86_64.rpm #此處報衝突，因為 perl-DBD-MySQL-4.023-6.el7.x86_64與perl-DBD-MySQL-4.044-1.el7.x86_64.rpm衝突了。
Preparing...                          ################################# [100%]
        file /usr/lib64/perl5/vendor_perl/Bundle/DBD/mysql.pm from install of perl-DBD-MySQL-4.044-1.el7.x86_64 conflicts with file from package perl-DBD-MySQL-4.023-6.el7.x86_64
        file /usr/lib64/perl5/vendor_perl/DBD/mysql.pm from install of perl-DBD-MySQL-4.044-1.el7.x86_64 conflicts with file from package perl-DBD-MySQL-4.023-6.el7.x86_64
        file /usr/lib64/perl5/vendor_perl/DBD/mysql/GetInfo.pm from install of perl-DBD-MySQL-4.044-1.el7.x86_64 conflicts with file from package perl-DBD-MySQL-4.023-6.el7.x86_64
        file /usr/lib64/perl5/vendor_perl/DBD/mysql/INSTALL.pod from install of perl-DBD-MySQL-4.044-1.el7.x86_64 conflicts with file from package perl-DBD-MySQL-4.023-6.el7.x86_64
        file /usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so from install of perl-DBD-MySQL-4.044-1.el7.x86_64 conflicts with file from package perl-DBD-MySQL-4.023-6.el7.x86_64
        file /usr/share/man/man3/Bundle::DBD::mysql.3pm.gz from install of perl-DBD-MySQL-4.044-1.el7.x86_64 conflicts with file from package perl-DBD-MySQL-4.023-6.el7.x86_64
        file /usr/share/man/man3/DBD::mysql.3pm.gz from install of perl-DBD-MySQL-4.044-1.el7.x86_64 conflicts with file from package perl-DBD-MySQL-4.023-6.el7.x86_64
        file /usr/share/man/man3/DBD::mysql::INSTALL.3pm.gz from install of perl-DBD-MySQL-4.044-1.el7.x86_64 conflicts with file from package perl-DBD-MySQL-4.023-6.el7.x86_64
[root@KerryDB tmp]# rpm -Uvh perl-DBD-MySQL-4.044-1.el7.x86_64.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:perl-DBD-MySQL-4.044-1.el7       ################################# [ 50%]
Cleaning up / removing...
   2:perl-DBD-MySQL-4.023-6.el7       ################################# [100%]
[root@KerryDB tmp]#

 

clip_image002

 

 

當然，我們也可以下載原始碼安裝，下載地址如下，但是最好使用cpan安裝(cpan -f DBD::mysql)，如果手工安裝的話，各種依賴項缺少，會讓你抓狂！ 下面是我的一個原始碼安裝的折騰過程。

 

https://cpan.metacpan.org/authors/id/D/DV/DVEEDEN/

 

從如下連結下載了DBD-mysql-4.050.tar.gz

 

https://cpan.metacpan.org/authors/id/D/DV/DVEEDEN/DBD-mysql-4.050.tar.gz

 

 

# tar -xzvf DBD-mysql-4.050.tar.gz 
# cd DBD-mysql-4.050
# perl Makefile.PL
Can't locate Devel/CheckLib.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 12.
BEGIN failed--compilation aborted at Makefile.PL line 12.

 

如上所示，遇到這個錯誤，是因為沒有安裝perl-Devel-CheckLib包，缺少相關依賴元件。在安裝包的檔案META.json中，你就可以看到安裝這個需要安裝哪些元件。如下截圖所示：

 

clip_image003

 

 

 

[root@KerryDB DBD-mysql-4.050]# yum list | grep "perl-Devel-CheckLib"
perl-Devel-CheckLib.noarch               0.99-2.el7                    base     
[root@KerryDB DBD-mysql-4.050]# yum list installed | grep "perl-Devel-CheckLib"
[root@KerryDB DBD-mysql-4.050]# yum install perl-Devel-CheckLib

 

接下來又遇到了“Can't exec "mysql_config": No such file or directory at Makefile.PL line 89”這個錯誤

 

[root@KerryDB DBD-mysql-4.050]# perl Makefile.PL
Can't exec "mysql_config": No such file or directory at Makefile.PL line 89.
 
Cannot find the file 'mysql_config'! Your execution PATH doesn't seem
not contain the path to mysql_config. Resorting to guessed values!
 
 
PLEASE NOTE:
 
For 'make test' to run properly, you must ensure that the
database user 'root' can connect to your MySQL server
and has the proper privileges that these tests require such
as 'drop table', 'create table', 'drop procedure', 'create procedure'
as well as others.
 
mysql> grant all privileges on test.* to 'root'@'localhost' identified by 's3kr1t';
 
You can also optionally set the user to run 'make test' with:
 
perl Makefile.PL --testuser=username
 
Can't exec "mysql_config": No such file or directory at Makefile.PL line 603.
Can't find mysql_config. Use --mysql_config option to specify where mysql_config is located
Failed to determine directory of mysql.h. Use
 
  perl Makefile.PL --cflags=-I<dir>
 
to set this directory. For details see DBD::mysql::INSTALL,
section "C Compiler flags" or type
 
  perl Makefile.PL --help
 
 
[root@KerryDB mysql]#  which mysql_config
/usr/bin/which: no mysql_config in (/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
 
[root@KerryDB DBD-mysql-4.050]# locate mysql_config
/usr/bin/mysql_config_editor
/usr/share/man/man1/mysql_config_editor.1.gz

 

查一下相關資料，解決這個問題要麼安裝libmysqlclient-dev，要麼在系統中查詢mysql_config檔案,在原始碼安裝過程中加上引數選項（如下所示），具體情況根據你的環境而定，有可能你在原始碼安裝過程還會遇到不同的錯誤。

 

 

# perl Makefile.PL --mysql_config=/usr/bin/mysql_config_editor
#make
#make test
#make install

 

更多安裝相關資料，可以參考文件https://metacpan.org/pod/DBD::mysql::INSTALL。

 

 

 

參考資料：

https://lefred.be/content/perl-mysql-8-0/

https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-mysql-ps8.html

相關文章