- 查看当前默认配置（MYSQL5.7）
[root@MYSQLB bin]# mysql -uroot -p123456
mysql> show global variables like 'slave_parallel_workers'; #默认是0，即单线程
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 0 |
+------------------------+-------+
mysql> show global variables like '%slave_parallel_type%';#默认是多线程机制是一个线程处理一个库
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+
mysql> show full processlist;
+----+-------------+--------------------+------+-------------+---------+---------------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+--------------------+------+-------------+---------+---------------------------------------------------------------+-----------------------+
| 9 | repl | MYSQLA:61254 | NULL | Binlog Dump | 1229510 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 10 | system user | | NULL | Connect | 1229504 | Waiting for master to send event | NULL |
| 11 | system user | | NULL | Connect | 315 | Slave has read all relay log; waiting for more updates | NULL |
| 13 | root | localhost | NULL | Sleep | 1222364 | | NULL |
| 14 | root | MYSQLA:1652 | test | Sleep | 15 | | NULL |
| 15 | root | MYSQLA:1658 | test | Sleep | 25 | | NULL |
| 16 | root | MYSQLA:1676 | test | Sleep | 5 | | NULL |
| 17 | root | MYSQLB:27432 | test | Sleep | 27 | | NULL |
| 18 | root | MYSQLB:27434 | test | Sleep | 7 | | NULL |
| 19 | root | MYSQLB:27436 | test | Sleep | 17 | | NULL |
| 22 | root | 192.168.40.1:55485 | test | Sleep | 287 | | NULL |
| 23 | root | 192.168.40.1:55495 | test | Sleep | 284 | | NULL |
| 24 | root | localhost | NULL | Query | 0 | starting | show full processlist |
+----+-------------+--------------------+------+-------------+---------+---------------------------------------------------------------+-----------------------+
13 rows in set (0.00 sec)
- 配置从服务器上并行复制的参数（开启 Enhanced Multi-Threaded Slave），双主则两台都配置
[root@MYSQLA ~]# vim /etc/my.cnf
#MySQL 5.7开启Enhanced Multi-Threaded 在Slave的my.cnf配置：
# slave
#变量slave-parallel-type可以有两个值：DATABASE 默认值，基于库的并行复制方式；LOGICAL_CLOCK：基于组提交的并行复制方式
slave-parallel-type=LOGICAL_CLOCK
#提交的事务都是可以并行回放(配合binary log group commit);
slave-parallel-workers=16
#将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用
#开启MTS功能后，务必将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80%的提升。这是因为并 行复制开启后对于元master.info这个文件的更新将会大幅提升
master_info_repository=TABLE
relay_log_info_repository=TABLE
#启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。
relay_log_recovery=ON
relay_log_purge=ON
- 再次检查配置
[root@MYSQL ~]# service mysqld restart
root@localhost : (none)【10:53:41】 2 SQL->show global variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 16 |
+------------------------+-------+
1 row in set (0.00 sec)
root@localhost : (none)【10:53:46】 3 SQL->show global variables like '%slave_parallel_type%';
+---------------------+---------------+
| Variable_name | Value |
+---------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
+---------------------+---------------+
1 row in set (0.01 sec)
root@localhost : (none)【10:54:02】 4 SQL->show full processlist;
+----+-------------+--------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+--------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
| 1 | system user | | NULL | Connect | 59 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 59 | Slave has read all relay log; waiting for more updates | NULL |
| 3 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 4 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 5 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 11 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 14 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 15 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 17 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 18 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 19 | system user | | NULL | Connect | 59 | Waiting for an event from Coordinator | NULL |
| 20 | root | MYSQLB:29734 | test | Sleep | 4 | | NULL |
| 21 | root | localhost | NULL | Query | 0 | starting | show full processlist |
| 22 | root | MYSQLA:5604 | test | Sleep | 2 | | NULL |
| 23 | repl | MYSQLA:5678 | NULL | Binlog Dump | 5 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+-------------+--------------+------+-------------+------+---------------------------------------------------------------+-----------------------+
22 rows in set (0.01 sec)
#查看
mysql> show slave status\G
- 说明
情况3：
主库的DDL(alter、drop、repair、create)导致只读节点延迟
可能1：只读节点与主库的DDL同步是串行进行的，如果DDL操作在主库执行时间很长，那么同样在备库也会消耗同样的时间，比如在主库对一张500W的表添加一个字段耗费了10分钟，那么在只读节点上也同样会耗费10分钟，所以只读节点会延迟600S，其他常见操作比如：
mysql> alter table test add column nn varchar(10);
mysql> alter table test add index(jj);
可能2：只读节点上有一个执行时间非常长的的查询正在执行，那么这个查询会堵塞来自主库的DDL，读节点表被锁，直到查询结束为止，进而导致了只读节点的数据延迟。在只读节点上可以通过执行show processlist命令查看连接的状态处于: Waiting for table metadata lock
解决办法：
对于可能1，只能说执行操作之前对可能带来的影响要有考量，对于情况2，可以kill掉只读节点上的大查询进行，就可以恢复只读节点与主节点的数据同步
情况4：
主库执行大事务导致延迟
主库执行了一条insert … select非常大的插入操作，该操作产生了近几百G的binlog文件传输到只读节点，进而导致了只读节点出现应用binlog延迟。
解决办法：
将大事务拆分成为小事务进行排量提交，这样只读节点就可以迅速的完成事务的执行，不会造成数据的延迟。
情况5：
无主键的表进行DML操作导致延迟
mysql> update test set kk='fafa01';
由于表中没有主键，所以导致了每一个事务条目的更新都是全表扫描，如果表中很很多的数据，则备库执行该更新的事务条目的时候，就会出现很多的全表扫描更新；进一步说明就是，由于表中没有主键，在ROW模式下，每删一条数据都会做全表扫，也就是说一条delete，如果删了10条，会做10次全表扫，所以slave会一直卡住；
拓展：
主键对于innodb来说，是非常重要的，每张表的设计的时候，都应该把主键默认的加上，不管你需不需要他，而且主键的设计最好选择自增型的主键，这里也可以略提一下自增主键的好处：
a.自增型主键以利于插入性能的提高；
b.自增型主键设计(int,bigint)可以降低二级索引的空间，提升二级索引的内存命中率；
c.自增型的主键可以减小page的碎片，提升空间和内存的使用
