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:
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:
|
<<: Detailed explanation of using JavaScript WeakMap
>>: Detailed explanation of CentOS configuration of Nginx official Yum source
Recommend
CSS3 diamond puzzle realizes the function of rotating only div and not rotating the background image
Demand background The project is made using Vue, ...
Best way to replace the key in json object
JSON (JavaScript Object Notation, JS Object Notat...
Introduction to the use of html area image hotspots with a list of related properties
The <area> tag is mainly used in image maps...
Four ways to create objects in JS
Table of contents 1. Create objects by literal va...
How to deploy Solidity smart contracts using ethers.js
If you have developed DApps on Ethereum, you may ...
How to implement Hover drop-down menu with CSS
As usual, today I will talk about a very practica...
Detailed steps to install Docker 1.8 on CentOS 7
Docker supports running on the following CentOS v...
One minute to experience the smoothness of html+vue+element-ui
Technology Fan html web page, you must know vue f...
Detailed explanation of the use and precautions of crontab under Linux
Crontab is a command used to set up periodic exec...
CSS implements the bottom tapbar function
Now many mobile phones have the function of switc...
How to add a pop-up bottom action button for element-ui's Select and Cascader
As shown in the figure below, it is a common desi...
Docker container operation instructions summary and detailed explanation
1. Create and run a container docker run -it --rm...
Practice of using SuperMap in Vue
Table of contents Preface Related Materials Vue p...
JS, CSS and HTML to implement the registration page
A registration page template implemented with HTM...
Use HTML to write a simple email template
Today, I want to write about a "low-tech&quo...