MariaDB manual Switchover

I’m going to explain how to do a manual Switchover in a MariaDB HA setups using master-slave replication.

In my example, let’s consider that I’ve a server01 and server02, both running on Ubuntu 20.04.

server02 is currently the master, server01 the slave.

We’ll perform a switchover to come back to a normal status, meaning server01 as master and server02 as slave.

Stop all the connections to the DB

root@server02:~# mysql
mysql> flush tables with read lock;

Recovering information from the master

Note the values for the parameters File and Position.

root@server02:~# mysql
mysql> SHOW MASTER STATUS\G;

***** 1. row *****
File: mysql-bin.007747
Position: 5512879
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Check slave status

We check that the replication is working properly and that the values for the parameters Master_Log_File and Read_Master_Log_Pos correspond to the values noted in point 1.3.

root@server01:~# mysql
mysql> SHOW SLAVE STATUS\G;

Stop slave and do the Switchover

From this step, the slave should switch back to master.

Stop the slave, reset the slave configuration to 0, reset the master configuration to 0 and switch over.

Finally we check that the changes have been made.

root@server01:~# mysql
mysql> STOP SLAVE;
mysql> reset slave all;
mysql> RESET MASTER;
mysql> SHOW SLAVE STATUS\G;
mysql> SHOW MASTER STATUS\G

Change configuration in my.cnf

In the configuration of the instance, we set the slave as read only and the opposite for the master.

### On Master ###
root@server01:~# cd /etc/mysql/
root@server01:/etc/mysql# vi mysql.conf.d/mysqld.cnf

# In my.cnf:
read_only=0
super_read_only=0

### On slave ###
root@server02:~# cd /etc/mysql/
root@server02:/etc/mysql# vi mysql.conf.d/mysqld.cnf

# In my.cnf:
read_only=1
super_read_only=1

Restart mysql service

Restart the service to apply the changes in my.cnf

### On Master ###
# Stop the MySQL service
root@server01:~# systemctl stop mysql.service

# Start the MySQL service
root@server01:~# systemctl start mysql.service

### On slave ###
# Stop the MySQL service
root@server02:~# systemctl stop mysql.service

# Start the MySQL service
root@server02:~# systemctl start mysql.service

Configure replication on new slave

root@server02:~# mysql
mysql> CHANGE MASTER TO MASTER_HOST='MASTER IP', MASTER_USER='repl', MASTER_PASSWORD='***';
mysql> START SLAVE;

VIP address swap

If the VIP address is on the server with the slave instance, we set up the VIP on the server with the master instance.

### On Slave ###
root@server02:~# ip addr del VIP.AD.DR.ESS/28 dev ens160

### On Master ###
root@server01:~# ip addr add VIP.AD.DR.ESS/28 dev ens160

### If blocked by firewall ###
iptables -I INPUT -i ens160 -d VIP.AD.DR.ESS -p icmp --icmp-type echo-request -j ACCEPT

Conclusion

The cluster is now back to a normal status, with server01 as master and server02 as slave.