0

i have 10 mysql servers and one central mysql server that's keeping track of some numerical indexes. Idea is that each time one of those 10 servers request and id, that id is marked as used and no other server can use it. For that reason i have implemented a Federated table system which works almost perfectly for me - each server is writing changes into local table and it's automatically reflected on the table of the main server. Like that each of those 10 servers can see what's the next available id without even knowing about each other.

Now, the problem is that on the central server, to Federated, "target" table i have added one additional column. It turns out i have to replicate that same change on all those 10 servers manually! My expectation was that once i add new column on "federated target" table, all dependent tables on those 10 servers will get updated automatically to match their "parent" structure like they did once created for the first time.

Is there a way to enforce this in MySql? Basically i wanna have 1 table and structural as well as data changes of that table to be reflected automatically on all those 10 servers.

2
  • Use a clustering solution like galera or mysql group replication. For what reason do you need 10 servers?
    – danblack
    Mar 9, 2020 at 23:41
  • Different clients, i wanna keep their systems separated due to large amount of data per client Mar 10, 2020 at 9:25

1 Answer 1

1

I recommend you use standard master/slave replication with each node being slave to every other node (the multi-source) option. This isn't master-master as log-slave-updates should not be enabled.

Use the server configuration auto_increment_increment to 10, and have each node having auto_increment_offset to a unique number I think will satisfy you requirement that each node has a different, non-conflicting numbering of ids as an alternate to centralized issuing of numbers.

You application will need to ensure that the same deletes or updates of a row do not happen on simultaneous nodes. Inserts should also use the auto_increment field only.

Federated tables is a bit of a hack that isn't particularly maintained.

Your Answer

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

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