Detailed introduction to the method of configuring multi-threaded master-slave replication on Mysql5.7 slave nodes

This article mainly introduces the relevant information of Mysql 5.7 slave node configuration multi-threaded master-slave replication. The introduction in the article is very detailed and has certain reference value for everyone. Friends who need it can come and join us. Let's see.

Preface

Mysql uses multi-threading for replication, which is supported starting from Mysql 5.6. However, there are flaws in the 5.6 version. Although it supports multiple Threads, but each database can only have one thread. That is to say, if we have only one database, only one thread will be 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 multiple threads, you need to configure it on the slave node.

Mysql 5.7 adds a type of master-slave replication. There are two types, as follows:

• DATABASE Library-based parallel replication, Each database corresponds to a replication thread

• LOGICAL_CLOCK Parallel replication method based on group submission, there can be multiple threads under the same database
  

The following steps are configured on the slave node.

View the current configuration

Before starting the configuration, let’s first 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)

It can be seen from the above that there is only one main process waiting for synchronization.

Check 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 there is only one under the unified database Threads perform copying and cannot copy in parallel.

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 processes working in parallel is 0

Configure multi-threading

1. Stop replicating 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 parallelism The number is 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 replicating from the slave node

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

5. Check the number of currently working threads

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, why do you need multi-threaded replication? Because there will be a delay in synchronization between master and slave, the purpose of multi-threading is to minimize this delay time. Although how to optimize master-slave is a system function, and different scenarios require different solutions, multi-threading can at least basically reduce latency. In addition, according to the actual situation of the database, whether it can really reduce the delay and how many threads to configure, you need to test repeatedly to get the data that suits you.

Summarize

The above is the detailed content of Detailed introduction to the method of configuring multi-threaded master-slave replication on Mysql5.7 slave nodes. For more information, please follow other related articles on the PHP Chinese website!