As a database administrator, you need to consider failures of hardware when thinking about the availability of your data.

This is a basic principle of High Availability.

In this post we go over MySQL’s default replication model to get a your data changes onto a second server!

What is asynchronous replication?

In MySQL, replication is the ability to transfer changes to your data to another MySQL process configured to listen for these changes.

Typically the second MySQL process resides on a second server to help achieve High Availability goals, but it’s not a requirement.

The default replication model is asynchronous.

Asynchronous replication means that the user or application that sends changes to the primary MySQL instance will not wait to know that the changes were sent to any other MySQL instance.

This is important to keep the performance impact of replication to a minimum.

Requirements for replication

The bare minimum requirements for MySQL replication are:

• two MySQL processes one primary ‘source’, and the a secondary ‘replica’. The secondary needs a copy of the data at the same point in time. If the data is different, replication will quickly fail.
• connectivity between the two processes, including a user with the REPLICATION SLAVE privilege
• binary logging enabled on the primary MySQL process. This is how the changes are recorded on the primary.
• unique server ids between the two processes so that MySQL can tell where the changes came from.

Considerations of asynchronous replication

With MySQL’s asynchronous replication, you get high availability with little to no impact to performance.

Most of the performance impact will come from recording the changes into the binary log.

That’s GREAT!

Hang on though.

There are issues that you need to be aware of with asynchronous replication:

• If the change is delayed on the secondary, you will temporarily have data differences. This can lead to stale reads if using the secondary to actively serve read requests.
• If the data is different on the secondary, the replicated change can fail. The primary won’t care.
• If the primary dies before the secondary gets the changes, you may have data loss.

These are critical issues to understand and plan around in your environment.

In the event that these are roadblocks for using asynchronous replication in your environment, MySQL natively supports semi-synchronous replication.

This model allows changes to be held up until at least one secondary acknowledges receipt of the change. This solves the issue of data loss, but the acknowledgement does not mean the change has been applied. So this does not solve stale reads or data differences.

There are also third party plugins like Galera or more recent MySQL versions that enable synchronous replication.

Synchronous changes are certified that they can be applied by all nodes in the cluster before returning to the client.

Each of these come with greater impacts to the performance of writes.

Conclusion

MySQL Asynchronous replication gives you a quick win when setting up your high availability solution for your database.

By understanding the implications of asynchronous replication, you can work with them or decide on a more strict replication model at the cost of write performance.