Mysql – Fan-In replication 4 Master -> 1 slave

mariadbMySQLreplication

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?

Best Answer

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:

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

Related Solutions

Mysql – Converting 3 Master + 1 Slave to 4 Master – Any advice

Here is the topology you just described

+-------------+
|             ^
|             |
V             |
M1 --> M2 --> M3
|
|
+----> S1

You would like to slip S1 as a Master into the Replication Ring so that it looks like this:

+---------------------+
|                     ^
|                     |
V                     |
M1 --> S1 --> M2 --> M3

Essentially, you only have to prep S1 to a Master and Point M2 to receive binary log entries from S1.

OK Here we go

STEP 01) Point your application at M3

STEP 02) Prep S1 to be a Master

set the server_id as a different number from M1, M2, M3
set log-slave-updates in my.cnf like you did for M1, M2, M3
enable binary logging on S1 the same way you enabled it on M1, M2, M3
restart mysql on S1

STEP 03) run STOP SLAVE; on M1

STEP 04) run SHOW SLAVE STATUS\G on S1 and M2

Make sure Seconds_Behind_Master is 0 on S1 and M2

STEP 05) run SHOW MASTER STATUS; on S1 (Record the binary log and position)

STEP 06) run this on M2

STOP SLAVE;
CHANGE MASTER TO master_host='IP Address of S1',
master_post=3306,
master_user='repluser',
master_password='replpass',
master_log_file='XXXX'
master_log_pos=YYYY;
START SLAVE;
SHOW SLAVE STATUS\G

where XXXX is the binary log from STEP 05 and YYYY is position from STEP 05

If the SHOW SLAVE STATUS\G says Yes for Slave_IO_Running and Slave_SQL_Running then you have achieved this:

+---------------------+
                      ^
                      |
                      |
M1 --> S1 --> M2 --> M3

STEP 07) run START SLAVE\G on M1

STEP 08) run SHOW SLAVE STATUS\G on M1, S1, M2, M3

Where Seconds_Behind_Master is 0 on all the servers...

STEP 09) Point you application to other servers as desired

Any questions ???

If none

Give it a Try !!!

Your mission, should you decide to accept this, is to practice this is a Dev/Staging Environment and make sure you trust this algorithm before doing this in Production.

In the event your data is caught or killed, the DBA StackExchange and I will disavow any knowledge of your actions.

Mysql – Master Slave Replication in Mysql

The simple answer is No.

In Mysql replication, Master copies the bin log files to slaves, and after that, it's work is over. Now the Slave will run the bin files and execute them, but there won't be any performance on Master.

There might be scenario where you are using full synchronous replication, in which master will wait for the slave to execute the query, but again it won't impact the performance in terms of memory or CPU, but the master will wait for the query to be executed.

Also, for your second question, Phil already answered it, that ssh sends data through encryption which uses a lot of CPU, hence if you want other ways, use the other methods which are described by Phil.

Best Answer

Related Solutions

Mysql – Converting 3 Master + 1 Slave to 4 Master – Any advice

Mysql – Master Slave Replication in Mysql

Related Question