This guide outlines the implementation of a highly available MySQL dual-master replication cluster protected by Keepalived for automatic VIP failover. The setup ensures continuous database service despite node failures.

Prerequisites

• Two Linux servers: 192.168.199.49 (Node A) and 192.168.199.50 (Node B)
• Shared virtual IP (VIP): 192.168.199.52
• Identical MySQL versions and identical root passwords on both nodes
• Network connectivity verified via ping between hosts
• Firewall rules permitting VRRP multicast traffic (224.0.0.18) and protocol vrrp)

MySQL Configuration

Modify /etc/my.cnf under the [mysqld] section on each node:

Node A (192.168.199.49)

log-bin = mysql-bin
binlog_format = MIXED
server-id = 2
expire_logs_days = 10
binlog-do-db = db1
sync_binlog = 1
auto-increment-increment = 2
auto-increment-offset = 2
slave-skip-errors = all

Node B (192.168.199.50)

log-bin = mysql-bin
binlog_format = MIXED
server-id = 1
expire_logs_days = 10
binlog-do-db = db1
sync_binlog = 1
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all

Restart MySQL on both nodes:

sudo systemctl restart mysqld

Replication Setup

On each node, create a dedicated replication user for the peer:

On Node A:

mysql -u root -p -e "
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'192.168.199.50' IDENTIFIED BY 'secure-repl-pass';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
"

On Node B:

mysql -u root -p -e "
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'192.168.199.49' IDENTIFIED BY 'secure-repl-pass';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
"

After locking tables, record the current binary log position on each node:

mysql -u root -p -e "SHOW MASTER STATUS;"

Perform initial data synchronization using mysqldump or logical export/import tools while tables remain locked. Then unlock:

mysql -u root -p -e "UNLOCK TABLES;"

Configuring Dual-Master Replicasion

On Node A, configure it to replicate from Node B:

mysql -u root -p -e "
STOP SLAVE;
CHANGE MASTER TO 
  MASTER_HOST='192.168.199.50',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='secure-repl-pass',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
START SLAVE;
"

On Node B, configure it to replicate from Node A:

mysql -u root -p -e "
STOP SLAVE;
CHANGE MASTER TO 
  MASTER_HOST='192.168.199.49',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='secure-repl-pass',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
START SLAVE;
"

Verify replication health on both nodes:

mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -E "(Slave_IO_Running|Slave_SQL_Running)"

Both values must report Yes.

Keepalived Installation and Configuration

Install Keepalived from source on both nodes:

sudo yum install -y openssl-devel
cd /tmp && wget https://www.keepalived.org/software/keepalived-2.2.8.tar.gz
tar xzf keepalived-2.2.8.tar.gz && cd keepalived-2.2.8
./configure --prefix=/usr/local/keepalived --sysconfdir=/etc
make && sudo make install

sudo cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
sudo mkdir -p /etc/keepalived
sudo cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
sudo cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

Health Check Script

Create /opt/check_mysql.sh on both nodes:

#!/bin/bash
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USER="root"
MYSQL_PASS="ocnt-123"
CHECK_INTERVAL=3
ATTEMPTS=3

health_check() {
  mysql --host="$MYSQL_HOST" --port="$MYSQL_PORT" \
        --user="$MYSQL_USER" --password="$MYSQL_PASS" \
        -e "SELECT 1;" > /dev/null 2>&1
}

for i in $(seq 1 $ATTEMPTS); do
  if health_check; then
    exit 0
  fi
  sleep 1
done

# Kill Keepalived if MySQL is unreachable
pkill keepalived
exit 1

Set permissions:

sudo chmod +x /opt/check_mysql.sh

Keepalived Configuration — Node A (Primary)

Edit /etc/keepalived/keepalived.conf:

! Configuration for MySQL HA Cluster
global_defs {
  router_id MYSQL_PRIMARY
}

vrrp_script chk_mysql {
  script "/opt/check_mysql.sh"
  interval 2
  weight -10
  fall 2
  rise 1
}

vrrp_instance VI_MYSQL {
  state MASTER
  interface eth0
  virtual_router_id 42
  priority 110
  advert_int 1
  authentication {
    auth_type PASS
    auth_pass mysql-ha-key
  }
  virtual_ipaddress {
    192.168.199.52/24 dev eth0
  }
  track_script {
    chk_mysql
  }
}

Keepalived Configuration — Node B (Secondary)

! Configuration for MySQL HA Cluster
global_defs {
  router_id MYSQL_SECONDARY
}

vrrp_script chk_mysql {
  script "/opt/check_mysql.sh"
  interval 2
  weight -10
  fall 2
  rise 1
}

vrrp_instance VI_MYSQL {
  state BACKUP
  interface eth0
  virtual_router_id 42
  priority 95
  advert_int 1
  authentication {
    auth_type PASS
    auth_pass mysql-ha-key
  }
  virtual_ipaddress {
    192.168.199.52/24 dev eth0
  }
  track_script {
    chk_mysql
  }
}

Service Activation

Start Keepalived on both nodes:

sudo systemctl enable keepalived
sudo systemctl start keepalived

Confirm VIP assignment:

ip addr show eth0 | grep "192.168.199.52"

Validation and Failover Testing

Connect clients to the VIP:

mysql -h 192.168.199.52 -u root -pocnt-123 -e "SHOW DATABASES;"

To test failover:

1. Stop MySQL on Node A: sudo systemctl stop mysqld
2. Observe VIP migration via ip addr and journalctl -u keepalived -f
3. Verify client connections continue uninterrrupted
4. Restart MySQL and Keepalived on Node A — VIP should automatically reassign if priority permits

Maintenance Commands

To disable replication temporarily:

mysql -e "STOP SLAVE;"

To reset replication configuration:

mysql -e "RESET SLAVE ALL; RESET MASTER;"