We help IT Professionals succeed at work.

Temporarily prevent Master Database from writing to replication log

SoLost
SoLost asked
on
Hi there,
I have 4 database servers.  1 Master and 3 Slave servers.

I am wanting to temporarily migrate to a new Master server while I upgrade the current Master, and then migrate back again.

If you look at the attached image you can see the setup that we currently have on the left, and the setup that I'm wanting to migrate to on the right.

My question :

Is there a way to temporarily stop the Master from logging to the replication binarly log file so that I can stop all of the slaves from replicating and they will have all stopped at the same point in the Masters binary log file.  Then, start the Master logging to the replication binary log file again without having lost any data that was inserted or modified during the period that the Master was not logging.

I am wondering about running the MASTER STOP command on the Master but not having a test environment I can not test to see if this will work or not.

Alternatively, is there a way to get the Slaves to all stop at a particular point in the Masters binary log file?
ServerLayout.jpg
Comment
Watch Question

theGhost_k8Database Consultant

Commented:
1. http://dev.mysql.com/doc/refman/5.0/en/set-sql-log-bin.html
If the user is not having super priv. you can disable logging using above command for that session.

2. At slave you can verify that slaves are at same state as that of master.
execute: STOP SLAVE
later you can after finishing your activities, take the master's binary  log name and position and use CHANGE MASTER TO... command followed by START SLAVE.
Commented:
Hi there,
I don't think that was what I was after.

I have however found a simple solution using the START SLAVE UNTIL syntax.

Basicly, I find the binary log and postition that the Master is currently writing to by running SHOW MASTER STATUS on the Master server.

Lets say that it came back with :
binlog.000001 and position = 12345678

I now want to tell the slaves to stop replicating from the Master when the Master starts writing to the next binary log file 000002

On all three slaves I would issue the command :
STOP SLAVE;
START SLAVE UNTIL MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=106;

Then, on the Master I tell it to start logging to a new file :
FLUSH LOGS;

The Master is now logging to binlog.000002 and all of the Slaves have caught up and stopped replicating at the same position.

It is now easy for me to use the CHANGE MASTER TO syntax on the Slaves to organise them into the structure that I want since they all have the same data on them.