Restore innobackupex backup in master to master replication

MySQL & MariaDB Percona XtraBackup
1

Hello all,

i’m new in percona so i have some doubts regarding a full restore from one node to another in master to master replication.
The situation is as follows:

master to master
node1 up <-----> node2 down

on node1 → /usr/bin/innobackupex --defaults-file=$FILE $OPTIONS $BASEDIR

node2 → restore from innobackupex $BASEDIR
Master_Log_File and MASTER_LOG_POS from xtrabackup_binlog_info
start slave;

node1 up and beeing updated
in order to start slave
Master_Log_File=? MASTER_LOG_POS=?

i have 2 nodes node1 and node2 in master to master replication.
Replication is broken, node1 is up and online node2 is down.
I want to restore node2 with a full innobackupex backup from node1.
I know that in file xtrabackup_binlog_info there is the binlog file and position to asign to node2 when i start slave.
The question is while node1 is on line and is beeing updated what binlog file and position should be set to the node1 to start the salve?

Thank you

2

Nobody has nothing to say or comment regarding this issue??

3

Hi ekonomi

Assuming that you started the restored node without any of its old binary logs and index file then you just start it at the first binary log that it created. If you left old ones in place then you will have to determine which was the first log after the restore and start there.

Kind regards

Ceri

4

OK, you mean to node1 should be asigned Master_Log_File and MASTER_LOG_POS extracted from command in node2?
Thank you

5

Hi ekonomi

The first time that you started node2 after its restore it will have created a new binary log. At that point it was in an identical state to node1, assuming writes have been prevented with super_read_only, or writes blocked by another means.

With standard replication you would use the File from SHOW MASTER STATUS on node2 as the MASTER_LOG_FILE for the replication co-ordinates of node1; the start position will be automatically used if you do not specify MASTER_LOG_POS. You should be careful to ensure that the file that you specify is the file that was written when it first started after the restore in order to guarantee that you have it in the correct place; with log_slave_updates you would generate binary log data as soon as replication starts.

If you are using GTID replication then you could be using MASTER_AUTO_POSITION instead of MASTER_LOG_FILE, as the servers will exchange information and position accordingly.

Kind regards,

Ceri

6

Hi Ceri,

Please correct if I am wrong !

In a replication Scenario without GTID

  1. Take an xtrabackup with slave info from NODE1
  2. Ensure log_slave_updates is enabled in config file on both nodes
  3. Restore the xtrabackup in NODE2
  4. Create replication users in both NODE1 and NODE2
  5. Execute the Change Master_Log_File and MASTER_LOG_POS statement from xtrabackup_binlog_info in NODE2
  6. Start slave and check replication is working
  7. Stop Slave on NODE2
  8. Reset binary logs on NODE 2 ( using RESET MASTER)
  9. Get Binary log position from NODE2 (SHOW MASTER STATUS)
  10. Execute the Change Master_Log_File and MASTER_LOG_POS with the binary log positions from Previous step On NODE1
  11. Start slave and check replication is working on NODE1
  12. Start Slave on NODE2
  13. Check slave status on both nodes for any errors.

Regards,
Johnson