Implementing High-Availability MySQL Master-Master Replication with Keepalived

Introduction

In traditional MySQL master-slave replication architectures, the slave database primarily serves as a backup of the master data. When the master database fails, the entire system becomes unavailable until manual failover procedure are completed. To address this limitation, we can implement a MySQL dual-master configuration where one database node active serves requests while the other remains as a hot standby. By integrating Keepalived with a virtual IP address, we ensure continuous service availability. In the event of a master failure, the standby node can rapidly assume service responsibilities with minimal downtime.

Server Configuration

Hostname	IP Address	Role	Version
db-master-1	192.168.1.101	Primary Master	8.0.26
db-master-2	192.168.1.102	Secondary Master	8.0.26
Virtual IP	192.168.1.100

MySQL Installation

Download and Extract

Download the MySQL community server package from the official repository and extract it to the appropriate location:


wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
tar -xf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.26-linux-glibc2.12-x86_64 /opt/mysql-server

System Configuration

Disable firewall and SELinux to avoid potential conflicts:


systemctl stop firewalld
systemctl disable firewalld
setenforce 0
sed -i 's/^SELINUX=enforcing$/SELINUX=disabled/' /etc/selinux/config

Directory Structure

Create necessary directories for MySQL operation and backup:


useradd mysql -s /sbin/nologin -M
mkdir -p /data/mysql/101/data
mkdir -p /data/mysql/101/logs/binlog
mkdir -p /data/mysql/101/logs/relaylog
mkdir -p /backup/mysql/101/xtrabackup
mkdir -p /backup/mysql/101/mysqldump
mkdir -p /scripts/mysql
chown -R mysql:mysql /data

Environment Variables

Set up PATH to include MySQL binaries:


echo 'export PATH=$PATH:/opt/mysql-server/bin' >> ~/.bash_profile
source ~/.bash_profile

Primary Node Configuration

Environment Setup


HOSTNAME=$(hostname)
MYSQL_PORT=3306
NODE_IP=192.168.1.101
NODE_ID=1

Configuration File

Create the MySQL configuration file for the primary node:


cat > /etc/my.cnf <<-EOF
[client]
port=${MYSQL_PORT}
socket=/data/mysql/${MYSQL_PORT}/mysql.sock
default-character-set=utf8mb4

[mysql]
prompt="\\u@\\h \\d \\r:\\m:\\s>"
auto-rehash
default-character-set=utf8mb4

[mysqld]
bind-address=0.0.0.0
port=${MYSQL_PORT}
user=mysql
basedir=/opt/mysql-server
datadir=/data/mysql/${MYSQL_PORT}/data
socket=/data/mysql/${MYSQL_PORT}/mysql.sock
pid-file=/data/mysql/${MYSQL_PORT}/mysql.pid
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
lower_case_table_names=1
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=8
long_query_time=10
slow_query_log=ON
slow_query_log_file=/data/mysql/${MYSQL_PORT}/${HOSTNAME}-query.log
log-queries-not-using-indexes=1
log-slow-admin-statements=1
log-error=/data/mysql/${MYSQL_PORT}/${HOSTNAME}-error.log
server-id=${NODE_ID}
relay_log=/data/mysql/${MYSQL_PORT}/relaylog/${HOSTNAME}-relaylog
relay-log-index=/data/mysql/${MYSQL_PORT}/relaylog/${HOSTNAME}-relay.index
log_slave_updates=1
read_only=0
relay_log_purge=1
log-bin=/data/mysql/${MYSQL_PORT}/binlog/${HOSTNAME}-binlog
log-bin-index=/data/mysql/${MYSQL_PORT}/binlog/${HOSTNAME}-binlog.index
binlog_format=row
binlog_rows_query_log_events=ON
binlog_cache_size=1M
max_binlog_size=2048M
expire_logs_days=7
sync_binlog=1
innodb_flush_log_at_trx_commit=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_gtid_simple_recovery=ON
auto_increment_increment=2
auto_increment_offset=1
EOF

Initialize Data Directory


mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql-server --datadir=/data/mysql/101/data

Start MySQL and Configure


cp /opt/mysql-server/support-files/mysql.server /etc/init.d/mysql101
systemctl enable mysql101
systemctl start mysql101

mysqladmin password 'secure_password'
mysql -uroot -p'secure_password' -e "CREATE USER 'admin'@'192.168.1.%' IDENTIFIED BY 'secure_password'; GRANT ALL PRIVILEGES ON *.* TO 'admin'@'192.168.1.%' WITH GRANT OPTION; FLUSH PRIVILEGES;"

