I have a server that someone has setup which are acting as a master on both database. Since they have been configured as both master on the same database, changes one on end has caused all sorts of issues on the other.

On one of the master is displaying two databases in the replication-do-db when I do show master status which is rather odd.

Here's the entry in my system that's of concern:

Replicate_Do_DB: db1,db2,d1,db2

MySQL Version is 5.1, I'm looking for a way to change the replication filter. CHANGE REPLICATION FILTER seems to only apply to version 5.5.

An you explain to me what I need to do to recover from this scenario? Thank you.

2 Answers 2


If I understand correctly: You have two DB's in a Master <-> Master setup, but one of them has the replicate-do-db rules meaning they are now (potentially) out of sync?

At present the DB with the replicate-do-db options is only replicating (processing) statements where the database concerned is in the list (the precise rules vary depending on whether you are using Statement Based or Row Based Replication).


Unfortunately the CHANGE REPLICATION FILTER option has only just been introduced in MySQL 5.7, prior to that you will need to find the set of entries in the my.cnf file for your database with the replicate_do_db = statements.

You will need to remove / hash (#) these out, and then restart MySQL.

After that the DB will replicate all statements that are passed across from the other master.

The final thing you will probably want to do is run something like pt-table-checksum to check the consistency of the two DB's. If needs be you may need to dump/recreate the second DB to make it consistent with the first one.

  • I have found those entries and have tried commenting them out and uncommenting. However, the issue still is when I do a SHOW SLAVE STATUS\G, it still shows multiple databases for replicate_do_db. e.g. replicate_do_db = db1, db2, db3, db1, db2, db3. I believe it should only be a single occurrence.
    – supmethods
    Mar 31, 2015 at 1:43
  • Also when I set it to read-only with the commands FLUSH TABLES WITH READ LOCK and SET GLOBAL READ_ONLY = 1, it still is writable. It a bit of a concern that it's writing when it's not meant. Is there something I'm missing here? Thanks.
    – supmethods
    Mar 31, 2015 at 1:46
  • You should have #replicate-do-db=db1 in your my.cnf for each DB, and then save the .cnf file, and restart the MySQL Database (alternatively just delete the rows). It should be one row per Database. As for the DB being writable, does your User have the SUPER privilege? as this will override the READ_ONLY. Try setting up a simple login without the SUPER privilege` and see if that works.
    – IGGt
    Mar 31, 2015 at 7:17
  • Yes, it's one statement per line e.g. replicate-do-db=<DB>. It's when I do a show slave status, two entries of the same database appears. As mentioned before, when I do a display I get replicate_do_db = db1, db2, db3, db1, db2, db3. Is this normal?
    – supmethods
    Mar 31, 2015 at 21:11
  • Strange, now the FLUSH TABLES WITH READ LOCK and SET GLOBAL READ_ONLY = 1 is locking the table for the root user.
    – supmethods
    Mar 31, 2015 at 21:45

This is a bug when your my.cnf can be read twice (may be due to the existence of more than one my.cnf by symlinks)

Mostly this happens in RPM installations where all path are default paths.

Solution: Just take new copy of my.cnf, make changes inside it with correct paths (eg basedir,datadir,etc) restart the MySQL instance with the new my.cnf with defaults file configuration path and check

mysqld_safe --defaults-file=/your/new/path/my.cnf

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.