Lock Master Log Position in Master-Master replication

Posted on

Question :

I have two servers connected via private IPs. Replication is partially working, however the problem I’m running into is when I change the MASTER_LOG_POS on Server B using Server A’s SHOW MASTER STATUS; command, Server A changes the log position immediately after.

Is there any way to stop MySQL from changing this position everytime? I just want them to sit still!

Below is what Server A’s master status is currently. It’ll increase (seemingly randomly) to something like 1583 if I try to apply that log position on Server B.

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1451 | db1,db2,db3  |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

Answer :

You can try following method

Flush tables with read lock on master(It will lock the master)
show master status
use this position accordingly in change master to command
unlock tables(It will unlock master)

Hope it helps.

Leave a Reply

Your email address will not be published.