Percona MySQL Master-Master replication is a popular scaling out method utilized by many enterprises that use MySQL.

Bobcares responds to all queries, large and small, as part of our Server management services.

Let’s see what the MySQL Master-Master replication tool does and how it works.

Percona MySQL Master-Master replication

Many companies that use MySQL for scale out use the MySQL Master-Master replication pattern, mostly in active-passive mode. Most businesses would have internal scripts to handle things like automatic fallback and slave cloning, without Open Source solution.

The standard/best practise is to set this to active/hot-standby mode. In other words, do not write to both nodes at the same time. Our application or middleware/proxy must limit writes to a single node. This will save us a lot of grief if/when replication fails.

What is the purpose of this tool and how does it work?

Currently, it uses Linux IP management tools and LVM to create snapshots. It can handle master-master pairs as well as master-master and a group of slaves. For the most basic cases, we would usually define “roles” and assign them to the servers. For example, we can assign the role reader to both servers in a master-master pair at the same time, but only one of them will have the role writer.

Each role will have two IP addresses assigned to it. It will ensure that some server handles all of these IPs. As a result, we can use DNS for load balancing without having to worry about TTL and other issues. If the active server in the pair fails, the passive node will take over the READER and WRITER roles. It can happen in a matter of seconds depending on the monitoring configuration.

IP-based high availability and load balancing require no additional hardware or software. It works well for a variety of applications that we can writte in a variety of languages. It complicates application-based fall-backs.

What happens if an application error causes us to write to the slave?

We’ll frequently break replication without realizing it. We can get around this by using a read-only user for the slave connection, but what if our application was simply mis-configured?

The tool also takes extra precautions to avoid application errors. To protect this, Master-Master Manager ensures that only one of the nodes is writable at all times and the other is set to –read-only. So we should be safe unless we use a user with SUPER privilege.

We can also configure it to remove the READER role from the server if replication becomes too slow. As a result, we do not need to do it for each application.

It also took extra precautions to ensure that nothing went out of sync silently. For example, even if the slave server reboots, data consistency cannot be guaranteed because replication may be restarted in the incorrect position. This can result in errors, but for some query patterns it will not.

LVM-based sync is also implemented, making it very easy to restore broken replication safely. Aside from simple “cloning” of nodes, the same tool can be used to create a backup using a variety of methods, including compressed backups and incremental backups with rdiff.

Finally, we’ve implemented safe role switching, which means we can move the writer to another node without disrupting replication. This is very useful if we need to restart one of the servers in a cluster to upgrade the OS or add more RAM.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our Skilled engineers explained what the MySQL Master-Master replication tool does and how it works.