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

Overview

This guide shows how to build a high‑availability MySQL cluster on two Ubuntu hosts using Docker containers, master‑master (bidirectional) replication, and Keepalived for virtual IP (VIP) failover.

Architecture

Two Dockerized MySQL instances run on separate hosts (node1 192.168.56.11, node2 192.168.56.12). Keepalived advertises a single VIP (192.168.56.88). Clients connect to the VIP; only the active MySQL node receives traffic. If the active MySQL stops, Keepalived either restarts it or disables itself, causing the VIP to move to the standby node.

Prerequisites

Ubuntu 20.04 (or compatible) on both nodes.

Docker installed on both nodes.

Root or sudo access.

Network connectivity between the two hosts.

Step 1 – Prepare Docker Images

Export the MySQL image on the source machine:

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

Copy mysql.tar to both hosts and load it: sudo docker load -i mysql.tar Run the container with persistent volumes and a root password:

sudo docker run -d \
  -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' \
  46b

(Replace 46b with the actual image ID.)

Step 2 – Configure MySQL Master‑Master Replication

Edit /home/hss/mysql/etc/mysql/my.cnf on each node. Essential parameters:

server_id = 11   # node1, use 12 on node2
log_bin = /var/lib/mysql/log/mysql-bin
binlog_format = mixed
sync_binlog = 100
log_slave_updates = 1
relay_log = /var/lib/mysql/log/relay-bin
relay_log_index = /var/lib/mysql/log/relay-bin.index
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery

Create the log directories and give them full permissions (777), then restart the containers.

Create a replication user on each node (adjust IPs accordingly):

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 on the primary, dump all databases, then unlock:

FLUSH TABLES WITH READ LOCK;

mysqldump -uroot -P3306 --all-databases --triggers --routines --events > /var/lib/mysql/backup/all_databases.sql

UNLOCK TABLES;

Import the dump on the standby node:

mysql -uroot -p -hlocalhost -P3306 < /var/lib/mysql/backup/all_databases.sql

Configure the standby as a slave of the primary (replace MASTER_LOG_FILE and MASTER_LOG_POS with the values shown by SHOW MASTER STATUS on the primary):

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 the replication threads and verify:

START SLAVE;

SHOW SLAVE STATUS\G

Both Slave_IO_Running and Slave_SQL_Running should be Yes.

Step 3 – Install and Configure Keepalived

Install build 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 and extract Keepalived 2.2.2:

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

Compile and install:

cd keepalived
./configure --prefix=/usr/local/keepalived --disable-dependency-tracking
sudo make && sudo make install

Create the init‑script link required on Ubuntu:

mkdir -p /etc/rc.d/init.d
ln -s /lib/lsb/init-functions /etc/rc.d/init.d/functions

Copy the default configuration and edit /etc/keepalived/keepalived.conf (example below). The configuration defines a VRRP instance, a health‑check script, and a virtual server for MySQL port 3306.

global_defs {
  router_id MYSQL_HA
  script_user root
}

vrrp_script restart_mysql {
  script "/usr/local/keepalived/restart_mysql.sh"
  interval 2
  weight 2
}

vrrp_instance VI_1 {
  state BACKUP
  interface enp0s8               # adjust to your NIC
  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 }
  }
}

Write the health‑check script /usr/local/keepalived/restart_mysql.sh:

#!/bin/bash
START_MYSQL="docker restart 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 it executable:

sudo chmod +x /usr/local/keepalived/restart_mysql.sh

Create a log directory for the script:

sudo mkdir -p /usr/local/keepalived/logs
sudo chmod 755 /usr/local/keepalived/logs

Reload systemd and start Keepalived on both nodes:

sudo systemctl daemon-reload
sudo systemctl start keepalived
sudo systemctl status keepalived

Step 4 – Verify HA Behavior

Confirm that the VIP (192.168.56.88) resolves to the active MySQL instance (use SHOW VARIABLES LIKE '%hostname%'; via the VIP).

Stop the MySQL container on the active node: docker stop <em>container_id</em> Keepalived will attempt to restart it. If the restart fails, Keepalived disables itself and the VIP moves to the standby node.

Check data replication by creating a database/table on the primary and verifying its presence on the secondary.

Run SHOW SLAVE STATUS\G on each node to ensure replication threads are healthy.

Common Pitfalls and Solutions

Password errors – Ensure the replication user exists on both sides or temporarily start MySQL with skip-grant-tables to reset credentials.

Missing volume mappings – Verify that the -v host directories exist and are writable (chmod 777 for quick testing, tighter permissions for production).

Dependency installation failures – Use the official Ubuntu repositories or downgrade conflicting libnl packages as described in the steps.

Keepalived service masked – Unmask and enable the service:

sudo systemctl unmask keepalived
sudo systemctl enable keepalived

Health‑check script not executable – Set executable bits and enable script security in keepalived.conf (add script_user root under global_defs).

Summary

By containerizing MySQL, configuring bidirectional replication, and placing Keepalived in front of the two nodes, you obtain a resilient MySQL service that automatically fails over when the active instance crashes. The VIP abstracts the underlying hosts, allowing clients to continue operating without changes. The provided scripts and configuration files can be adapted to other Linux distributions or MySQL versions as needed.