主從複製延遲推薦解決方案
如何提高複製的效能
現象描述
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資料庫中經常使用的一個功能，它可以有效地保證主資料庫的資料安全，並減輕主資料庫的備份壓力，以及分擔主資料庫的一部分查詢壓力。
-------學自《深入淺出MySQL》