How to enable the automatic Failover with MariaDB? With our Server Management Services, bobcares offers solutions for your MariaDB issues.

Automatic Failover with MariaDB Monitor

MariaDB Monitor keeps an eye on a Master-Slave replication cluster. It checks the condition of the backends and assigns server roles like master and slave, which help routers determine where to route a query. Additionally, It can also change the replication cluster’s configuration by conducting Failover, switchover, and rejoin.

Failover and other comparable operations require MariaDB 10.0.2 or later and are also initially only supported in conjunction with GTID-based replication and for simple topologies, i.e., one master and numerous slaves. The MariaDB Monitor includes Failover, switchover, and rejoin capability, although neither automated Failover nor automated rejoin is in enable mode by default.

How does the Failover functionality works?

The basic operation it performs is replacing a failed master with a running slave. Let’s see the steps in detail:

• Choose the most latest slave of the old master to be the new master. The following criteria are in ascending order of priority:
   
  1. disk space is not low.
   
  2. log_slave_updates is on.
   
  3. gtid_current_pos (most processed events).
   
  4. gtid_IO_pos (latest event in relay log).
   
• Cancel and try again later if the new master has unprocessed relay log items.
   
• Get the new master ready as follows:
   
  1. Remove the slave connection that the new master uses to replicate from the old master.
   
  2. Deactivate the read_only-flag.
   
  3. Activate planned server events (if event handling is on). Only events that were enabled on the previous master are active.
   
  4. Execute the commands in promotion_sql_file.
   
  5. If an external master exists, then begin replication from it.
   
• Instruct all other slaves to replicate the new master.
   
  1. STOP SLAVE and RESET SLAVE.
   
  2. CHANGE MASTER TO.
   
  3. START SLAVE.
   
• Confirm that all slaves are replicating. A successful Failover happens when the initial three steps execute successfully.
   

How to create automatic Failover?

Consider there are four servers – server1, server2, server3, and lastly, server4. The server1 is the initial master whereas the others are slaves. In addition, there is a monitor called TheMonitor that monitors those servers. The MaxScale configuration file would look something like this:

[server1]
type=server
IP Address =192.168.121.52
port=3301
protocol=MariaDBBackend

[server2]
...

[server3]
...

[server4]
...

[TheMonitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3,server4
...

In order to enable the automatic failover, we have to add auto_failover=true to the monitor section in the configuration file.

[TheMonitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3,server4
auto_failover=true
...

We can use the command $ maxctrl list servers in order to confirm all servers are running fine.

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────┐
│ Server  │ IP Address      │ Port │ Connections │ State           │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server1 │ 192.168.121.52  │ 3301 │ 0           │ Master, Running │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server2 │ 192.168.121.191 │ 3301 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server3 │ 192.168.121.114 │ 3301 │ 0           │ Slave, Running  │
├─────────┼─────────────────┼──────┼─────────────┼─────────────────┤
│ server4 │ 192.168.121.211 │ 3301 │ 0           │ Slave, Running  │
└─────────┴─────────────────┴──────┴─────────────┴─────────────────┘

Consider that server1 goes down now. Then the Failover will occur automatically, and an existing slave will be promoted to the new master.

$ maxctrl list servers
┌─────────┬─────────────────┬──────┬─────────────┬────────────────────────┐
│ Server  │ IP Address      │ Port │ Connections │ State                  │
├─────────┼─────────────────┼──────┼─────────────┼────────────────────────┤
│ server1 │ 192.168.121.52  │ 3301 │ 0           │ Down                   │
├─────────┼─────────────────┼──────┼─────────────┼────────────────────────┤
│ server2 │ 192.168.121.191 │ 3301 │ 0           │ Master, Slave, Running │
├─────────┼─────────────────┼──────┼─────────────┼────────────────────────┤
│ server3 │ 192.168.121.114 │ 3301 │ 0           │ Slave, Running         │
├─────────┼─────────────────┼──────┼─────────────┼────────────────────────┤
│ server4 │ 192.168.121.211 │ 3301 │ 0           │ Slave, Running         │
└─────────┴─────────────────┴──────┴─────────────┴────────────────────────┘

For a short moment, we may observe that the state of server1 is Down but the state of server2 remains Slave, Running.

[Looking for a solution to another query? We are just a click away.]

Conclusion

In conclusion, we have seen the operational details of Failover. We have also included the steps from our Tech team on how to automate Failover functionality in MariaDB.