02 February 2011

Need to Replicate to a Database with a Different Name?

This is the first time this has happened to me, so I got excited about it until I noticed that this command has been with us from nearly the very beginning (ie MySQL 4).

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!

The Wrinkle
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... :)

3 comments:

MySQL DBA Guy said...

No responses yet? :)

mysqlcluster said...

I was just thinking would be it possible for replication between
master db with diffrent name on slave and the next day got this blog by twitter.
really it works

Thanks for giving such information
looking some more info on Mysql clustering
:)

Matty said...

Hi Ankur
you can learn more about MySQL Cluster architecture from the pages here:
http://www.mysql.com/products/cluster/

A popular whitepaper is the Architecture and New Features guide which is posted as follows (note requires registration):
http://www.mysql.com/why-mysql/white-papers/mysql_wp_cluster7_architecture.php