Not the answer you need?
Register and ask your own question!

Troubles with native auth in xtrabackup 8.0

dennis.insidedennis.inside EntrantCurrent User Role Novice
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

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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/
    https://www.percona.com/doc/percona-...mysql-ps8.html

    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.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hello again Dennis

    You bumped into a known issue here https://jira.percona.com/browse/PXB-...a2_password%22

    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.
    https://www.percona.com/blog/2017/03...indows-docker/

    Let us know if you have any more questions? Thanks!
  • dennis.insidedennis.inside Entrant Current User Role Novice
    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 :-)

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

    Thank you for trying to help me.
    I wish you a nice day and stay healthy
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.