How to Build a Highly Available MySQL Cluster with HAProxy and Keepalived on OpenStack

Overview

HAProxy + Keepalived is a popular high‑availability solution; HAProxy provides load balancing while Keepalived handles failover. The tutorial implements a MySQL HA setup where a single write‑node requirement is satisfied using Keepalived’s sorry_server feature. When network, MySQL, or server issues occur, traffic automatically switches to a standby node and returns when the primary recovers.

Architecture Diagram

Node Preparation

All nodes must have SELinux set to permissive and firewalls disabled:

# sed -i.bak "s/SELINUX=enforcing/SELINUX=permissive/g" /etc/selinux/config
# setenforce 0
# chkconfig firewalld off
# service firewalld stop

Install Percona XtraDB Cluster RPMs

# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm -y
# yum install Percona-XtraDB-Cluster-56

Database Configuration

Each node receives a customized /etc/my.cnf. Example for Node1 :

[mysqld]
log_bin
binlog_format = ROW
innodb_buffer_pool_size = 122M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 20M
innodb_file_per_table = 1
datadir = /var/lib/mysql
wsrep_cluster_address = gcomm://openstack1,openstack2,openstack3
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
wsrep_slave_threads = 8
wsrep_cluster_name = Cluster
wsrep_node_name = openstack1
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
socket = /var/lib/mysql/mysql.sock
user = mysql
bind-address = 172.16.8.62
default_storage_engine = innodb
wsrep_provider_options = "pc.recovery=TRUE;gcache.size=300M"
wsrep_sst_method = rsync
wsrep_node_address = "172.16.8.62"
[mysqld_safe]
pid-file = /run/mysqld/mysql.pid
syslog
!includedir /etc/my.cnf.d

Node2 and Node3 have identical sections with their respective IP addresses.

Start the Cluster

On the first node (openstack1) bootstrap the cluster:

# systemctl start [email protected]
# systemctl enable [email protected]

On CentOS 6 the command would be # /etc/init.d/mysql bootstrap-pxc. This tells Galera that this is the initial node, so no data sync is performed.

Start MySQL on the remaining nodes:

# systemctl start mysql
# systemctl enable mysql

Run the secure installation script on any node:

/usr/bin/mysql_secure_installation

Clustercheck Tool

Create /etc/sysconfig/clustercheck with credentials:

MYSQL_USERNAME="clustercheckuser"
MYSQL_PASSWORD="clustercheckpassword!"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"

Install xinetd and add a service definition /etc/xinetd.d/galera-monitor to expose the check on port 9200:

service galera-monitor
{
    port = 9200
    disable = no
    socket_type = stream
    protocol = tcp
    wait = no
    user = root
    group = root
    server = /usr/bin/clustercheck
    type = UNLISTED
    per_source = UNLIMITED
    log_on_failure = HOST
    flags = REUSE
}

Update /etc/services to add:

mysqlchk    9200/tcp    # mysqlchk

HAProxy Installation and Configuration

# yum -y install haproxy

Configure rsyslog to capture HAProxy logs:

# cat /etc/rsyslog.d/haproxy.conf
local2.=info    /var/log/haproxy-access.log
local2.notice   /var/log/haproxy-info.log

Restart rsyslog: # systemctl restart rsyslog Create /etc/haproxy/haproxy.cfg (backup original first). Key sections:

global
    log 127.0.0.1   local2
    maxconn 1024
    user haproxy
    group haproxy
    daemon
    stats socket /var/run/haproxy.sock mode 600 level admin

defaults
    log     global
    mode    http
    option  tcplog
    option  dontlognull
    retries 3
    option  redispatch
    maxconn 1024
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms

listen openstack_cluster 172.16.8.60:3306
    mode tcp
    balance leastconn
    option httpchk
    server openstack1 172.16.8.62:3306 check port 9200
    server openstack2 172.16.8.63:3306 check port 9200
    server openstack3 172.16.8.64:3306 check port 9200

listen stats 0.0.0.0:9000
    mode http
    stats enable
    stats uri /haproxy
    stats realm HAProxy\ Statistics
    stats auth admin:Changeme_123
    stats hide-version
    stats admin if TRUE

Start HAProxy and enable on boot:

# systemctl start haproxy
# chkconfig haproxy on

Allow remote root access (example):

# mysql -u root -p
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY "Changeme_123";

Keepalived Installation and Configuration

# yum install keepalived -y

Create a health‑check script /etc/keepalived/check_haproxy.sh:

#!/bin/bash
A=`ps -C haproxy --no-header | wc -l`
if [ $A -eq 0 ]; then
    systemctl restart haproxy
    sleep 3
    if [ `ps -C haproxy --no-header | wc -l` -eq 0 ]; then
        service keepalived stop
    fi
fi

Make it executable: # chmod +x /etc/keepalived/check_haproxy.sh Backup the original keepalived.conf and create a new one:

# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
vrrp_script chk_http_port {
    script "/etc/keepalived/check_haproxy.sh"
    interval 2
    weight -10
}
global_defs {
    router_id LVS_DEVEL
}
vrrp_instance VI_1 {
    state MASTER
    interface enp0s3
    virtual_router_id 51
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        chk_http_port
    }
    virtual_ipaddress {
        172.16.8.60
    }
}

Adjust priority and state on backup nodes accordingly.

Kernel Parameter for VIP Binding

# echo 'net.ipv4.ip_nonlocal_bind = 1' >> /etc/sysctl.conf
# sysctl -p

Start Keepalived

# service keepalived restart
# chkconfig keepalived on

Testing

1. Verify each MySQL instance can create databases:

# mysql -u root -p
MariaDB [(none)]> create database this_mariadb1;
MariaDB [(none)]> create database this_mariadb2;
MariaDB [(none)]> create database this_mariadb3;

2. Test the floating IP by connecting to 172.16.8.60 and confirming the query returns expected results.

3. Test load balancing via HAProxy:

# mysql -u root -pChangeme_123 -h 172.16.8.60 -P3306 -e "select Host, User, Password from mysql.user"

4. Access HAProxy statistics UI at http://172.16.8.60:9000/haproxy (login admin/Changeme_123).

5. Simulate failures by shutting down one or two database servers; the cluster continues to serve traffic, demonstrating true high‑availability.

Conclusion

The step‑by‑step procedure establishes a resilient MySQL cluster on OpenStack, leveraging Percona XtraDB Cluster for data consistency, HAProxy for TCP load balancing, and Keepalived for virtual IP failover. The provided scripts, configuration files, and test commands enable rapid replication of the setup in similar environments.