Skip to content

Master Switch Cookbook

Imported from Confluence

Content may be outdated. Verify before following any procedures. View original | Last updated: March 2022

Move Master to mysql2

Preparation

Open four terminal windows:

  1. ssh into mysql1 and run sudo mytop
  2. ssh into mysql2 and run sudo mytop
  3. ssh -A into mysql1
  4. ssh -A into mysql2

Note:* Always use the fqdn for the new host!
Note2: Always run the script as a regular user*, never as root! The user must have full sudo rights on all servers, however.

Make sure replication is not lagging

On mysql2  connect to mysql and check replication. Look for seconds behind master it should be 0:

rwunderer@mysql2:~$ sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 84269027
Server version: 5.6.42-84.2-log Percona Server (GPL), Release 84.2, Revision 6b2b987

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql1.prd-aws.fyber.com
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.003466
          Read_Master_Log_Pos: 23931588
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 12068695
        Relay_Master_Log_File: mysql-bin.003466
             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: cms_db_production.advertisement_completion_rates,bre2_db_production.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 23931588
              Relay_Log_Space: 12068864
              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    <====== should be ZERO!
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: 170226088
                  Master_UUID: 16618a60-e528-11e9-b922-0ad2fa8d64aa
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

mysql>

Verify current configuration

On mysql2, start the switching script without arguments:

master_switch

This should produce an output similar to:

rwunderer@mysql1:~$ master_switch 

Checking configuration... 

mysql1.prd-aws.fyber.com mysql2.prd-aws.fyber.com mysql3.prd-aws.fyber.com  
WARN: No mha_manager host found!


####################### 
Current configuration: 

MASTER is mysql1.prd-aws.fyber.com 
STATSDB is mysql2.prd-aws.fyber.com 
STATSDB2 is  
MHA_MANAGER is  


Current layout as reported by mha_manager:
  • If you run the script for the first time it might ask you to accept ssh'ing into the other mysql servers
  • Check that all mysql servers are listed (line 5). If not, edit the list at the start of /usr/local/bin/switch_lib
  • Ignore the warning about mha_manager (line 6)
  • Verify MASTER and STATSDB are displayed as expected (lines 12, 13)
  • If you get ERROR: No master host found!  you most likely forgot to enable ssh agent forwarding ( ssh -A )
  • If anything else looks OFF fix it before proceeding!

Run first attempt at switching (will most likely fail)

Again, on mysql2 run:

master_switch --newmaster=mysql2.prd-aws.fyber.com
  • This step is expected to complain that you're trying to move master and statsdb to the same server. This used to be a problem when there was much more traffic on these servers. Nowadays it is most likely OK, so we will proceed to force the change.
  • Even though we expect this stop to fail we should not skip it. This gives the script a chance to report any other problems it might find!
  • If anything else looks OFF fix it before proceeding!

So far nothing has been actually changed and we could abort here without harm!

Run actual master switch

Again, on mysql2 force the switch to occur:

master_switch --force --newmaster=mysql2.prd-aws.fyber.com
  • While the script is running watch window 1 and 2 (mytop) for any signs of trouble
  • In case of queries piling up on the old master, kill them with fire (in Window 3, on mysql1):
sudo pt-kill --host 127.0.0.1 --port 3306 --run-time 1 --busy-time 5 --interval 1 --kill --victims all --ignore-user root
  • When the script is done you should see clients starting to connect to the new master. After this you're done!

Move Master back to mysql1

Follow the procedure above with just these two changes:

  • Run the master_switch script in Window 3 (mysql1 - new master)
  • In case of queries piling up you need to kill them in Window 4, on mysql2.