Reduce human interaction when using an asynchronous slave to a MySQL InnoDB Cluster

on
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

A MySQL replication topology can be very complex (never underestimate a DBA’s creativity). But it’s very frequent to use an asynchronous slave from your primary database to run reporting or logical backup… or any kind of read workload you need. It can also be used as delay slave for data restore purpose.

Once you decided to provide HA to your primary database by migrating to MySQL InnoDB Cluster, you can of course still need and use one or more asynchronous slaves.

You have then an architecture that looks like this :

So as you can notice, your asynchronous slave needs to pick one of the members of the cluster as master. However when this node that act as master crashes (or is stopped for maintenance) what’s happening ?

In fact, replication will break and a human intervention (or a script) will have to change the master on the slave and restart replication to keep the changes again:

As InnoDB Cluster is using GTIDs, this operation is easy, but it would be even better if this change could be done automatically more quickly and without human intervention.

Thanks to the router this is possible ! The plan is to run MySQL Router on the slave itself and connect the replication locally.

This is how it looks like :

 

The process is then fully automated and no human intervention is required. So the router is very useful to connect applications to the cluster but not only. Of course multiple slaves can use the same router or have their own router to replicate from the InnoDB Cluster.

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

5 thoughts on “Reduce human interaction when using an asynchronous slave to a MySQL InnoDB Cluster

  1. Hi,
    Thanks for the nice pictorial information.
    The concept of asynchronous slave to a cluster via local router is working good only when the node was down.
    if the node is removed from the cluster, the asynchronous node is still pointing to the removed node and the changes that are made in the removed node is being replicated to the asynchronous node.
    can you suggest if there is anyway to get this issue resolved ?

  2. Hi lefred ,

    Have you tried two InnoDB Cluster with Master – Master Classic Replication in between using MySQL Router?

    Note: Let us assume the Application Layer will handle conflict resolution

Leave a Reply

Your email address will not be published. Required fields are marked *

recent

Last Tweets Last Tweets

Locations of visitors to this page
categories