Fan-In replication 4 Master -> 1 slave 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?
There is no solution for fan-in with traditional MySQL replication. 

Another option is Tungsten Replicator. Here are a couple of posts on how it supports fan-in replication:


http://narmitag.wordpress.com/2012/09/27/setting-up-fan-in-using-tungsten-replicator/
https://code.google.com/p/tungsten-replicator/wiki/TRCMultiMasterInstallation


Another option is Galera replication, which is integrated with Percona XtraDB Cluster and MariaDB Galera Cluster. In Galera, all cluster nodes take replicated changes from all other cluster nodes. Which satisfies your goal of fan-in -- sort of -- but it means that all nodes have all the data.

For what it's worth, MariaDB 10 made their General Availability announcement on March 31, 2014.

I don't recommend the Federated storage engine. It should really be deprecated, because it's slow, buggy, and fragile. My feel is that MySQL is considering scrapping it. See http://www.tocker.ca/2013/10/17/how-do-you-use-the-federated-storage-engine.html