federated tables? synchronization? replication? [mysql]

Question

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.

Answer 1

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.