MySQL replication : Replicating only certain databases

Replication replicates EVERYTHING from the master to slave(s), whereas you may just want one or two databases replicated.

At first 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.%
About This Author

I'm a software engineer with many years of experience, open source enthusiast, now I'm creating and contributing to awesome PHP web projects. I love coding as much as learning, and I enjoy trying new languages and patterns. My passion revolves around (but is not limited to) back-end development.

You are not signed in. Sign in to post comments.