MySQL Production Replica Set: HA with Group Replication

1. Disable THP & Update Limits

# Permanently disable THP on AL2023
echo 'transparent_hugepage=never' | sudo tee /etc/default/grub.d/thp.cfg
sudo grub2-mkconfig -o /boot/grub2/grub.cfg

# Set soft/hard limits for MySQL user
cat <<EOF | sudo tee /etc/security/limits.d/mysql.conf
mysql soft nofile 1048576
mysql hard nofile 1048576
mysql soft nproc 65535
mysql hard nproc 65535
EOF

2. Kernel Optimization (sysctl)

cat <<EOF | sudo tee /etc/sysctl.d/99-mysql.conf
# Database specific tuning
vm.swappiness = 1
vm.dirty_ratio = 15
vm.dirty_background_ratio = 5
fs.file-max = 2097152

# Network tuning for replication
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 8192
net.ipv4.ip_local_port_range = 1024 65535
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 30
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
EOF

sudo sysctl --system

1. The Keepalived Config (/etc/keepalived/keepalived.conf)

vrrp_script check_mysql {
    script "/usr/local/bin/check_mysql_primary.sh"
    interval 2
    weight -20
    fall 2
    rise 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass MySecureVIPPass
    }
    virtual_ipaddress {
        10.0.1.100/24
    }
    track_script {
        check_mysql
    }
}

2. The Primary Check Script (/usr/local/bin/check_mysql_primary.sh)

This script returns 0 only if the node is the Primary and is ONLINE.

#!/bin/bash
# Check if this node is the PRIMARY in the Group
IS_PRIMARY=$(mysql -N -s -e "SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_ROLE='PRIMARY' AND MEMBER_STATE='ONLINE' AND MEMBER_HOST='$(hostname -I | awk '{print $1}')'")

if [ "$IS_PRIMARY" -eq 1 ]; then
    exit 0 # Success: Weight stays same
else
    exit 1 # Failure: Weight drops, VIP moves
fi