MySQL Replication Filter Expansion: Adding New Databases with Efficient Backup and Restore

1 Technical Background

The original master‑slave replication filter needs to be extended to synchronize several new databases, and the solution must minimize both time and storage costs.

2 Technical Solution

Option 1: Backup All Filtered Databases

Pros: Simple operation, identical to regular backup‑restore; just back up the selected databases.

Cons: High cost when existing databases are large (hundreds of GB), making backup and recovery expensive.

Option 2: Backup Only the New Databases

Pros: Only the newly added databases are backed up, reducing backup time and storage usage.

Cons: Slightly more complex workflow that requires a clear understanding of the process.

The article adopts Option 2 for the following demonstration.

3 Operation Process

Process Overview

Slave – Stop the SQL replay thread.

Master – Backup the new databases DB4 and DB5.

Slave – Replay transactions up to a specific GTID using UNTIL SQL_AFTER_GTIDS .

Slave – Restore the DB4 and DB5 backups.

Slave – Add replication filter rules for DB4 and DB5.

Slave – Restart the replication threads.

1. Slave – Stop the SQL Replay Thread

This step keeps the IO thread receiving new binlog events while preventing the SQL thread from applying them, avoiding GTID duplication when the master backup is taken. 2024‑10‑01 00:00:00 Slave synced GTID:1‑100 2024‑10‑01 00:01:00 Master starts backup, GTID in backup:1‑110 2024‑10‑01 00:02:00 Slave syncs GTID:1‑150 2024‑10‑01 00:10:00 Slave restores backup and resets GTID to 1‑110 → replication error because slave had already advanced beyond that point.

Command to stop the SQL thread:

MySQL> STOP SLAVE SQL_THREAD;

2. Master – Backup DB4 and DB5

When using mysqldump , do not set set-gtid-purged=off so that GTID information is retained in the dump file.

Backup command example:

shell> mysqldump -h127.0.0.1 -uzhenxing -P3306 -pxxxxx \
    --hex-blob --triggers --routines --events --flush-logs \
    --single-transaction --databases db4 db5 > db4_db5.sql

3. Slave – Replay Up to a Specific GTID

This critical step aligns the slave’s SQL thread with the GTID recorded in the backup, ensuring a seamless continuation after restoration.

Commands:

## 1. Get GTID from backup file (tail the file)
shell> tail -n100 db4_db5.sql | grep GTID_PURGED
-- GTID state at the end of the backup
SET @@GLOBAL.GTID_PURGED='13fc4692-48d2-11ef-8a8f-02000aba382b:1-12756';

## 2. Set the replay stop point (only the ending GTID is needed)
START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS='13fc4692-48d2-11ef-8a8f-02000aba382b:12756';

## 3. Monitor until Executed_Gtid_Set reaches 12756
MySQL> SHOW SLAVE STATUS\G
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   Replicate_Wild_Do_Table: db1.%,db2.%
   Until_Condition: SQL_AFTER_GTIDS
   Executed_Gtid_Set: 13fc4692-48d2-11ef-8a8f-02000aba382b:1-11200

# After reaching the target GTID
MySQL> SHOW SLAVE STATUS\G
   Slave_IO_Running: Yes
   Slave_SQL_Running: No
   Replicate_Wild_Do_Table: db1.%,db2.%
   Until_Condition: SQL_AFTER_GTIDS
   Executed_Gtid_Set: 13fc4692-48d2-11ef-8a8f-02000aba382b:1-12756

4. Slave – Restore DB4 and DB5 Backups

The restoration runs the dump file; the final SET @@GLOBAL.GTID_PURGED may error if GTID_EXECUTED is already present, which can be ignored.

Restore command:

## Data restoration
shell> mysql < db4_db5.sql

## Possible harmless error
ERROR 1840 (HY000) at line 424: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

5. Slave – Add Replication Filter Rules for DB4 and DB5

## Dynamic filter configuration
MySQL> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%','db2.%','db4.%','db5.%');

## Persist to my.cnf
replicate_wild_do_table = db1.%
replicate_wild_do_table = db2.%
replicate_wild_do_table = db4.%
replicate_wild_do_table = db5.%

6. Slave – Start Replication Threads

START SLAVE;
SHOW SLAVE STATUS\G

4 Additional Notes

Backup Method

The examples use mysqldump ; Xtrabackup can also be used but requires handling of transportable tablespaces during recovery.

Table‑Level Replication Filter

The article demonstrates database‑level filter addition; table‑level filters follow the same syntax but mysqldump does not support selective table backup.

Keywords: MySQL, master‑slave replication, backup, restore, GTID, replication filter.