主从复制延迟推荐解决方案
如何提高复制的性能
现象描述
MySQL从库上通过SHOW PROCESSLIST可以看到有且仅有一个SQL线程在解析中继日志Relay Log并应用，例如：
mysql> show processlist \G;
*************************** 1. row ***************************
Id: 5
User: root
Host: 192.168.211.1:63440
db: ssm
Command: Sleep
Time: 2185
State:
Info: NULL
*************************** 2. row ***************************
Id: 6
User: root
Host: 192.168.211.1:63488
db: ssm
Command: Sleep
Time: 2190
State:
Info: NULL
*************************** 3. row ***************************
Id: 15
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 4. row ***************************
Id: 16
User: system user
Host:
db: NULL
Command: Connect
Time: 2
State: Waiting for master to send event
Info: NULL
*************************** 5. row ***************************
Id: 17
User: system user
Host:
db: NULL
Command: Connect
Time: 2
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL 那么写入压力特别大的场景下，考虑到主库是多线程并发在写入（应用服务器并发连接写入），而从库仅仅只有一个SQL线程在应用日志，就容易出现从库追不上主库的情况，可以在从库上通过SHOW SLAVE STATUS来查看从库落后主库的时间：
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.211.136
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 1271
Relay_Log_File: mysqld-relay-bin.000011
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1271
Relay_Log_Space: 620
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 136
Master_UUID: cb248a05-3538-11eb-8893-005056253f37
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec) 其中 Seconds_Behind_Master 显示了预估从库落后主库的秒数，不是特别精准，只是一个预估值。
从库的数据落后主库的问题，当然可以通过提高从库配置的硬件来解决。但更推荐通过架构设计来解决这个问题，通过减少从库需要做的写入操作或者在从库上实现多线程写入操作都能够解决。
2个解决方案
方案一
通过拆分减少一个从库上需要数据同步的表来解决。首先考虑配置一主多从的架构，然后在不同的从库上，通过设置不同 replicate-do-db、replicate-do-table、replicate-ignore-db、replicate-ignore-table或replicate-wild-do-table参数，使得不同的从库复制不同的库/表，减少每个从库上需要写入的数据。
例如，假设主库为M1，从库为S1、S2、S3，其中设置从库S1仅需要复制databaseA，而从库S2仅需要复制databaseB，从库S3仅需要复制databaseC，那么每个从库只需要执行自己需要复制的库/表相关的SQL就可以了，如下图所示。
这时，由于主库M1需要给S1、S2、S3三个从库（或者更多从库）都发送完整的Binlog日志，I/O 和网络压力较大，再改进一下架构：配置 MySQL 多级主从架构减轻主库压力，如下图所示。
- 主库M1首先给二级主库M2推送完整的Binlog。
- 二级主库M2打开log-slave-updates配置，保证主库M1传送过来的Binlog能够被记录在二级主库M2的RelayLog和Binlog中；二级主库M2选择BLACKHOLE引擎作为表引擎，降低二级主库上I/O的压力。
- 为二级主库M2配置3个从库S1、S2、S3，三个从库通过配置不同replicate-do-db等参数，让S1、S2、S3复制不同的库/表。
通过多级主从的方式，提高从库的复制性能，同时尽量降低对主库的影响。
注意：BLACKHOLE引擎就是一个“黑洞”引擎，在创建表的时候，选择BLACKHOLE引擎，那么写入表的数据不会真实地写入磁盘，仅仅记录Binlog日志，极大降低了磁盘的I/O。
方案一的优点在于能够自由拆分从库，方便地把热点数据分散开来；缺点在于维护起来不够简洁，并且由于从库S1、S2、S3上都没有主库完整的数据，在主库M1出现意外宕机的情况，应用处理较为麻烦。需要提前和应用沟通好异常的处理解决方案。
方案二
MySQL 5.6提供了基于 Schema的多线程复制，允许从库并行更新。例如，主库上存在 2个Schema，即ssm和replication。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| replication |
| ssm |
+--------------------+
5 rows in set (0.00 sec) MySQL 5.6的从库在同步主库时，通过设置参数 slave_parallel_workers为 2，让MySQL从库在复制时启动两个SQL线程。参数设置前：
mysql> show variables like '%slave_parallel_workers%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.50 |
+-----------+
1 row in set (0.00 sec)mysql> show processlist \G;
*************************** 1. row ***************************
Id: 5
User: root
Host: 192.168.211.1:63440
db: ssm
Command: Sleep
Time: 3480
State:
Info: NULL
*************************** 2. row ***************************
Id: 6
User: root
Host: 192.168.211.1:63488
db: ssm
Command: Sleep
Time: 3485
State:
Info: NULL
*************************** 3. row ***************************
Id: 15
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 4. row ***************************
Id: 16
User: system user
Host:
db: NULL
Command: Connect
Time: 1297
State: Waiting for master to send event
Info: NULL
*************************** 5. row ***************************
Id: 17
User: system user
Host:
db: NULL
Command: Connect
Time: 1297
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
5 rows in set (0.00 sec)设置参数后如下：注意需要先stop slave,再start slave才有效果
mysql> set global slave_parallel_workers=2;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show processlist \G;
*************************** 1. row ***************************
Id: 5
User: root
Host: 192.168.211.1:63440
db: ssm
Command: Sleep
Time: 3603
State:
Info: NULL
*************************** 2. row ***************************
Id: 6
User: root
Host: 192.168.211.1:63488
db: ssm
Command: Sleep
Time: 3608
State:
Info: NULL
*************************** 3. row ***************************
Id: 15
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 4. row ***************************
Id: 18
User: system user
Host:
db: NULL
Command: Connect
Time: 2
State: Waiting for master to send event
Info: NULL
*************************** 5. row ***************************
Id: 19
User: system user
Host:
db: NULL
Command: Connect
Time: 2
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 6. row ***************************
Id: 20
User: system user
Host:
db: NULL
Command: Connect
Time: 2
State: Waiting for an event from Coordinator
Info: NULL
*************************** 7. row ***************************
Id: 21
User: system user
Host:
db: NULL
Command: Connect
Time: 2
State: Waiting for an event from Coordinator
Info: NULL
7 rows in set (0.00 sec) 通过设置slave_parallel_workers 参数，让ssm和replication两个Schema拥有自己独立的SQL线程，这样也大大提高了从库的复制速度。
复制是MySQL数据库中经常使用的一个功能，它可以有效地保证主数据库的数据安全，并减轻主数据库的备份压力，以及分担主数据库的一部分查询压力。
原文链接：http://www.cnblogs.com/process-h/p/14109211.html
如果觉得本文对你有帮助，可以关注一下我公众号，回复关键字【面试】即可得到一份Java核心知识点整理与一份面试大礼包！另有更多技术干货文章以及相关资料共享，大家一起学习进步！
京公网安备 11010502036488号