How to Build Zabbix Database High Availability with Keepalived and MySQL GTID

ZABBIX Database High Availability Architecture

ZABBIX Database High Availability Implementation

Two MariaDB servers (ZABBIX‑DB1 and ZABBIX‑DB2) are installed with identical system and data versions. High availability is achieved with keepalived providing a virtual IP (VIP) and MySQL dual‑master replication.

MySQL Master‑Slave Replication

Each server acts as master and slave of the other, forming a bidirectional replication topology. When one node fails, the VIP moves to the surviving node, allowing continuous service.

Replication Process

Master records changes to the binary log before committing transactions.

Slave I/O thread copies the master’s binary log to its relay log.

SQL thread replays the relay log to keep the slave data consistent.

GTID (Global Transaction ID) is used to simplify failover and reduce manual intervention.

GTID Working Principle

Master generates a GTID for each transaction and records it in the binlog.

Slave I/O thread writes the binlog to the local relay log.

Slave SQL thread checks the relay log for GTID entries; if not executed, it runs the transaction and records the GTID.

During parsing, primary keys are used for indexed lookup; otherwise a full table scan occurs.

GTID Advantages

Each transaction has a unique ID executed only once per server.

No need to specify binary log file name and position during replication setup.

Reduces manual intervention and shortens service outage when a master fails.

GTID Limitations

Not supported for non‑transactional storage engines.

Cannot replicate CREATE TABLE … SELECT statements.

A single statement cannot modify both transactional and non‑transactional tables.

All servers in a replication group must enable or disable GTID uniformly.

GTID requires a server restart (except for MySQL 5.7).

Enabling GTID disables the traditional replication method.

Does not support temporary table creation or dropping.

Does not support sql_slave_skip_counter.

ZABBIX Database Creation and User Grants

mysql> create database zabbix character set utf8 collate utf8_bin;</code>
<code>mysql> grant all privileges on zabbix.* to zabbix@localhost identified by 'zabbix';</code>
<code>mysql> grant all privileges on *.* to 'root'@'%' identified by 'zabbix@zabbix' with grant option;</code>
<code>mysql> grant all privileges on *.* to 'zabbix'@'%' identified by 'zabbix' with grant option;</code>
<code>mysql> flush privileges;</code>
<code>mysql> show databases;

ZABBIX High‑Availability Database Replication Configuration

Configuration file my.cnf for ZABBIX‑DB1:

[mysqld]
log-bin=mysql-bin
log-bin=/mariadb/binlogs/mysql-bin
server-id=82
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-do-db=zabbix
replicate-do-db=zabbix
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
binlog_format=MIXED
expire_logs_days=7
max_binlog_size=100m
binlog_cache_size=4m
max_binlog_cache_size=512m

Configuration file my.cnf for ZABBIX‑DB2 (only server-id differs):

[mysqld]
log-bin=mysql-bin
log-bin=/mariadb/binlogs/mysql-bin
server-id=89
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-do-db=zabbix
replicate-do-db=zabbix
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
binlog_format=MIXED
expire_logs_days=7
max_binlog_size=100m
binlog_cache_size=4m
max_binlog_cache_size=512m

After editing, restart MariaDB:

systemctl restart mariadb

Data Import to Slave

Export data from the master:

mysqldump -uzabbix -pzabbix zabbix > /opt/zabbix.sql

Copy the dump to the slave and import:

scp [email protected]:/opt/zabbix.sql /opt/
mysql -uzabbix -pzabbix
use zabbix;
source /opt/zabbix.sql;

Replication User Grants

# Grant replication slave user on master
grant replication slave on *.* to 'zabbixslave'@'21.254.248.89' identified by 'zabbixslave';
flush privileges;
show master status;

Slave Control Commands

stop slave;
reset slave;
start slave;
show slave status \G;

Keepalived Introduction

Keepalived provides high‑availability clustering using the VRRP protocol. It elects a master node that holds a virtual IP; backup nodes take over when the master fails.

Database Monitoring Script

#!/bin/bash
mysqld=$(netstat -na | grep "LISTEN" | grep "3306" | wc -l)
if [ "${mysqld}" -eq 0 ]; then
    systemctl stop keepalived
    killall keepalived
fi
ping 21.254.248.82 -w1 -c1 >/dev/null
if [ $? -ne 0 ]; then
    systemctl stop keepalived
    killall keepalived
fi

Keepalived Configuration

Configuration for ZABBIX‑DB1 (master):

!Configuration File for keepalived
global_defs {
    router_id lvs-mysql01
}

vrrp_script chkmysql {
    script "/etc/keepalived/chkmysql.sh"
    fall 2
    rise 2
}

vrrp_instance mysql-ha {
    state MASTER
    interface ens33
    virtual_router_id 100
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        21.254.248.121
    }
    track_script {
        chkmysql
    }
}

Configuration for ZABBIX‑DB2 (backup):

!Configuration File for keepalived
global_defs {
    router_id lvs-mysql02
}

vrrp_script chkmysql {
    script "/etc/keepalived/chkmysql.sh"
    fall 2
    rise 2
}

vrrp_instance mysql-ha {
    state BACKUP
    interface ens33
    virtual_router_id 100
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        21.254.248.121
    }
    track_script {
        chkmysql
    }
}

HA Switch Verification

Stop MariaDB on DB1, run the monitoring script to stop keepalived, and verify that the VIP moves to DB2. Screenshots illustrate the VIP migration and service continuity.

Database Failback

To revert the VIP to DB1, start the local MariaDB, then start keepalived. Verify the VIP address again.

Periodic Backup and Cleanup

Backup script (runs daily):

#!/bin/bash
/usr/bin/mysqldump -uzabbix -pzabbix zabbix | gzip > /mysql-backup/zabbix_$(date +%Y%m%d_%H%M%S).sql.gz

Cleanup script (removes backups older than 20 days):

#!/bin/bash
find /mysql-backup -name "zabbix*.sql.gz" -type f -mtime +20 -exec rm -rf {} \; >/dev/null 2>&1

Both scripts are scheduled via crontab entries.