MySQL replication - replicate to multiple DBs on slave?

Question

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?

Answer 1

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:

SELECT * FROM db1.table1 JOIN db2.table2 ON db1.table1_id = db2.table2_id WHERE ...

Next, in order to understand why this doesn't work, you first need to understand the basics of MySQL replication. In summary:

1. On the master, a statement is executed
2. On the master, that statement is written to the binary log
3. On the slave, the SLAVE IO_THREAD connects to the master, reads the binary log from a known position, and writes the queries to the local relay log
4. On the slave, the SLAVE 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

1. Run multiple instances of MySQL on the same host on different ports. This can be done with mysqld_multi. This is very easy and can fit in with your rewrite rules.
2. Use the BLACKHOLE storage engine with AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers on the master. This is very ugly, but could work for certain use cases

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:

mysql> create database db1;
Query OK, 1 row affected (0.03 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

master> use db1;
Database changed
mysql> create table foo ( id int auto_increment primary key, name varchar(32) not null ) engine=innodb; 
Query OK, 0 rows affected (0.16 sec)

mysql> use db2;
Database changed
master> create table foo ( id int auto_increment primary key, name varchar(32) not null ) engine=BLACKHOLE;
Query OK, 0 rows affected (0.05 sec)

master> use db1;
Database changed
master> CREATE TRIGGER insert_into_blackhole AFTER INSERT ON foo FOR EACH ROW INSERT INTO db2.foo SELECT * FROM db1.foo WHERE id = NEW.id;

master> CREATE TRIGGER update_into_blackhole AFTER UPDATE ON foo FOR EACH ROW UPDATE db2.foo2 SET id = NEW.id, name = NEW.name WHERE id = OLD.id;

master> CREATE TRIGGER delete_from_blackhole AFTER DELETE ON foo FOR EACH ROW DELETE FROM db2.foo2 WHERE id = OLD.id;

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.

• ONLY INSERT/UPDATE/DELETE operations will get reproduced in both places.
• ALTER TABLE operations will not get reproduced, so changes will have to be made to both the BLACKHOLE and the main table manually.
• TRUNCATE TABLE operations will not get reproduced.
• It will be very difficult to detect out-of-sync data.
• Bulk operations will block until the trigger has completed. BLACKHOLE is quite fast because it's essentially /dev/null, but in large enough batches simply generating the statements may take a long time. If you perform a bulk INSERT/UPDATE/DELETE of 1000 rows, that will generate 1000 individual statements to execute against the BLACKHOLE table. You can improve on this by setting up a relay slave.
• Because of the conversion from bulk updates to individual row updates, the slave may have a tendency to lag behind.

Using a relay slave: Your replication topology would look like this:

master -> relay -> slave

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.