Issue running pt-upgrade against MySQL 5.6 and MySQL 8.0

Polyglot Projects Percona Toolkit
1

Working with a Percona consultant to help with our migration from community versions of MySQL 5.6 to MySQL 8.0. We’re running on CentOS 7.9.

One of our goals is to get some comparison metrics. To that end, here is what we’ve done:

  • created test instances of MySQL 5.6 and MySQL 8.0
  • both instances are up and running using sockets
  • each instance has a snapshot of production data
  • created a distilled slowlog from our production instance slowlog using pt-query-digest

Finally, here’s how we’re running pt-upgrade:
pt-upgrade S=/tmp/mysql-5.6.sock S=/tmp/mysql-8.0.sock distilled_slowquery.log

This gives us this error:

DBI connect(';mysql_socket=/tmp/mysql-8.0.sock;mysql_read_default_group=client','',...) failed: 
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 at /usr/bin/pt-upgrade line 998.

If I look at the Percona doc for pt-upgrade I get the impression that the tool doesn’t support MySQL 5.6 however if I change the parameters I use for pt-upgrade and use (comparing the 5.6 instance against itself):
pt-upgrade S=/tmp/mysql-5.6.sock S=/tmp/mysql-5.6.sock distilled_slowquery.log

We get:

*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /usr/bin/pt-upgrade line 3643.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /usr/bin/pt-upgrade line 3643.

# A software update is available:

#-----------------------------------------------------------------------
# Logs
#-----------------------------------------------------------------------

File: distilled_slowquery.log
Size: 1606660

#-----------------------------------------------------------------------
# Hosts
#-----------------------------------------------------------------------

host1:

  DSN:       S=/tmp/mysql-5.6.sock
  hostname:  vtg.com
  MySQL:     MySQL Community Server (GPL) 5.6.51

host2:

  DSN:       S=/tmp/mysql-5.6.sock
  hostname:  vtg.com
  MySQL:     MySQL Community Server (GPL) 5.6.51
<---snip--->

Thoughts on how to get this tool to work with 5.6 compared to 8.0?
Thanks!!

1 Like
2

Hi, the problem is not related to 5.6. It is 8.0 new authentication plugin. Make sure you upgrade client libraries and try again.

1 Like
3

Also make sure you configure my.cnf in mysql 8 to include:
default-authentication-plugin=mysql_native_password

As by default mysql 8 tries to use SSL connections.

1 Like
4

@Ivan_Groenewold I am using mysql8.0.34 and all client libraries are latest i.e. 8.0.34 but after moving from mysql_native_password to cashing_sha2_password all is good except percona tool kit utilities like pt-kill, pt-archiver etc are not working getting below error message -

DBI connect(‘;host=127.0.0.1;mysql_read_default_group=client’,‘zafar’,…) failed: 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 at /bin/pt-kill line 2095.

May you please help here or guide me.

[root@test]# yum list installed | grep client
dhclient.x86_64                         12:4.2.5-83.el7.centos.1       @updates
libsmbclient.x86_64                     4.10.16-25.el7_9               @updates
libwbclient.x86_64                      4.10.16-25.el7_9               @updates
mysql-commercial-client.x86_64          8.0.34-1.1.el7                 @/mysql-commercial-client-8.0.34-1.1.el7.x86_64
mysql-commercial-client-plugins.x86_64  8.0.34-1.1.el7                 @/mysql-commercial-client-plugins-8.0.34-1.1.el7.x86_64
openldap-clients.x86_64                 2.4.44-25.el7_9                @updates
openssh-clients.x86_64                  7.4p1-23.el7_9                 @updates
pmm2-client.x86_64                      2.40.0-6.el7                   @percona-release-x86_64
samba-client-libs.x86_64                4.10.16-25.el7_9               @updates
sssd-client.x86_64                      1.16.5-10.el7_9.15             @updates
5

After upgrading perl-DBD-mysql version from 4.023 (which is officially latest updated version compatible with centos7) to 4.050 it is working fine.