Xtrabackup_binlog_info | xtrabackup_binlog_pos_innodb which file is correct for replication?

MySQL & MariaDB Percona XtraBackup
1

Let me know which file - xtrabackup_binlog_info | xtrabackup_binlog_pos_innodb - is correct for replication.

[Our system construction]

  • has 2 database servers : source and replica
  • RedHat Enterprise Linux 7
  • MySQL 8.0.22-1.el7
  • percona-xtrabackup 80-8.0.12-1.el7
  • using only InnoDB as storage engine

[Situation]
I made backup from source server by using xtrabackup and moved to another to make replica.
At another server, I done prepare > copy-back > start mysqld, all steps succeeded.
But I was confused which file is correct to use for CHANGE MASTER TO query - xtrabackup_binlog_info or xtrabackup_binlog_pos_innodb.

I done CHANGE MASTER TO query by contain of xtrabackup_binlog_info, it succeeded in replication.
Otherwise replication failed by using xtrabackup_binlog_pos_innodb.

[Contains of those files]

cat xtrabackup_binlog_info

mysql-bin.000587 156

cat xtrabackup_binlog_pos_innodb

mysql-bin.000036 871616440

  • MASTER STATUS at source mysql when backuping was finished:
    *** this system is already running and updating data.

SHOW MASTER STATUS;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000587 | 15036 | | | |
±-----------------±---------±-------------±-----------------±------------------+

and the file “mysql-bin.000036” was not exists each servers.

I think xtrabackup_binlog_pos_innodb is not usable for CHANGE MASTER TO query,
but percona’s document says below:

This output can also be found in the xtrabackup_binlog_pos_innodb file, but it is only correct when no other than XtraDB or InnoDB are used as storage engines.
(at https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup_bin/working_with_binary_logs.html )

In my case, xtrabackup_binlog_pos_innodb should be also correct but it contains wrong position.

Should I use xtrabackup_binlog_info for Replication?

1 Like
2

Hi @rkwmt_ns,
Always use xtrabackup_binlog_info to be safe. Cheers!

2 Likes
3

Hi @rkwmt_ns . To complement what @matthewb said, on the next release of Xtrabackup we will no longer generate xtrabackup_binlog_pos_innodb . The reason is that after MySQL / PS 8.0.17 the server stopped populating correct binlog coordinations to general tablespace, which is where xtrabackup_binlog_pos_innodb read it from. More details at MySQL Bugs: #104287: trx_sys binlog coordinates broken after clone plugin

You should always use xtrabackup_binlog_info.

2 Likes
4

I got it. Thanks for your replay!!

1 Like
5

Thank for your information. I will check it out!

1 Like