mysql主从开并发参数_MySQL 主从复制-开启并发SQL线程复制 – 鸡书之家

单独重启IO/SQL线程：

# 关闭从库的 IO线程

mysql> stop slave io_thread;

Quer

# 开启从库的 IO线程

mysql> start slave io_thread;

Query OK, 0 rows affected (0.00 sec)

# 关闭从库的 SQL线程

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

# 开启从库的 SQL线程

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

MySQL-SQL多线程复制：

slave_parallel_workers: 该参数决定了slave上启动多个SQL thread线程来并行应用数据的。默认值是0代表不允许并行，取值范围可以是0~1024。

查看默认SQL线程配置：

# 默认不允许 SQL线程并行运行

mysql> show variables like '%slave_parallel_w%';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| slave_parallel_workers | 0 |

+------------------------+-------+

1 row in set (0.00 sec)

配置多线程：

vim /usr/local/mysql/conf/my.cnf

[mysqld]

slave-parallel-workers=5

查看修改后参数：

# 可以看到有一个 IO线程 和 5个 SQL线程

mysql> show processlist;

+----+-----------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-----------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+

| 4 | event_scheduler | localhost | NULL | Daemon | 1572 | Waiting on empty queue | NULL |

| 8 | root | localhost | mysql | Query | 0 | starting | show processlist |

| 37 | system user | | NULL | Connect | 79 | Waiting for master to send event | NULL |

| 38 | system user | | NULL | Query | 79 | Slave has read all relay log; waiting for more updates | NULL |

| 39 | system user | | NULL | Connect | 79 | Waiting for an event from Coordinator | NULL |

| 40 | system user | | NULL | Connect | 79 | Waiting for an event from Coordinator | NULL |

| 41 | system user | | NULL | Connect | 79 | Waiting for an event from Coordinator | NULL |

| 42 | system user | | NULL | Connect | 79 | Waiting for an event from Coordinator | NULL |

| 43 | system user | | NULL | Connect | 79 | Waiting for an event from Coordinator | NULL |

+----+-----------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+

9 rows in set (0.00 sec)