Replication Filters in MySQL an Overview

What are Replication Filters in MySQL?

A replication filter is used to filter out the necessary databases and tables that will be replicated in the replica.

The replication filter can be set either in my. cnf or the command line.

The change made in those databases will be logged in binary log files. The filter can be created in both source (using –binlog-* option)  and replica (using –replicate-* option)

Flow Diagram Of Replication Filter

Replication Filter types in MySQL?

  1. Binary log filter
    • Binlog_do_db
    • Binlog_ignore_db
  2. Replicate filter
    • Replicate_do_db
    • Replicate_ignore_db
    • Replicate_do_table
    • Replicate_ignore_table
    • Replicate_wild_ignore_table
    • Replicate_rewrite_db
  3. Multi-Source replication filter

Binary log Filter

      In the source server, Binary log filters are used to filter out the writes inside the binlog files. By mentioning the filters, we can control the writes in binlog files, which will be simultaneously replicated in the replica server as well.

i) Binlog_do_db

        By using the Binlog_do_db variable, the binary log will only log the changes made by the mentioned database. To specify more than one database, use this option multiple times, once for each database. In my. cnf file add the following line

        In the MySQL configuration file, add the

                  binlog_do_db=imdb

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      456 | imdb        |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

ii) Binlog_ignore_db

By using the Binlog_ignore_db variable, the binary log will ignore the changes made by the mentioned database. Changes made by those databases will not be logged in binlog files.

    In the MySQL configuration file, add the

                  binlog_ignore_db=imdb

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000006 |      156 |              | imdb             |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Replicate Filter

       Replication filter will be applied in the replica server, where you can restrict the replicating process in the replica using this replicating filter.

i) Replicate_do_db

In the MySQL configuration file, add the

     In replica, If you want to replicate the changes made by the specific database, then you can use Replicate_do_db.

                  replicate_do_db=imdb
mysql> show slave status\G
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.168.211
                 Master_User: repl_user
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: binlog.000005
         Read_Master_Log_Pos: 156
              Relay_Log_File: relay-log.000011
               Relay_Log_Pos: 365
       Relay_Master_Log_File: binlog.000005
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
             Replicate_Do_DB: imdb
         Replicate_Ignore_DB:
          Replicate_Do_Table:
      Replicate_Ignore_Table:

ii) Replicate_ignore_db

 In the replica server, If you want to ignore the changes made by the specific database then we can use the Replicate_ignore_db variable. This variable will replicate the changes made by all the other databases, except the specific one.

 In the MySQL configuration file, add the

                  replicate_ignore_db=imdb
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.168.211
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000006
          Read_Master_Log_Pos: 156
               Relay_Log_File: relay-log.000015
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: imdb
           Replicate_Do_Table:
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 

iii) Replicate_do_table

     In replica, If you want to replicate the changes made by the specific table inside a database, then you can use the Replicate_do_table variable.

 In the MySQL configuration file, add the

                  replicate_do_table= imdb.movies

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.168.211
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 659
               Relay_Log_File: relay-log.000018
                Relay_Log_Pos: 868
        Relay_Master_Log_File: binlog.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: imdb.movies
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table:

iv) Replicate_ignore_table

      In the replica server, If you want to ignore the changes made by a specific table inside a database, then you can use Replicate_ignore_table. This variable will replicate changes of all other tables in a database, except the mentioned one.

 In the MySQL configuration file, add the

                  replicate_Ignore_Table= imdb.actors

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.168.211
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 1517
               Relay_Log_File: relay-log.000020
                Relay_Log_Pos: 893
        Relay_Master_Log_File: binlog.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: imdb.actors
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 

v) Replicate_wild_do_table

The replica threads will be restricted to replicating tables that match the specified wildcard pattern. Patterns can contain the % and _ wildcard characters, which have the same meaning as for the LIKE pattern-matching operator. 

In the MySQL configuration file, add the following

                   replicate_wild_do_table= imdb.movies%

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.168.211
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 2089
               Relay_Log_File: relay-log.000022
                Relay_Log_Pos: 893
        Relay_Master_Log_File: binlog.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: imdb.movies%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2089
              Relay_Log_Space: 1096

vi) Replicate_wild_ignore_table

        The replica threads will be prohibited from replicating tables that match the specified wildcard pattern. Replica will not replicate the changes made by those tables that match the mentioned wildcard pattern.

 In the MySQL configuration file, add the

                  replicate_wild_ignore_table= imdb.movies%

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.168.211
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 3730
               Relay_Log_File: relay-log.000024
                Relay_Log_Pos: 1181
        Relay_Master_Log_File: binlog.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: imdb.movies%
                   Last_Errno: 0
                   Last_Error: 

vii) Replicate_rewrite_db

     The replica to create a replication filter that translates the default database to to_name if it was from_name on the source. Only statements involving tables are affected if from_name is the default database on the source.

  In the MySQL configuration file, add the

                  replicate_rewrite_db=imdb->imdb_full

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.168.211
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 4228
               Relay_Log_File: relay-log.000026
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4228
              Relay_Log_Space: 524
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 290a305f-a62f-11eb-8830-080027b81a94
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 290a305f-a62f-11eb-8830-080027b81a94:1-7
                Auto_Position: 0
         Replicate_Rewrite_DB: (imdb, imdb_full)

Multi-source replication Filter

In Multi-Source replication, a replica will have more than one source. Each channel will be created for every source. We can filter the replication process in replicas by applying the channel-based replication filters. If the identical database or table is present on multiple sources, and the replica wants to replicate it from one source, then a channel based replication filter helps a lot.

We have two source -source 1, source 2, and one replica 

      The following is an example of setting a channel-based replication filter for a specific channel. Here we have applied the filter for the channel – source 2. Here the selected database is imdb and the table chosen is movies.

 In the MySQL configuration file, add the

                  replicate_do_table=’source 2’:imdb.movies

mysql> stop slave for channel 'source 2';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(imdb.movies) FOR CHANNEL 'source 2';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave for channel 'source 2';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status for channel 'source 2'\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.168.198
                  Master_User: repl_user2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 4073
               Relay_Log_File: relay-log-master@00202.000009
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: imdb.movies
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4073
              Relay_Log_Space: 702
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 3
                  Master_UUID: 1c94c9b1-c117-11eb-937d-080027c46d28
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 290a305f-a62f-11eb-8830-080027b81a94:1-7
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: source 2
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 1
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)


Hope, this blog help you to know in detail about MySQL replication filters in details. For further reference on filters use the ref manual here.

One thought on “Replication Filters in MySQL an Overview

  1. Like a bad movie, replication filters never have a good ending in production. Remember to always test before releasing to production, and when you believe your test plan is complete, guess what? Testing never ends

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s