MySQL 8: Creating a replica using mydumper and myloader

Introduction

Replication is a fundamental feature for any database system that aims for high availability, fault tolerance, or simply for running analytics on a dedicated server without hindering the performance of the primary database. MySQL, being one of the most popular open-source relational databases, provides various replication capabilities. This tutorial will guide you through MySQL 8 replication using two powerful tools – mydumper and myloader.

Mydumper is a high-performance, multi-threaded backup tool for MySQL that has gained popularity due to its efficiency over the traditional mysqldump. Similarly, myloader is a data loader tool for MySQL which loads the data dumped by mydumper at a very high pace.

We’ll explore step-by-step procedures, assuming you’re familiar with the basic concepts of MySQL, Linux command line, and you have both master and replica servers set up with MySQL 8 installed.

Prerequisites

To get the most out of this tutorial, you should have the following:

• MySQL 8 installed and configured on both Master and Replica servers.
• Network connectivity between Master and Replica servers.
• Sufficient permissions for database operations on both servers.
• Installation of mydumper and myloader on the Master server.

Step 1 – Configuring the MySQL Master Server

The first step is to configure the MySQL master server to allow replication. Open the MySQL configuration file:

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

Add the following lines under the [mysqld] section:

server-id = 1
log_bin = mysql-bin
binlog_format = row
expire_logs_days = 10
max_binlog_size = 100M
    read_only = OFF

Now, restart the MySQL service for the changes to take effect:

sudo systemctl restart mysql.service

Output: Restarting MySQL 8.0 database server: mysqld. …done.

Step 2 – Creating a Backup Using mydumper

With mydumper you can create a backup without significantly affecting SQL operations on the master server.

mydumper --host localhost --user root --password your_password --outputdir /backup-directory --threads 4 --verbose 3

The above command creates a backup of all databases in the /backup-directory, using 4 threads. Adjust the thread count as needed based on server capacity.

Step 3 – Transferring the Backup to the Replica Server

To transfer the backup from the master to the replica, you can use secure copy (scp):

scp -r /backup-directory user@replica-host:/path/to/replica-backup-directory

Step 4 – Setting Up the MySQL Replica Server

On the replica server, open MySQL configuration and add the following:

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

server-id = 2
relay_log = mysql-relay-bin
read_only = ON

And then restart MySQL:

sudo systemctl restart mysql.service

Step 5 – Creating the replication user on the Master Server

Login to your MySQL master server and create a replication user:

mysql -u root -p

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;

This user will be used for the replication process.

Step 6 – Loading the Backup on Replica Using myloader

With the backup successfully transferred, you can now restore the databases on the replica:

myloader --directory /path/to/replica-backup-directory --threads 4 --verbose 3 --overwrite-tables --host localhost --user root --password replica_server_password

This restores the databases to the replica server and overwrites any existing tables with the same name.

Step 7 – Starting the Replication Process

It’s time to start replicating data. On the replica MySQL instance:

mysql -u root -p

CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replica_user', MASTER_PASSWORD='replica_password', MASTER_AUTO_POSITION = 1;
START SLAVE;

MASTER_HOST should be replaced with the IP address of the master server. Finally, verify the replication status:

SHOW SLAVE STATUS \\G

Look for Slave_IO_Running and Slave_SQL_Running; both should say Yes.

Conclusion

In conclusion, replication in MySQL 8 using mydumper and myloader greatly simplifies the complex task of database synchronization. Following the outlined steps should aid in setting up an efficient replica environment which enhances both data redundancy and accessibility.