Set Up MySQL 8.4 Replication for Zabbix Using Percona XtraBackup

by

MySQL replication is a powerful way to create a reliable and scalable database environment for Zabbix. One effective method for setting up replication is to use Percona XtraBackup, a robust tool that allows non-blocking backups of MySQL databases. Below is a step-by-step guide to achieve this.

Prerequisites

  • Master and Slave servers running MySQL (preferably Percona Server for compatibility).
  • Zabbix installed on the Master.
  • Percona XtraBackup installed on both servers.
  • Proper network configuration between the Master and Slave.
  • Administrative privileges on both servers.

Step 1: Prepare the Master Database for Replication

Binary logging is enabled by default. In /var/lib/mysql directory you can find a binlog.000001 file after MySQL started. However some parameters can change the default behaviour. For example log-bin=mysql-bin will change the default file name to mysql-bin.000001. The server-id=1 is the default value. If server-id is not defined an informational message is issued. Edit the MySQL configuration file on the Master (/etc/mysql/my.cnf) to customize binary logging and set the server ID:

[mysqld] 
log-bin=mysql-bin 
server-id=1 
binlog-format=ROW

Create a user for replication. Replica connects to the source db with a user account with priviledges only for replication process because the password is stored in replica’s metadata repository slave_master_info in plain text .

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'mypassword'; 
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';

Restart MySQL to apply the changes:

sudo systemctl restart mysql

Step 2: Backup the Master Using XtraBackup 8.4

Install Percona XtraBackup if not already installed:

sudo apt update 
sudo apt install curl
curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo apt install gnupg2 lsb-release ./percona-release_latest.generic_all.deb
sudo apt update
sudo percona-release enable pxb-84-lts
sudo apt install percona-xtrabackup-84

Perform a full backup of the Zabbix database. You can ommit the –password option if it is configured in /root/.my.cnf file:

xtrabackup --backup --target-dir=/path/to/backup --user=root --password=mypassword

You can also run the command for specific databases using the option –databases like below:

xtrabackup --backup --target-dir=/path/to/backup --datadir=/var/lib/mysql --databases='mysql sys performance_schema information_schema zabbix' --user=root --password=mypassword

At the end of the output you will see the following lines:

Done: Writing file /root/xtrabackup/backup-my.cnf
Writing /root/xtrabackup/xtrabackup_info
Done: Writing file /root/xtrabackup/xtrabackup_info
Transaction log of lsn (27713193268790) to (27714064243819) was copied.
completed OK!

Prepare the backup to ensure consistency. Prepare mode applies the redo and rollback logs to the backed-up data, ensuring that the database files are in a clean state before restoring them.

Redo logs: Ensures all committed transactions are applied to bring the database to a consistent state.

Rolling back uncommitted transactions: Ensures that incomplete transactions are removed, similar to how MySQL/InnoDB crash recovery works

xtrabackup --prepare --target-dir=/path/to/backup

In the file /path/to/backup/xtrabackup_binlog_info it is the source binlog file name and the position that the replication should start from.

cat /path/to/backup/xtrabackup_binlog_info
Example output:mysql-bin.000001 12345

Step 3: Restore the Backup on the Slave

Copy the backup to the Slave server:

rsync -av /path/to/backup/ user@slave-server:/path/to/backup/

Stop MySQL on the Slave:

sudo systemctl stop mysql

Remove existing data and restore the backup:

sudo rm -rf /var/lib/mysql/* 
sudo xtrabackup --copy-back --target-dir=/path/to/backup 
sudo chown -R mysql:mysql /var/lib/mysql

Start MySQL:

sudo systemctl start mysql

Step 4: Configure the Slave for Replication

Edit the MySQL configuration file on the Slave (/etc/mysql/my.cnf) to set the server ID:

[mysqld] 
server-id=2

Restart MySQL:

sudo systemctl restart mysql

Configure the replication settings on the Slave. For MySQL 8.0 run the following command:

CHANGE MASTER TO MASTER_HOST='master-server-ip', MASTER_USER='replica_user', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=12345;

For MySQL 8.4 run the following command:

CHANGE REPLICATION SOURCE TO SOURCE_HOST='master-server-ip', SOURCE_USER='replica_user',  SOURCE_PASSWORD='your_password', SOURCE_LOG_FILE='mysql-bin.000001', SOURCE_LOG_POS=12345;

Start the replication:

START SLAVE;

Verify the replication status:

SHOW SLAVE STATUS \G;

Ensure that Slave_IO_Running and Slave_SQL_Running are both set to Yes.

Leave a Comment