2

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.

Improve this question
3
  • Do you want centralize only suppliers table? All client can view all the data or the complete data must be seen only to the central database? How about to add an origin field to supplier instead of prefix? origin | code | name client1 | 1 | Mark client2 | 1 | Join
    – Giovanni
    Feb 24, 2015 at 9:59
  • If client1 inserts new records, client2 must see client1 changes? client1 can update rows inserted by client2?
    – Giovanni
    Feb 24, 2015 at 10:06
  • Hi Giovanni. In this case, all clients could see all data and could edit. But editing other's data is not a must. If the solution is too complex, we could only allow to add.
    – Fran
    Feb 24, 2015 at 11:57

1 Answer 1

Reset to default
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.

Improve this answer
1
  • I have been reading about multimaster solutions, but I think it makes it too complex by replicating all data everywhere. Multisource approach fits me best, but I am a little worried about leaving mysql and adopting mariaDB. MysQL multisource is still not GA released. Still being tested and this would be for a productive environment. Would you recommend?
    – Fran
    Feb 24, 2015 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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