Click here to Skip to main content
14,690,227 members
Articles » Database » Database » MySQL
Article
Posted 21 Dec 2018

Tagged as

Stats

12.3K views
1 bookmarked

MySQL Auto Failover using Keepalived on CentOS

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
5 Dec 2019CPOL
A tutorial on how to setup Keepalived for MySQL Auto Failover

Introduction

Keepalived is a lightweight and high availability framework solution in Linux. It uses Linux Virtual Server (LVS) to perform load balancing and failover tasks on. The Keepalived can be used to monitor systems and to automatically failover to a standby if problems occur. If the primary server goes down, the floating IP will be moved to the second server automatically, allowing service to resume.

Image 1

How Does Keepalived Work?

In Master-Slave Replication database setup, when the Master failed, we need to manually make the configuration changes in order to failover to the next available server. When this happens, downtime is expected since manual failover will take time.

Keepalived is a utility that provides interface failover. With a good implementation of Keepalived, you will be able to failover a virtual IP address when the master server becomes unavailable and automatically switches that IP over to the hot standby server. As a result, the second server will handle the traffic without any downtime in the production environment.

Prerequisites

We’ll be using CentOS Linux as the operating system for this tutorial. Here are the following prerequisites that you need to setup first in order to complete this guide:

  1. Working MySQL Master-Slave Replication
    To setup a Master-Slave Replication, follow the steps here.
  2. Virtual IP Address (VIP) of Master Server
    You can follow the steps here to learn how to configure a VIP on your server.

Network Scenario for this tutorial:

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

Tutorial

Step 1. Install Keepalived

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

sudo yum -y install keepalived

Step 2. Backup Default Keepalived Config File

Let’s back up the default Keepalived configuration file if in case you’d like to use it later as a template or for reference.

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

Step 3. Configure Keepalived Config File in Master Server

You can copy the following configuration block below but you have to make appropriate changes for your environment which include 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 {
interface 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
}              
}

Tip

Run this command to show the current network configuration and see the configured network interface in the server:

ip addr show

Image 2

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

Image 3

Take note that the Virtual IP Address (192.168.40.224) is assigned to the Master Server (192.168.40.222)

Step 4. Configure Keepalived config File in Slave Server

Again, you can copy the following configuration block below, but you need to make appropriate changes for your 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    # IP address of Slave Server

unicast_peer {
192.168.40.222                   # IP address of Master Server
}

priority 101                     # slave server should have a priority number lower than the slave

track_script {
     chk_mysql
}

virtual_ipaddress {
192.168.40.224/24 dev enp0s3     # Virtual IP address
}               
}

Step 5. Run Command on Both Servers

Run this command on both servers for the firewall:

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;

Step 6. Configure Keepalived Service

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

systemctl enable keepalived

Step 7. Start the Keepalived Service

Start the Keepalived service:

systemctl start keepalived

Step 8. Check Status of Keepalived

Now check the status of the Keepalived on Master and Slave servers by:

service keepalived status

You will know if you have configured the Keepalived service properly if the status indicated is active as shown below:

Image 4

Notes

Here are the following commands you will need to control Keepalived:

service keepalived start    - used to start the Keepalived service
service keepalived stop     - used to stop the Keepalived service
service keepalived restart  - used to restart the Keepalived service
service keepalived status   - used to check the status of the Keepalived service

If you encountered a problem like this:

Keepalived[1650]: WARNING - default user 'keepalived_script' 
for script execution does not exist - please create.

Resolve this by:

sudo useradd -g users -M keepalived_script

and restart the Keepalived service:

sudo systemctl restart keepalived

You can check the logs of the keepalived here:

/var/log/messages

Test

To the most awaited part of testing if the Keepalived is going to work for the failover, let’s test it by first shutting down the Master Server (192.168.40.222).

Now, you will see that the Virtual IP address (192.168.40.224) is now shifted to the Slave Server (192.168.40.223).

Image 5

Check the status of the Keepalived in the Slave Server.

Image 6

History

  • 5th December, 2019: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Renz Ladroma
Philippines Philippines
From the beautiful islands of the Philippines. I like words, queries and scripts.

Comments and Discussions

 
Questionhow to do when Master server mysql come back Pin
Member 1459340714-Sep-19 20:40
MemberMember 1459340714-Sep-19 20:40 
QuestionRe: how to do when Master server mysql come back Pin
Member 147664977-Mar-20 19:55
MemberMember 147664977-Mar-20 19:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.