MySQL slave master switch

MySQL Slave Master replication

MySQL Slave Master replication

MySQL slave to master switch is an operation required in a few situations. Sometimes master dies and you need to make DB available as soon as possible. In this case if the DB is reasonably big then restoring from backup might take to long. Also keep present that slave has a current copy of data, you might have new data after the last backup that you can not afford to lose. Some other common situations where you might require such a procedure is when you want to upgrade your MySQL servers. For instance from Percona 5.6 to Percona 5.7. Also this will help to avoid long downtime.

Notice: if you have classic master slave replication some downtime is unavoidable, there are different setups that can be done to achieve having a cluster that does not stop working if you take the master offline, but this is NOT the objective of this post.

this post is some what related to another recent post; Mysql DB server OS change / replacement or distro upgrade; study case

Ok, let’s stop the chitchat and let’s get some stuff done.

The procedure preparations

1- If you are running a website Enable Maintenance page. Can be achieved easily by replacing an index with a basic html page.

2- Check replication MySQL master to MySQL slave is working and there is no lag:

mysql> show slave status\G;

If all is good then you might proceed as this basically tells that you have a consistent MySQL slave DB or not. If there is a problem you will find delay or errors, this is a correct output with no errors or delays:

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.1
Master_User: replrs
Master_Port: 5433
Connect_Retry: 60
Master_Log_File: mysql-bin.006558
Read_Master_Log_Pos: 158031192
Relay_Log_File: relay-bin.018258
Relay_Log_Pos: 66237315
Relay_Master_Log_File: mysql-bin.006558
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: 158031192
Relay_Log_Space: 158031591
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: 50
1 row in set (0.00 sec)

ERROR:
No query specified
mysql>

If there is some problem you might need to solve that first.

Now let’s make a parenthesis let’s assume the following server details:
10.1.1.1 machine-master-db, this DB is writable MySQL master.
10.1.1.2 machine-slave-db, this db is readonly MySQL slave.

After the operation 10.1.1.2 machine-slave-db will be the master.

3- Enable firewall rules on both servers so other servers can not write to DB during the operation. For instance like the following on master:

/sbin/iptables -I INPUT 1 -p tcp --destination-port 3306 -j DROP 
/sbin/iptables -I INPUT 1 -p tcp -s 10.1.1.2 --destination-port 3306 -j ACCEPT

This enables the Current master to be unreachable by everyone but the current slave. this same operation

3.1- Make sure the MySQL connectivity is still working after operation:

[[email protected] ~]# telnet 10.1.1.2 3306
 Trying 10.1.1.2...
 Connected to 10.1.1.2.
 Escape character is '^]'.
 S
 xW(b6%TZ��F/XPf3g/Q,'}mysql_native_password^]

telnet> quit
 Connection closed.
 [[email protected] ~]#

You should then repeat the same operation from MySQL slave to master and vise-versa. Then make sure the APP or website can not connect to DB.

4- (Optional) You need to change the APP to point to new DB (MySQL slave). the way to do this varies depending on your setup. E.g. if you are using DNS or host file to point your app to DB server then you need to make those changes in this step. Just to say you use hosts file to define where the DB is then you need to; Change host file on web servers and `service dns-cache restart` or/and maybe `service nscd restart` (can be done even later if wanted):

 [email protected]:~$ cat /etc/hosts | grep db
 10.1.1.2 machine-master-db
 #10.1.1.1 machine-master-db #Old master
 [email protected]:~$ sudo /etc/init.d/dns-clean restart

MySQL slave master; do the switch!

5- Make master readonly, this step is in case you still have a master working. if you only have MySQL slave working you can skip this step and the next:

mysql> set global read_only=ON;
 Query OK, 0 rows affected (0.00 sec)
 mysql> show variables like '%read_only%';
 +------------------+-------+
 | Variable_name | Value |
 +------------------+-------+
 | innodb_read_only | OFF |
 | read_only | OFF |
 | super_read_only | OFF |
 | tx_read_only | OFF |
 +------------------+-------+
 4 rows in set (0.00 sec)

mysql>

6- Flush everything to DB to get a clean database with a new starting point of bin-logs:

mysql> flush tables; flush logs;

7- make sure to record current master log position (DB1 at this point):

mysql> show master status;
 +------------------+----------+--------------+------------------+-------------------+
 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+-------------------+
 | mysql-bin.006863 | 4500| | | |
 +------------------+----------+--------------+------------------+-------------------+
 1 row in set (0.00 sec)

mysql>

8- This is time to stop the slave:

slave mysql> stop slave;

9- Make slave writable:

slave mysql> set global read_only=OFF;
 Query OK, 0 rows affected (0.00 sec)
 mysql> show variables like '%read_only%';
 +------------------+-------+
 | Variable_name | Value |
 +------------------+-------+
 | innodb_read_only | OFF |
 | read_only | OFF |
 | super_read_only | OFF |
 | tx_read_only | OFF |
 +------------------+-------+
 4 rows in set (0.00 sec)

mysql>

10- We are done with works on slave, at this point we can open firewall and let the APP use the slave.
11- setup old master as slave:

mysql>CHANGE MASTER TO MASTER_HOST='10.1.1.2',
 MASTER_USER='repl',
 MASTER_PASSWORD='p4ssword',
 MASTER_LOG_FILE='MYSQL_MASTER_LOG_FILE',
 MASTER_LOG_POS=MYSQL_MASTER_LOG_POSITION;

12- Check replication:

mysql> show slave status\G
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 10.1.1.1
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000014
 Read_Master_Log_Pos: 12255788
 Relay_Log_File: relay-bin.000002
 Relay_Log_Pos: 283
 Relay_Master_Log_File: mysql-bin.000014
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 (...)
 Seconds_Behind_Master: 0
 (...)

The replication might take a while to catchup, as long as the numbers are going down you have nothing to worry. You might also need to change some MySQL configuration if you notice the replication lag does not go down or if there are errors. But there are many variables here and this goes out of this posts objective.

All done, lets make sure it all works

13- Test that everything works by performing a simple write on new master (DB2 in this case) and check if it replicates to new slave (DB1) e.g.:

mysql> create database test2;

14- restore firewall, back to production configs. if you do not have any system firewall normally something like:

iptables -F

14.1 Then you can run tests on the MySQL slave and master DB and check there is access from web servers or app.
15- Remove maintenance page if applies to you and DONE with the MySQL slave master switch.

Posted in Databases, Development, DNS, Linux, MySQL, Networking, Tips

Leave a Reply

Your email address will not be published. Required fields are marked *

*