Hi, I’m currently beginning to upgrade our production environment to Percona Server 8.0, and as part of that we’ll be moving backups from xtrabackup 2.4 to xtrabackup 8.0. One thing I’m struggling with, though, is passing the MySQL password through to the xtrabackup command. We have an in-house secrets manager that we use for MySQL passwords, and previously I could take the password from there and supply it to xtrabackup using the MYSQL_PWD environment variable, but that doesn’t seem to work any more.
I know I could specify the password on the command-line, but that seems insecure compared to an environment variable. I’d also prefer to avoid using a .my.cnf file if possible, to avoid having the password on disk. Is there any way to make this work?
Thanks for the reply! Here’s a log from a server that I just upgraded to Percona 8.0/xtrabackup 8.0 (to be specific, I’m using the official Percona packages for Debian bullseye on both hosts here):
leigh@80host:~$ MYSQL_PWD=<redacted> sudo -E xtrabackup --backup --user backup --datadir /var/lib/mysql/ --target-dir=/tmp/blah
2025-02-27T23:37:58.414347-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=30000 --innodb_buffer_pool_size=22G --tmpdir=/var/lib/mysql-tmp --innodb_data_home_dir=/var/lib/mysql/ --innodb_file_per_table=1 --innodb_flush_method=O_DSYNC --innodb_adaptive_hash_index=0 --innodb_log_group_home_dir=/var/lib/mysql --innodb_log_files_in_group=2 --innodb_log_file_size=256M --open_files_limit=65535 --innodb_io_capacity=4000 --log_bin=/var/log/mysql/bin.log --log-bin-index=/var/log/mysql/bin.log.index --innodb_flush_log_at_trx_commit=1 --datadir=/var/lib/mysql --datadir=/var/lib/mysql/
2025-02-27T23:37:58.414579-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --backup=1 --user=backup --target-dir=/tmp/blah
xtrabackup version 8.0.35-32 based on MySQL server 8.0.35 Linux (x86_64) (revision id: c339924a)
250227 23:37:58 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'backup' (using password: NO).
250227 23:37:58 version_check Connected to MySQL server
250227 23:37:58 version_check Executing a version check against the server...
250227 23:37:58 version_check Done.
2025-02-27T23:37:58.508939-00:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: backup, password: not set, port: not set, socket: not set
2025-02-27T23:37:58.509614-00:00 0 [ERROR] [MY-011825] [Xtrabackup] Failed to connect to MySQL server: Access denied for user 'backup'@'localhost' (using password: NO)
On a 5.7/xtrabackup 2.4 host the output looks like this:
leigh@57host:~$ MYSQL_PWD=<redacted> sudo -E xtrabackup --backup --user backup --datadir /var/lib/mysql/ --target-dir=/tmp/blah
xtrabackup: recognized server arguments: --server-id=50124 --innodb_buffer_pool_size=2G --tmpdir=/var/lib/mysql-tmp --innodb_data_home_dir=/var/lib/mysql/ --innodb_file_per_table=1 --innodb_flush_method=O_DSYNC --innodb_adaptive_hash_index=0 --innodb_log_group_home_dir=/var/lib/mysql --innodb_log_files_in_group=2 --innodb_log_file_size=256M --open_files_limit=65535 --innodb_io_capacity=4000 --log_bin=/var/log/mysql/bin.log --innodb_flush_log_at_trx_commit=1 --datadir=/var/lib/mysql/
xtrabackup: recognized client arguments: --backup=1 --user=backup --target-dir=/tmp/blah
250227 23:38:56 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'backup' (using password: NO).
250227 23:38:56 version_check Connected to MySQL server
250227 23:38:56 version_check Executing a version check against the server...
250227 23:38:56 version_check Done.
250227 23:38:56 Connecting to MySQL server host: localhost, user: backup, password: not set, port: not set, socket: not set
Using server version 5.7.42-46-log
xtrabackup version 2.4.29 based on MySQL server 5.7.44 Linux (x86_64) (revision id: 2e6c0951)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: open files limit requested 65535, set to 65535
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = /var/lib/mysql/
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = /var/lib/mysql
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 268435456
InnoDB: Number of pools: 1
250227 23:38:56 >> log scanned up to (774701367768)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 17 for mysql/slave_worker_info, old maximum was 0
250227 23:38:56 [01] Copying /var/lib/mysql/ibdata1 to /tmp/blah/ibdata1
and it continues on from there.
It’s odd that both versions include “password: not set” in their output, but for 5.7, the backup does fail if I don’t set the MYSQL_PWD variable, so it’s definitely using it. If I grep the xtrabackup binary for MYSQL_PWD, grep finds it on 5.7:
I was able to reproduce your grep test going all the way back to PXB 8.0.2. I suggest opening a bug report on this https://jira.percona.com/ so our PXB engineer can take a look.
I think this is an undocumented usage/side effect. Please create a feature/enhancement request. We do have this mechanism for xbcloud. If we add this environmental variable support, xtrabackup should support for all secret keys/password variables.
Also, using the command line is OK because the password is masked. The output of ps command will not show it. Unfortunately, the command line password or my.cnf file are the only workarounds for now.
Thanks for confirming that! I’ve left a feature request on Jira, and in the meantime I’ll see if we can adapt our processes to supply the password on the command line.