Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
    
+1, Brilliant explanation –  Ashwin kumar Jul 17 '12 at 7:14
    
Amazing, thanks for the insight. Fantastic detail! I will do some testing with the blackhole storage engine. To answer your question, we're exploring the option of replicating into multiple databases because: 1) the tables are small and don't change that often, 2) we want to be able to use foreign keys in each of our databases (we use Doctrine ORM which doesn't work with foreign keys in different databases), and 3) for political/legal reasons one of the databases to which I want to replicate is on a different physical server (the non-shared data is owned by different companies). –  Jason Mc Jul 17 '12 at 20:37
1  
If you need to do this for legal reasons, I would suggest splitting by using multiple instances rather than hacking together a failure-prone replication solution. I also forgot to add that the relay slave version will only work with statement based replication due to the way triggers are called. –  Aaron Brown Jul 17 '12 at 21:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.