Thursday, July 19, 2012

Replicate a Database with different Name in MySQL

It happened to me   first time, so I was very excited about it until I noticed that this command has been with us from nearly the very beginning (ie MySQL 4). We need to replicate a database with different name. Was thinking it will be a big task J finally got the easy solution and working now. J

The Situation
Master Server contains db1
Slave Server contains db1 and db2
Slave Server needs db1 from the Master to replicate to db2

The Solution
On my.cnf on the slave server:
--replicate-rewrite-db=from_name->to_name

Thus, this situation would dictate the following:
--replicate-rewrite-db=db1->db2
Easy!

In replication there may be scenario like below
Part 1
What if the Master Server contained db1 and db2; and
the Slave needs only db1 from the Master to replicate to db2 on the Slave?

Part 2
We don't want to replicate both db1 and db2 to this slave, although we want to replicate both databases to another slave - this means that the filter for databases should reside on my.cnf on the slave...

Assuming that we are using replicate-rewrite-db=db1->db2:
Using replicate-do-db on the my.cnf file on the slave, which database would you use with that command?

db1 or db2?  It only takes a moment in time to find out... :)
 

No comments: