Mysql 5.7 从节点配置多线程主从复制

Mysql 5.7 从节点配置多线程主从复制

 当前配置下的主从复制的进程数
	mysql> show processlist;
	+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
	| Id | User        | Host      | db    | Command | Time | State                                                  | Info             |
	+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
	|  4 | root        | localhost | mysql | Query   |    0 | starting                                               | show processlist |
	| 10 | system user |           | NULL  | Connect | 2793 | Waiting for master to send event                       | NULL             |
	| 11 | system user |           | NULL  | Connect | 2728 | Slave has read all relay log; waiting for more updates | NULL             |
	+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
 有一个主进程在等待同步
 查看复制类型和并行数量配置
	mysql>  show variables like 'slave_parallel_type';
	+---------------------+----------+
	| Variable_name       | Value    |
	+---------------------+----------+
	| slave_parallel_type | DATABASE |
	+---------------------+----------+
 当前的复制类型是 DATABASE，也就是统一数据库下只有一个线程进行复制，不能并行复制。
	mysql> show variables like 'slave_parallel_workers';
	+------------------------+-------+
	| Variable_name          | Value |
	+------------------------+-------+
	| slave_parallel_workers | 0     |
	+------------------------+-------+	
	当前并行工作的进程数是 0
配置多线程
1、停止从节点复制
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
2、设置复制类型为 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.00 sec)
3、设置并行数量为 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.01 sec)
4、启动从节点复制且查看工作的线程数
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show processlist;
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db    | Command | Time | State                                                  | Info             |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
|  4 | root        | localhost | mysql | Query   |    0 | starting                                               | show processlist |
| 12 | system user |           | NULL  | Connect |    9 | Waiting for master to send event                       | NULL             |
| 13 | system user |           | NULL  | Connect |    9 | Slave has read all relay log; waiting for more updates | NULL             |
| 14 | system user |           | NULL  | Connect |    9 | Waiting for an event from Coordinator                  | NULL             |
| 15 | system user |           | NULL  | Connect |    9 | Waiting for an event from Coordinator                  | NULL             |
| 16 | system user |           | NULL  | Connect |    9 | Waiting for an event from Coordinator                  | NULL             |
| 17 | system user |           | NULL  | Connect |    9 | Waiting for an event from Coordinator                  | NULL             |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+