MySQL GTID Replication + MHA Automatic Failover

Configure MySQL 8.0 master-slave replication using GTID for reliable data synchronization, then implement MySQL High Availability (MHA) for automatic failover detection and promotion.

Prerequisites

Multiple servers (minimum 3)
Root access to all servers
Basic MySQL administration knowledge
Network connectivity between servers

What this solves

MySQL replication with GTID (Global Transaction Identifier) provides reliable database high availability by maintaining synchronized copies of your data across multiple servers. When combined with MHA (Master High Availability), your database automatically fails over to a replica when the primary server becomes unavailable, minimizing downtime for production applications.

Step-by-step installation

Update system packages

Start by updating your package manager to ensure you get the latest MySQL 8.0 packages.

sudo apt update && sudo apt upgrade -y

sudo dnf update -y

Install MySQL 8.0 server

Install MySQL server package on all nodes that will participate in replication (master and all replicas).

sudo apt install -y mysql-server mysql-client
sudo systemctl enable --now mysql

sudo dnf install -y mysql-server mysql
sudo systemctl enable --now mysqld

Secure MySQL installation

Run the security script to set root password and remove test databases. Use a strong password for production environments.

sudo mysql_secure_installation

Select these options when prompted: Remove anonymous users (Y), Disallow root login remotely (Y), Remove test database (Y), Reload privilege tables (Y).

Configure MySQL master server

Configure the master server with GTID enabled, unique server ID, and binary logging. Replace 203.0.113.10 with your master server IP.

[mysqld]
Server identification
server-id = 1

Binary logging
log-bin = /var/log/mysql/mysql-bin.log
binlog-format = ROW
max_binlog_size = 100M
expire_logs_days = 7

GTID configuration
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON

Replication settings
bind-address = 203.0.113.10
replica-skip-errors = 1062

Performance tuning
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Configure MySQL replica servers

Configure each replica server with unique server IDs and GTID enabled. Use server-id = 2, 3, 4, etc. for additional replicas.

[mysqld]
Server identification (increment for each replica)
server-id = 2

Binary logging
log-bin = /var/log/mysql/mysql-bin.log
binlog-format = ROW
max_binlog_size = 100M
expire_logs_days = 7

GTID configuration
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON

Replication settings
bind-address = 203.0.113.11
replica-skip-errors = 1062
read-only = 1

Performance tuning
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Restart MySQL services

Restart MySQL on all servers to apply the configuration changes.

sudo systemctl restart mysql
sudo systemctl status mysql

Create replication user on master

Create a dedicated replication user with proper privileges on the master server.

sudo mysql -u root -p

CREATE USER 'replication'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongReplicationPass123!';
GRANT REPLICATION SLAVE ON . TO 'replication'@'%';
FLUSH PRIVILEGES;
EXIT;

Configure replication on replica servers

Set up each replica to connect to the master using GTID-based replication. Run this on each replica server.

sudo mysql -u root -p

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='203.0.113.10',
  SOURCE_USER='replication',
  SOURCE_PASSWORD='StrongReplicationPass123!',
  SOURCE_AUTO_POSITION=1;

START REPLICA;
SHOW REPLICA STATUS\G
EXIT;

Install MHA dependencies

Install required packages for MHA (Master High Availability) on all MySQL servers and the MHA manager node.

sudo apt install -y libdbd-mysql-perl libconfig-tiny-perl liblog-dispatch-perl libparallel-forkmanager-perl libdbi-perl cpanminus
sudo cpanm MHA::NodeUtil

sudo dnf install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-DBI cpanminus
sudo cpanm MHA::NodeUtil

Download and install MHA

Download the latest MHA packages from GitHub and install them on all servers.

cd /tmp
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz

tar -xzf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58
perl Makefile.PL
make
sudo make install

cd /tmp
tar -xzf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58
perl Makefile.PL
make
sudo make install

Configure SSH key authentication

Set up passwordless SSH access between all MHA nodes using the mysql user account.

sudo useradd -r -s /bin/bash -d /var/lib/mysql mysql 2>/dev/null || true
sudo -u mysql mkdir -p /var/lib/mysql/.ssh
sudo -u mysql ssh-keygen -t rsa -N "" -f /var/lib/mysql/.ssh/id_rsa

