SST with xtrabackup_v2 fails with access denied for sst user on donor node

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi,

I have restored and bootstrapped the first node for my Percona XtraDB Cluster (8.0). However as I try to add the second node to the cluster I get the following error:

..Waiting for SST streaming to complete!
2023-04-15T17:36:03.153832+03:00 0 [Note] [MY-000000] [Galera] (d6fa3e5e-8c53, 'ssl://0.0.0.0:4567') turning message relay requesting off
2023-04-15T17:36:12.942347+03:00 0 [ERROR] [MY-000000] [WSREP-SST] ******************* FATAL ERROR **********************
2023-04-15T17:36:12.942382+03:00 0 [ERROR] [MY-000000] [WSREP-SST] xtrabackup_checkpoints missing. xtrabackup/SST failed on DONOR. Check DONOR log
2023-04-15T17:36:12.942401+03:00 0 [ERROR] [MY-000000] [WSREP-SST] Line 2417
2023-04-15T17:36:12.942434+03:00 0 [ERROR] [MY-000000] [WSREP-SST] ******************************************************
2023-04-15T17:36:12.942534+03:00 0 [ERROR] [MY-000000] [WSREP-SST] Cleanup after exit with status:2
2023-04-15T17:36:12.956064+03:00 0 [Warning] [MY-000000] [Galera] 0.0 (crdltstpdb11): State transfer to 1.0 (crdltstpdb21) failed: -22 (Invalid argument)
2023-04-15T17:36:12.956085+03:00 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():1216: Will never receive state. Need to abort.
2023-04-15T17:36:12.956106+03:00 0 [Note] [MY-000000] [Galera] gcomm: terminating thread
2023-04-15T17:36:12.956124+03:00 0 [Note] [MY-000000] [Galera] gcomm: joining thread
2023-04-15T17:36:12.956215+03:00 0 [Note] [MY-000000] [Galera] gcomm: closing backend
2023-04-15T17:36:13.225295+03:00 0 [ERROR] [MY-000000] [WSREP] Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.29.128.11' --datadir '/var/lib/mysql/' --basedir '/usr/' --plugindir '/usr/lib64/mysql/plugin/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '15285' --mysqld-version '8.0.31-23.2'   '' : 2 (No such file or directory)
2023-04-15T17:36:13.225492+03:00 0 [ERROR] [MY-000000] [WSREP] Failed to read uuid:seqno from joiner script.
2023-04-15T17:36:13.225510+03:00 0 [ERROR] [MY-000000] [WSREP] SST script aborted with error 2 (No such file or directory)
2023-04-15T17:36:13.225577+03:00 3 [Note] [MY-000000] [Galera] Processing SST received

on the donor node I can see the following error:

2023-04-15T17:36:12.931827+03:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --open_files_limit=2000000 --log_bin=crdltstpdb11-bin --log-bin-index=crdltstpdb11-bin.index --defaults_group=mysqld
2023-04-15T17:36:12.932049+03:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --target-dir=/var/backup/mysql/xtrabackup/ --xtrabackup-plugin-dir=/usr/lib64/xtrabackup/plugin/ --parallel=4 --compress --compress-threads=4 --check-privileges=1 --galera-info=1 --user=backup --password=* --host=127.0.0.1 --ssl-ca=/etc/mysql/certificates/client-cert.pem --ssl-cert=/etc/mysql/certificates/client-cert.pem --ssl-key=/etc/mysql/certificates/client-key.pem --port=3306 --no-version-check=1 --parallel=4 --user=mysql.pxc.sst.user --password=* --socket=/var/lib/mysql/mysql.sock --lock-ddl=1 --backup=1 --galera-info=1 --transition-key=* --stream=xbstream --xtrabackup-plugin-dir=/usr/bin/pxc_extra/pxb-8.0/lib/plugin --target-dir=/tmp/pxc_sst_51A1/donor_xb_aXzI
/usr/bin/pxc_extra/pxb-8.0/bin/xtrabackup version 8.0.32-26 based on MySQL server 8.0.32 Linux (x86_64) (revision id: 34cf2908)
2023-04-15T17:36:12.932080+03:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: 127.0.0.1, user: mysql.pxc.sst.user, password: set, port: 3306, socket: /var/lib/mysql/mysql.sock
2023-04-15T17:36:12.937894+03:00 0 [ERROR] [MY-011825] [Xtrabackup] Failed to connect to MySQL server: Access denied for user 'mysql.pxc.sst.user'@'127.0.0.1' (using password: YES)

my wsrep settings:

