生產環境MySQL 5.7從庫複製報錯
檢視錯誤日誌

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
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
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
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

報錯原因：
MySQL 5.7開啟了多執行緒複製，與多執行緒複製相關的有一個引數slave_pending_jobs_size_max，錯誤日誌中提示這個引數設定的小。這個引數在MySQL 5.6以後引入。
對於多執行緒複製從庫，這個引數設定從庫工作佇列中沒有應用的事件快取。預設單位是位元組。如果沒有開啟多執行緒複製，則這個引數沒有作用。
這個引數的最小值是1024位元組，預設值是16MB。最大值是18446744073709551615。
注意，這個引數的值不能超過主庫的max_allowed_packet引數的值，否則從庫的工作佇列可能會變滿。

解決方法：
將這個引數設定的大一些，並重啟複製；也可以關閉多執行緒複製。

mysql> set global slave_pending_jobs_size_max=29999104;
mysql> show global variables like 'slave_pending_jobs_size_max';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| slave_pending_jobs_size_max | 29999104 |
+-----------------------------+----------+
1 row in set (0.00 sec)
mysql> show global variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 4 |
+------------------------+-------+
1 row in set (0.00 sec)

MySQL 5.7從庫報錯exceeds of slave_pending_jobs_size_max. Error_code: 1864

相關文章