Issue running pt-upgrade against MySQL 5.6 and MySQL 8.0

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

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

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