Mysql – Single slave – multiple master MySQL replication

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.

Using replicate-wild-do-table=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:

• The replicate-* options are restrictive by their presence. The *-do-* options seem to be telling the server what to "do," but in fact they are telling the server what to only do.

• The complete absence of any replicate-* configuration variables means "replicate everything."

In the simplest case, when there are no --replicate-* options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html

That, I think, is the point you are needing. It holds true for all MySQL 5.x.

Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ..., and then START SLAVE, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.

If you started out with identical data sets, everything should behave exactly as you would expect it to.

What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."

When you use the --replicate-* options, the document cited above also offers this tip:

it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options

You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.