How to Enable Binlog Compression in MySQL

When using MySQL with ROW‑based binlog format, the binlog files can grow very large, consuming significant network bandwidth during replication; a single large transaction may produce a binlog of several gigabytes, stressing the master‑to‑slave link.

How to Enable Binlog Compression

The slave_compressed_protocol system variable controls whether the replication protocol uses compression. Enabling it can alleviate bandwidth pressure caused by large binlog transfers.

# Master & Slave
set global slave_compressed_protocol = ON ;

stop slave io_thread;start slave io_thread;
# Changing the parameter requires restarting replication for it to take effect.

Compression Effectiveness

To verify the benefit, a benchmark was performed using a sysbench workload that populates ten tables with 500,000 rows each, using 128 threads.

socket='/data/mysql/data/7777/mysqld.sock'
user='root'
password='123'
db='lubao7hao'
table_num='10'
table_size='500000'
thread_num='128'

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
  --mysql-socket=${socket} --mysql-user=${user} --mysql-password=${password} \
  --mysql-db=${db} --oltp-tables-count=${table_num} \
  --oltp-table-size=${table_size} --report-interval=10 \
  --rand-init=on --max-requests=0 --oltp-test-mode=nontrx \
  --oltp-nontrx-mode=select --oltp-read-only=off \
  --max-time=360000 --num-threads=${thread_num} prepare

Monitoring Results

Performance metrics were captured for both the uncompressed and compressed scenarios on the master and slave servers. Screenshots of CPU usage and network I/O were taken for each state (closed and opened compression).

Conclusion

Master CPU

Slave CPU

Master Send

Master Recv

Slave Send

Slave Recv

Uncompressed

4.24%

2.7%

14.50MB/s

7.31MB

35KB/s

14.60MB/s

Compressed

8.50%

3.18%

6.68MB/s

7.27MB/s

35KB/s

6.61MB/s

1. Enabling slave_compressed_protocol cuts network traffic roughly in half, achieving about a 50% compression ratio.

2. Compression does increase CPU usage; if the server is already CPU‑bound, enabling it may not be advisable.

Precautions

In MySQL versions prior to 5.7.21 or 8.0.4, using slave_compressed_protocol=ON together with semi‑synchronous replication can trigger a bug that stalls writes on the master (see MySQL Bug 86230 ).