MySQL Backup Strategy for Master-Master Replication

[Varun Datta]

Hi,

We are developing backup Strategy for MYSQL Master-Master Replication FT Setup.

Application always makes connection to only one Master MySQL Database and there are minimal chances of update conflict.

Full backup can be taken on any one or both MySQL Master Databases but i am not sure from which Master MySQL database we should take binlog backup.

If application makes connection from Node 1 , then binlogs will be created on Node 1 and Node 2 will only recieved updates from Node 1 . Node 2 will not create any binlog for changes recoieved via replication and same is applicable for vice-versa.

One thing can be done is to see from which Node application makes connection and from that we can take binlog backup , that too is not reliable if application switchover just happened before backup was started.

If we take binlog backup on both databases, then to do Point-in-time recovery becomes tougher as it will not be sure which Node's binlog backup to use.

Most Likely Startegy
---------------------
If we use log_slave_updates=ON binlogs will be created on both Master MySQL database. We can take fullbackup and binlog backup on both Master MySQL database and we can use any Node's backup for Restore & Recovery.

Please let us know if my backup startegy is ok or provide your views on any other backup strategy.

Thanks,
Varun Datta