Mysql – federated tables? synchronization? replication? [thesql]

data synchronizationmerge-replicationMySQLreplication

I have a REMOTE suppliers' table with, let's say, a code and a name, and several client databases will be creating new suppliers.

Server database

code  |  name
------+-------
1     |  Mark
2     |  John
3     |  Jodie

I thought that a federated table would fit the best, as every client could create new suppliers in a centralized table which assigns incremental codes,

Obviously, if the Internet connection is down, clients won't be able to create new suppliers, so this won't work for me.

I thought that the best idea would be assigning a prefix to each client, and the code count would be assigned locally.

Local table client A

code   |  name
-------+------
A1     |  Mark
A2     |  Jodie

Local table client B

code  |  name
------+------
B1    |  John

Then, the data would be merged into a single remote table.

Remote merged table

code  |  name
------+---------
A1    |  Mark
B1    |  John
A2    |  Jodie

But at this point, I am lost, I don't know if there is a solution for this problem, or I would need to merge them with a cron job + script.

Maybe a multiple-masters-to-1-slave would work?

Is there any way to schedule merge jobs?

Any idea would be appreciated.

Best Answer

You are asking for pain by having the data split among machines is a haphazard way.

Consider Galera (PXC, etc) as a multi-master approach. If one node goes down or is isolated by a network outage, it is self healing after things come back.

Yes there are multiple solutions (now) for multi-sourcing a slave. MariaDB has had an offering for some time. Oracle's MySQL has one. Galera allows each node to be slave to some 'external' Master.

Related Question