Resolving MySQL Replication Failure Caused by max_binlog_cache_size Misconfiguration

In daily operations, unexpected online issues often stem from misconfigurations. Recently the DMP database operation platform reported a master‑slave replication error where the SQL thread was disconnected due to an abnormal parameter setting.

The alert indicated that the MySQL replica's SQL thread had stopped. Running show slave status\G confirmed the thread was halted. Further inspection with

select * from performance_schema.replication_applier_status_by_worker;

showed the error message about exceeding max_binlog_cache_size.

The error pointed to the max_binlog_cache_size variable being improperly set. The master had 10 GB while the replica only 10 MB, causing the replication failure.

According to MySQL documentation, binlog records all data‑modifying operations. In replication, the master sends its binlog to the slave. max_binlog_cache_size limits the total binlog cache for all clients; when a multi‑statement transaction exceeds this limit, the error occurs. The related binlog_cache_size allocates cache per client.

The mismatch was resolved by increasing the replica’s max_binlog_cache_size dynamically: mysql> set global max_binlog_cache_size=10240000000; After adjusting the value and restarting replication, the system returned to normal.

Proper configuration and timely adjustment of MySQL parameters are essential for system stability and performance; administrators should understand the semantics of replication‑related variables and keep them consistent across master and slave to avoid production incidents.