wsrep_provider=/usr/lib64/galera4/libgalera_smm.so
wsrep_provider_options="socket.ssl=yes;socket.ssl_ca=/etc/mysql/certificates/server-cert.pem;socket.ssl_cert=/etc/mysql/certificates/server-cert.pem;socket.ssl_key=/etc/mysql/certificates/server-key.pem;socket.ssl_cipher=ECDHE-RSA-AES256-GCM-SHA384;base_dir=/var/lib/mysql"
wsrep_cluster_name=crdltstpdb
wsrep_cluster_address=gcomm://10.29.128.10:4567,10.29.128.11:4567
wsrep_node_name=crdltstpdb11
wsrep_node_address=10.29.128.10
wsrep_sst_method=xtrabackup-v2
wsrep_applier_threads=8
wsrep_log_conflicts

worth noting is that I was using the same cluster config before I took the XtraBackup (which I am now restoring) and the cluster was running fine and in sync. Note that I have recently upgraded this cluster from 5.7 → 8.0 so some settings might be out of date for the new cluster version

2

I have also bootstrapped the cluster from the other node with the same result

3

Hello @crimson_pirate,
This is odd because in PXC 8 we introduced automatic password management for the SST process. Can you manually delete this user from your bootstrapped node and attempt the SST again? The user should get auto-recreated with a password the script knows about.

4

Hi,

Thank you for the reply. I couldn’t find the ‘mysql.pxc.sst.user’@‘127.0.0.1’ user on the bootstrapped node at all so not sure it was even created. In the config file under [xtrabackup] I tried changing host=127.0.0.1 to host=localhost and also commented out the “skip-name-resolve” parameter. Now at least I can see that the mysql.pxc.sst.user have tried to run some queries as “localhost” his time. However it seems to fail with some privilege issues when trying to add the 2nd node as the donor log indicates:

2023-05-24T13:11:59.211460+03:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --open_files_limit=2000000 --log_bin=crdltstpdb11-bin --log-bin-index=crdltstpdb11-bin.index --defaults_group=mysqld
2023-05-24T13:11:59.211705+03:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --target-dir=/var/backup/mysql/xtrabackup/ --xtrabackup-plugin-dir=/usr/lib64/xtrabackup/plugin/ --parallel=4 --compress --compress-threads=4 --check-privileges=1 --galera-info=1 --user=backup --password=* --host=localhost --ssl-ca=/etc/mysql/certificates/client-cert.pem --ssl-cert=/etc/mysql/certificates/client-cert.pem --ssl-key=/etc/mysql/certificates/client-key.pem --port=3306 --socket=/var/lib/mysql/mysql.sock --no-version-check=1 --parallel=4 --user=mysql.pxc.sst.user --password=* --socket=/var/lib/mysql/mysql.sock --lock-ddl=1 --backup=1 --galera-info=1 --transition-key=* --stream=xbstream --xtrabackup-plugin-dir=/usr/bin/pxc_extra/pxb-8.0/lib/plugin --target-dir=/tmp/pxc_sst_RpYz/donor_xb_kYdO
/usr/bin/pxc_extra/pxb-8.0/bin/xtrabackup version 8.0.32-26 based on MySQL server 8.0.32 Linux (x86_64) (revision id: 34cf2908)
2023-05-24T13:11:59.213809+03:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: mysql.pxc.sst.user, password: set, port: 3306, socket: /var/lib/mysql/mysql.sock
2023-05-24T13:11:59.232225+03:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.31-23.2
2023-05-24T13:11:59.247382+03:00 0 [ERROR] [MY-011825] [Xtrabackup]  missing required privilege SHOW DATABASES on *.*
2023-05-24T13:11:59.247575+03:00 0 [ERROR] [MY-011825] [Xtrabackup] failed to execute query 'SHOW DATABASES LIKE 'PERCONA_SCHEMA';' : 1227 (42000) Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation

audit log:

<AUDIT_RECORD>
  <NAME>Query</NAME>
  <RECORD>1505618_2023-05-24T10:35:09</RECORD>
  <TIMESTAMP>2023-05-24T10:36:01Z</TIMESTAMP>
  <COMMAND_CLASS>show_grants</COMMAND_CLASS>
  <CONNECTION_ID>21</CONNECTION_ID>
  <STATUS>0</STATUS>
  <SQLTEXT>SHOW GRANTS</SQLTEXT>
  <USER>mysql.pxc.sst.user[mysql.pxc.sst.user] @ localhost []</USER>
  <HOST>localhost</HOST>
  <OS_USER></OS_USER>
  <IP></IP>
  <DB></DB>
