multi master replication

Question

Let us say I have a master(m) slave(s) replication. Now, in the scheme of things, I introduce another database/schema(foo) not associated with the master in any way and I want this schema to be replicated into the slave(s). Can I do this?

AFAIK, this cannot be done. What is the best way to pull this off? The reason I want to do this is I want to join tables from foo with s/m.

The data replication need not happen in real time, can be a daily cron job too. Is mysqldump the way to go? Is there some hack that I can pull off?

Answer 1

I don't know if it's the best way, but you can always pull the binary log and let it execute somewhere else.

A MySQL node can be Master AND Slave at the same time, but it cannot be Slave of two Masters.

See this similiar post:

https://stackoverflow.com/questions/1576603/is-it-possible-to-do-n-master-1-slave-replication-with-mysql

Answer 2

I have answered this question many times before

• Apr 25, 2011 : MySQL in Star Topology (1st Answer StackOverflow)
• Apr 26, 2011 : MySQL in Star Topology (2nd Answer StackOverflow)
• Apr 28, 2011 : MySQL in Star Topology (ServerFault)
• Aug 29, 2011 : MySQL Replication : 1 Slave / Multiple Masters
• Feb 03, 2012 : One slave, multiple masters MySql
• Jan 21, 2013 : Change the Existing Topology of a MySQL Replication Setup
• Apr 18, 2013 : Single slave - multiple master MySQL replication

Here is what you need to do to implement it:

• You need to record each master's current log file and position
• You need to round-robin connect the Slave to each Master
  • STOP SLAVE;
  • CHANGE MASTER TO <information for the next Master>;
  • START SLAVE;
• Keep in mind the MySQL can only do CHANGE MASTER TO command using a single host

ALTERNATIVE : MariaDB (Warning : Alpha Release right now)

As a radical alternative, you could upgrade to MariaDB because they have what is called multisource replication. MariaDB reworked CHANGE MASTER TO command to set up an I/O thread to point to a different master. Think of it: multiple I/O threads is exactly the implementation needed for true multimaster replication.

CAVEAT : I would worry a lot about this because if two or more Masters try to update the same table, you will get deadlocks. I would setup these multiple masters and replicate specific databases per I/O thread to avoid table deadlocks.

In other words, note this example:

• You have three databases : db1, db2, and db3
• Run the Following on the Slave
  • Setup CHANGE MASTER TO 'io1' for db1 from server1
  • Setup CHANGE MASTER TO 'io2' for db2 from server2
  • Setup CHANGE MASTER TO 'io3' for db3 from server3
• Write all changes to db1 on server1
• Write all changes to db2 on server2
• Write all changes to db3 on server3
• Do not write changes to db1 on server2 or server3
• Do not write changes to db2 on server1 or server3
• Do not write changes to db3 on server1 or server2

If you follow this exact paradigm, then MariaDB can provide what you need.