Let us learn more about MySQL cluster Keepalived and how to set up Keppalived for MySQL Auto Failover with the support of our MySQL support services at Bobcares.

MySQL Auto Failover using Keepalived on CentOS

Keepalived is a Linux framework solution that is lightweight and high-availability. It performs load balancing and failover tasks on Linux Virtual Server (LVS).

Keepalived can be used to monitor systems and automatically switch to standby mode if problems arise.  If the primary server fails, the floating IP will be automatically moved to the backup server, allowing the service to resume.

How Does Keepalived Work?

When the Master fails in a Master-Slave Replication database setup, we must manually make configuration changes in order to failover to the next available server.

Downtime is to be expected when this occurs because manual failover takes time.

Keepalived is a program that handles interface failover. We will be able to failover a virtual IP address when the master server becomes unavailable and automatically switch that IP over to the hot standby server with a good implementation of Keepalived.

As a result, the traffic in the production environment will be handled by the second server without any downtime.

Requirements

Note here in this article we are using CentOS Linux as the operating system. Here are the requirements that we need to setup first to complete the process as per :

1. Working MySQL Master-Slave Replication.
2. Virtual IP Address (VIP) of Master Server.

Consider the Network Scenario given below as an example:

Master Server – 192.168.40.222
Slave Server – 192.168.40.223
Virtual IP Address – 192.168.40.224

MySQL cluster KeepAlived: Configuration and setup

Install Keepalived

Keepalived is usually available in most Linux repositories. Here’s the command to install Keepalived service on both servers:

sudo yum -y install keepalived

Backup Default Keepalived Config File

Let’s save the default Keepalived configuration file for later use as a template or for reference.

mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.back

Configure Keepalived Config File in Master Server

We can copy the configuration block below, but we must make the necessary changes for the environment, including the IPs and interfaces, priority, and unicast_peer.

Location: /etc/keepalived/keepalived.conf

vrrp_script chk_mysql {
script "pidof mysqld"
interval 2 # every 2 seconds
weight 2
}

vrrp_instance VI_1 { i
nterface enp0s3 # the network interface to which the virtual address is assigned 
state MASTER # MASTER for master server 
virtual_router_id 50 # the virtual router ID must be unique to each VRRP instance
 unicast_src_ip 192.168.40.222 # IP address of Master Server,

unicast_peer {
192.168.40.223 # IP address of Slave Server
}
priority 102 # master server should always have a higher priority number
# than the slave
track_script {
chk_mysql
}
virtual_ipaddress {
192.168.40.224/24 dev enp0s3 # Virtual IP address
}
}

We can use this command to see the server’s configured network interface and the current network configuration:

ip addr show
[root2localhost~]# ip addre show
1: lo:<LOOPBACK, UP, LOWER_UP> mtu 65536 qdusc noqueue state UNKNOWN group default qlen 1000
Link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host 1o
valid_1ft forver preferred-1ft forever
iner6::1/128 scope host
valid-lft forver preferres-lft forever

2: enp0s3; <BRAODCAST, MULTICAST, UP, LOWER_UP> mtu 1500 qdisc pfifo-fast state UP group defauklt qlen 1000
link/ether 08;00:27:95:a0;06 brd ff:ff:ff:ff:ff:ff
inet 192.168.40.222/24 brd 192.168.40.255 scope global boprefixroute enp0s3
invaludp-lft forver preferrred-lft forevr.

Using this command, we can also see the Virtual IP Address assigned to the Master Server, as shown below:

[root@localhost ~]# ip addr show
1: lo: <loopback, up,="" lower_up=""> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid ift forever preferred ift forever
inet6 ::1/128 scope host
valid_ift forever preferred_ift forever

2: enp033: <broadcast, multicast,="" up,="" lower_up=""> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:95:a0:06 brd ff:ff:ff:ff:ff:ff
inet 192.168.40.222/24 brd 192.168.40.255 scope global noprefixroute enp0s3
valid ift forever preferred ift forever
inet 192.168.40.224/24 brd 192.168.40.255 scope global secondary noprefixroute enp0s3:1
valid_ift forever preferred ift forever
inet6 fe80:: 445c: 587d:dl4e: 267b/64 scope link tentative noprefixroute dadfailed
valid_ift forever preferred_ift forever
inet6 fe80::20:39f6:66fc:985e/64 valid ift forever preferred ift forever scope link noprefixroute.


Take note that the Master Server has the Virtual IP Address (192.168.40.224). (192.168.40.222).

Configure the Keepalived configuration file in the slave server.

Again, we can copy the configuration block below, but we must make the necessary changes for the environment:

Location: /etc/keepalived/keepalived.conf
vrrp_script chk_mysql {
script "pidof mysqld"
interval 2 # every 2 seconds
weight 2
}

vrrp_instance VI_1 { interface enp0s3 # the network interface to which the virtual address is assigned state BACKUP # BACKUP for slave server virtual_router_id 50 # the virtual router ID must be unique to each VRRP instance unicast src IP 192.168.40.223 # Slave Server IP address.

unicast_peer {
192.168.40.222 # IP address of Master Server
}
priority 101 # slave server should have a priority number lower than the slave t
rack_script {
chk_mysql
}
virtual_ipaddress {
192.168.40.224/24 dev enp0s3 # Virtual IP address
}
}

Run Command on Both Servers

For the firewall, run the following command on both servers:

firewall-cmd --direct --permanent --add-rule ipv4 filter INPUT 0 --in-interface enp0s3 --destination 224.0.0.18 --protocol vrrp -j ACCEPT; firewall-cmd --direct --permanent --add-rule ipv4 filter OUTPUT 0 --out-interface enp0s3 --destination 224.0.0.18 --protocol vrrp -j ACCEPT;
firewall-cmd –reload;

Configure Keepalived Service

Configure the Keepalived service to start at the start of the server automatically:

systemctl enable keepalived,

Start the Keepalived Service

Type in the following command line to start up the Keepalived service:

systemctl start keepalived

Check Status of Keepalived

We can now use the following command line to check the status of the Keepalived on the Master and Slave servers:

service keepalived status

We will know if we have configured the Keepalived service properly if the status indicated is active.

[Need assistance with similar queries? We are here to help]

Conclusion

To conclude we have now gone through the configurations on how to set up the MySQL cluster keepalived in a few simple steps with the assistance of our MySQL support services. We also learned more about the Kepplive id and its usage and the various configurations and steps necessary to set it up.