Secondary Node Configuration

Environment Setup


HOSTNAME=$(hostname)
MYSQL_PORT=3306
NODE_IP=192.168.1.102
NODE_ID=2

Configuration File

Create the MySQL configuration file for the secondary node:


cat > /etc/my.cnf <<-EOF
[client]
port=${MYSQL_PORT}
socket=/data/mysql/${MYSQL_PORT}/mysql.sock
default-character-set=utf8mb4

[mysql]
prompt="\\u@\\h \\d \\r:\\m:\\s>"
auto-rehash
default-character-set=utf8mb4

[mysqld]
bind-address=0.0.0.0
port=${MYSQL_PORT}
user=mysql
basedir=/opt/mysql-server
datadir=/data/mysql/${MYSQL_PORT}/data
socket=/data/mysql/${MYSQL_PORT}/mysql.sock
pid-file=/data/mysql/${MYSQL_PORT}/mysql.pid
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
lower_case_table_names=1
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=8
long_query_time=10
slow_query_log=ON
slow_query_log_file=/data/mysql/${MYSQL_PORT}/${HOSTNAME}-query.log
log-queries-not-using-indexes=1
log-slow-admin-statements=1
log-error=/data/mysql/${MYSQL_PORT}/${HOSTNAME}-error.log
server-id=${NODE_ID}
relay_log=/data/mysql/${MYSQL_PORT}/relaylog/${HOSTNAME}-relaylog
relay-log-index=/data/mysql/${MYSQL_PORT}/relaylog/${HOSTNAME}-relay.index
log_slave_updates=1
read_only=0
relay_log_purge=1
log-bin=/data/mysql/${MYSQL_PORT}/binlog/${HOSTNAME}-binlog
log-bin-index=/data/mysql/${MYSQL_PORT}/binlog/${HOSTNAME}-binlog.index
binlog_format=row
binlog_rows_query_log_events=ON
binlog_cache_size=1M
max_binlog_size=2048M
expire_logs_days=7
sync_binlog=1
innodb_flush_log_at_trx_commit=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_gtid_simple_recovery=ON
auto_increment_increment=2
auto_increment_offset=2
EOF

Initialize and Start


mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql-server --datadir=/data/mysql/101/data
cp /opt/mysql-server/support-files/mysql.server /etc/init.d/mysql102
systemctl enable mysql102
systemctl start mysql102

mysqladmin password 'secure_password'
mysql -uroot -p'secure_password' -e "CREATE USER 'admin'@'192.168.1.%' IDENTIFIED BY 'secure_password'; GRANT ALL PRIVILEGES ON *.* TO 'admin'@'192.168.1.%' WITH GRANT OPTION; FLUSH PRIVILEGES;"

Replication Setup

Create Replication Users

On both nodes, create a replication user:


mysql -uroot -p'secure_password' -e "CREATE USER 'replicator'@'192.168.1.%' IDENTIFIED WITH mysql_native_password BY 'repl_password'; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.1.%';"

Configure Replication

Clear existing binary logs on both nodes:


mysql -uroot -p'secure_password' -e "RESET MASTER;"

On the primary node (192.168.1.101), configure it to replicate from the secondary:


mysql -uroot -p'secure_password' -e "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='192.168.1.102', MASTER_USER='replicator', MASTER_PASSWORD='repl_password', MASTER_PORT=3306, MASTER_AUTO_POSITION=1; START SLAVE;"

On the secondary node (192.168.1.102), configure it to replicate from the primary:


mysql -uroot -p'secure_password' -e "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='192.168.1.101', MASTER_USER='replicator', MASTER_PASSWORD='repl_password', MASTER_PORT=3306, MASTER_AUTO_POSITION=1; START SLAVE;"

Verify Replication

Check replication status on both nodes:


mysql -uroot -p'secure_password' -e "SHOW SLAVE STATUS\G"

The output should show "Slave_IO_Running: Yes" and "Slave_SQL_Running: Yes" on both nodes.

Test Data Synchronization

On the primary node, create a test database:


mysql -uroot -p'secure_password' -e "CREATE DATABASE test_primary;"

On the secondary node, verify the database exists:


mysql -uroot -p'secure_password' -e "SHOW DATABASES;"

On the secondary node, create a different test database:


mysql -uroot -p'secure_password' -e "CREATE DATABASE test_secondary;"

On the primary node, verify the database exists:


mysql -uroot -p'secure_password' -e "SHOW DATABASES;"

Keepalived Configuration

Installation


yum install keepalived -y

Primary Node Configuration


