Detailed explanation of how to configure multi-threaded master-slave replication from MySQL 5.7 slave nodes

Preface

MySQL supports multi-threaded replication starting from MySQL 5.6, but there is a defect in version 5.6. Although it supports multi-threading, each database can only have one thread. That is to say, if we only have one database, there is only one thread working during master-slave replication. It is equivalent to the previous single thread. Starting from MySQL 5.7, parallel master-slave replication under the same database is supported. However, by default, it is still a single database and a single thread. If you need to use multi-threading, you need to configure it on the slave node.

MySQL 5.7 adds a new type of master-slave replication, with two types as follows:

DATABASE Library-based parallel replication, each database corresponds to a replication thread
LOGICAL_CLOCK Parallel replication based on group submission, multiple threads can exist under the same database

The following steps are configured on the slave node.

View the current configuration

Before starting the configuration, let's take a look at the number of master-slave replication processes under the current configuration.

mysql> show processlist;
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 91749 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 208 | Slave has read all relay log; waiting for more updates | NULL |
| 37 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

From the above, we can see that there is only one main process waiting for synchronization.

Check out the replication type and parallel number configuration below

mysql> show variables like 'slave_parallel_type';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+
1 row in set (0.00 sec)

The current replication type is DATABASE, which means that there is only one thread for replication under the unified database and parallel replication is not possible.

mysql> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 0 |
+------------------------+-------+
1 row in set (0.01 sec)

The current number of parallel working processes is 0

Configuring Multithreading

1. Stop replication from the node

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

2. Set the replication type to LOGICAL_CLOCK

mysql> set global slave_parallel_type='logical_clock';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slave_parallel_type';
+---------------------+---------------+
| Variable_name | Value |
+---------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
+---------------------+---------------+
1 row in set (0.01 sec)

3. Set the number of parallel operations to 4

mysql> set global slave_parallel_workers=4;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 4 |
+------------------------+-------+
1 row in set (0.00 sec)

4. Start replication from the node

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

5. Check the number of threads currently working

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 37 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 38 | system user | | NULL | Connect | 8 | Waiting for master to send event | NULL |
| 39 | system user | | NULL | Connect | 7 | Slave has read all relay log; waiting for more updates | NULL |
| 40 | system user | | NULL | Connect | 8 | Waiting for an event from Coordinator | NULL |
| 41 | system user | | NULL | Connect | 8 | Waiting for an event from Coordinator | NULL |
| 42 | system user | | NULL | Connect | 8 | Waiting for an event from Coordinator | NULL |
| 43 | system user | | NULL | Connect | 8 | Waiting for an event from Coordinator | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
7 rows in set (0.00 sec)

Finally, let’s talk about why multi-threaded replication is needed? Because there will be a delay in synchronization between the master and the slave, the purpose of multithreading is to minimize this delay. Although how to optimize the master-slave relationship is a system function and different scenarios require different solutions, multithreading can at least reduce latency from a basic perspective. In addition, based on the actual situation of the database, whether the delay can be truly reduced and how many threads to configure require repeated testing to obtain data that suits you.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:

Comprehensive interpretation of MySQL master-slave replication, from principle to installation and configuration
How to configure MySQL master-slave replication under Windows
MySQL configuration SSL master-slave replication
Detailed explanation of the configuration method of MySQL master-slave replication read-write separation
MySQL (master/slave) master-slave replication principle and configuration graphic detailed explanation
MySQL5.6 master-slave replication synchronization detailed configuration (picture and text)
In-depth analysis of semi-synchronous and asynchronous MySQL master-slave replication configuration
Introduction to the heartbeat function of MySQL master-slave replication configuration
The principle and configuration method of MySQL master-slave replication (more detailed)
MySQL master-slave replication configuration process

<<: Detailed explanation of using JavaScript WeakMap

>>: Detailed explanation of CentOS configuration of Nginx official Yum source