MySQL + Keepalived HA Service Configuration

IP	Role
192.168.10.2	MySQL Node01
192.168.10.3	MySQL Node02
192.168.10.100	VIP

In the Keepalived configuration, the concept of VIP (Virtual IP) refers to a virtual IP address that can be used by the standby node to take over services from the primary node, preventing client connection interruptions.

Configure Master-Slave Synchronization

First, refer to the MySQL master-slave deployment document to deploy MySQL services on both nodes, then configure master-slave synchronization as described in this document.

1. Log in to the MySQL Node01 and MySQL Node02 nodes to create master-slave synchronization users

   /usr/local/mysql/bin/mysql --socket=/usr/local/mysql/mysql.sock -uroot -p123456
   # Create repl
   create user 'repl'@'%' identified by '123456';
   grant replication slave on *.* to 'repl'@"%";
   flush privileges;

   • The newly created master-slave synchronization user in the command is repl, with a password 123456. Be sure to replace it during actual deployment.

2. Check the master_log_file and master_log_pos information on both MySQL Node01 and MySQL Node02 nodes

   > show master status;

3. Log in to the MySQL Node01 node to configure master-slave synchronization

   /usr/local/mysql/bin/mysql --socket=/usr/local/mysql/mysql.sock -uroot -p123456
   change master to master_host="192.168.10.3",master_port=3306,master_user="repl",master_password="123456",master_log_file="mysql-bin.000001",master_log_pos=2936,get_master_public_key=1;
   start slave;

   • In the change master statement, ensure that the master_host address is replaced with the actual MySQL Node02 server IP during deployment.
   • The values for master_log_file and master_log_pos in the change master statement are obtained from the show master status; command executed on the master node. Adjust according to your actual deployment.

4. Check the master-slave synchronization status

   show slave status\G
   
   # In the output, both Slave_IO_Running and Slave_SQL_Running being Yes indicates normal master-slave synchronization.

5. Log in to the MySQL Node02 node to configure master-slave synchronization

   /usr/local/mysql/bin/mysql --socket=/usr/local/mysql/mysql.sock -uroot -p123456
   change master to master_host="192.168.10.2",master_port=3306,master_user="repl",master_password="123456",master_log_file="mysql-bin.000001",master_log_pos=2936,get_master_public_key=1;
   start slave;

   • In the change master statement, ensure that the master_host address is replaced with the actual MySQL Node01 server IP during deployment.
   • The values for master_log_file and master_log_pos in the change master statement are obtained from the show master status; command executed on the master node. Adjust according to your actual deployment.

6. Check the master-slave synchronization status

   show slave status\G
   
   # In the output, both Slave_IO_Running and Slave_SQL_Running being Yes indicates normal master-slave synchronization.

7. At this point, the synchronization status of both MySQL instances should be active and normal.

Configure Keepalived

Install Keepalived

Both MySQL servers need to install the Keepalived service

yum install -y keepalived

Modify the Keepalived Configuration File

MySQL Node01 Node

Modify the /etc/keepalived/keepalived.conf file as follows

global_defs {
    router_id hap-mysql-ha-01
}

vrrp_script check_mysql_health {
    script "/usr/local/mysql/script/check_mysql_health.sh"
    interval 10
}

vrrp_sync_group VG1 {
    group {
        VI_1
    }
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0          # Change to the network interface name of the deployed host
    virtual_router_id 180   # Unique among all VRRP routers in the same subnet, range 0-255
    priority 100            # Priority, different for each node
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass HAP-MySQL-Keepalived-Auth
    }
    track_script {
        check_mysql_health
    }
    notify_master /usr/local/mysql/script/notify_master.sh
    notify_backup /usr/local/mysql/script/notify_backup.sh
    virtual_ipaddress {
        192.168.10.100      # VIP address
    }
}

• Note that the priority values in the configuration files of the two nodes are different.
• By default, you only need to modify the interface and VIP address.

MySQL Node02 Node

Modify the /etc/keepalived/keepalived.conf file as follows

global_defs {
    router_id hap-mysql-ha-01
}

vrrp_script check_mysql_health {
    script "/usr/local/mysql/script/check_mysql_health.sh"
    interval 10
}

vrrp_sync_group VG1 {
    group {
        VI_1
    }
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0          # Change to the network interface name of the deployed host
    virtual_router_id 180   # Unique among all VRRP routers in the same subnet, range 0-255
    priority 90             # Priority, different for each node
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass HAP-MySQL-Keepalived-Auth
    }
    track_script {
        check_mysql_health
    }
    notify_master /usr/local/mysql/script/notify_master.sh
    notify_backup /usr/local/mysql/script/notify_backup.sh
    virtual_ipaddress {
        192.168.10.100      # VIP address
    }
}

