Creating a Replicant from an XtraBackup backup fails with binary log errors

MySQL & MariaDB Percona XtraBackup
1

Hi,
I am trying to create a replica to a Percona Mysql database as per the instructions here: How to setup a replica for replication in 6 simple steps with Percona XtraBackup - Percona XtraBackup

This is using Percona 2.4 on a Red Hat 7.9 system.

The problem is, xtrabackup does not seem to be creating the binary log files correctly. There’s a step in STEP 5 where it says " $ cat /var/lib/mysql/xtrabackup_binlog_info"

I have performed the cat, but the binlog that is returned simply does not exist. It’s not recovered in the backup.

So when I perform the CHANGE MASTER, I get this error on my Slave: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

Is this a known issue? I think I can fix it by looking at mysqlbinlog data/mysql-bin.000001 and grab the last end_log_pos from it… (See Fixing MySQL replication after slaves’s relay log was corrupted – Redips spideR Net)

Then use those values in “change master to”? Or I can stop slave, then reset it, then look at Relay_Master_Log_File and Exec_Master_Log_Pos values. Is that safe? Will the replicant
be missing data?

Thanks for any pointers.

2

Hello @mschwage,
Are you cat’ing that file on the replica after you restore the backup? This is not a known issue as these steps have been vetted/verified by many thousands who use our software, including myself directly in our training classes. Can you please provide the output of the backup process? Did you notice any errors?

3

Are you using absolute paths for binlogs in server config?
Please share your my.cnf

Also share the output of xtrabackup_binlog_info file.

4

Hi @mschwage
My guess is that you don’t have log_bin enabled in your MySQL Primary. I did a quick test to confirm:

With binlog disabled:

mysql -u root -p****** -e "SELECT @@log_bin"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| @@log_bin |
+-----------+
|         0 |
+-----------+

[centos@ip-172-31-82-157 ~]$ sudo xtrabackup --backup --user=root --password=***** --target-dir=/backups/
...
221027 14:36:24 completed OK!
[centos@ip-172-31-82-157 ~]$ sudo cat /backups/xtrabackup_binlog_info
cat: /backups/xtrabackup_binlog_info: No such file or directory

With binlog enabled:

mysql -u root -p****** -e "SELECT @@log_bin"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| @@log_bin |
+-----------+
|         1 |
+-----------+

[centos@ip-172-31-82-157 ~]$ sudo xtrabackup --backup --user=root --password=***** --target-dir=/backups/
...
221027 14:34:38 completed OK!
[centos@ip-172-31-82-157 ~]$ sudo cat /backups/xtrabackup_binlog_info
mysql-bin.000002        414

Having binlogs enabled is one of the pre-requisites, as mentioned in the page you linked:

  • Source A system with a MySQL-based server installed, configured and running. This system is called the Source, and is where your data is stored and used for replication. We assume the following about this server:
    • Communication enabled with others by the standard TCP/IP port
    • Installed and configured SSH server
    • Configured user account in the system with the appropriate permissions and privileges
    • Enabled binlogs and the server-id set up to 1

Please confirm if you have your binlogs enabled.