Restore master from slave backup

MySQL & MariaDB Percona XtraBackup
1

I have been making my backups for a while now on my slave servers, and was going to do a disaster recovery practice run. I have seen some articles now stating that --slave-info, and --safe-slave-backup should be used on slave backups which I have not done.

More importantly I have only seen articles for restoring a slave from a master backup, or rebuilding a slave from a slave backup. I haven’t seen anything for recreating a master from a slave backup. Am I going to have complications when trying to do my restore?

Thanks,
Steve

2

Hi stevednd;

First thing is to clarify the two options you mentioned:

–slave-info: when you do a basic backup of a server with binary logging enabled, the binlog file/position you get from Xtrabackup will allow you to setup a slave of the server you are backing up. To get the binlog file/position of the master of a slave you are backing up, you use --slave-info. When using that, you now have a backup that you could use to setup a new slave of either the slave the backup is from, or the master of the slave the backup is from.

[url]The innobackupex Option Reference

–safe-slave-backup: this is good to use any time you backup a slave. Basically Xtrabackup waits for the variable Slave_open_temp_tables to be 0, implying that there are no ALTERS processing (which are your enemy when doing backups). The only caveat here is that if Slave_open_temp_tables never gets to zero (or takes a long time), your backup will pause until a certain point and then will exit with an error. You can control how long Xtrabackup waits with the --safe-slave-backup-timeout option.

[url]The innobackupex Option Reference

So in short, yes I would start using both of those options.

To simplify the remaining discussion, lets set some generic server names:

Server A is your current top level master.
Server B is your current top level slave (of Server A).
Server C is the new server you will be setting up using the backup from Server B.

Given all of that, how not having --slave-info affects you depends on what you want to do with the backup. Since you are currently backing up Server B without --slave-info, in the simplest case, the backup is useful for creating a slave of Server B. However you could do various things to “promote” Server C like pausing replication from Server A to Server B, doing a “show slave status \G” on Server B to find out what binlog file/position it is at with Server A, and then use that info to do a CHANGE MASTER TO on Server C to make it a slave of Server A instead of Server B. At that point both Server B and your new Server C would be slaves of Server A.

Now if you use --slave-info to backup Server B, you could directly setup Server C as a slave of either Server A or Server B, since the backup will have both binlog files/positions (thanks to --slave-info). So basically --slave-info gives you more options out of the gate, which is always nice to have.

As far as “creating a master from a slave backup”, that is up for interpretation. Generally speaking, if a master dies, you would promote a slave (that hopefully has the same data) to be the new master. So in your case, you could promote Server B to be the new master if Server A died, and then use the backup of Server B to create Server C as a slave of Server B (the new master), which would put you back where you started before the disaster (Server B would take the place of Server A, and Server C would take the place of Server B).

Hopefully that makes sense. If not, let me know and I can clarify. =)

-Scott

3

Hello,

I am in the same situation, but I handle my mysql deployment with ansible. I balance mysql request with haproxy (read and write backend), so I would prefer to avoid to change the master to slave, because I would have to modify the hardcoded ips of my machines in the ansible inventory files (that would be possible to generate them but really not the best) to generate a new haproxy.cfg.

So is it still possible to restore a mysql master from a slave backup ? Event if it requires to completly shutdown the master (I use docker so shuting down container is quite simple)

Thanks,
Xavier

4

Hi trompx;

Yes, any full and valid backup could be setup as a new standalone master under normal circumstances. Things only get more complicated when you involve replication, and need the right binary log / position from a backup to set up replication from a specific master.

-Scott

5

Hello scott,

Thank you very much for the fast reply.

Just to be sure… I have a cron task which backup the slave every hour but in case of a master failure I backup the slave so it has the latest data
and restore it on the master.

To restart the replication on the slave, do I still need a specific operation such as setting the binary log/position as the data on master is exactly the same as the slave ?

6

Hi trompx;

If you were going to restore the same backup on the master and slave at the same time, then you could just point the slave at the starting position of the master (before any traffic hits it).

If in the case of a master failure you are going to promote the slave to the master, and then restore the old slave backup as the new slave, then you would use the binary log / pos from the backup to point the new slave at the new master (since the new master is the old slave where the backup is from).

You also might want to be using the “safe-slave-backup” option if you are not already:
[url]The innobackupex Option Reference

-Scott

7

Yes when I restore my master, I put my system in maintenance mode so no traffic comes to the slave. So I’ll just point the slave at the starting position of master, thanks!