mysql多线程复制

mysql主从复制原理：

1. master节点上的binlogdump线程，在slave与其正常连接的情况下，将binlog发送到slave上。

2.slave节点上的I/O线程，通过读取master节点发送的内容，并将数据复制到本地的relaylog中。

3.slave节点上的SQL线程，读取relaylog中的日志，并将其事务在本地执行。

而master这边是通过并发线程提交，事物通过LSN写入binlog；但是Slave只有一个IO线程和Thread线程，是单线程，所以在业务大的情况下就很容易造成主从延时

mysql v5.6引入并发复制，基于数据库级别的，这意味着slave可以基于一个数据库处理连续事物；即一个数据库一个线程；当然如果只有一个数据库或者说所有的高负载都在单个库上，这样作用就不是很大；

mysql v5.7.2进行了优化，增加了参数slave_parallel_type，参数有两个选项：

LOGICAL_CLOCK：基于逻辑时钟 ，可以在一个DATABASE中并发执行relay log事物

DATABASE： 基于数据库，v5.6默认是这个参数，改参数每个库只能一个线程；

 

V5.6设置步骤：

操作之前查看，发现主从一共2个线程，一个IO_thread，一个SQL_thread：

mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  6 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist |
| 14 | system user |           | NULL | Connect |    6 | Waiting for master to send event                                            | NULL             |
| 15 | system user |           | NULL | Connect |    6 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+

 先停止主从复制stop slave;

而后设置主从复制线程数， 我这里设置5

开启主从复制 start salve;

再show下，发现多了5个

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> set global slave_parallel_workers=5;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  6 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist |
| 16 | system user |           | NULL | Connect |    5 | Waiting for master to send event                                            | NULL             |
| 17 | system user |           | NULL | Connect |    5 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 18 | system user |           | NULL | Connect |    5 | Waiting for an event from Coordinator                                       | NULL             |
| 19 | system user |           | NULL | Connect |    5 | Waiting for an event from Coordinator                                       | NULL             |
| 20 | system user |           | NULL | Connect |    5 | Waiting for an event from Coordinator                                       | NULL             |
| 21 | system user |           | NULL | Connect |    5 | Waiting for an event from Coordinator                                       | NULL             |
| 22 | system user |           | NULL | Connect |    5 | Waiting for an event from Coordinator                                       | NULL             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
8 rows in set (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.12
                  Master_User: repl
                  Master_Port: 3358
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 34353123
               Relay_Log_File: pid_mysql-relay-bin.000008
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

V5.7设置步骤

 

--设置前查看原配置
mysql> show global variables like '%parallel%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| slave_parallel_type    | DATABASE |
| slave_parallel_workers | 0        |
+------------------------+----------+
2 rows in set (0.05 sec)
--停止主从复制
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
--开5个并发（并发个数自己定义0-1024）
mysql> set global slave_parallel_workers = 5;
Query OK, 0 rows affected (0.04 sec)
--设置并发类型为逻辑时钟
mysql> set global slave_parallel_type = 'LOGICAL_CLOCK';
Query OK, 0 rows affected (0.04 sec)
--查看设置后的值
mysql> show global variables like '%parallel%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 5             |
+------------------------+---------------+
2 rows in set (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
--查看线程状态，多了5个线程
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  6 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
| 14 | system user |           | NULL | Connect |    6 | Waiting for master to send event                       | NULL             |
| 15 | system user |           | NULL | Connect |    6 | Slave has read all relay log; waiting for more updates | NULL             |
| 16 | system user |           | NULL | Connect |    6 | Waiting for an event from Coordinator                  | NULL             |
| 17 | system user |           | NULL | Connect |    6 | Waiting for an event from Coordinator                  | NULL             |
| 18 | system user |           | NULL | Connect |    6 | Waiting for an event from Coordinator                  | NULL             |
| 19 | system user |           | NULL | Connect |    6 | Waiting for an event from Coordinator                  | NULL             |
| 20 | system user |           | NULL | Connect |    6 | Waiting for an event from Coordinator                  | NULL             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
8 rows in set (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.12
                  Master_User: repl
                  Master_Port: 3358
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 3923
               Relay_Log_File: localhost-relay-bin.000006
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes