How to configure multi-thread master-slave replication in Mysql 5.7 slave node, mysql5.7

How to configure multi-thread master-slave replication in Mysql 5.7 slave node, mysql5.7

Preface

Mysql multi-threaded replication is supported since Mysql 5.6. However, Mysql 5.6 has some drawbacks. Although multi-threaded replication is supported, each database can only have one thread. That is to say, if we only have one database, only one thread is working during master-slave replication. It is equivalent to the previous single thread. Mysql 5.7 supports parallel master-slave replication in the same database. However, by default, it is still a single thread in a single database. If multithreading is required, it must be configured on the slave node.

Mysql 5.7 adds one type to master-slave replication. There are two types:

• DATABASE-based parallel replication, each DATABASE corresponds to a replication thread
• LOGICAL_CLOCK is a group-submitted parallel replication mode. Multiple Threads can exist in the same database.
  

The following steps are performed on the slave node.

View 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)

As shown above, only one master process is waiting for synchronization.

The following describes the replication type and parallel quantity configuration.

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, that is, there is only one thread in the unified DATABASE for replication, and parallel replication is not allowed.

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

The number of concurrent processes is 0.

Configure Multithreading

1. Stop slave node Replication

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 parallelism 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 slave node Replication

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, why do I need multi-thread replication? Because synchronization between the master and slave nodes has a delay, multithreading is designed to minimize the delay. Although the optimization of Master/Slave is a system function, different scenarios require different solutions, but multithreading can reduce the latency at least. In addition, based on the actual situation of the database, whether the latency can be really reduced, and how many threads are configured, You Need To repeatedly test the appropriate data.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.