mkdir -p /etc/keepalived/checks
cat > /etc/keepalived/keepalived.conf <<-EOF
global_defs {
    router_id mysql-primary
}

vrrp_script check_mysql {
    script "/etc/keepalived/checks/mysql_check.sh"
    interval 2
    timeout 3
    fall 2
    rise 3
}

vrrp_instance MySQL-HA {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    track_script {
        check_mysql
    }
    virtual_ipaddress {
        192.168.1.100/24 dev eth0 label eth0:1
    }
}
EOF

Secondary Node Configuration


mkdir -p /etc/keepalived/checks
cat > /etc/keepalived/keepalived.conf <<-EOF
global_defs {
    router_id mysql-secondary
}

vrrp_script check_mysql {
    script "/etc/keepalived/checks/mysql_check.sh"
    interval 2
    timeout 3
    fall 2
    rise 3
}

vrrp_instance MySQL-HA {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    track_script {
        check_mysql
    }
    virtual_ipaddress {
        192.168.1.100/24 dev eth0 label eth0:1
    }
}
EOF

Health Check Script

Create a health check script on both nodes:


cat > /etc/keepalived/checks/mysql_check.sh <<-EOF
#!/bin/bash

LOGFILE="/var/log/mysql_check.log"
MYSQL_PORT=3306
PING_TARGET="192.168.1.1"

# Check network connectivity
if ! ping -c 2 $PING_TARGET &> /dev/null; then
    echo "$(date) - Network check failed" >> $LOGFILE
    exit 1
fi

# Check MySQL port
if ! nc -z localhost $MYSQL_PORT &> /dev/null; then
    echo "$(date) - MySQL port $MYSQL_PORT is not accessible" >> $LOGFILE
    exit 1
fi

# Check MySQL replication status
REPL_STATUS=$(mysql -uadmin -p'secure_password' -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running" | awk '{print $2}')
if [[ "$REPL_STATUS" != "Yes Yes" ]]; then
    echo "$(date) - MySQL replication is not healthy" >> $LOGFILE
    exit 1
fi

echo "$(date) - All checks passed" >> $LOGFILE
exit 0
EOF

chmod +x /etc/keepalived/checks/mysql_check.sh

Start Keepalived


systemctl enable --now keepalived
systemctl status keepalived

High Availability Testing

Connect via Virtual IP

Connect to MySQL using the virtual IP:


mysql -uadmin -p'secure_password' -h 192.168.1.100 -e "SELECT @@hostname;"

Create Test Data

Insert test data to verify replication:


mysql -uadmin -p'secure_password' -h 192.168.1.100 -e "
CREATE DATABASE ha_test;
CREATE TABLE ha_test.users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (id)
);
INSERT INTO ha_test.users (name, email) VALUES
('John Doe', 'john@example.com'),
('Jane Smith', 'jane@example.com'),
('Bob Johnson', 'bob@example.com');
"

Simulate Primary Node Failure

Stop the MySQL service on the primary node:


systemctl stop mysql101

Verify failover by connnecting via virtual IP:


mysql -uadmin -p'secure_password' -h 192.168.1.100 -e "SELECT @@hostname;"

Insert additional test data on the new primary:


mysql -uadmin -p'secure_password' -h 192.168.1.100 -e "
INSERT INTO ha_test.users (name, email) VALUES
('Alice Williams', 'alice@example.com'),
('Charlie Brown', 'charlie@example.com');
"

Recover Primary Node

Restart the MySQL service on the original primary node:


systemctl start mysql101

Verify data synchronization on the recovered node:


mysql -uadmin -p'secure_password' -h 192.168.1.101 -e "SELECT * FROM ha_test.users;"

Tags: MySQL Replication high-availability Keepalived Dual-Master

Posted on Mon, 27 Jul 2026 16:35:36 +0000 by alecodonnell

Freaks City

Implementing High-Availability MySQL Master-Master Replication with Keepalived

Introduction

Server Configuration

MySQL Installation

Download and Extract

System Configuration

Directory Structure

Environment Variables

Primary Node Configuration

Environment Setup

Configuration File

Initialize Data Directory

Start MySQL and Configure

Secondary Node Configuration

Environment Setup

Configuration File

Initialize and Start

Replication Setup

Create Replication Users

Configure Replication

Verify Replication

Test Data Synchronization

Keepalived Configuration

Installation

Primary Node Configuration

Secondary Node Configuration

Health Check Script

Start Keepalived

High Availability Testing

Connect via Virtual IP

Create Test Data

Simulate Primary Node Failure

Recover Primary Node

Hot Tags