Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am planning to have a master-slave system in which occasionally the master and slave will reverse roles. To do this, I will stop further updates to master, and redirect traffic to the slave. But, before I make the slave the new master, I need to make sure the slave has finished reading all the updates from the old master. How can I do that? Is running "flush logs" on the master sufficient?

An solution that can be readily scripted would be preferred.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

FLUSH LOGS won't do anything useful for you.

With no log_type option, FLUSH LOGS closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file.

-- http://dev.mysql.com/doc/refman/5.5/en/flush.html

The slave gets events as soon as they're written to the file... not when fsync() or fdatasync() is called and not when the file is closed or rotated... the events are sent across almost instantaneously.

Additionally, it's not only that you need the slave to finish reading everything from the master. You really need to make sure it has also executed everything from the master.

A better solution would likely be to set the two machines up in master-master where each of them is always a slave to the other. You'll read that master-master (a.k.a. "circular") replication brings with it some new issues and concerns, but many of these are related to writing to both machines at the same time and potentially causing replication to break due to conflicting writes applied to each machine at almost the same time.

What you'll do, instead, is treat one machine as the master, only allowing your application to write to that one. When you want to switch... then all you have to do is switch... point your application connections that do the writing to the opposite machine.

An excellent and painless way to do this is with HAProxy. It can even connect your application only to the preferred server if the preferred server is up, and switch your application to the backup server if the preferred server is down. You configure your application's MySQL connection to point to HAProxy and configure HAProxy to relay those connections into the appropriate MySQL instance.

But, if you really want to do it manually, here's the way to know the two machines are currently in sync:

On the master:

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (6.53 sec)

mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
            File: mysql-bin.002211
        Position: 977403994
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec)

Keep that connection open, because it holds the read lock, which prevents any thread from writing to the master. Your application will be able to read, but will block on writes. Check SHOW MASTER STATUS several times in a row and when the Position stops incrementing (which should be almost immediately since you shouldn't get back the prompt from the FLUSH TABLES WITH READ LOCK until all pending writes are done) your master is frozen and will not write anything else to the binary log until you unlock the tables with UNLOCK TABLES; or you disconnect. If you shut down the master while still holding the read lock, nothing else on the system should be changed.

On the slave: (extra fields removed for clarity)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxxxxxxx
                  Master_User: xxxxxxxx
              Master_Log_File: mysql-bin.002211    <<<<<
          Read_Master_Log_Pos: 977403994           <<<<<
        Relay_Master_Log_File: mysql-bin.002211    <<<<<
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 977403994           <<<<<
        Seconds_Behind_Master: 0
             Master_Server_Id: 11
1 row in set (0.00 sec)

mysql>

When the Master_Log_File and Relay_Master_Log_File on the slave match the log file on the master and when Read_Master_Log_Pos (the last position the slave has received from the master) and Exec_Master_Log_Pos (the last position the slave has executed from the master) agree with the information you got from the master, and the *Running states are Yes and Seconds_Behind_Master is 0, then your slave has received and executed everything from the master and the two servers at that moment contain identical data in their tables.

The one caveat here is that if you're going to do this manually, you should probably -- somehow -- disconnect your application from the master before locking the tables, because if there are pending writes, they will happen on the master as soon as you unlock the tables.

share|improve this answer
1  
The reason I don't want to keep things perpetually in master-master is that the slave will get updated when I update the web application, and if those updates also got replicated to the master, the web app using that master would break (because it is not yet updated). But your answer was very helpful. I would upvote it but I don't have 15 rep yet. –  Paul Lynch Jun 21 '13 at 17:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.