MySQL开启多线程复制
程序员文章站
2022-03-09 09:29:18
...
关闭复制
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
设置并发同步类型为逻辑时钟方式
mysql> set global slave_parallel_type=logical_clock;
Query OK, 0 rows affected (0.00 sec)
默认是datebase，每个线程只能处理一个数据库
配置成基于逻辑时钟的方式
配置并发线程数：
mysql> set global slave_parallel_workers=32;
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)
mysql> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 32 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 20 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 21 | system user | | NULL | Connect | 5 | Waiting for master to send event | NULL |
| 22 | system user | | NULL | Connect | 5 | Slave has read all relay log; waiting for more updates | NULL |
| 23 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 24 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 25 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 26 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 27 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 28 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 29 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 30 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 31 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 32 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 33 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 34 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 35 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 36 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 37 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 38 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 39 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 40 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 41 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 42 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 43 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 44 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 45 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 46 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 47 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 48 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 49 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 50 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 51 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 52 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 53 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
| 54 | system user | | NULL | Connect | 5 | Waiting for an event from Coordinator | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
35 rows in set (0.00 sec)
如果要永久生效，需要修改配置文件
slave_parallel_workers=32
slave_parallel_type=logical_clock