Replication in MySQL is an important feature for achieving High Availability of your data.

Asynchronous replication is the default in MySQL.

It is easy to configure and does not incur a significant penalty to performance.

However, one major drawback is potential data loss.

In this post you will learn how semi-synchronous replication in MySQL can protect against data loss.

Data loss in asynchronous replication

With asynchronous replication, you might encounter a scenario where the Source server dies before the replica gets the committed changes.

In this situation, it is possible to end up with data loss.

Data loss is when the Source confirms the changes to the client, but those changes are lost
during the failure.

To overcome potential data loss, MySQL supports semi-synchronous replication as a plugin.

How semi-synchronous replication works

Semi-synchronous replication allows you to specify 1 or more Replicas that must confirm data changes before the Source responds to the client making the change.

This allows MySQL to ensure that the changes exist on at least one extra MySQL server.

There is an important caveat here. The Source only waits for the Replica to confirm receipt of the changes; not that those changes have been applied on the replica.

To explain this, remember that handling replication is two separate processes on the Replica:

• the IO thread retrieves changes from the Source, and stores them in a relay log to be applied later.
• the SQL thread processes the changes from the Replica’s relay log and applies them.

This balances the risk of data loss with too much performance impact to write operations.

Enabling semi-synchronous replication

The basic steps to enable semi-synchronous replication are:

• install the plugin
• ensure that the plugin is active
• set appropriate options at runtime

The commands to execute are roughly the same between the Source and the Replica.

The important difference is that the name of the plugin is different depending on the role of the server.

Also, the source has an extra option to configure how long in milliseconds to wait for a replica to acknowledge before switching back to asynchronous replication.

This is an important feature to understand!

If semi-synchronous replicas go offline, the source will fall back to asynchronous mode.

This ensures that changes can proceed without blocking the client for too long.

Conclusion

MySQL’s default model of asynchronous replication has a significant drawback in a risk of data loss.

This risk can be mitigated with semi-synchronous replication, and we saw how easy it is to configure.

But be aware that enabling semi-synchronous replication is not without a cost in terms of write performance.

A Data Guardian will want to run a real production impact analysis when evaluating the use of semi-synchronous replication.