An Unique feature in MySQL is Replication. In MySQL replication the changes are recorded in Binary log of master irrespective of the Engines. The Binlogs are then applied on the slave.

What is Replication filters in MySQL?

• This filters are used to control which databases and tables has to be replicated to slave.
• The filters can be applied master at the time of binlog creation. It is not advisable as it prevent point in time recovery from bin logs.
• The filters can also be applied on the slave side too.

In MySQL 5.7 They introduce a new replication filters and how to use this filters.( See Online-Filters )

ONLINE FILTERS:

• Filter replication events is also known as Partial Replication.
• The filtering events on the master server binlog-do-db and binlog-ignore-db is traditional way and dangerous.
• In MySQL 5.7 adding/changing replication filter rules becomes an online operation on slave without restarting MySQL server, using the CHANGE REPLICATION FILTER command.

Currently, the slave server runs without filtered replication

The master database server contains 4 databases (Test, World, Employee, School). Let’s replicate only two databases (Test, Employee) out of the four databases.

–Replicate-do-db

The slave threads will be restricted to replicating the specified databases.

Example 1

Change Replication Filter replicate_do_db = (test,employee);

Output

NOTE:

This operation cannot be performed with a running slave sql thread. so stop the sql Thread.

If you want to remove the Replicate_do_db filter you just empty the value of filter name.

Example 2

Change Replication Filter replicate_do_db = ( );

–Replicate-ignore-db

The Slave threads will be prohibited from replicating the specified databases

Example 3

Change Replication Filter replicate_ignore_db = (employee,school);

Statement-based replication. The USE statement causes employee is the default database. Thus the replicate-ignore-db option matches,and the insert statement is ignored. The table options are not checked.( The slave SQL thread not to replicate any statement where the default database.)

Row-based replication. The default database has no effect on how the slave reads database options when using row-based replication. Thus, the use statement makes no difference in how the replicate-ignore-db  option is handled, the database specified by this option does not match the database where the insert statement changes data, so the slave proceeds to check the table options. The table specified by replicate-do-table matches the table to be updated.

Output

–Replicate-wild-do-table

• The Slave threads will be restricted to replicating tables that match the specified wildcard pattern.
• In This option applies to tables, views, and triggers. It does not apply to stored procedures and functions, or events.
• We can include literal wildcard characters in the database or table name patterns, escape them with a backslash.

Example 4

Change Replication Filter replicate_wild_do_table = (‘world.Country%,employee.sch%’);

Output:

–Replicate-wild-ignore-table

• The Slave threads will be prohibited from replicating tables that match the specified wildcard pattern.

Example 5

Change Replication Filter replicate_wild_ignore_table = (‘employee.cont%’);

Output:

–Replicate-rewrite-db

• The slave to create a replication filter that translates the default database to to_name if it was from_name on the master. Only statements involving tables are affected  if from_name is the default database on the master.
• The server uses the first one with a from_name value that matches. The database name translation is done before the replication rules are tested.

Example

Change replication filter replicate_rewrite_db = ((employee,test));

Output:

These replication features avoid the downtime to restart MySQL server while applying the filters. The following are the list of Dynamic filters available.

• REPLICATE_DO_DB
• REPLICATE_IGNORE_DB
• REPLICATE_DO_TABLE
• REPLICATE_IGNORE_TABLE
• REPLICATE_WILD_DO_TABLE
• REPLICATE_WILD_IGNORE_TABLE
• REPLICATE_REWRITE_DB

Note :

Replication filters will behave differently based on your Binlog format .