Backup user connecting as '...@localhost' and not '...@127.0.0.1'

MySQL & MariaDB Percona XtraBackup
1

I am in the process of creating a dedicated user role to perform backups.

The mysqld instance runs with --skip-name-resolve and so the user must be ‘backup’@‘127.0.0.1’ (and not ‘backup’@‘localhost’).

When launching the following command:

xtrabackup --defaults-file=/opt/backup/credentials.cnf --backup --slave-info --safe-slave-backup --parallel=16 --target-dir=/opt/backup/mysql-202212021053/mysql

(the user and password are in the credentials file)

It dies with Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;mysql_socket=/var/run/mysqld/mysqld.sock','backup',...) failed: Access denied for user 'backup'@'localhost' (using password: YES) at - line 1536.
I assumed this was because it was connecting over port 3306, so I forced it to use the socket by adding --socket=/var/run/mysqld/mysqld.sock but the error message remains unchanged (Access denied for user ‘backup’@‘localhost’ (using password: YES))

If I create the user instead as ‘backup’@‘localhost’ and perform the necessary grants, I get the warning
(Code 1285): MySQL is started in --skip-name-resolve mode; you must restart it without this switch for this grant to work

The xtrabackup process does appear to work but I would rather not see the error 1285 appear when setting up the user. This sounds like something that will break in an upgrade (i.e. the --skip-name-resolve will actually do what it says).

Is this a bug? Otherwise, how can I get xtrabackup to use 127.0.0.1 instead of localhost?

1 Like
2

Hi dla,

What you actually want is to make it connect via TCP (127.0.0.1) Instead of socket (localhost)

please add to your /opt/backup/credentials.cnf file either on [client] or [xtrabackup] group:

host=127.0.0.1

Let us know how it goes.

2 Likes
3

Perfect, that was exactly what I needed.

1 Like