We use a slave to run xtrabackup and backup our database due to storage constraints. The data generated from the xtrabackup_binlog_info and xtrabackup_binlog_pos_innodb files is for the binlog of that server as opposed to the master of the slave. Is there any way to get the binlog information of where the slave is up to when we take a backup, either at the time of the backup/prepare or after a restore? As it’s difficult to purpose a slave from these backups otherwise.
Gonna bump this, because we’re still seeing this issue as of XtraBackup 2.0. Will also post a bug report on LaunchPad, because this effectively means we can’t use XtraBackup without an accurate binlog position.
I’ve tried replicating this on a local setup to make it easier to see (as I’m not writing anything to the DB, so the binlog position is static throughout the backup process) using MySQL Sandbox, in a master-slave setup as follows:
Master:
SHOW MASTER STATUS:
File: mysql-bin.000023 Position: 589 Binlog_Do_DB: Binlog_Ignore_DB:
Slave:
SHOW SLAVE STATUS:
… Master_Log_File: mysql-bin.000023 Read_Master_Log_Pos: 589 Relay_Log_File: mysql_sandbox20576-relay-bin.000040 Relay_Log_Pos: 735 Relay_Master_Log_File: mysql-bin.000023… Exec_Master_Log_Pos: 589…
Backup from master (no prepare):
xtrabackup_binlog_info:
mysql-bin.000023 589
Backup from slave (before prepare):
xtrabackup_binlog_info:
mysql-bin.000006 227
Backup from slave (after prepare):
xtrabackup_binlog_info:
mysql-bin.000006 227
xtrabackup_binlog_pos_innodb:
./mysql-bin.000006 227
The thing that makes even less sense is the file that the slave backup binlog files are referring to doesn’t even exist; it’s a binlog position from quite a while ago. It just seems to be a completely random location. If I restore the backup to one of the slave sandboxes, it has the correct data (with changes more recent than the binlog position the backup reports) but without a relevant binlog position, I’ve no idea where to start replicating from, which makes provisioning slaves from slaves impossible.