1

There is a Central Master DB(on Cloud) and there are multiple Locations which have their own DB(lets call the latter - Local DB).

The Local DBs are Single Tenant and contain data for that particular Location alone. All data from Local DBs need to be synced with Central Master DB, i.e, Central Master DB behaves like a Multi-tenant DB with data from all locations.

The web-application load balances between the Central Master DB or Local DB based on availability. Preference is always given to Central Master DB when connectivity is there; if the connectivity to Cloud is not present, the data needs to be written to Local DB which then later needs to be synced with the Central Master DB.

What kind of Replication strategy should be followed so that Master syncs only the data relevant for that LocalDB? In someway, this is similar to ‘filtered replication in a master-master replication setup’ - but am not able to find much about this; and also, am not sure if this is the right approach or not. Or is, replication not a possibility, and only a custom sync needs to be done?

Also, not sure which of MySQL 5.7 and Postgres v11 is better for this kind of use case - this question arises as this use-case is primary and we can choose between either of the databases. Only RDBMS and no NOSQLs are considered.

0

1 Answer 1

1

If the "Local DBs" were only read slaves then this would be feasible using replication filters. https://dev.mysql.com/doc/refman/7.0/en/change-replication-filter.html

However since you have some sort of master master requirements (the need to be able to write to both Local DBs and the Central Master) this will get messy quickly. Reason being MySQL is not meant for master master replication out of the box let alone master master with bi-directional replica filter demands. As such, there are two possible solutions I can think of.

1.) Split the the Central Master into multiple databases which are location specific, reflecting their given Local DB partner. Each partner pair should then become a Galera "cluster" consisting of both nodes plus an arbitrator.

2.) Have the entire topology you are describing be a Galera cluster and don't discriminate between location based data, i.e all servers have all data. This solution would obviously greatly reduce complexity but at the cost of increased resource requirements.

3.) If you prefer to use Postgres, you can experiment with some variant of the 2 above mentioned solutions using https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/ But it seems at this time that multimaster and logical replication aren't coexisting.

To answer your other questions:

-I would try to achieve some sort of replication instead of a custom sync which would be difficult to implement especially if your data set is large. Also a custom sync solution will result in data inconsistency the majority of the time.

-In my opinion MySQL has more mature replication facilities available relative to your particular challenge.

Not the answer you're looking for? Browse other questions tagged or ask your own question.