2

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.

0

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).

http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replicate-do-db

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 '15 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 '15 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 '15 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 '15 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 '15 at 21:45

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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