How to Promote a Slave/Replica to Master in MySQL

Overview

This post is to show you how to promote a Slave/Replica on a master/Slave configuration.

1. Stop transactions/writes on the current Master

    When you want to promote a slave to Master, the first step is to set the master to read only, to make sure all transactions are commited and to kill all opened sessions.
    You can stop transactions/writes by running on of the following commands on your Master.
     
        Using the Flush tables.

flush tables with read lock;

        Setting the global READ_ONLY to ON.

show variables like "read_only";
set global read_only=1;   You can also type set global read_only = ON;
show variables like "read_only"; 

    If all transsactions are not gone after few minutes, kill all remaining opened transactions if necessary.

When when moving the master role, I prefer to stop the Primary Master then promote the Slave as Master.

2. Make sure your Slave has the same data as the current Master

       On the current Master if it is not stopped.

  show master status\G

*************************** 1. row ***************************
            File: log-bin.000056
        Position: 425307404
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec)

       On the Slave

show slave status/G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000058
          Read_Master_Log_Pos: 39696180
               Relay_Log_File: mysql_relay-bin.000127
                Relay_Log_Pos: 39696324
        Relay_Master_Log_File: log-bin.000058
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 39696180
              Relay_Log_Space: 39696521
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 123
1 row in set (0.00 sec) 

3. Stop the replication on the slave

stop slave;
change master to master_host=’’;
reset slave;

4. check the status on the new master

show master status\G

5. If you have multiple Slaves

If you have a multiple slaves, make sure they are now pointing to the new master.