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.
Best Answer
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 thereplicate_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.