How to Trim MySQL Replication Traffic with Binlog Filtering Parameters
This article explains how MySQL replication works by copying the master’s binlog to the slave and shows eight configuration parameters that let you filter databases and tables to reduce binlog volume, network load, and replication latency.
MySQL replication works by sending the master’s binary log (binlog) over the network with an I/O thread to the slave, where an SQL thread parses the events and applies them to the slave database.
The size of the binlog directly impacts the I/O thread and the network between master and slave.
While the amount of binlog generated cannot be changed—any query that modifies data creates an event—MySQL provides eight parameters that let you control which databases or tables are recorded or replicated, thereby reducing the amount of data transferred.
Binlog_Do_DB : specifies which databases (schemas) should be recorded in the binlog.
Binlog_Ignore_DB : specifies which databases should be excluded from the binlog.
Replicate_Do_DB : on the slave, lists databases to replicate; multiple databases are separated by commas.
Replicate_Ignore_DB : on the slave, lists databases to ignore.
Replicate_Do_Table : on the slave, lists tables to replicate.
Replicate_Ignore_Table : on the slave, lists tables to ignore.
Replicate_Wild_Do_Table : similar to Replicate_Do_Table but supports wildcard patterns.
Replicate_Wild_Ignore_Table : similar to Replicate_Ignore_Table but supports wildcard patterns.
The first two parameters are set on the master, while the remaining six are configured on the slave.
By using these parameters to filter the binlog, you can minimize the amount of data transferred from master to slave, reduce network traffic and I/O load, and lower the number of events the SQL thread must parse and apply, ultimately improving replication latency on the slave.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
Java High-Performance Architecture
Sharing Java development articles and resources, including SSM architecture and the Spring ecosystem (Spring Boot, Spring Cloud, MyBatis, Dubbo, Docker), Zookeeper, Redis, architecture design, microservices, message queues, Git, etc.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.