MySQL Database Replication for Multiple Tables

Question

Previously, I have experience in doing the following database replication.

(1) I have 2 tables within 1 database in Machine A

(2) I update 2 tables in Machine A

(3) Machine A will replicate 2 tables to Machine B. Machine B will also contain 2 tables within 1 database.

Now, I would like to accomplish the following :

(1) I have Table A, within 1 database in Machine A.

(2) I have Table B, within 1 database in Machine B.

(3) I would like to replicate Table A and Table B to Machine C.

(4) Machine C will have Table A and Table B, within ONE database.

Is it possible this to be accomplished, through database replication?

Answer 1

Multi-master replication isn't really possible unless your using Cluster, then I don't think you can use the example your talking about unless the two tables are really the same data simply seperate partitions.

Answer 2

Unfortunately, you can have only Master per mysql server. So for instance you could run two separate instances of mysql on different ports on Machine C that slaved from Machine A and Machine B respectively, but not from both in one server.

Depending on your situation, doing that might be get you close enough that some other replication technique (like periodically using mysqldump to copy one table across on Machine C) would work. It would just depend on your needs for the slaves (how big are the tables (i.e. how quickly can they be copied via a non-slaving method), how out of date is acceptable, do you really need them in one DB or is one server good enough, etc).

After a second thought, there is one type of multi-master replication which is possible and might serve your needs if you just want the data in one DB and don't really need Machine C. In that case, you could actually have one of the servers be the Master for Table A and Slave for Table B, while the other is Master for Table B (and if necessary, Slave for Table A). Decent looking explanation.