Copy the public key to all other MySQL servers in the replication setup.

sudo -u mysql ssh-copy-id mysql@203.0.113.10
sudo -u mysql ssh-copy-id mysql@203.0.113.11
sudo -u mysql ssh-copy-id mysql@203.0.113.12

Create MHA configuration

Create the MHA manager configuration file with your MySQL topology. Adjust IP addresses and paths for your environment.

sudo mkdir -p /etc/mha
sudo mkdir -p /var/log/mha

[server default]
manager_log=/var/log/mha/mysql-cluster.log
manager_workdir=/var/log/mha
master_binlog_dir=/var/log/mysql
user=mha
password=MHAManagerPass123!
ping_interval=3
repl_user=replication
repl_password=StrongReplicationPass123!
ssh_user=mysql

[server1]
hostname=203.0.113.10
port=3306
candidate_master=1

[server2]
hostname=203.0.113.11
port=3306
candidate_master=1

[server3]
hostname=203.0.113.12
port=3306
no_master=1

Create MHA management user

Create a dedicated user for MHA manager operations on all MySQL servers.

sudo mysql -u root -p

CREATE USER 'mha'@'%' IDENTIFIED WITH mysql_native_password BY 'MHAManagerPass123!';
GRANT ALL PRIVILEGES ON . TO 'mha'@'%';
FLUSH PRIVILEGES;
EXIT;

Test MHA configuration

Verify that MHA can connect to all nodes and the replication setup is working correctly.

sudo masterha_check_ssh --conf=/etc/mha/mysql-cluster.cnf
sudo masterha_check_repl --conf=/etc/mha/mysql-cluster.cnf

Create MHA systemd service

Create a systemd service to manage the MHA manager process automatically.

[Unit]
Description=MySQL High Availability Manager
After=network.target mysql.service
Requires=mysql.service

[Service]
Type=forking
User=root
Group=root
ExecStart=/usr/local/bin/masterha_manager --conf=/etc/mha/mysql-cluster.cnf --remove_dead_master_conf --ignore_last_failover
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure
RestartSec=30

[Install]
WantedBy=multi-user.target

Start MHA manager service

Enable and start the MHA manager service to begin monitoring your MySQL replication cluster.

sudo systemctl daemon-reload
sudo systemctl enable --now mha-manager
sudo systemctl status mha-manager

Configure monitoring and health checks

Create replication monitoring script

Set up automated monitoring to track replication lag and health status.

#!/bin/bash

MySQL replication health check script
MAX_LAG=30  # Maximum acceptable lag in seconds

Check replication status
REPL_STATUS=$(mysql -u mha -p'MHAManagerPass123!' -e "SHOW REPLICA STATUS\G" 2>/dev/null)

if [[ -z "$REPL_STATUS" ]]; then
    echo "ERROR: Cannot connect to MySQL or not a replica"
    exit 1
fi

