Percona xtrabackup PITR from replica backup

MySQL & MariaDB Percona XtraBackup
1

Hi Team,

How do i get the binlog position of master to perform PITR from full backup made on replica using percona xtrabackup.

The xtrabackup_slave_info file on repliica does not have binary position of master when gtid is enabled.

example:
cat xtrabackup_slave_info

SET GLOBAL gtid_purged=‘13ff6abd-63d9-11ea-8394-1458d042d710:1-1084709801, 96edfe72-893b-11ec-8866-48df37209308:1-78229846, e2ee9fa7-0202-11ec-a5e3-1458d042aa68:1-2890824404’;

CHANGE MASTER TO MASTER_AUTO_POSITION=1;

1 Like
2

Hello, the xtrabackup_binlog_info should have the binlog coordinates and the gtid info of the backup. Check Working with Binary Logs - Percona XtraBackup for more info

1 Like
3

Hi Igroene,

The xtrabackup_binlog_info file has binlog info of replica and not primary when i take backup on replica.

How to i get the primary binlog info ?

1 Like
4

The primary’s binlog coordinates are not recorded in xtrabackup_slave_info on a gtid environment since the replica doesn’t have that info. You are supposed to use master_auto_position to set up replication after restoring the backup.
If I understand correctly what you want to do, to roll forward the replica’s backup using the primary’s logs you can use mysqlbinlog with the --skip-gtids option and provide all the available files from the master.

1 Like
5

Okay. Thanks.

Can you point me to any document of it to use --skip-gtids and do
PITR.

1 Like
6

Sorry I made a typo above. The option to exclude gtid sets is called --exclude-gtids. Basically you feed all the binary logs in order, and then skip the gtids you don’t need. For example:

mysqlbinlog --exclude-gtids=‘7aab582d-a12c-11ec-9bf1-080027d5f945:6’ binlog.000002 binlog.000003 | mysql -uroot -p

This assuming that you have all the bin logs to fill in the gtid gaps since the backup.

You might also want to check out the process described here MySQL Point in Time Recovery the Right Way

2 Likes