</AUDIT_RECORD>
<AUDIT_RECORD>
  <NAME>Query</NAME>
  <RECORD>1505619_2023-05-24T10:35:09</RECORD>
  <TIMESTAMP>2023-05-24T10:36:01Z</TIMESTAMP>
  <COMMAND_CLASS>show_databases</COMMAND_CLASS>
  <CONNECTION_ID>21</CONNECTION_ID>
  <STATUS>1227</STATUS>
  <SQLTEXT>SHOW DATABASES LIKE 'PERCONA_SCHEMA'</SQLTEXT>
  <USER>mysql.pxc.sst.user[mysql.pxc.sst.user] @ localhost []</USER>
  <HOST>localhost</HOST>
  <OS_USER></OS_USER>
  <IP></IP>
  <DB></DB>
</AUDIT_RECORD>
<AUDIT_RECORD>
  <NAME>Quit</NAME>
  <RECORD>1505620_2023-05-24T10:35:09</RECORD>
  <TIMESTAMP>2023-05-24T10:36:01Z</TIMESTAMP>
  <CONNECTION_ID>21</CONNECTION_ID>
  <STATUS>0</STATUS>
  <USER>mysql.pxc.sst.user</USER>
  <PRIV_USER>mysql.pxc.sst.user</PRIV_USER>
  <OS_LOGIN></OS_LOGIN>
  <PROXY_USER></PROXY_USER>
  <HOST>localhost</HOST>
  <IP></IP>
  <DB></DB>
</AUDIT_RECORD>

current privs for old(127.0.0.1) and new(localhost) xtrabackup user:

mysql> show grants for 'backup'@'localhost';
GRANT RELOAD, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON *.* TO `backup`@`localhost`
GRANT AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `backup`@`localhost`
GRANT SELECT, INSERT, CREATE ON `PERCONA_SCHEMA`.* TO `backup`@`localhost`
GRANT ALTER ON `PERCONA_SCHEMA`.`xtrabackup_history` TO `backup`@`localhost`
GRANT SELECT ON `performance_schema`.`keyring_component_status` TO `backup`@`localhost`
GRANT SELECT ON `performance_schema`.`log_status` TO `backup`@`localhost`
GRANT SELECT ON `performance_schema`.`replication_group_members` TO `backup`@`localhost`
mysql> show grants for 'backup'@'127.0.0.1';
GRANT RELOAD, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON *.* TO `backup`@`127.0.0.1`
GRANT AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `backup`@`127.0.0.1`
GRANT SELECT, INSERT, CREATE ON `PERCONA_SCHEMA`.* TO `backup`@`127.0.0.1`
GRANT ALTER ON `PERCONA_SCHEMA`.`xtrabackup_history` TO `backup`@`127.0.0.1`
GRANT SELECT, UPDATE ON `backup_status`.`backup_status_crdlstgpdb11` TO `backup`@`127.0.0.1`
GRANT SELECT, UPDATE ON `backup_status`.`backup_status_crdlstgpdb21` TO `backup`@`127.0.0.1`
GRANT SELECT ON `performance_schema`.`keyring_component_status` TO `backup`@`127.0.0.1`
GRANT SELECT ON `performance_schema`.`log_status` TO `backup`@`127.0.0.1`
GRANT SELECT ON `performance_schema`.`replication_group_members` TO `backup`@`127.0.0.1`
mysql>
5

I tried granting SHOW DATABASES to the mysql.pxc.sst.role since it was missing. now SST proceeds and fails on the joiner node instead with the following error

