4

I have a server that someone has setup which are acting as a master-master on both servers. Since they have been configured as both master on the same database, changes in one 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.

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

mysqlrockstar
  • 656
  • 7
  • 11
supmethods
  • 287
  • 4
  • 16

1 Answers1

1

Have you tried changing it from /etc/my.cnf

[mysqld]    
replicate-do-db=db1
replicate-do-db=db2

Also check what is the value in binlog-do-db. Ensure that you are not using same database names in both replicate-do-db and binlog-do-db

mysqlrockstar
  • 656
  • 7
  • 11
  • Nope. There's only a single entry on the server that's meant be slave for db1 and db2. There's no binlog-do-db either. I'm suspecting there might have redundant meta data or some other problems. Any parts of the MySQL system I can look at to see this information? e.g. the information_schema or other configuration files. Thanks. – supmethods Mar 25 '15 at 21:45
  • That is the mistake. In `my.cnf` it should not be in a single line `replicate-db-db=db1,db2`. The correct way to tell MySQL to enable replication for select DBs is to issue the parameter **multiple times** as mentioned in answer – mysqlrockstar Mar 26 '15 at 17:32
  • I've configured the database as you've mentioned in the answer. It's when I do a show slave status\G, it displays multiple entries of the same database. e.g. db1, db2, db3, db1, db2, db3. Is this normal? 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 26 '15 at 22:11