I am looking into the Replication issues that surface
in a Failover scenario (Master goes down and Slave is
pressed into service).
I understand that it is critical for the Master and
Slave databases to mirror each other for Replication
to work. I am trying to ensure that this integrity is
maintained when the Failover scenario occurs.
Reading the MySQL FAQ, I see the following
1. On the Master, execute these commands:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
Record the log name and the offset from the output
of the SHOW MASTER STATUS statement.
2. On the Slave, issue this command, where the
replication coordinates that are the arguments to the
MASTER_POS_WAIT() function are the values recorded in
the previous step:
mysql> SELECT MASTER_POS_WAIT('log_name',
The SELECT statement will block until the Slave
reaches the specified log file and offset. At that
point, the Slave will be in sync with the Master and
the statement will return.
3. On the Master, issue the following statement to
allow the master to begin processing updates again:
mysql> UNLOCK TABLES;
4. Also, I plan to add an extra (maybe redundant)
check to make sure that the SQL Thread is in 'Has read
all relay log' state.
Q1. Will the above steps be __sufficient__ to
synchronize the databases?
Q2. Are there any gotchas that I need to watch out
I would also like to hear any recommendations of how
to handle the scenario in which the Master database
crashes. Since I cannot connect to the Master
database, I cannot perform above Steps 1, 2 and 3.
Q3. Will Step 4 work in this scenario (since it is
file based)? If it does work is it sufficient to
ensure database mirroring?
Q4. If Step 4 does not work what is the recommendation
for this scenario.
Looking forward to all your feedback. Thank you.
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.