I am trying to merge 4 source db's (one for each region) into a global db. We use ID ranges as primary key on the tables in each region and so the merge should happen nicely. I have a fairly high volume of updates/inserts occurring in these regional db's, about 1MB a second.
However -
The blackhole solution does not quite work for more than two master DB's. I have 4 source DB's and chaining them serially like that would slow down the updates/inserts too much and overall make the system too fragile.
I don't think the stop slave/switch master/start slave loop would work either because that would slow things down too much. All source db's update at the same time.
I can't use MariaDB since it's still in Alpha and this is a production environment.
The source DB's have all the same name and exact same table structure. Although when there are upgrades, we will have situations where the table structures are out of sync for a few days during gradual rollout.
Here is what I tried -
One Slave for each region source master
Each Slave has federated tables that point to tables in the Global DB
Triggers on the replicated table in the slave pass on the change to the Global DB via updates/inserts/deletes on the federated table
Problem here - the update/deletes on the Federated tables in the slave, seem to trigger "select *" within the global db for some reason and are s-l-o-w. Yes, I have the proper indexes all around.
Help!! Any other ideas?
