Replication: Same Server, Rewrite database

MySQL support same-server replication into another database, Its quite a weired requirement, but in reality weired is common.

Consider a server 192.168.5.70, which has 2 databases db1 and db2
Now we shall set up replication for two tables on db1, ie. table1 and table2.

Here is the my.cnf

[mysqld]
server-id=1
#### Replication ####
report-host=master-is-slave-host
log-bin=192.168.5.70-binlog
relay-log=192.168.5.70-relaylog

replicate-same-server-id=1

binlog-do-db=db1

# Note.... On rewrite, the  command is changed into buffer
# so the replicate-do-db and replicate-do-table should have the
# re-written db name.
replicate-rewrite-db=db1->db2
replicate-do-table=db2.table1
replicate-do-table=db2.table2

Lets look at it carefully.

replicate-same-server-id=1
This is to tell the slave the commands that it has to execute will be having the same server-id as its own.

binlog-do-db=db1
To log only database db1

replicate-rewrite-db=db1->db2
Let the slave know that whatever command is for db1 it has to execute on db2

replicate-do-table=db2.table1
replicate-do-table=db2.table2

Note how the table in db2 is replicated, as against the general thinking of db1.table1, MySQL could be using some buffered string and then evaluating the replicate-do-table rule.

5 thoughts on “Replication: Same Server, Rewrite database”

  1. Hi Sudhir,
    I follow above steps for replication of two databases on the same server.
    But when I update or modify one database then there is no effect on the other one database table.
    So explain briefly what should I have to do.

  2. I have also done everything you did but I cannot see any changes in db2. I have used same mysql instance. Were you using too? And have you done anything else which is not mentioned in the above steps? Thank you very much in advance.

    1. Hey,

      Did you check the mysql.log file for errors ? Its the best place to figure out silly mistakes that we generally make.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.