Not the answer you need?
Register and ask your own question!

Getting the binlog position when backing up from a slave

WheresWardyWheresWardy ContributorInactive User Role Beginner
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.


  • WheresWardyWheresWardy Contributor Inactive User Role Beginner
    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:



    File: mysql-bin.000023 Position: 589 Binlog_Do_DB: Binlog_Ignore_DB: </pre>



    ... 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...</pre>

    Backup from master (no prepare):


    mysql-bin.000023 589</pre>

    Backup from slave (before prepare):


    mysql-bin.000006 227</pre>

    Backup from slave (after prepare):


    mysql-bin.000006 227</pre>


    ./mysql-bin.000006 227</pre>

    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.