Xtrabackup 8.0.14 - MySQL 8.0.21 - Access denied for user 'backup'@'localhost'

MySQL & MariaDB Percona XtraBackup
1

Hi to all,
I can’t generate a backup using xtrabackup and defaults_file

I’ve generate an backup user with these grants:

GRANT SELECT, INSERT, CREATE, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON *.* TO `backup`@`localhost`
GRANT BACKUP_ADMIN ON *.* TO `backup`@`localhost`

as show in this post.
As requird in the post, I also generated the /etc/mysql/backup.cnf and the /usr/local/bin/backup-mysql.sh.

The only difference is that in this phase of dev, i decide to do not use the encrypt .

I can connect to mysql server using the backup user, by the mysql command:

[backup@dadi ~]$ mysql -u backup -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 244
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

So i don’t understand because xtrabackup don’t accept the connect by backup.cnf and give me this error:
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','backup',...) failed: Access denied for user 'backup'@'localhost' (using password: YES) at - line 1536.

Thanks

Stefano G.

1 Like
2

Hi @StefanoG ,

This error message shows that there is an issue in connecting to mysql. Access denied for user 'backup'@'localhost' (using password: YES) means it was trying to use backup user and sent a password to the server.

Can you please send your full xtrabackup command and a copy of my.cnf you used to connect (you can redact password)?

1 Like
3

Hi Marcelo,
this is my backup-mysql-noenc.sh

#!/bin/bash

export LC_ALL=C

days_of_backups=7
backup_owner="backup"
defaults_file="/etc/mysql/backup.cnf"
parent_dir="/var/backup/base"
todays_dir="${parent_dir}/$(date +%u)"
log_file="${todays_dir}/backup-progress.log"
now="$(date +%Y%m%d-%H%M%S)"
processors="$(nproc --all)"

# Use this to echo to standard error
error () {
    printf "%s: %s\n" "$(basename "${BASH_SOURCE}")" "${1}" >&2
    exit 1
}

trap 'error "An unexpected error occurred."' ERR

sanity_check () {
    # Check user running the script
    if [ "$(id --user --name)" != "$backup_owner" ]; then
        error "Script can only be run as the \"$backup_owner\" user"
    fi
}

set_options () {
    # List the xtrabackup arguments
    xtrabackup_args=(
        "--defaults-file=${defaults_file}"
        "--backup"
        "--extra-lsndir=${todays_dir}"
        "--compress"
        "--stream=xbstream"
        "--parallel=${processors}"
        "--compress-threads=${processors}"
        "--slave-info"
    )

    backup_type="full"

    # Add option to read LSN (log sequence number) if a full backup has been
    # taken today.
    if grep -q -s "to_lsn" "${todays_dir}/xtrabackup_checkpoints"; then
        backup_type="incremental"
        lsn=$(awk '/to_lsn/ {print $3;}' "${todays_dir}/xtrabackup_checkpoints")
        xtrabackup_args+=( "--incremental-lsn=${lsn}" )
    fi
}

rotate_old () {
    # Remove the oldest backup in rotation
    day_dir_to_remove="${parent_dir}/$(date --date="${days_of_backups} days ago" +%u)"

    if [ -d "${day_dir_to_remove}" ]; then
        rm -rf "${day_dir_to_remove}"
    fi
}

take_backup () {
    # Make sure today's backup directory is available and take the actual backup
    mkdir -p "${todays_dir}"
    find "${todays_dir}" -type f -name "*.incomplete" -delete
    xtrabackup "${xtrabackup_args[@]}" --target-dir="${todays_dir}" > "${todays_dir}/${backup_type}-${now}.xbstream.incomplete" 2> "${log_file}"

    mv "${todays_dir}/${backup_type}-${now}.xbstream.incomplete" "${todays_dir}/${backup_type}-${now}.xbstream"
}

sanity_check && set_options && rotate_old && take_backup

# Check success and print message
if tail -1 "${log_file}" | grep -q "completed OK"; then
    printf "Backup successful!\n"
    printf "Backup created at %s/%s-%s.xbstream\n" "${todays_dir}" "${backup_type}" "${now}"
else
    error "Backup failure! Check ${log_file} for more information"
fi

and this the backup.cnf that is in /etc/mysql/backup.cnf (naturally the pwd is fake :wink: )

[client]
user=backup
password=###obfuscated### :)
1 Like
4

Hi @Marcelo_Altmann has You seen my post?
have some indications to resolve this issue?

1 Like
5

Hi @StefanoG .

Here I would advise you to troubleshoot the issue without the current script. Try to take a backup manually wiht xtrabackup passing --defaults-file=/etc/mysql/backup.cnf and check if it works. If it doesn’t try to manually pass the username and password via parameters (–user --password).

1 Like
6

Hi @Marcelo_Altmann,
I found my fault, the password in the cnf file MUST be enclosed by doublequotes (")
Thanks for the support

Stefano G.

1 Like