Automating failover in a master-master replication topology

[Vangelis Pagonis]

Hi all,

We have implemented the following architecture topology in our environment:

2 application servers connecting through VIP to a 2 node mysqlrouter cluster, where both have a first-available failover policy, and
mysqlrouter1 has first available db1 second db2 and
mysqlrouter2 has first-available db2 second db1

There is always one active mysqlrouter, connecting application servers always to the active db server in a master-master replication, where second master is passive in offline_mode.

In case e.g. db1 fails, myrouter1 will failover to db2, then we could manually relocate VIP so router2 with first available db2 comes active. We could then run any maintenance on db1. this worked and tested vice versa but manually.

We are looking into automating the failover procedure, running some checks first in db level before failing over to passive node. How could we achieve such automation? Should automation script/condition run on router level?
Is there any tool/utility providing such automation on failover processes?

All our servers run on CentOS 8.2, mysql version is 5.7.32 and mysqlrouter version 8.021.

Thanks a lot for you assistance.

Regards,

Vangelis