MySQL Replication Filtering New Database and Table Filtering Solution

Introduction

As shown in the figure, it is necessary to add several databases to the original primary-replica replication filtering configuration for synchronization, and a most efficient way needs to be considered.

Here, efficiency mainly refers to time and space costs. How to complete the configuration change with less time and space?

Solution 1: Back up all the databases that need replication filtering at the same time.

• Advantages: The operation is simple. This solution is no different from the conventional backup and recovery. Just pay attention to backing up only the specified databases when performing the backup.
• Disadvantages: If the amount of data in the databases that are already being synchronized is too large, the recovery cost will be relatively high. As shown in the figure above, assuming that both DB1 and DB2 are several hundred gigabytes in size, the cost of performing a backup will be relatively high.

Solution 2: Only back up the newly added databases.

• Advantages: Only the newly added databases need to be backed up and recovered, and both the backup time and space occupation are relatively less.
• Disadvantages: Compared with Solution 1, the operation will be a bit more complicated, and a clear understanding of the processing procedures and details is required.

This article mainly presents an example of the actual operation process based on Solution 2.

How to do it?

Step overview

1. Replica - Stop the SQL replay thread of the replica.
2. Primary - Back up the DB4 and DB5 databases.
3. Replica - Replay transactions using UNTIL SQL_AFTER_GTIDS.
4. Replica - Restore the backups of DB4 and DB5.
5. Replica - Add and configure the replication filtering policies for DB4 and DB5.
6. Replica - Start the replication thread.

‍

1. Replica - Stop the SQL replay thread of the replica

Explanation: This step is used to ensure that the IO thread of the replica continues to receive the newly added logs from the master, but without replaying them. The purpose is to avoid the situation where the GTIDs recorded in the backup have already been applied on the replica when the master is backing up the newly added databases, which may lead to data duplication when restoring the backup. For example:

1. At 00:00:00 on October 1, 2024, the replica synchronized to GTID: 1 - 100.
2. At 00:01:00 on October 1, 2024, the master starts to back up, and the GTIDs recorded in the backup are 1 - 110.
3. At 00:02:00 on October 1, 2024, the replica synchronized to GTID: 1 - 150.
4. At 00:10:00 on October 1, 2020, the replica restores the backup and resets the GTIDs to the ones recorded in the backup (i.e., 1 - 110), which is abnormal.

In fact, the replica has already synchronized to GTIDs above 1 - 150, but is then restored to 1 - 110. An error will be reported when starting the replication.

For this step, you just need to execute the following commands on the replica.

Command

MySQL> STOP SLAVE SQL_THREAD;

2. Primary - Back up the DB4 and DB5 databases

Explanation: For this step, only back up the newly added databases for replication filtering. It should be noted that if you use mysqldump to perform the backup, don't add set-gtid-purged=off. You need to let the GTID information be recorded in the backup file so that it can be specified when restoring.

Command

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. Replay transactions using UNTIL SQL_AFTER_GTIDS

Explanation: This step is a crucial operation. It is necessary to specify the UNTIL SQL_AFTER_GTIDS method for the SQL thread of the replica, so as to make the transaction replay of the replica catch up to the transaction point recorded in the backup file and keep the GTIDs for the replay on the replica consistent with those recorded in the backup. Only in this way can a normal connection be achieved.

Command

## 1. Obtain the GTID position information in the backup file (you can just get it from the end of 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 endpoint on the replica (Note: Here, only the specified GTID value 12756 needs to be set, rather than the continuous interval 1-12756 in the backup).
START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS='13fc4692-48d2-11ef-8a8f-02000aba382b:12756';

## 3. Continuously monitor the SQL thread restoration status until Executed_Gtid_Set becomes the specified value 12756.
MySQL> show slave status\G
  -- Replay Status During the Process
             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
            
MySQL> show slave status\G
    --  The status when SQL_AFTER_GTIDS is completed: (Slave_SQL_Running is NO, and the GTID endpoint is consistent with the endpoint in the backup file).
             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. Replica - Restore the backups of DB4 and DB5

Explanation: This step is the actual backup and restoration process. Just restore the corresponding databases directly. SET @@GLOBAL.GTID_PURGED will be executed automatically at the end of the restoration. After the restoration, conduct another round of confirmation.

‍

Note: When restoring here, an error may be reported for the final SET @@GLOBAL.GTID_PURGED. This can be ignored because the replica already has a GTID_EXECUTED value, and it will not have an impact.

Command

## data recovery
shell> mysql  < db4_db5.sql

## This error can be ignored. However, other data errors cannot be ignored.
ERROR 1840 (HY000) at line 424: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

5. Replica - Add and configure the replication filtering policies for DB4 and DB5

Command

## Dynamically configure the replication filtering policy.
MySQL> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%','db2.%','db4.%','db5.%');

## Persist the configuration to the configuration file.
shell> cat /data/mysql/3306/my.cnf.3306
...
## Replication Filter Rules
replicate_wild_do_table = db1.%
replicate_wild_do_table = db2.%
replicate_wild_do_table = db4.%
replicate_wild_do_table = db5.%
...

6. Replica - Start the replication thread

Command

START SLAVE;
SHOW SLAVE STATUS\G

Supplementary explanation

Backup methods

In the document, the backup and recovery examples are done using mysqldump. In practice, Xtrabackup can also be used. However, when using Xtrabackup for recovery, it needs to be combined with the transportable tablespace feature for the recovery.

Table-level replication filtering

In the document, only the addition of database-level replication filtering is demonstrated. The operation method for table-level is basically the same, except that for different tables under different databases, they cannot be backed up simultaneously (mysqldump does not support this).

‍