Mysql replication fails because master and slave have equal MySQL server UUIDs

MySQL & MariaDB Percona XtraBackup
1

Hello,
I am testing a restore after a master/slave failover recovery using XtraBackup. But after the restore, I get this error message on the Slave:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Here’s the scenario:

  • MySQL 5.6 running on RHEL 7.9.
  • Servers M and S, master and slave, running successfully with normal status outputs.
  • I shut down M, and make my slave think it’s a master (remove the REPLICATION section for /etc/my.cnf). S is now running successfully.
  • I perform a backup of S. I shut it down, copy the backup to M, and restore. I remove the data/auto.cnf file which contains the server UUID. I bring M up.
  • I now bring M down, rsync the data directory to S, remove the data/auto.cnf file there, and start S. It generates the same UUID as M.
  • I provision S as a slave. It is not able to sync because of the Fatal error.

I thought the uuid was generated every time a system was started, if auto.cnf didn’t exist. So why are mine the same?

Thanks.

BTW, here’s my restore script.

#!/bin/bash

master=db_master
replica=db_replica
replica_ip=$(host $replica | sed -e 's/.*address //')

[ $(hostname -s) = $master ] || { echo "ERROR: Run this on the db master." 1>&2; exit 1; }
[ -z "$1" ] && { echo "Usage: $0 <day>|Failover     (example: $0 Tue     or    $0 Failover)" 2>&1; exit 1; }
day="$1"

systemctl stop mysql

[ -e /db_filesystem/data.$(date +%m%d%H%M) ] && { echo "ERROR: /db_filesystem/data.$(date +%m%d%H%M) exists." 1>&2; exit 1; }
mv /db_filesystem/data /db_filesystem/data.$(date +%m%d%H%M)
mkdir /db_filesystem/data
chown mysql:mysql /db_filesystem/data

echo "(SLAVE MUST HAVE BEEN BACKED UP ALREADY)"

success=false
if [ $day = Failover ] ; then
       scp -r $replica:/db_filesystem/backup/Failover /db_filesystem/restores/ || exit 1
fi

fs_space=$(df -h /db_filesystem | tail -1 | awk '{print $4}' | sed -e 's/G//')
need_space=$(du -sh /db_filesystem/restores/$day | awk '{print $1}' | sed -e 's/G//')

[[ $(( $fs_space - $need_space )) -lt 10 ]] && echo "Not enough space for restore. Ensure that $(( $need_space + 10)) Gig are available in /db_filesystem." 1>&2 && exit 1

if /usr/bin/xtrabackup --copy-back --target-dir=/db_filesystem/restores/$day/ ; then
        chown -R mysql:mysql /db_filesystem/data
        ssh $replica systemctl stop mysql
        if systemctl start mysql ; then
                success=true
        fi
fi

if $success; then
        mysql -u root << EOSQL
GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'$replica_ip' IDENTIFIED BY 'OBFUSCATED';
EOSQL
        systemctl stop mysql
        ssh $replica "rm -rf /db_filesystem/data"
        rsync -avpP /db_filesystem/data $replica:/db_filesystem
        rm -f /db_filesystem/data/auto.cnf
        systemctl start mysql
        read master_log_file master_log_pos < <(echo "show master status;" | mysql -u root  | grep -v ^File | awk  '{print $1 " " $2}')
        ssh $replica "sed -i -e '/^read-only/s/0/1/' /etc/my.cnf"
        ssh $replica "rm -f /db_filesystem/data/auto.cnf; systemctl start mysql"
        ssh $replica "mysql -u root << EOSQL
CHANGE MASTER TO
       MASTER_HOST='${master}',
       MASTER_USER='repl',
       MASTER_PASSWORD='OBFUSCATED',
       MASTER_LOG_FILE='$master_log_file',
       MASTER_LOG_POS=$master_log_pos;
START SLAVE;
EOSQL
"
        ssh $replica 'echo "SHOW SLAVE STATUS \G" | mysql -u root'
fi
echo "Master uuid: $(cat /db_filesystem/data/auto.cnf)"
echo "Replica uuid: $(ssh $replica cat /db_filesystem/data/auto.cnf)"
1 Like
2

Hmmm… I may have jumped the gun. I reran it again, and it worked!? I don’t get it.

Well, in the future if I need to change UUIDs, is there a way to do it?

1 Like
3

Hello, the UUID should be regenerated if you delete the auto.cnf file. See MySQL :: MySQL 5.6 Reference Manual :: 17.1.4 Replication and Binary Logging Options and Variables

1 Like
4

True, but it was regenerating the same one on the master as well as the slave. And sometimes, the slave auto.cnf file didn’t even show up. I’m going to try and reproduce.

1 Like