Explanation of Mysql 5.7 Slave Node Configuring Multi Thread Master Slave Replication

Preface

Mysql replication with multithreading is supported from Mysql 5.6, but there are defects in version 5.6. Although multithreading 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. Equivalent to the previous single thread. Beginning with Mysql 5.7, parallel master-slave replication under the same 1 database is supported. However, by default, it is a single database with a single thread, and if you need to use multiple threads, you need to configure it at the slave node.

Mysql 5.7 adds one type to master-slave replication, and there are two types, as follows:

DATABASE parallel replication based on libraries, one replication thread per database LOGICAL_CLOCK Parallel replication mode based on group commit, which can have multiple threads under the same database

The following steps configure on the slave node.

View the current configuration

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

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 only 1 main process is waiting for synchronization.

Look at the replication type and parallel quantity 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, that is, only one thread replicates under the unified 1 database, and cannot replicate in parallel.

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

The number of processes currently working in parallel is 0

Configure multithreading

1. Stop copying 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 parallels 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 under 1

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 multithreaded replication? Because there will be a delay in synchronization between master and slave, the purpose of multithreading is to minimize this delay. Although how to optimize master and slave is the function of a system, and different scenarios require different solutions, multithreading can at least reduce the delay time on the basis. In addition, according to the actual situation of the database, whether the delay can be really reduced and how many threads are configured requires repeated tests to get the data suitable for yourself.

Summarize