[MariaDB] Failover in a master/slave configuration

Hi all,

in this tutorial we are going to perform a failover in a master/slave configuration in MariaDB. This can happen in a controlled way if the master needs some maintenance or it can be the result of a system failure.

To create a master/slave configuration just follow this article:
Create a master/slave configuration in MariaDB

1 – Master stop

A crash of the master database is not as rare as you can think. In this tutorial it makes no difference if the are problems on the database or on the server, just suppose that the master database is not accessible any more. This is equivalent to the following command:

systemctl stop mariadb

However, the database could have been intentionally stopped in order to perform some maintenance on it.

2 – The slave becomes the new primary

In order for the slave to become the new master, it first has to stop applying old master logs:

stop slave;

And then all the logs and configurations must be cleared and the database must return to the status it was before starting applying master logs. This is accomplished by the following command:

reset master;

The output of the following command on the new master indicates that it is actually a master database:

show master status;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      313 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

3 – The old master becomes the new slave

If the old master becomes available again, it can be made a slave of the new master. To do this there is the need to create the user dedicated to the replica in the new master:

GRANT REPLICATION SLAVE ON *.* TO 'user_replica2'@'%' IDENTIFIED BY 'user_replica2';

And then just issue the following commands on the old master:

CHANGE MASTER TO MASTER_HOST='NEW_MASTER_SERVER_IP', MASTER_USER='user_replica2', MASTER_PASSWORD='user_replica2';

start slave;

NOTE: in this article it is supposed that the old master is soon available to become the new slave, but if it becomes available after some time and changes have been done on the database to replicate, is can be necessary to dump it from the new master and copy to the new slave as described in the already mentioned article Create a master/slave configuration in MariaDB.

NOTE: any application that was writing on the old master, now must point to the new master in order to continue working. This can be done if the application server does not point to the actual IP address of the server, but to a Virtual IP address that can be brought down on the old master server and brought up on the new master server.

That’s all. See you in the next tutorial!