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
JavaScript MouseEvent Case Study
MouseEvent When the mouse performs a certain oper...
Detailed explanation of the steps for configuring the Centos7 bridge network under VMware
The complete steps of Centos7 bridge network conf...
How to install suPHP for PHP5 on CentOS 7 (Peng Ge)
By default, PHP on CentOS 7 runs as apache or nob...
Solve the problem of "Welcome to nginx on Fedora!" after installing nginx on Centos7, and there is no default.conf file in the conf.d directory
Problem Description Install nginx on Tencent Clou...
Beginners understand MySQL deadlock problem from source code
After many difficult single-step debugging late a...
Docker cleanup environment operation
Start cleaning carefully! List unused volumes doc...
WeChat applet implements a simple handwritten signature component
Table of contents background: need: Effect 1. Ide...
The simplest form implementation of Flexbox layout
Flexible layout (Flexbox) is becoming increasingl...
Implementation code for using mongodb database in Docker
Get the mongo image sudo docker pull mongo Run th...
How to handle MySQL numeric type overflow
Now, let me ask you a question. What happens when...
Solve the problem of shrinking Mysql transaction log and log files being too large to shrink
1. MS SQL SERVER 2005 --1. Clear the log exec(...
Why is it not recommended to use index as the key attribute value in Vue?
Table of contents Preface The role of key The rol...
JS implements the rock-paper-scissors game
This article example shares the specific code of ...
How to use @media in mobile adaptive styles
General mobile phone style: @media all and (orien...
How to use DQL commands to query data in MySQL
In this article, the blogger will take you to lea...