How to Build MySQL Master‑Master HA with Keepalived and Docker

Introduction

MySQL is a critical storage layer for many business systems; a database outage can severely impact read/write operations. High availability is essential, as illustrated by a recent B‑Station incident that took two hours to recover.

Solution Overview

The chosen HA solution combines MySQL master‑master replication with Keepalived for health checking, automatic restart, and virtual IP (VIP) traffic switching.

Master‑Master Mode

Two MySQL instances run on separate servers, synchronizing data bidirectionally. Only one instance serves client traffic at a time; if it fails, the other takes over. Without Keepalived, failover must be performed manually.

Keepalived Functions

Detects MySQL service health on both servers and attempts automatic restart.

Provides a VIP that clients use; traffic is directed to the active MySQL instance.

If restart fails, Keepalived stops itself, causing traffic to shift to the other server.

Replication Principle

MySQL master‑master works by each server acting as both master and slave, using binlog, I/O thread, dump thread, SQL thread, and relay logs to keep data consistent.

Environment Setup

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

<code>sudo docker save -o mysql.tar hcr:5000/hschub/hscmysql:0.0.2
sudo chmod 777 mysql.tar
sudo docker load -i mysql.tar</code>

Containers are started with volume mappings for data and configuration:

<code>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</code>

Master‑Slave Configuration

Topology:

192.168.56.11 node1 – master

192.168.56.12 node2 – slave

Key steps include editing /home/hss/mysql/etc/mysql/my.cnf on both nodes (setting server_id , binlog options, relay log paths, etc.), creating replication user, locking tables, recording binlog file and position, backing up the database, restoring on the slave, and configuring CHANGE MASTER TO with the recorded file/position.

<code>CREATE USER 'vagrant'@'192.168.56.12' IDENTIFIED BY 'vagrant';
GRANT REPLICATION SLAVE ON *.* TO 'vagrant'@'192.168.56.12';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysqldump -uroot -P3306 --all-databases --triggers --routines --events > /var/lib/mysql/backup/all_databases.sql;
UNLOCK TABLES;
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;</code>

Master‑Master Conversion

After the master‑slave setup, the configuration is swapped so each node becomes both master and slave. Replication users are created on both nodes, CHANGE MASTER TO is run with the opposite node’s IP, and START SLAVE is executed.

Keepalived Installation

Dependencies are installed, the Keepalived source is downloaded, compiled, and installed.

<code># 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 and extract
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
# Build
cd keepalived
./configure --prefix=/usr/local/keepalived --disable-dependency-tracking
sudo make && sudo make install</code>

The configuration file /etc/keepalived/keepalived.conf defines a VRRP instance with a virtual IP (192.168.56.88), health‑check script restart_mysql.sh , and LVS virtual server for MySQL port 3306.

<code>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_port 3306 } }
}</code>

The health‑check script restarts the MySQL container if it is not running and logs actions:

<code>#!/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</code>

Permissions are set, log directory created, and Keepalived is started on both nodes:

<code>sudo systemctl start keepalived
sudo systemctl status keepalived
ps -ef | grep keepalived</code>

Tests show that when the MySQL container on a node stops, Keepalived restarts it within seconds, and if restart fails, the VIP automatically switches to the other node, ensuring continuous service.

Pitfalls and Solutions

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

Missing volume mapping – ensure -v options are correct.

Permission issues on data directories – apply chmod 777 .

Dependency installation failures – update package lists or downgrade conflicting packages.

Keepalived service masked – unmask with systemctl unmask keepalived .

Missing script user – add script_user root in global_defs .

Script not executable – set executable permission and enable script security.

By following these steps, a robust MySQL master‑master high‑availability cluster can be built and maintained.