Extract key metrics
IO_RUNNING=$(echo "$REPL_STATUS" | grep "Replica_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$REPL_STATUS" | grep "Replica_SQL_Running:" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$REPL_STATUS" | grep "Seconds_Behind_Source:" | awk '{print $2}')

echo "Replica IO Running: $IO_RUNNING"
echo "Replica SQL Running: $SQL_RUNNING"
echo "Seconds Behind Master: $SECONDS_BEHIND"

if [[ "$IO_RUNNING" != "Yes" ]] || [[ "$SQL_RUNNING" != "Yes" ]]; then
    echo "CRITICAL: Replication threads not running"
    exit 2
fi

if [[ "$SECONDS_BEHIND" != "NULL" ]] && [[ $SECONDS_BEHIND -gt $MAX_LAG ]]; then
    echo "WARNING: Replication lag is ${SECONDS_BEHIND} seconds"
    exit 1
fi

echo "OK: Replication is healthy"
exit 0

sudo chmod +x /usr/local/bin/check-mysql-replication.sh

Set up automated health monitoring

Create a cron job to regularly check replication health and alert on issues.

sudo crontab -e

# Check MySQL replication health every 5 minutes
/5    * /usr/local/bin/check-mysql-replication.sh >> /var/log/mysql-replication-check.log 2>&1

Verify your setup

Test your MySQL replication and MHA setup with these verification commands.

# Check master status
sudo mysql -u root -p -e "SHOW MASTER STATUS\G"

Check replica status on each replica server
sudo mysql -u root -p -e "SHOW REPLICA STATUS\G"

Verify GTID is working
sudo mysql -u root -p -e "SELECT @@gtid_executed;"

Check MHA manager status
sudo systemctl status mha-manager
sudo masterha_check_status --conf=/etc/mha/mysql-cluster.cnf

Test replication by creating a test database
sudo mysql -u root -p -e "CREATE DATABASE test_replication; USE test_replication; CREATE TABLE test (id INT PRIMARY KEY, data VARCHAR(100)); INSERT INTO test VALUES (1, 'replication test');"

Verify data appears on replicas
sudo mysql -u root -p -e "SELECT * FROM test_replication.test;"

Note: You can monitor replication health by checking /var/log/mha/mysql-cluster.log for MHA manager logs and /var/log/mysql-replication-check.log for automated health check results.

Test automatic failover

Perform a controlled failover test to ensure MHA responds correctly to master failures.

# Simulate master failure (run on master server)
sudo systemctl stop mysql

Monitor MHA logs during failover
sudo tail -f /var/log/mha/mysql-cluster.log

Check which server became the new master
sudo masterha_check_status --conf=/etc/mha/mysql-cluster.cnf

Verify applications can connect to new master
sudo mysql -h 203.0.113.11 -u root -p -e "SELECT @@server_id, @@read_only;"

Warning: Only perform failover tests in non-production environments or during scheduled maintenance windows. Always have a recovery plan before testing automatic failover.

Performance optimization

Optimize replication performance

Configure additional settings to improve replication performance for high-traffic environments.

[mysqld]
Multi-threaded replication
replica-parallel-type = LOGICAL_CLOCK
replica-parallel-workers = 8
replica-preserve-commit-order = 1

Replication performance
replica-net-timeout = 60
replica-compressed-protocol = 1

Binary log optimization
binlog_cache_size = 1M
binlog_stmt_cache_size = 1M

GTID optimization
binlog_gtid_simple_recovery = 1

Connection optimization
max_connections = 500
max_connect_errors = 1000000

Configure semi-synchronous replication

Enable semi-synchronous replication for better data consistency guarantees.

# On master server
sudo mysql -u root -p -e "INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so'; SET GLOBAL rpl_semi_sync_source_enabled = 1;"

On replica servers
sudo mysql -u root -p -e "INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so'; SET GLOBAL rpl_semi_sync_replica_enabled = 1; STOP REPLICA IO_THREAD; START REPLICA IO_THREAD;"

You can now integrate your MySQL replication cluster with Prometheus monitoring or set up automated backup verification for complete database reliability.

Common issues

Symptom	Cause	Fix
Replica SQL thread stops	Duplicate key or constraint violation	`SET GLOBAL sql_slave_skip_counter = 1; START REPLICA;` or investigate with `SHOW REPLICA STATUS\G`
MHA manager won't start	SSH connectivity or MySQL permissions	Run `masterha_check_ssh` and `masterha_check_repl` to identify connection issues
High replication lag	Network latency or heavy write load	Enable multi-threaded replication and optimize network between servers
GTID consistency errors	Mixed GTID and non-GTID transactions	Ensure all servers have `gtid-mode=ON` and `enforce-gtid-consistency=ON`
Failover gets stuck	Insufficient privileges for MHA user	Grant `ALL PRIVILEGES` to MHA user and verify `SUPER` privilege is included

Next steps

Running this in production?

Want this handled for you? Running MySQL replication at scale adds a second layer of work: capacity planning, failover drills, performance monitoring, and 24/7 incident response when things break. See how we run infrastructure like this for European teams.

Automated install script

Run this to automate the entire setup

install.sh

#!/usr/bin/env bash
set -euo pipefail

# MySQL GTID Replication Setup Script
# Sets up MySQL master with GTID replication ready

# Color codes
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

# Configuration
REPL_USER="replication"
REPL_PASS="StrongReplicationPass123!"
SERVER_ID=${1:-1}
BIND_IP=${2:-$(hostname -I | awk '{print $1}')}

usage() {
    echo "Usage: $0 [server_id] [bind_ip]"
    echo "  server_id: Unique server ID (default: 1 for master)"
    echo "  bind_ip: IP address to bind MySQL (default: auto-detect)"
    exit 1
}

log() {
    echo -e "${GREEN}[INFO]${NC} $1"
}

warn() {
    echo -e "${YELLOW}[WARN]${NC} $1"
}

error() {
    echo -e "${RED}[ERROR]${NC} $1" >&2
    exit 1
}

cleanup() {
    warn "Installation failed. Check logs for details."
}

trap cleanup ERR

# Check prerequisites
if [[ $EUID -ne 0 ]]; then
    error "This script must be run as root"
fi

# Detect distribution
if [ -f /etc/os-release ]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian) 
            PKG_MGR="apt"
            PKG_INSTALL="apt install -y"
            PKG_UPDATE="apt update && apt upgrade -y"
            MYSQL_SERVICE="mysql"
            MYSQL_CONFIG="/etc/mysql/mysql.conf.d/mysqld.cnf"
            MYSQL_LOG_DIR="/var/log/mysql"
            FIREWALL_CMD="ufw"
            ;;
        almalinux|rocky|centos|rhel|ol|fedora)
            PKG_MGR="dnf"
            PKG_INSTALL="dnf install -y"
            PKG_UPDATE="dnf update -y"
            MYSQL_SERVICE="mysqld"
            MYSQL_CONFIG="/etc/my.cnf.d/mysql-server.cnf"
            MYSQL_LOG_DIR="/var/log/mysql"
            FIREWALL_CMD="firewall-cmd"
            ;;
        amzn)
            PKG_MGR="yum"
            PKG_INSTALL="yum install -y"
            PKG_UPDATE="yum update -y"
            MYSQL_SERVICE="mysqld"
            MYSQL_CONFIG="/etc/my.cnf"
            MYSQL_LOG_DIR="/var/log/mysql"
            FIREWALL_CMD="firewall-cmd"
            ;;
        *)
            error "Unsupported distribution: $ID"
            ;;
    esac