• Note that the priority values in the configuration files of the two nodes are different.
• By default, you only need to modify the interface and VIP address.

Create MySQL Related Scripts

The following MySQL related scripts need to be created on both nodes.

Create a directory for storing the scripts

mkdir -p /usr/local/mysql/script/

Execute vim /usr/local/mysql/script/check_mysql_health.sh to create the MySQL status check script

The check_mysql_health.sh script content is as follows:

#!/bin/bash

# MySQL related information
mysql_user="root"
mysql_password="123456"
mysql_host="127.0.0.1"
mysql_cli="/usr/local/mysql/bin/mysql"

# Log function
mysql_keepalived_log_file="/var/log/mysql_keepalived.log"
log_info() {
    echo "$(date +"%Y-%m-%d %H:%M:%S") INFO: $1" >> "$mysql_keepalived_log_file"
}

# Check configuration
max_retries=3
retry_interval=2

check_mysql_status() {
    # Check if MySQL service is running
    $mysql_cli -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" <<< 'show status;' > /dev/null 2>&1
    mysql_status=$?

    # Check if MySQL process is running
    ps aux | grep mysqld | grep -v grep | grep -v check_mysql_health > /dev/null 2>&1
    mysql_process=$?
}

# Continuously check MySQL service status, retrying up to max_retries times
retry_count=0
while true; do
    check_mysql_status

    if [ $mysql_status -eq 0 ] && [ $mysql_process -eq 0 ]; then
        # Both MySQL service and process are normal, exit the loop
        exit 0
    else
        if [ $retry_count -ge $max_retries ]; then
            # Maximum retry attempts reached, perform action to stop Keepalived service and exit
            log_info "After $max_retries retries, MySQL remains unhealthy, stop Keepalived."
            systemctl stop keepalived
            exit 0
        fi
    fi

    retry_count=$((retry_count + 1))
    sleep $retry_interval
done

Execute vim /usr/local/mysql/script/notify_master.sh to create a script executed by Keepalived when the node is chosen as Master

The notify_master.sh script content is as follows:

#!/bin/bash
# MySQL related information
mysql_user="root"
mysql_password="123456"
mysql_host="127.0.0.1"
mysql_cli="/usr/local/mysql/bin/mysql"

# Log function
mysql_keepalived_log_file="/var/log/mysql_keepalived.log"
log_info() {
    echo "$(date +"%Y-%m-%d %H:%M:%S") INFO: $1" >> "$mysql_keepalived_log_file"
}

# Stop the synchronization thread on the current node
if $mysql_cli -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" <<< 'stop slave;'; then
    log_info "The current node becomes master, successfully stopped master-slave replication on the current node."
else
    log_info "The current node becomes master, failed to stop master-slave replication on the current node."
fi

Execute vim /usr/local/mysql/script/notify_backup.sh to create a script executed by Keepalived when the node is chosen as Backup

The notify_backup.sh script content is as follows:

#!/bin/bash
# MySQL related information
mysql_user="root"
mysql_password="123456"
mysql_host="127.0.0.1"
mysql_cli="/usr/local/mysql/bin/mysql"

# Log function
mysql_keepalived_log_file="/var/log/mysql_keepalived.log"
log_info() {
    echo "$(date +"%Y-%m-%d %H:%M:%S") INFO: $1" >> "$mysql_keepalived_log_file"
}

# Start the synchronization thread on the current node
if $mysql_cli -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" <<< 'start slave;' ; then
    log_info "The current node becomes backup, successfully started master-slave replication on the current node."
else
    log_info "The current node becomes backup, failed to start master-slave replication on the current node."
fi

Add executable permissions to all scripts

chmod +x /usr/local/mysql/script/*.sh

Start Keepalived

systemctl start keepalived
systemctl enable keepalived

Recheck MySQL Master-Slave Synchronization and VIP Status

The normal status at this time should be:

• The synchronization thread of the MySQL on the VIP node is stopped
• The synchronization thread of the MySQL on the other node is running and normal

Troubleshooting

Both Servers Have VIP

Usually this can be caused by:

• There is a firewall or other network restriction between the two machines

  • Port is restricted (defaults to using port 112)

  • The network environment doesn't support VRRP (Virtual Router Redundancy Protocol)

• The two machines cannot communicate with each other’s address

• The priority set in the configuration for both nodes might be the same, causing a failure to elect a primary node

• Check the /var/log/messages log file for troubleshooting

Neither Server Has VIP

Usually this can be caused by:

• The vrrp_script script specified in keepalived.conf is executing abnormally and always returns a non-zero error value, causing the keepalived state to be unable to initialize and bind the VIP.

  • For example, MySQL has not started, or the password in the script to connect to MySQL is incorrect. You can execute the script manually for troubleshooting.

• Check the /var/log/messages log file for troubleshooting