Mysql – How to swap roles of Master-Master

MySQLreplication

I've just taken over the responsibility of managing our MySQL databases. We're running Percona Server (GPL), Release rel24.1, Revision 217. Our setup involves two servers both running as master and slave at the same time. However, the functional slave has read_only enabled and all client connections go to the functional master.

If I ever need to swap roles during maintenance, how might this change the typical role swapping process? Is it safe to just switch read_only and direct all client connections to the new functional master? This is of course assuming functional slave has caught up to functional master, I've stopped all client connections, etc. Since replication isn't normally used on slave even though it's running, is it smart enough to know how to pick up where the old master left off? The last time I tried to do this they fell out of sync with each other and it needed to be resolved by using rsync, causing us almost an entire day of downtime.

On mysql07:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql08
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.017456
          Read_Master_Log_Pos: 29738626
               Relay_Log_File: relay.006949
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.017456
             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: 29738626
              Relay_Log_Space: 442
              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: 101215970
1 row in set (0.00 sec)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.020222
        Position: 59745708
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

On mysql08 (has read_only set to 1):

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql07
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.020222
          Read_Master_Log_Pos: 59883146
               Relay_Log_File: relay.010757
                Relay_Log_Pos: 59883292
        Relay_Master_Log_File: mysql-bin.020222
             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: 59883146
              Relay_Log_Space: 59883481
              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: 101215972
1 row in set (0.00 sec)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.017456
        Position: 30086889
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Best Answer

Since replication isn't normally used on slave even though it's running

That statement doesn't quite make sense.

In master/master, both servers are each other's master and slave and there is no real distinction between them -- only an administrative distinction, as you seem to understand.

If set up correctly, then yes, SET GLOBAL READ_ONLY = 0; on the former functional slave will allow direct writes by nonprivileged users.

However ... if your servers are set up correctly, then you should be able to make a "test" change directly to the data on the machine you call the slave, using any account with the SUPER privilege (which is immune to the global READ_ONLY setting) and it should replicate to the master. If that isn't what happens, then your setup is not correct and you need to understand why.

You should also be able to SHOW SLAVE STATUS; on each machine, and see data that matches SHOW MASTER STATUS when run on the opposite machine.

Until these things are true, then the answer to the question is no, it absolutely is not safe, because your system isn't working the way you believe it is.

Once you can see replication events going both directions and being executed no matter which direction they originate from, then yes, disconnecting the clients from the former functional master and connecting them the the former functional slave should be a relatively painless transition.

You may have missed an opportunity during your previous experience to fully understand what went wrong, and why.

Related Question