《MYSQL教程Mysql 5.7從節(jié)點配置多線程主從復制的方法詳解》要點：
本文介紹了MYSQL教程Mysql 5.7從節(jié)點配置多線程主從復制的方法詳解，希望對您有用。如果有疑問，可以聯(lián)系我們。
MYSQL實例前言
MYSQL實例Mysql 采用多線程進行復制是從 Mysql 5.6 開始支持的內(nèi)容,但是 5.6 版本下有缺陷,雖然支持多線程,但是每個數(shù)據(jù)庫只能一個線程,也就是說如果我們只有一個數(shù)據(jù)庫,則主從復制時也只有一個線程在工作.相當于還是以前的單線程. 從 Mysql 5.7 開始支持同一數(shù)據(jù)庫下并行主從復制.不過默認情況下,還是單數(shù)據(jù)庫單個線程,如果需要使用多線程,需要在從節(jié)點進行配置.
MYSQL實例Mysql 5.7 對主從復制增加了一種類型,共有兩種類型,如下：
MYSQL實例下面的步驟,在從節(jié)點上進行配置.
MYSQL實例查看當前配置
MYSQL實例在開始配置之前,我們先看一下當前配置下的主從復制的進程數(shù).
MYSQL實例
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)
MYSQL實例從上面看出只有一個主進程在等待同步.
MYSQL實例下面查看復制類型和并行數(shù)量配置
MYSQL實例
mysql> show variables like 'slave_parallel_type';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+
1 row in set (0.00 sec)
MYSQL實例當前的復制類型是 DATABASE,也就是統(tǒng)一數(shù)據(jù)庫下只有一個線程進行復制,不能并行復制.
MYSQL實例
mysql> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 0 |
+------------------------+-------+
1 row in set (0.01 sec)
MYSQL實例當前并行工作的進程數(shù)是 0
MYSQL實例配置多線程
MYSQL實例1、停止從節(jié)點復制
MYSQL實例
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
MYSQL實例2、設置復制類型為 LOGICAL_CLOCK
MYSQL實例
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)
MYSQL實例3、設置并行數(shù)量為 4
MYSQL實例
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)
MYSQL實例4、啟動從節(jié)點復制
MYSQL實例
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
MYSQL實例5、查看一下當前工作的線程數(shù)
MYSQL實例
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)
MYSQL實例最后說一下為什么需要多線程復制?因為主從之間的同步會有延時,多線程的目的是為了盡量減少這個延時時間.雖然如何優(yōu)化主從是一個系統(tǒng)的功能,不同的場景需要不同的解決方案,但是多線程至少從基礎(chǔ)上能減少延遲時間.另外根據(jù)數(shù)據(jù)庫的實際情況,能否真正減少延時,以及配置多少線程,則需要反復的測試得出適合自己的數(shù)據(jù).
MYSQL實例總結(jié)
MYSQL實例以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學習或者工作能帶來一定的幫助,如果有疑問大家可以留言交流,謝謝大家對維易PHP的支持.
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/4051.html
