I've got a single table in a master DB that I would like to replicate to multiple databases on one slave server. Like this:
masterDB.tableA -> slaveDB1.tableA
masterDB.tableA -> slaveDB2.tableA
slaveDB1 and slaveDB2 are on the same slave server. Is this possible?
I can get slaveDB1's replication working no problem, but it's as it it's ignoring the command in my.cnf for slaveDB2:
replicate-rewrite-db="masterDB->slaveDB1"
replicate-rewrite-db="masterDB->slaveDB2"
replicate-wild-do-table=slaveDB1.tableA%
replicate-wild-do-table=slaveDB2.tableA%
Am I missing something, or can this just not be done with MySQL replication?
Best Answer
The first obvious question is why would you want to do this? It's probably a bad idea and completely unnecessary, since you can join across databases:
Next, in order to understand why this doesn't work, you first need to understand the basics of MySQL replication. In summary:
SLAVE IO_THREAD
connects to the master, reads the binary log from a known position, and writes the queries to the local relay logSLAVE SQL_THREAD
reads the relay log and executes the statements.You seem to be expecting that by having multiple rewrite statement in place for the same database, that the SQL thread will execute the relay log statements twice. This is not the case. From the documentation, replicate-rewrite-db simply changes the value of
USE db
as it is being written to the relay log, so one or the other of your statements is being evaluated, but not both.To accomplish what you are trying to do, there are a couple options that come to mind
The way #2 would look is like this:
On the master server, create the second database and a copy of the table that uses the BLACKHOLE storage engine:
On the slave, create the tables as their regular storage engine.
INSERT/UPDATE/DELETE operations into master://db1.foo will get written to the binary log for db2.foo and then get replayed on the slave, where it will exist in both places.
There are a lot of caveats with this, of which here are only a few. I recommend extensive testing.
Using a relay slave: Your replication topology would look like this:
In the relay slave topology, you'd end up with an unaltered master and instead, the BLACKHOLE configuration would be on the relay slave. Your regular slave that contained the copies would be a slave of the relay. This would prevent any slowdown of the writes on the master. Note, due to the way triggers are applied on slaves, the relay slave topology will only work with STATEMENT based replication.
CAVEAT: I'm certain I've missed some critical detail, so test this extensively.
My advice: Don't do it. I can't think of any good reasons why you would need to have the same exact data replicated in two places on the same server.