MySQL replication notes 2:replicating only certain databases

Here is my notes on setting up replication on MySQL. In a lot of cases, that is not good enough, because it replicates EVERYTHING from the master to slave(s), whereas you may just want one or two databases replicated.

At first I thought I could just add this to /etc/my.cnf on the slave:
[mysqld]
replicate-do-db=MyDb

That didn’t work very well for statements like this, assuming you are in a database other than MyDb:

insert into MyDb.TableInMyDb values (SomeValue)

Fine, I thought, let me add this to slave’s my.cnf:

replicate-wild-do-table=MyDb.%

It’s not enough. For the Sql statement above, it still couldn’t catch and execute that statement.

After some searching and testing, I found what I was looking for. As far as I can tell, it works beautifully. All you need to do is to modify my.cnf on slave. Just add the databases to ignore in the list. Below is a sample:

replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = testdb
replicate-wild-do-table=MyDb.%

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.