MySQL 5.7從庫報(bào)錯(cuò)exceeds of slave_pending_jobs_size_max. Error_code: 1864怎么辦

這篇文章主要介紹了MySQL 5.7從庫報(bào)錯(cuò)exceeds of slave_pending_jobs_size_max. Error_code: 1864怎么辦，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

生產(chǎn)環(huán)境MySQL 5.7從庫復(fù)制報(bào)錯(cuò)
查看錯(cuò)誤日志

1. 2018-04-02T00:16:27.388790Z 22595518 [Note] Slave SQL thread for channel 'master_182' initialized, starting replication in log 'mysql-bin.000183' at position 200105916, relay log '/opt/mysql01/db_log/mysqld-relay-bin-master_182.000480' position: 200106105
   

2. 2018-04-02T00:16:27.794506Z 22595518 [ERROR] Slave SQL for channel 'master_182': Cannot schedule event Update_rows_v1, relay-log name /opt/mysql01/db_log/mysqld-relay-bin-master_182.000480, position 200106867 to Worker thread because its size 28375738 exceeds 28375040 of slave_pending_jobs_size_max. Error_code: 1864
   

3. 2018-04-02T00:16:27.794526Z 22595518 [Warning] Slave: Cannot schedule event Update_rows_v1, relay-log name /opt/mysql01/db_log/mysqld-relay-bin-master_182.000480, position 200106867 to Worker thread because its size 28375738 exceeds 28375040 of slave_pending_jobs_size_max. Error_code: 1864
   

4. 2018-04-02T00:16:27.794532Z 22595518 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000183' position 200105916

報(bào)錯(cuò)原因：
MySQL 5.7開啟了多線程復(fù)制，與多線程復(fù)制相關(guān)的有一個(gè)參數(shù)slave_pending_jobs_size_max，錯(cuò)誤日志中提示這個(gè)參數(shù)設(shè)置的小。這個(gè)參數(shù)在MySQL 5.6以后引入。
對(duì)于多線程復(fù)制從庫，這個(gè)參數(shù)設(shè)置從庫工作隊(duì)列中沒有應(yīng)用的事件緩存。默認(rèn)單位是字節(jié)。如果沒有開啟多線程復(fù)制，則這個(gè)參數(shù)沒有作用。
這個(gè)參數(shù)的最小值是1024字節(jié)，默認(rèn)值是16MB。最大值是18446744073709551615。
注意，這個(gè)參數(shù)的值不能超過主庫的max_allowed_packet參數(shù)的值，否則從庫的工作隊(duì)列可能會(huì)變滿。

解決方法：
將這個(gè)參數(shù)設(shè)置的大一些，并重啟復(fù)制；也可以關(guān)閉多線程復(fù)制。

1. mysql> set global slave_pending_jobs_size_max=29999104;
   

2. 
   

3. mysql> show global variables like 'slave_pending_jobs_size_max';
   

4. +-----------------------------+----------+
   

5. | Variable_name | Value |
   

6. +-----------------------------+----------+
   

7. | slave_pending_jobs_size_max | 29999104 |
   

8. +-----------------------------+----------+
   

9. 1 row in set (0.00 sec)
   

10. 
    

11. mysql> show global variables like 'slave_parallel_workers';
    

12. +------------------------+-------+
    

13. | Variable_name | Value |
    

14. +------------------------+-------+
    

15. | slave_parallel_workers | 4 |
    

16. +------------------------+-------+
    

17. 1 row in set (0.00 sec)

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“MySQL 5.7從庫報(bào)錯(cuò)exceeds of slave_pending_jobs_size_max. Error_code: 1864怎么辦”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來學(xué)習(xí)!