MySQL Replication Filters: Pro Tips to Filter Databases and Tables to ‘Rescue’ Your Slave – ITFROMZERO

Table of Contents

Real-world Story: When ‘Copy-Pasting’ All Data Becomes a Disaster

Not long ago, I was tasked with setting up a Replica server for the Data Analyst team. At that time, the Master system was carrying nearly 1TB of data from 50 different microservices.

My biggest mistake was keeping the default configuration: whatever the Master had, the Slave got. After just two weeks, the Slave’s hard drive hit critical levels (95% disk usage) because it contained tons of junk data that the Analyst team never used. Worse, syncing massive log tables caused Seconds_Behind_Master to spike to 3,600 seconds. The Slave was constantly freezing due to I/O bottlenecks.

The breaking point was one night when I had to wake up at 3 AM to clean up the database because the Slave had completely frozen. The lesson was clear: in a production environment, syncing 100% of the data is sometimes a massive waste of resources. We need MySQL Replication Filters.

Why shouldn’t you sync everything?

Filtering synced data provides three key benefits:

Cost Savings: The Slave only keeps necessary data. You can reduce disk space from 1TB to 200GB, significantly saving on Cloud costs.
Security: Prevent sensitive information like user_passwords or credit_cards from appearing on reporting servers.
Query Speed: Reducing I/O load helps the Slave process SQL statements faster, bringing replication lag down to near zero.

Two Approaches: Filtering at the Master or Filtering at the Slave?

You have two choices, but be careful as each has its own ‘traps’.

1. Filtering at the Master (Binary Log Filters)

The Master will only record changes to specified databases in the Binlog via binlog-do-db or binlog-ignore-db.

# Configuration on Master (my.cnf)
[mysqld]
binlog-do-db=db_important

Warning: I recommend not using this method. MySQL filters based on the database currently being USEd. If you are USE db_other but UPDATE db_important.table, that change will be ignored. Consequently, the Slave will miss data without you ever knowing.

2. Filtering at the Slave (Replication Filters) – The Safest Way

The Master sends all logs, and the Slave chooses which ones to execute. This is safer because it doesn’t lose original data on the Master.

Parameters to remember:

replicate-do-db: Only sync this database.
replicate-wild-do-table: Sync by pattern (e.g., sales_%). This is the best choice.

Standard Configuration for Production Environments

Based on operational experience, I always prioritize wildcard filtering to avoid errors related to database context.

Step 1: Edit the Slave Configuration File

Open the my.cnf file on the Slave and add the following configuration:

[mysqld]
# Sync only the ecommerce database
replicate-wild-do-table=ecommerce.%

# Sync only report tables in the analytics database
replicate-wild-do-table=analytics.report_%

# Exclude temporary log tables
replicate-wild-ignore-table=ecommerce.temp_logs

Step 2: Apply Changes

You can restart MySQL to apply the configuration. However, to avoid downtime, perform it directly in the MySQL shell:

STOP SLAVE SQL_THREAD;
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('ecommerce.%', 'analytics.report_%');
START SLAVE SQL_THREAD;

Step 3: Verify the Results

Run the command SHOW SLAVE STATUS\G. Carefully check the Replicate_Wild_Do_Table line to ensure the filters are working as expected.

‘Hard-won’ Tips to Avoid Breaking the System

Prioritize Wildcards: Always use replicate-wild-do-table instead of replicate-do-db. It helps you avoid extremely annoying ‘cross-database update’ errors.
Manage Relay Logs: Even if the Slave filters out data, the Master still sends the entire log file. Configure relay_log_purge = 1. The Slave will automatically delete logs after processing, preventing disk space issues from temporary files.
Regular Monitoring: Use pt-table-checksum to check for data drift between Master and Slave. Don’t be complacent; sometimes your filter might miss a few important tables due to a typo in the pattern.

Mastering this filtering technique not only makes your system run smoother but also proves your system optimization skills to your colleagues. If your Slave is ‘gasping for air’ due to data overload, try applying this today!