else
    error "Cannot detect distribution"
fi

# Create MySQL log directory
mkdir -p "$MYSQL_LOG_DIR"

echo "[1/10] Updating system packages..."
$PKG_UPDATE

echo "[2/10] Installing MySQL server..."
case "$PKG_MGR" in
    apt)
        $PKG_INSTALL mysql-server mysql-client
        ;;
    dnf|yum)
        $PKG_INSTALL mysql-server mysql
        ;;
esac

echo "[3/10] Enabling and starting MySQL service..."
systemctl enable --now $MYSQL_SERVICE

echo "[4/10] Waiting for MySQL to be ready..."
sleep 5
while ! mysqladmin ping --silent; do
    sleep 2
done

echo "[5/10] Creating MySQL configuration..."
# Backup existing config if it exists
if [ -f "$MYSQL_CONFIG" ]; then
    cp "$MYSQL_CONFIG" "${MYSQL_CONFIG}.backup"
fi

# Create directory if it doesn't exist
mkdir -p "$(dirname "$MYSQL_CONFIG")"

cat > "$MYSQL_CONFIG" << EOF
[mysqld]
# Server identification
server-id = $SERVER_ID

# Binary logging
log-bin = $MYSQL_LOG_DIR/mysql-bin.log
binlog-format = ROW
max_binlog_size = 100M
expire_logs_days = 7

# GTID configuration
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON

# Replication settings
bind-address = $BIND_IP
replica-skip-errors = 1062

# Performance tuning
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

# Security
local-infile = 0
EOF

# Set proper ownership and permissions
chown mysql:mysql "$MYSQL_CONFIG"
chmod 644 "$MYSQL_CONFIG"
chown -R mysql:mysql "$MYSQL_LOG_DIR"

echo "[6/10] Restarting MySQL with new configuration..."
systemctl restart $MYSQL_SERVICE
sleep 5

echo "[7/10] Securing MySQL installation..."
# Set root password and secure installation
MYSQL_ROOT_PASS="SecureRootPass123!"

