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

A MySQL replication topology can be very complex (never underestimate a DBA’s creativity). But it’s very frequent to use an asynchronous replica 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 replica 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 replicas can use the same router or have their own router to replicate from the InnoDB Cluster.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

17 Comments

  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 ?

    • Hi Vimnay,

      You are right, if you stop group replication without stopping MySQL this won’t work.

      But it’s an interesting use case.

      Thx

      • Hi, Lefred
        In my case, this issue has been solved by MySQL Router v8.0.26,
        but I think the Asynchronous Connection Failover mechanism will also be a good soluion.

        • Of course now people should use Asynchronous Connection Failover which is perfectly supporting Group Replication. This post is from 2017, when this was not yet available.

  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

  3. hello sir
    my question is what if the whole cluster goes down? is the slave node connected to the cluster become the master?if not, my scenario i want to reach is switching to a remote master node in case the cluster went down
    than you in advance

    • Hi Ragheed,
      In this scenario, it does not. But it’s possible, you will have to configure the primary node to be asynchronous slave of the new promoted Asynchronous Master (previous slave). As soon as one Group Member (cluster node) becomes an asynchronous slave, the full cluster becomes a slave.
      Regards,

  4. Hi lefred
    thank you for you reply.
    so if i got it right, once i configure the Master node in the cluster as async slave, the whole cluster become slave to the remote Master.
    my scenario is to do a primary(cluster) and DR (single node) sites. so whenever the primary site goes down the DR node becomes the master until the Primary site get fixed.
    right??
    thank you in advance

    • Hi Ragheed,

      You need to take care of the routing by yourself currently, the MySQL Router won’t take that slave in consideration.
      Also once the async slave is promoted as async master, you need to take care that nobody will write on the cluster if it shows up. When cluster is back, you need to configure the Primary node to be slave of the async-master and only when it catch up you can switch back roles.

      Cheers,

  5. Hello,
    I have implemented similar kinda setup in our environment
    DataCenter1_node1 gets aysnc replication events from MySQL router that is installed on node1 itself
    MySQL router has the view of DataCenter2 cluster and vice versa for DataCenter2_node1

    what if complete DC1_node1 is powered off unknowingly.

    will the async repl switch automatically to another node; say node2
    do we have to write a script to switch the async repl node to the new primary instance.

    I understand Group Replication will take care of electing new primary host but what about aysnc replication failover

    Any thoughts on this?

    Regards
    Sudi

    • Hi Sudi,
      In this example, there is only one DC. It seems you are talking about 2 DCs.
      Are you using 2 different groups ?
      Currently if the Asynchronous Master (which is the Primary Master) dies, then the router will send the replica to the new Master. If the replica dies… nothing happens and you have to promote a new replica by yourself.
      Cheers.

      • Hi Lefred,
        Thank you for your reply.
        Yes in my case,
        There are 2 DataCenters (DC); so 2 separate MySQL8 Group Replication cluster exists. Active site and DR site.
        Bi-Directional Async replication is setup between primary Host for each of the DC;

        DC1_node1—> Async repl —-> node1_mysqlrouter. —->DC2_node1
        DC2_node1 –> Async repl –> DC2_node1_mysqlrouter –> DC1_node1
        Note: mysql8 & mysqlrouter are installed on same host

        if DC1_node1 or DC2_node1 goes completely down Async replication is broken.
        So is manual commands/script only the option to point Async replication to newly elected primary host?

        Regards
        Sudi

Leave a Reply to TimCancel Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.