查看错误日志
-
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)