How to handle asynchronous slave synchronization with Galera?

Question

I have a server with a service that uses mariadb database. This machine is remote and the database is far away in a Galera cluster of two servers.

Currently, this server is connected using an ssh tunnel to one of two clustered servers using the mariadb tcp port.

This service reads from one tableA and write to another tableB, and there are no link or foreign keys between these two tables. So I it is acceptable that tableA can have an older version of tableB or vice versa because as I said there is not link between these data.

I faced a connection issue between this server and my cluster and of course the service was completely down and I was wondering if I can improve the consistency of this service using a local database.

Questions

• Can I set up a slave replication to this remote server even with a galera master?
• Does a slave server accepts writes and synchronize them as soon as possible?

My idea is to have a local database with an asynchronous synchronization.
The service will read from tableA and writes to tableB locally and then its local database will keep tableA synchronized with remote galera Cluster and propagates writes to tableB to galera Cluster. However if the connection between servers fails, I expect the service will keep working with its local copy, accumulate data writes in tableB and restore all sync as soon as the connection comes back.

It is possible? Do I have any other option in my scenario?

A BIT MORE CONTEXT

Just to clarify the scenario, this is why I need this synchronization.

The Galera Cluster is a database for the main application and many application servers work with it with live transactions.

The remote server with its local database to be synchronized, uses the user table of database to authenticate users to specific services, for example there is an exim instance as mail exchange that authenticates users. Moreover this exim stores in a table of local database some logs of received messages.

This is an example why it doesn't care too much if the local database loses sync with the main Galera database. The worst case is that a fresh user just added to Galera database doesn't work with exim service because the user table is not synchronized, but it will work as soon as the sync comes back. The same for email logs, if there is not sync, the main application will not see last logs from exim.
As I said there is no write or fk conflict risk because one table (email log table) is read by Galera and written by remote server and another one (user table) is written by Galera cluster and read by the remote server.

With my current solution (mysql port direct to Galera using ssh tunnel), if the ssh tunnel goes down, exim doesn't work at all because it cannot access the database.

Answer 1

Yes, it is possible.

You must setup one node in the Galera Cluster like you would any regular slave

The topology you are looking for would resemble this (Image comes from a severalnines.com Blog)

Given this topology

• You would enable log-bin on MySQL1
• You would create the replication user on MySQL1
• You would execute CHANGE MASTER TO command on NewGalera1 connecting to MySQL1.

Please read my post from September 2016 on more info concerning binary logs (Is binary logs required on the slave in order to do replication?).

HEADS UP : Please note that writes must only occur on the MySQL1 server (your local database)

Answer 2

To be honest, even if you could make it happen, and there are ways to do it, this setup with two “sides” actively writing is not a good idea. The simplest way would be to configure bi-directional replication between one Galera node and the node that you have remote. There are caveats, though.

I’m assuming you want to perform writes on both ends, so that would be writes on Galera Cluster and writes on the remote node. Otherwise, what’d be the point of Galera? If this is true then let’s consider this scenario.

1. You perform a conflicting write on both ends. What will happen is that:

   a. If write will come first from the remote node, it will be executed on Galera and the write issued directly on Galera will not be executed. That’s kinda ok scenario.

   b. If write coming directly to Galera will be first committed first, write executed on the remote node will result in broken replication link. You will have one transaction that exists only on Galera cluster and another conflicting transaction that exists only on the remote node. There’s no way easy or automated way to sync them.

2. There is no network connectivity between Galera and remote node. Both ends can execute transactions however they see fit. If there will be any kind of conflicting transactions (for example, the same row was updated in different way on both ends), once the replication reconnects, it will break on the first conflicting transaction. See point 1.

Unless you ensure somehow there are no conflicting transactions on both ends, this will always end up badly. If you can ensure that conflicts won’t happen (for example, remote node works on one schema and Galera cluster works on another schema, exclusive set of tables or something else), then just setup bi-directional replication and all you will have to come up with is some automation to re-establish replication when there’s a break in the connectivity or the Galera node, which you connect remote node to, goes down.