**************** FATAL ERROR **********************
2023-05-24T14:38:11.737536+03:00 0 [ERROR] [MY-000000] [WSREP-SST] Move failed.
2023-05-24T14:38:11.737576+03:00 0 [ERROR] [MY-000000] [WSREP-SST] Keeping /var/lib/mysql//sst-xb-tmpdir for further diagnosis Check /var/lib/mysql//sst-xb-tmpdir/innobackup.move.log for details /var/lib/mysql//sst-xb-tmpdir may be removed if not needed for further diagnosis.
2023-05-24T14:38:11.737608+03:00 0 [ERROR] [MY-000000] [WSREP-SST] Line 2559
2023-05-24T14:38:11.739302+03:00 0 [ERROR] [MY-000000] [WSREP-SST] ------------ innobackup.move.log (START) ------------
        2023-05-24T14:38:11.552884+03:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --open_files_limit=2000000 --log_bin=crdltstpdb21-bin --log-bin-index=crdltstpdb21-bin.index --defaults_group=mysqld --datadir=/var/lib/mysql/
        2023-05-24T14:38:11.553115+03:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --target-dir=/var/backup/mysql/xtrabackup/ --xtrabackup-plugin-dir=/usr/lib64/xtrabackup/plugin/ --parallel=4 --compress --compress-threads=4 --check-privileges=1 --galera-info=1 --user=backup --password=* --host=localhost --socket=/var/lib/mysql/mysql.sock --ssl-ca=/etc/mysql/certificates/client-cert.pem --ssl-cert=/etc/mysql/certificates/client-cert.pem --ssl-key=/etc/mysql/certificates/client-key.pem --port=3306 --no-version-check=1 --move-back=1 --force-non-empty-directories=1 --transition-key=* --generate-new-master-key=1 --xtrabackup-plugin-dir=/usr/bin/pxc_extra/pxb-8.0/lib/plugin --target-dir=/var/lib/mysql//sst-xb-tmpdir
        /usr/bin/pxc_extra/pxb-8.0/bin/xtrabackup version 8.0.32-26 based on MySQL server 8.0.32 Linux (x86_64) (revision id: 34cf2908)
        2023-05-24T14:38:11.553143+03:00 0 [Note] [MY-011825] [Xtrabackup] cd to /var/lib/mysql/sst-xb-tmpdir/
        2023-05-24T14:38:11.559483+03:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
        2023-05-24T14:38:11.559513+03:00 0 [Note] [MY-011825] [Xtrabackup] Loading xtrabackup_keys
        2023-05-24T14:38:11.647991+03:00 0 [Note] [MY-011825] [Xtrabackup] Loading xtrabackup_keys
        2023-05-24T14:38:11.735531+03:00 0 [ERROR] [MY-011370] [InnoDB] Plugin keyring_file reported: 'File '/etc/mysql/mysql-keyring/keyring.backup' not found (OS errno 30 - Read-only file system)'
        2023-05-24T14:38:11.735572+03:00 0 [ERROR] [MY-011373] [InnoDB] Plugin keyring_file reported: 'Could not flush keys to keyring's backup'
        2023-05-24T14:38:11.735598+03:00 0 [ERROR] [MY-012656] [InnoDB] Encryption can't find master key, please check the keyring is loaded. ret=0
        2023-05-24T14:38:11.735608+03:00 0 [ERROR] [MY-011825] [Xtrabackup] can't generate new master key. Please check keyring plugin settings.

2023-05-24T14:38:11.739334+03:00 0 [ERROR] [MY-000000] [WSREP-SST] ------------ innobackup.move.log (END) ------------
2023-05-24T14:38:11.739359+03:00 0 [ERROR] [MY-000000] [WSREP-SST] ******************************************************
2023-05-24T14:38:11.739461+03:00 0 [ERROR] [MY-000000] [WSREP-SST] Cleanup after exit with status:22
2023-05-24T14:38:11.747200+03:00 0 [ERROR] [MY-000000] [WSREP] Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.29.128.11' --datadir '/var/lib/mysql/' --basedir '/usr/' --plugindir '/usr/lib64/mysql/plugin/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --parent '16346' --mysqld-version '8.0.31-23.2'   '' : 22 (Invalid argument)
2023-05-24T14:38:11.747248+03:00 0 [ERROR] [MY-000000] [WSREP] Failed to read uuid:seqno from joiner script.
2023-05-24T14:38:11.747262+03:00 0 [ERROR] [MY-000000] [WSREP] SST script aborted with error 22 (Invalid argument)
2023-05-24T14:38:11.747357+03:00 3 [Note] [MY-000000] [Galera] Processing SST received
2023-05-24T14:38:11.747381+03:00 3 [Note] [MY-000000] [Galera] SST received: 00000000-0000-0000-0000-000000000000:-1
2023-05-24T14:38:11.747402+03:00 3 [System] [MY-000000] [WSREP] SST completed
2023-05-24T14:38:11.747428+03:00 2 [Note] [MY-000000] [Galera]  str_proto_ver_: 3 sst_seqno_: -1 cc_seqno: 48 req->ist_len(): 67
2023-05-24T14:38:11.747456+03:00 2 [ERROR] [MY-000000] [Galera] Application received wrong state:
        Received: 00000000-0000-0000-0000-000000000000
        Required: 6573dee4-fa18-11ed-9924-426e98daf856
2023-05-24T14:38:11.747464+03:00 2 [ERROR] [MY-000000] [Galera] Application state transfer failed. This is unrecoverable condition, restart required.
2023-05-24T14:38:11.747472+03:00 2 [Note] [MY-000000] [Galera] ReplicatorSMM::abort()
2023-05-24T14:38:11.747480+03:00 2 [Note] [MY-000000] [Galera] Closing send monitor...
2023-05-24T14:38:11.747488+03:00 2 [Note] [MY-000000] [Galera] Closed send monitor.

edit: managed to resolve this by changing the dir of the keyring_file_data to the datadir so something wrong with the permissions. I now have the cluster up and running!

It seems strange that I had to grant the “SHOW DATABASES” manually to the mysql.pxc.sst.role though

6

How do I “grant” SHOW DATABASES to mysql.pxc.st.role "?

7

What’s this? Check permissions of the keyring path.