MySQL Router HA with Keepalived

After having explained how to achieve HA for MySQL Router for people who doesn’t want to install the MySQL Router on the application servers and after having illustrated how to use Pacemaker, this article explains how to setup HA for MySQL Router using keepalived.

Keepalived is very popular, maybe because it’s also very easy to use. We can of course use 2 or more servers. The principle is the same as on the previous articles, if the router dies, the virtual IP used by the application server(s) to connect to MySQL is sent to another machine where mysqlrouter is still running.

Let’s have a look at the configuration, in this case we use 2 machines, mysql1 and mysql2.

Configuration

Let’s configure our 2 routers. The configuration file is /etc/keepalived/keepalived.conf and the VIP are using to contact the router is 192.168.87.5

We also have to decide which one will act as master and which one will the backup: mysqlrouter1 will be the master and mysqlrouter2 the backup.

mysqlrouter1

global_defs {
   notification_email {
     lefred @ lefred.be
   }
   notification_email_from mycluster @ lefred.be 
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_script chk_mysqlrouter {
  script "/bin/killall -0 /usr/bin/mysqlrouter" # check the haproxy process
  interval 2 # every 2 seconds
  weight 2 # add 2 points if OK
  fall 2
}

vrrp_instance VI_1 {
    state MASTER
    interface eth1
    virtual_router_id 51
    priority 102
    advert_int 1
    virtual_ipaddress {
        192.168.87.5
    }
    track_script {
        chk_mysqlrouter
    }
}

The important here is the state which is set to MASTER and the priority.

mysqlrouter2

global_defs {
   notification_email {
     lefred @ lefred.be
   }
   notification_email_from mycluster @ lefred.be 
   smtp_server 192.168.200.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
   vrrp_skip_check_adv_addr
   vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_script chk_mysqlrouter {
  script "/bin/killall -0 /usr/bin/mysqlrouter" # check the haproxy process
  interval 2 # every 2 seconds
  weight 2 # add 2 points if OK
  fall 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth1
    virtual_router_id 51
    priority 101
    advert_int 1
    virtual_ipaddress {
        192.168.87.5
    }
    track_script {
        chk_mysqlrouter
    }
}

We can see that the state and the priority are different.

Now we can start keepalived and see that the VIP will be enabled on the master (mysqlrouter1):

[root@mysqlrouter1 ~]# systemctl start keepalived
[root@mysqlrouter1 ~]# ip add sho eth1
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:ab:eb:b4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.87.3/24 brd 192.168.87.255 scope global eth1
       valid_lft forever preferred_lft forever
    inet 192.168.87.5/32 scope global eth1
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:feab:ebb4/64 scope link 
       valid_lft forever preferred_lft forever

We can see that 192.168.87.5 is now available on eth1.

If we stop mysqlrouter on mysqlrouter1, we will see after 2 seconds max that the ip will be moved to mysqlrouter2:

[root@mysqlrouter1 ~]# systemctl stop mysqlrouter
[root@mysqlrouter1 ~]# ip add sho eth1
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:ab:eb:b4 brd ff:ff:ff:ff:ff:ff
    inet 192.168.87.3/24 brd 192.168.87.255 scope global eth1
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:feab:ebb4/64 scope link 
       valid_lft forever preferred_lft forever


[root@mysqlrouter2 ~]# ip add sho eth1
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:f9:23:f1 brd ff:ff:ff:ff:ff:ff
    inet 192.168.87.4/24 brd 192.168.87.255 scope global eth1
       valid_lft forever preferred_lft forever
    inet 192.168.87.5/32 scope global eth1
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fef9:23f1/64 scope link 
       valid_lft forever preferred_lft forever

Conclusion

Once again, we reached MySQL Router High Availability easily, very quickly and with standard tools included in most GNU/Linux distributions.

Let me know if you are interested in other open source tools available to achieve MySQL Router HA.