1

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?

Improve this question
1
  • mysql 5.7 have this feature now. A slave can have more than one master.
    – kasi
    Oct 30, 2017 at 6:18

2 Answers 2

Reset to default
0

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

Improve this answer
0

I have answered this question many times before

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.

Improve this answer
1
  • 1
    Deadlocks when multiple masters update the same table isn't a concern with MariaDB 10.0 because you cannot replicate the same table in the same schema from more than 1 master, at all. That doesn't work, in much the same way conventional replication fails when master and slave tables aren't identical... try to delete or update a missing row and the slave SQL thread stops the show. Two masters can have the same schema names, as long as there are no overlaps in table name. (e.g. in my setup here, both masters have an accounting db, but by dumb luck, the two masters have no same-named tables).
    – Michael - sqlbot
    Jul 13, 2013 at 4:59