Troubles with native auth in xtrabackup 8.0

MySQL & MariaDB Percona XtraBackup
1

Hi Community,

My company asked me to update to mysql 8 and accordingly to percona xtrabackup 8.

To the environment:
I use the debian:buster Docker Container and install Xtrabackup 8.0.9-1 via dpkg

For backward compatibility reasons I would like to offer native authentication as well. The MySQL server has the following setting:

mysql> show global variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

When I run xtrabackup, I get the following error:

Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;host=db','root',...) failed: Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1536.

But when I run a mysql client, I can connect without problems. I have extended my.cnf and xtrabackup --print-defaults tells me the following:

root@092f323f7741:/# xtrabackup --print-defaults
xtrabackup would have been started with the following arguments:
--default-authentication-plugin=mysql_native_password --default-authentication-plugin=mysql_native_password --default-authentication-plugin=mysql_native_password

Nevertheless xtrabackup 8 does not use the native way. What am I doing wrong?

Thanks for help
Dennis

2

Hello dennis.inside

There were a few blog posts and doc pages around native authentication. Not in the context of Percona Xtrabackup but I’m wondering if they are relevant here. It to do with forcing authentication. Here are a couple of examples:

https://www.percona.com/blog/2018/01…_socket-users/
[URL=“https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-mysql-ps8.html”]https://www.percona.com/doc/percona-...mysql-ps8.html[/URL]

If those aren’t relevant, let me know the exact versions of MySQL (Percona Server for MySQL?) and perhaps share the my.cnf?
I’ll also check in with the team anyway as in that first error you shared there’s this reference

/usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so

Which I thought was curious as it references mariadb19.

3

Hello again Dennis

The team asked if I could get you to confirm the source version of MySQL that you are migrating from and information about the current installation…

You should get some of this by running

mysqld -v

Also are you running this in a container? Information like that will really help.

4

Hello again Dennis

You bumped into a known issue here [URL=“Issue Navigator - Percona JIRA”]https://jira.percona.com/browse/PXB-...a2_password"[/URL]

However, you need to install Percona Xtrabackup on the same host as the server, then you should be able to connect successfully and complete your backup.

One of my colleagues tried this out and here’s the code used, as you can see the error reproduced


# xtrabackup --backup --user=msandbox --target-dir=./ --port=25723 --host=127.0.0.1 --protocol=tcp -p
xtrabackup: recognized client arguments: --backup=1 --user=msandbox --target-dir=./ --port=25723 --host=127.0.0.1 --password 
Enter password: 
xtrabackup version 8.0.10 based on MySQL server 8.0.18 Linux (x86_64) (revision id: 94f9645) 200319 11:19:32 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=25723' as 'msandbox' (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;host=127.0.0.1;port=25723','msandbox',...) failed: Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1536.
200319 11:19:32 Connecting to MySQL server host: 127.0.0.1, user: msandbox, password: set, port: 25723, socket: not set
Using server version 8.0.18
xtrabackup: uses posix_fadvise().
xtrabackup: Can't change dir to '/home/percona/sandbox/msb_glibc8.0.18/data/' (OS errno 2 - No such file or directory)
xtrabackup: cannot my_setwd /home/percona/sandbox/msb_glibc8.0.18/data/

You can see the same error occurred because they connected to find the data directory but it was inaccessible to Xtrabackup

As you’re running in a container, you might be interested to read this blog post but… installing PXB in the same host is probably the way to go if you can.
[URL=“Running Percona XtraBackup on Windows ... in Docker - Percona Database Performance Blog”]https://www.percona.com/blog/2017/03...indows-docker/[/URL]

Let us know if you have any more questions? Thanks!

5

sorry I didn’t get back to you sooner. I was out of the office last week.

Thanks for the help. In the meantime, we have decided not to use the native auth anymore.

I think there’s something I didn’t quite explain. I don’t want to migrate a database. I wanted to support a simple swap of mysql 5.7 and mysql 8 with the same PXB container (same version). But now we will not do that, because it is too time consuming.

I will now try to run mysql 8 and PXB 8 together in separate containers. At the moment I still get the error there as well:

Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;host=db','root',...) failed: Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1536.

while

mysql -u root -p -h db

works well if i type this in the PXB container.

Here the full response of starting a backup:

xtrabackup: recognized client arguments: --backup=1 --host=db --user=root --password=* --target-dir=/xtrabackup_backupfiles/2020-03-23_14-12-56/full

xtrabackup version 8.0.9 based on MySQL server 8.0.18 Linux (x86_64) (revision id: c5cbbe4)

200323 14:12:56 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=db' as 'root' (using password: NO).

Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;host=db','root',...) failed: Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1536.

200323 14:12:56 Connecting to MySQL server host: db, user: root, password: set, port: not set, socket: not set

Failed to connect to MySQL server: Access denied for user 'root'@'192.168.80.2' (using password: NO).

I’m sure that there is something wrong with my way to go with the password :slight_smile:

To be complete, here is my docker-compose File:


version: '3.3' 
services: 
db: 
image: mysql:8.0 
environment:
MYSQL_ROOT_PASSWORD: 'XXX'
volumes:
- ./volumes/db-data:/var/lib/mysql
backup:
build: xtrabackupd8
volumes:
- ./volumes/db-data:/var/lib/mysql
- ./volumes/backup:/xtrabackup_backupfiles
secrets:
- database_password

secrets:
database_password:
external: true

Dennis

// Edit:
After I pass the password directly to

xtrabackup --pasword

It seems to work :slight_smile:

Thank you for trying to help me.
I wish you a nice day and stay healthy

6

Thanks for the update. I have to say that this seems very particular to your installation, so it gets hard for us to help you in such detail in the ‘open source’ forum (other users might comment though)…

If you come to a point where you could do with some of our professional advice, please do get in touch… you can email to my Forum username @percona.com and it will reach me OK.

7

Run the following query on the mysql server :

ALTER USER ‘<mysql_yser>’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘[YOUR_PASSWORD]’;

the message :

“Failed to connect to MySQL server: DBI connect(‘;mysql_read_default_group=xtrabackup;host=db’,‘root’,…) failed: Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at - line 1536.”

will disappear

Line