MySQL 8: How to create a replica using Percona XtraBackup

Introduction

Creating a replica (or slave) of a MySQL database is a foundational practice for ensuring data redundancy, maintaining backup, and facilitating load distribution across databases. With the release of MySQL 8, creating a replica has never been more robust and flexible, particularly when coupled with Percona’s XtraBackup tool, which allows you to perform hot backups of MySQL, MariaDB, and Percona Server databases. In this tutorial, you’ll learn step-by-step how to create a replica of a MySQL 8 database using Percona XtraBackup.

Prerequisites

Before starting this tutorial, you should have:

• A running instance of MySQL 8 on the master server.
• Sudo or root access on the master and replica servers.
• Percona XtraBackup installed on the master server. Instructions for installation can be found on Percona’s official website.

Step 1: Configure the Master Server

Begin by configuring the master MySQL server to accept replication connections and to generate binary logs:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add or uncomment the following configurations:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = my_database
binlog_format           = row

Replace my_database with your actual database name. Then, restart MySQL to apply the changes:

sudo systemctl restart mysql

Step 2: Creating a Database User for Replication

Create a dedicated user that will be used by the slave to connect to the master:

mysql -u root -p
CREATE USER 'replica_user'@'%' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;
exit

Step 3: Taking a Backup with XtraBackup

Now, let’s use Percona XtraBackup to create a full backup of the master server:

sudo xtrabackup --backup --user=root --password=password --target-dir=/data/backup

Step 4: Preparing the Backup

Before the backup can be used on the replica, it must be prepared:

sudo xtrabackup --prepare --target-dir=/data/backup

Step 5: Copying the Backup to the Replica

Use a tool like rsync to copy the backup to the replica server:

rsync -avP /data/backup replica_user@replica_ip:/data/backup

Step 6: Setting Up the Replica Server

On the replica server, stop MySQL and configure it for replication:

sudo systemctl stop mysql
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add or update the following lines:

server-id               = 2
relay-log               = /var/log/mysql/mysql-relay-bin.log

Then start MySQL with the –skip-networking option to prevent any remote connections during preparation:

sudo mysqld --skip-networking &

Step 7: Restoring the Backup

With the replica MySQL server still running without networking, restore the backup:

sudo xtrabackup --copy-back --target-dir=/data/backup
chown -R mysql:mysql /var/lib/mysql

Step 8: Setting the Replica Configuration

Obtain the binary log file position from the backup to sync the replica with the master:

cat /data/backup/xtrabackup_binlog_info

You’ll see output like:

mysql-bin.000001 1234

This information will be used to point the replica to the correct position in the master’s binary log.

Now, configure the slave information on the replica:

mysql -u root -p
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='replica_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1234;
START SLAVE;
SHOW SLAVE STATUS\G
exit

Conclusion

In this tutorial, you have learned to create a MySQL 8 replica using Percona XtraBackup. We covered installing prerequisites, configuring the master and replica, backing up the master, copying this backup, preparing the replica, and finally, starting the replication process. With these steps, you can ensure your data is robustly backed up and easily scale your MySQL operations.