How to do HA failover for mysqlrouter with keepalived using docker

Question

So i have mysqlrouter docker container that is load balancing request to my mysql innodb cluster and every thing is working perfectly. Now being that having a single mysqlrouter in my setup also means that the single router is a single-point-of-failure, i want to achieve HA with spinning another docker container - mysqlrouter-bkup. Also, i want to spin 2 keepalived docker containers monitoring the mysqlrouter cluster so as to cause failover (i.e make the backup instance a master when the master instance dies).

What i have managed to do so far (without any proper documented guidance on how to achieve this using docker containers online) is:

Spin up the first mysqlrouter with the following command:

docker run --name mysqlrouter -p 6446:6446 -p 4667:4667 --net=smartdev-pro_internalnet -e MYSQL_HOST=mysql1 -e MYSQL_PORT=3306 -e MYSQL_USER=clusterAdmin -e MYSQL_PASSWORD=password -e MYSQL_INNODB_CLUSTER_MEMBERS=3 -e MYSQL_CREATE_ROUTER_USER=0 mysql/mysql-router

Spin up the second mysqlrouter with the following command:

docker run --name mysqlrouter-bkup -p 6448:6446 -p 4669:4667 --net=smartdev-pro_internalnet -e MYSQL_HOST=mysql1 -e MYSQL_PORT=3306 -e MYSQL_USER=clusterAdmin -e MYSQL_PASSWORD=password -e MYSQL_INNODB_CLUSTER_MEMBERS=3 -e MYSQL_CREATE_ROUTER_USER=0 mysql/mysql-router

Spin up the first keepalived docker container:

docker run --name keepalived1 --env KEEPALIVED_STATE="MASTER" --env KEEPALIVED_INTERFACE="eth0" --env KEEPALIVED_UNICAST_PEERS="#PYTHON2BASH:['172.18.0.9', '172.18.0.10']" --env KEEPALIVED_ROUTER_ID="100" --env KEEPALIVED_PRIORITY="200" --env KEEPALIVED_PASSWORD="password" --env KEEPALIVED_VIRTUAL_IPS="172.18.0.100" --cap-add=NET_ADMIN --cap-add=NET_BROADCAST --cap-add=NET_RAW --net=host osixia/keepalived:2.0.20

Spin up the second keepalived docker container:

docker run --name keepalived2 --env KEEPALIVED_STATE="BACKUP" --env KEEPALIVED_INTERFACE="eth0" --env KEEPALIVED_UNICAST_PEERS="#PYTHON2BASH:['172.18.0.9', '172.18.0.10']" --env KEEPALIVED_ROUTER_ID="101" --env KEEPALIVED_PRIORITY="100" --env KEEPALIVED_PASSWORD="password" --env KEEPALIVED_VIRTUAL_IPS="172.18.0.100" --cap-add=NET_ADMIN --cap-add=NET_BROADCAST --cap-add=NET_RAW --net=host osixia/keepalived:2.0.20

172.18.0.9 is mysqlrouter ip address and 172.18.0.10 is mysqlrouter-bkup ip address.

So all containers start successfully and the complete logs for both keepalived services are posted here: click me because i couldn't post more than 30,000 characters here.

Now in my nodejs mysql connection string, 172.18.0.100 (which is the virtual ip of keepalived) is what i supply and the host.

const connection = mysql.createPool({
  host: 172.18.0.100,
  user: process.env.USER,
  password: process.env.PASSWORD,
  database: process.env.DATABASE,
  port: 6446,
  connectionLimit: 20
});

So with this setup, everything works fine until i stop the mysqlrouter container to see if it will failover to mysqlrouter-bkup, it doesn't.

Please can someone point out to me what i am doing wrong.

Extra Info: i am running this containers on docker desktop(with wsl2), windows 11. And please if there is a better way of achieving my purpose using docker on windows, i am open to it.

How to do HA failover for mysqlrouter with keepalived using docker

0 Answers