4503.com,从库查看情形:

从库设置

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

#在三十二线程复制时,在队列中Pending的事件所据有的最大内部存款和储蓄器,默以为16M,假使内部存款和储蓄器富余,大概延缓非常大时,可以相符调大;注意

mysqlstop slave;mysqlset global slave_pending_jobs_size_max=20000000;mysql start slave;

这些值要比主库的max_allowed_packet大

上述所述是笔者给大家介绍的Mysql 1864
主从错误解决方式,希望对我们享有利于,假使我们有别的疑问请给本身留言,笔者会及时还原我们的。在这里也极度多谢咱们对台本之家网址的支撑!

mysql update erp_mkpf set usnam='测试1864错误';ERROR 1197 (HY000):Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage; increase this mysqld variable and try againmysql set global max_binlog_cache_size=8388608000000;Query OK, 0 rowsaffected (0.00 sec)mysql begin;Query OK, 0 rowsaffected (0.00 sec)mysql update erp_mkpf set usnam='测试1864错误';Query OK, 70466 rowsaffected (0.38 sec)Rows matched:70466 Changed: 70466 Warnings: 0mysql commit;Query OK, 0 rowsaffected (0.08 sec)

从字面意思看了大器晚成晃是因为slave_pending_jobs_size_max默许值为16777216,不过slave选拔到的slave_pending_jobs_size_max为17085453;

从库进行如下SQL

mysql show slavestatus\GLast_SQL_Errno: 1864Last_SQL_Error: Cannot scheduleevent Update_rows, relay-log name ./HE1-relay-bin.000005, position 494 toWorker thread because its size 8200 exceeds 1024 ofslave_pending_jobs_size_max.

技术方案

主库操作:

故障模拟: