How to Build MySQL Master‑Master HA with Keepalived: A Step‑by‑Step Guide

Introduction

MySQL is often the core storage for business systems, and a database outage can severely impact read/write operations. To achieve rapid recovery and high availability, this guide demonstrates how to build a MySQL master‑master cluster combined with Keepalived for automatic failover.

1. Solution Overview

The architecture consists of two MySQL instances running in Docker containers on separate Ubuntu servers, configured for master‑master replication, and a Keepalived service providing a virtual IP (VIP) that clients connect to. Keepalived monitors MySQL, attempts to restart it on failure, and switches traffic to the healthy node.

MySQL Master‑Master Mode

Both servers host a MySQL instance that synchronizes data bidirectionally. Only one instance serves client traffic at a time; the other acts as a standby. Without Keepalived, failover must be performed manually.

Keepalived Functions

Periodically checks MySQL service health and restarts it if it crashes.

Provides a virtual IP that abstracts the actual MySQL host from clients.

When a node fails and cannot be restarted, Keepalived disables itself, allowing the other node to take over the VIP.

2. Master‑Master Replication Mechanics

Replication works by each server writing changes to its binary log (binlog). The opposite server reads the binlog via an I/O thread, stores it in a relay log, and replays it with an SQL thread, keeping data in sync.

The primary writes changes to binlog.

The replica’s I/O thread fetches the binlog and writes it to a local relay‑log.

The replica’s SQL thread reads the relay log and executes the statements.

3. Preparing the MySQL Environment

Two Ubuntu VMs with Docker are used. The MySQL Docker image is saved, transferred, and loaded on both hosts.

sudo docker save -o mysql.tar hcr:5000/hschub/hscmysql:0.0.2
sudo chmod 777 mysql.tar

Load the image on each server: sudo docker load -i mysql.tar Run the container with data and configuration volume mappings:

sudo docker run -p 3306:3306 --name mysql \
  -v /home/hss/mysql/data:/var/lib/mysql \
  -v /home/hss/mysql/etc/mysql:/etc/mysql \
  -e MYSQL_ROOT_PASSWORD='123456' -d 46b

4. Configuring MySQL Master‑Slave (then Master‑Master)

Edit /home/hss/mysql/etc/mysql/my.cnf on each node to set unique server_id and enable binlog, relay‑log, and related options. Create the log directories and grant appropriate permissions.

server_id = 11
log_bin = /var/lib/mysql/log/mysql-bin
binlog_format = mixed
log_slave_updates = 1
relay_log = /var/lib/mysql/log/relay-bin
...

Create a replication user on the primary:

CREATE USER 'vagrant'@'192.168.56.12' IDENTIFIED BY 'vagrant';
ALTER USER 'vagrant'@'192.168.56.12' IDENTIFIED WITH mysql_native_password BY 'vagrant';
GRANT REPLICATION SLAVE ON *.* TO 'vagrant'@'192.168.56.12';
FLUSH PRIVILEGES;

Lock tables, note the current binlog file and position, dump all databases, and import the dump on the replica.

FLUSH TABLES WITH READ LOCK;
# note File and Position
mysqldump -uroot -P3306 --all-databases --triggers --routines --events > /var/lib/mysql/backup/all_databases.sql
UNLOCK TABLES;

Configure the replica to start replication:

CHANGE MASTER TO MASTER_HOST='192.168.56.11',
MASTER_PORT=3306,
MASTER_USER='vagrant',
MASTER_PASSWORD='vagrant',
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=1020;
START SLAVE;

Verify with SHOW SLAVE STATUS \\G and ensure both Slave_IO_Running and Slave_SQL_Running are Yes.

5. Converting to Master‑Master

Swap the replication direction on the second node, create a replication account for the first node, and adjust CHANGE MASTER TO parameters accordingly. After restarting the slave threads on both nodes, the cluster operates in true master‑master mode.

6. Setting Up Keepalived

6.1 Use Cases

If MySQL crashes, Keepalived can either stop itself (letting the other node take over) or attempt to restart MySQL first.

6.2 Installation

Install required libraries, download the source tarball, extract, configure, compile, and install.