mysql << EOF
ALTER USER 'root'@'localhost' IDENTIFIED BY '$MYSQL_ROOT_PASS';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
FLUSH PRIVILEGES;
EOF

# Save root password for future use
echo "[client]" > /root/.my.cnf
echo "user=root" >> /root/.my.cnf
echo "password=$MYSQL_ROOT_PASS" >> /root/.my.cnf
chmod 600 /root/.my.cnf

echo "[8/10] Creating replication user..."
mysql << EOF
CREATE USER '$REPL_USER'@'%' IDENTIFIED WITH mysql_native_password BY '$REPL_PASS';
GRANT REPLICATION SLAVE ON *.* TO '$REPL_USER'@'%';
FLUSH PRIVILEGES;
EOF

echo "[9/10] Configuring firewall..."
case "$FIREWALL_CMD" in
    ufw)
        if command -v ufw >/dev/null 2>&1; then
            ufw allow 3306/tcp
        fi
        ;;
    firewall-cmd)
        if command -v firewall-cmd >/dev/null 2>&1 && systemctl is-active firewalld >/dev/null 2>&1; then
            firewall-cmd --permanent --add-port=3306/tcp
            firewall-cmd --reload
        fi
        ;;
esac

echo "[10/10] Verifying installation..."
if systemctl is-active --quiet $MYSQL_SERVICE; then
    log "MySQL service is running"
else
    error "MySQL service is not running"
fi

# Test MySQL connection
if mysql -e "SELECT @@server_id, @@gtid_mode;" >/dev/null 2>&1; then
    log "MySQL connection successful"
    
    # Display configuration
    echo
    log "MySQL GTID Replication Setup Complete!"
    echo "  Server ID: $SERVER_ID"
    echo "  Bind IP: $BIND_IP"
    echo "  Replication User: $REPL_USER"
    echo "  Root Password: $MYSQL_ROOT_PASS"
    echo
    warn "IMPORTANT: Save these credentials securely!"
    echo
    log "To setup a replica, run this script on another server with:"
    echo "  $0 2 <replica_ip>"
    echo
    log "Then configure replication on the replica with:"
    echo "  CHANGE REPLICATION SOURCE TO"
    echo "    SOURCE_HOST='$BIND_IP',"
    echo "    SOURCE_USER='$REPL_USER',"
    echo "    SOURCE_PASSWORD='$REPL_PASS',"
    echo "    SOURCE_AUTO_POSITION=1;"
    echo "  START REPLICA;"
else
    error "MySQL connection failed"
fi

Review the script before running. Execute with: bash install.sh

#MySQL #GTID #replication #high availability #MHA

Setup MySQL replication with GTID and automatic failover

Prerequisites

What this solves

Step-by-step installation

Update system packages

Install MySQL 8.0 server

Secure MySQL installation

Configure MySQL master server

Server identification

Binary logging

GTID configuration

Replication settings

Performance tuning

Configure MySQL replica servers

Server identification (increment for each replica)

Binary logging

GTID configuration

Replication settings

Performance tuning

Restart MySQL services

Create replication user on master

Configure replication on replica servers

Install MHA dependencies

Download and install MHA

Configure SSH key authentication

Create MHA configuration

Create MHA management user

Test MHA configuration

Create MHA systemd service

Start MHA manager service

Configure monitoring and health checks

Create replication monitoring script

MySQL replication health check script

Check replication status

Extract key metrics

Set up automated health monitoring

Verify your setup

Check replica status on each replica server

Verify GTID is working

Check MHA manager status

Test replication by creating a test database

Verify data appears on replicas

Test automatic failover

Monitor MHA logs during failover

Check which server became the new master

Verify applications can connect to new master

Performance optimization

Optimize replication performance

Multi-threaded replication

Replication performance

Binary log optimization

GTID optimization

Connection optimization

Configure semi-synchronous replication

On replica servers

Common issues

Next steps

Running this in production?

Related tutorials

Implement ClickHouse backup automation with compression and S3 integration

Benchmark database performance with sysbench and fio integration

Configure MySQL binary log backup and point-in-time recovery

Don't want to manage this yourself?