MySQL Replication (3 masters, 1 Slave)

Question

I am currently in the process of setting up mySQL replication.

I need to get data from three places.

So for example I have

NEED THIS DATA
server1 - database1
server2 - database2
server3 - database3

STORE DATA HERE
server4 - database4

I need this for reporting purposes.

I have read you can not connect to more then one master. So with that being said I am going to try a "master-chain" topology because I think I understand what is going on with this one

So the data flow will look like this

server1 -> server2 -> server3 -> server4

server2 and server3 will have blackhole storage engines for the replicated databases so we don't actually store the information on these servers but the information is still logged to the binary-log so all the statements will trickle down to keep server4 up to date.

Am I understanding this correctly?

Answer 1

This will work but isn't the nicest implementation and might well cause you problems in the future. Before you add a new table on server1 or server2 you'll have to create the BLACKHOLE tables on the downstream servers otherwise you'll end up with the real tables. I guess you could work around this by writing a script that runs on server2 and server3 each night that looks for any non-blackhole tables and runs an ALTER TABLE to modify them into BLACKHOLE tables.

Do you need the reporting data to be bang up to date? If not I'd recommend throwing out this whole idea and just pulling dumps of the data into server4 periodically as required.

edit: (the next day) After some thought I think that if this was me I would probably look at running three separate MySQL instances on server4 each of them as a direct slave of one of the servers.

Using this approach there's no replication chain, the replication setup is far simpler/normal and each db pair is self contained.

It's easy and fairly normal to run multiple MySQL instances, there's even a tool called mysqld_multi that will help you to set it up.