# Install dependencies
sudo apt-get install -y libssl-dev openssl libpopt-dev libnl-dev libnl-3-dev libnl-genl-3.dev daemon libc-dev libnfnetlink-dev gcc
# Download Keepalived
cd /usr/local
sudo wget https://www.keepalived.org/software/keepalived-2.2.2.tar.gz
sudo tar -zxvf keepalived-2.2.2.tar.gz
mv keepalived-2.2.2 keepalived
cd keepalived
./configure --prefix=/usr/local/keepalived --disable-dependency-tracking
sudo make && sudo make install

Create missing init‑script links for Ubuntu, copy configuration files, and adjust the main configuration.

mkdir -p /etc/rc.d/init.d
ln -s /lib/lsb/init-functions /etc/rc.d/init.d/functions
sudo mkdir /etc/sysconfig
sudo cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
sudo cp /usr/local/keepalived/keepalived/etc/init.d/keepalived /etc/init.d/
sudo cp /usr/local/keepalived/sbin/keepalived /sbin/
sudo mkdir /etc/keepalived
sudo cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

6.3 VIP Configuration

Assign a virtual IP (e.g., 192.168.56.88) on the network interface and add routing rules.

ip addr del 192.168.56.88 dev enp0s8:1
ifconfig enp0s8:1 192.168.56.88 broadcast 192.168.56.255 netmask 255.255.255.0 up
route add -host 192.168.56.88 dev enp0s8:1

6.4 Keepalived Configuration File

global_defs {
    router_id MYSQL_HA
}
vrrp_script restart_mysql {
    script "/usr/local/keepalived/restart_mysql.sh"
    interval 2
    weight 2
}
vrrp_instance VI_1 {
    state BACKUP
    interface enp0s8
    virtual_router_id 51
    priority 101
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    track_script { restart_mysql }
    virtual_ipaddress { 192.168.56.88 }
}
virtual_server 192.168.56.88 3306 {
    delay_loop 2
    lb_algo wrr
    lb_kind DR
    persistence_timeout 60
    protocol TCP
    real_server 192.168.56.11 3306 { weight 3 TCP_CHECK { connect_timeout 10 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } }
    real_server 192.168.56.12 3306 { weight 3 TCP_CHECK { connect_timeout 10 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } }
}

6.5 Restart Script

#!/bin/bash
START_MYSQL="docker restart mysql"
STOP_MYSQL="docker stop mysql"
LOG_FILE="/usr/local/keepalived/logs/mysql-check.log"
HAPS=$(ps -C mysqld --no-header | wc -l)
date "+%Y-%m-%d %H:%M:%S" >> $LOG_FILE
echo "check mysql status" >> $LOG_FILE
if [ $HAPS -eq 0 ]; then
    echo $START_MYSQL >> $LOG_FILE
    $START_MYSQL >> $LOG_FILE 2>&1
    sleep 3
    if [ $(ps -C mysqld --no-header | wc -l) -eq 0 ]; then
        echo "start mysql failed, killall keepalived" >> $LOG_FILE
        killall keepalived
    fi
fi

Make the script executable, create a logs directory, and reload systemd.

sudo chmod +x /usr/local/keepalived/restart_mysql.sh
sudo mkdir /usr/local/keepalived/logs
sudo chmod 755 /usr/local/keepalived/logs
sudo systemctl daemon-reload

7. Testing and Validation

Start Keepalived on both nodes, verify the process is running, and check syslog for health‑check messages. Stop the MySQL container on one node; Keepalived should restart it within a few seconds. Then stop Keepalived on the primary node and confirm that client connections automatically switch to the standby node via the VIP.

8. Common Pitfalls and Fixes

Incorrect MySQL passwords – use skip-grant-tables to reset.

Missing volume mounts – ensure /home/hss/mysql/data and /home/hss/mysql/etc are correctly bound.

Dependency installation failures – update apt sources or downgrade conflicting packages.

Keepalived service masked – unmask with systemctl unmask keepalived and ensure the functions script link exists.

Script permission or security errors – set executable flag and add script_user root in global_defs.

By following these steps, you can achieve a resilient MySQL HA solution that automatically recovers from failures and provides uninterrupted service to applications.