Learn how to filter specific databases or tables from being replicated. This guide covers configuration options to replicate only the data you need on specific replicas.
Replication filters allow users to configure replicas to intentionally skip certain events.
MariaDB provides options that can be used on a to restrict local changes to specific databases from getting written to the , which also determines whether any replicas replicate those changes.
The following options are available, and they are evaluated in the order that they are listed below. If there are conflicting settings, binlog_do_db prevails. Before , they are only available as options; from they are also available as system variables.
binlog_do_dbThe option allows you to configure a to write statements and transactions affecting databases that match a specified name into its . Since the filtered statements or transactions will not be present in the , its replicas will not be able to replicate them.
This option will not work with cross-database updates with . See the section for more information.
This option cannot be set dynamically.
When setting it on the command-line or in a server in an , the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times. For example:
This will tell the primary to do the following:
Write statements and transactions affecting the database named db1 into the .
Write statements and transactions affecting the database named db2 into the .
Don't write statements and transactions affecting any other databases into the .
binlog_ignore_dbThe option allows you to configure a to not write statements and transactions affecting databases that match a specified name into its . Since the filtered statements or transactions will not be present in the , its replicas will not be able to replicate them.
This option will not work with cross-database updates with . See the section for more information.
This option cannot be set dynamically.
When setting it on the command-line or in a server in an , the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times. For example:
This will tell the primary to do the following:
Don't write statements and transactions affecting the database named db1 into the .
Don't write statements and transactions affecting the database named db2 into the .
Write statements and transactions affecting any other databases into the .
The option is effectively ignored if the option is set, so those two options should not be set together.
MariaDB provides options and system variables that can be used on used on a to filter events replicated in the .
The following options and system variables are available, and they are evaluated in the order that they are listed below. If there are conflicting settings, the respective replicate_do_ prevails.
replicate_rewrite_dbThe option (and, from , system variable), allows you to configure a to rewrite database names. It uses the format primary_database->replica_database. If a replica encounters a event in which the default database (i.e. the one selected by the statement) is primary_database, then the replica will apply the event in replica_database instead.
This option will not work with cross-database updates with . See the section for more information.
This option only affects statements that involve tables. This option does not affect statements involving the database itself, such as , , and .
This option's rewrites are evaluated before any other replication filters configured by the replicate_* system variables.
Statements that use table names qualified with database names do not work with other replication filters such as .
Until , this option could not be set dynamically.
When setting it on the command-line or in a server in an , the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times. For example:
This will tell the replica to do the following:
If a event is encountered in which the default database was db1, then apply the event in db3 instead.
If a event is encountered in which the default database was db2, then apply the event in db4 instead.
See for how to configure this system variable with .
replicate_do_dbThe system variable allows you to configure a to apply statements and transactions affecting databases that match a specified name.
This system variable will not work with cross-database updates with or when using and the statement is logged statement based. For statement-based replication, only the default database (that is, the one selected by USE) is considered, not any explicitly mentioned tables in the query. See the section for more information.
When setting it dynamically with , the system variable accepts a comma-separated list of filters.
When setting it dynamically, it is not possible to specify database names that contain commas. If you need to specify database names that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server in an when the server is .
When setting it dynamically, the must be stopped. For example:
When setting it on the command-line or in a server in an , the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:
This will tell the replica to do the following:
Replicate statements and transactions affecting the database named db1.
Replicate statements and transactions affecting the database named db2.
Ignore statements and transactions affecting any other databases.
See for how to configure this system variable with .
replicate_ignore_dbThe system variable allows you to configure a to ignore statements and transactions affecting databases that match a specified name.
This system variable will not work with cross-database updates with or when using and the statement is logged statement based. For statement-based replication, only the default database (that is, the one selected by USE) is considered, not any explicitly mentioned tables in the query. See the section for more information.
When setting it dynamically with , the system variable accepts a comma-separated list of filters.
When setting it dynamically, it is not possible to specify database names that contain commas. If you need to specify names or patterns that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server in an when the server is .
When setting it dynamically, the must be stopped. For example:
When setting it on the command-line or in a server in an , the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:
This will tell the replica to do the following:
Ignore statements and transactions affecting databases named db1.
Ignore statements and transactions affecting databases named db2.
Replicate statements and transactions affecting any other databases.
The system variable is effectively ignored if the system variable is set, so those two system variables should not be set together.
See for how to configure this system variable with .
replicate_do_tableThe system variable allows you to configure a to apply statements and transactions that affect tables that match a specified name. The table name is specified in the format: dbname.tablename.
This system variable will not work with cross-database updates with . See the section for more information.
This option only affects statements that involve tables. This option does not affect statements involving the database itself, such as , , and .
When setting it dynamically with , the system variable accepts a comma-separated list of filters.
When setting it dynamically, it is not possible to specify database or table names or patterns that contain commas. If you need to specify database or table names that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server in an when the server is .
When setting it dynamically, the must be stopped. For example:
When setting it on the command-line or in a server in an , the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:
This will tell the replica to do the following:
Replicate statements and transactions affecting tables in databases named db1 and which are named tab.
Replicate statements and transactions affecting tables in databases named db2 and which are named tab.
Ignore statements and transactions affecting any other tables.
See for how to configure this system variable with .
replicate_ignore_tableThe system variable allows you to configure a to ignore statements and transactions that affect tables that match a specified name. The table name is specified in the format: dbname.tablename.
This system variable will not work with cross-database updates with . See the section for more information.
When setting it dynamically with , the system variable accepts a comma-separated list of filters.
When setting it dynamically, it is not possible to specify database or table names that contain commas. If you need to specify database or table names that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server in an when the server is .
When setting it dynamically, the must be stopped. For example:
When setting it on the command-line or in a server in an , the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:
This will tell the replica to do the following:
Ignore statements and transactions affecting tables in databases named db1 and which are named tab.
Ignore statements and transactions affecting tables in databases named db2 and which are named tab.
Replicate statements and transactions affecting any other tables.
The system variable is effectively ignored if either the system variable or the system variable is set, so the system variable should not be used with those two system variables.
See for how to configure this system variable with .
replicate_wild_do_tableThe system variable allows you to configure a to apply statements and transactions that affect tables that match a specified wildcard pattern.
The wildcard pattern uses the same semantics as the operator. This means that the following characters have a special meaning:
_ - The _ character matches any single character.
% - The % character matches zero or more characters.
\ - The \
This system variable will work with cross-database updates with . See the section for more information.
The system variable does filter databases, tables, and .
The system variable does not filter , , and . The system variable will need to be used to filter those.
If the table name pattern for a filter is just specified as %, then all tables in the database will be matched. In this case, the filter will also affect certain database-level statements, such as , and .
When setting it dynamically with , the system variable accepts a comma-separated list of filters.
When setting it dynamically, it is not possible to specify database or table names or patterns that contain commas. If you need to specify database or table names or patterns that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server in an when the server is .
When setting it dynamically, the must be stopped. For example:
When setting it on the command-line or in a server in an , the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:
This will tell the replica to do the following:
Replicate statements and transactions affecting tables in databases that start with db and whose table names start with tab.
Replicate statements and transactions affecting the database named app1.
Ignore statements and transactions affecting any other tables and databases.
See for how to configure this system variable with .
replicate_wild_ignore_tableThe system variable allows you to configure a to ignore statements and transactions that affect tables that match a specified wildcard pattern.
The wildcard pattern uses the same semantics as the operator. This means that the following characters have a special meaning:
_ - The _ character matches any single character.
% - The % character matches zero or more characters.
\ - The \
This system variable will work with cross-database updates with . See the section for more information.
The system variable does filter databases, tables, and .
The system variable does not filter , , and . The system variable will need to be used to filter those.
If the table name pattern for a filter is just specified as %, then all tables in the database will be matched. In this case, the filter will also affect certain database-level statements, such as , and .
When setting it dynamically with , the system variable accepts a comma-separated list of filters.
When setting it dynamically, it is not possible to specify database or table names or patterns that contain commas. If you need to specify database or table names or patterns that contain commas, then you will need to specify them by either providing the command-line options or configuring them in a server in an when the server is .
When setting it dynamically, the must be stopped. For example:
When setting it on the command-line or in a server in an , the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times. For example:
This will tell the replica to do the following:
Ignore statements and transactions affecting tables in databases that start with db and whose table names start with tab.
Ignore statements and transactions affecting all the tables in the database named app1.
Replicate statements and transactions affecting any other tables and databases.
The system variable is effectively ignored if either the system variable or the system variable is set, so the system variable should not be used with those two system variables.
See for how to configure this system variable with .
How you configure replication filters with depends on whether you are configuring them dynamically or whether you are configuring them in a server in an .
Setting Replication Filter Options Dynamically with Multi-Source Replication
The usage of dynamic replication filters changes somewhat when is in use. By default, the variables are addressed to the default connection, so in a multi-source environment, the required connection needs to be specified. There are two ways to do this.
Prefixing the Replication Filter Option with the Connection Name
One way to change a replication filter for a multi-source connection is to explicitly specify the name when changing the filter. For example:
Changing the Default Connection
Alternatively, the default connection can be changed by setting the system variable, and then the replication filter can be changed in the usual fashion. For example:
Setting Replication Filter Options in Option Files with Multi-Source Replication
If you are using and if you would like to make this filter persist server restarts by adding it to a server in an , then the option file can also include the connection name that each filter would apply to. For example:
The statement has a few options that can be used to filter certain types of events.
IGNORE_SERVER_IDSThe option for CHANGE MASTER can be used to configure a to ignore events that originated from certain servers. Filtered binary log events will not get logged to the replica’s , and they will not be applied by the replica.
DO_DOMAIN_IDSThe option for CHANGE MASTER can be used to configure a to only apply events if the transaction's is in a specific value. Filtered events will not get logged to the replica’s , and they will not be applied by the replica.
IGNORE_DOMAIN_IDSThe option for CHANGE MASTER can be used to configure a to ignore events if the transaction's is in a specific value. Filtered events will not get logged to the replica’s , and they will not be applied by the replica.
The way that a replication filter is interpreted can depend on the .
When an event is logged in its statement-based format, many replication filters that affect a database will test the filter against the default database (i.e. the one selected by the statement). This applies to the following replication filters:
When an event is logged in its statement-based format, many replication filters that affect a table will test the filter against the table in the default database (i.e. the one selected by the statement). This applies to the following replication filters:
This means that cross-database updates do not work with replication filters and statement-based binary logging. For example, if were set, then the following would not replicate with statement-based binary logging:
If you need to be able to support cross-database updates with replication filters and statement-based binary logging, then you should use the following replication filters:
When an event is logged in its row-based format, many replication filters that affect a database will test the filter against the database that is actually affected by the event.
Similarly, when an event is logged in its row-based format, many replication filters that affect a table will test the filter against the table in the database that is actually affected by the event.
This means that cross-database updates work with replication filters and statement-based binary logging.
Keep in mind that DDL statements are always logged to the in statement-based format, even when the system variable is set to ROW. This means that the notes mentioned in always apply to DDL.
When using Galera cluster, replication filters should be used with caution. See for more details.
This page is licensed: CC BY-SA / Gnu FDL
[mariadb]
...
binlog_do_db=db1
binlog_do_db=db2[mariadb]
...
binlog_ignore_db=db1
binlog_ignore_db=db2[mariadb]
...
replicate_rewrite_db=db1->db3
replicate_rewrite_db=db2->db4STOP SLAVE;
SET GLOBAL replicate_do_db='db1,db2';
START SLAVE;[mariadb]
...
replicate_do_db=db1
replicate_do_db=db2STOP SLAVE;
SET GLOBAL replicate_ignore_db='db1,db2';
START SLAVE;[mariadb]
...
replicate_ignore_db=db1
replicate_ignore_db=db2STOP SLAVE;
SET GLOBAL replicate_do_table='db1.tab,db2.tab';
START SLAVE;[mariadb]
...
replicate_do_table=db1.tab
replicate_do_table=db2.tabSTOP SLAVE;
SET GLOBAL replicate_ignore_table='db1.tab,db2.tab';
START SLAVE;[mariadb]
...
replicate_ignore_table=db1.tab
replicate_ignore_table=db2.tabSTOP SLAVE;
SET GLOBAL replicate_wild_do_table='db%.tab%,app1.%';
START SLAVE;[mariadb]
...
replicate_wild_do_table=db%.tab%
replicate_wild_do_table=app1.%STOP SLAVE;
SET GLOBAL replicate_wild_ignore_table='db%.tab%,app1.%';
START SLAVE;[mariadb]
...
replicate_wild_ignore_table=db%.tab%
replicate_wild_ignore_table=app1.%STOP SLAVE 'gandalf';
SET GLOBAL gandalf.replicate_do_table='database1.table1,database1.table2,database1.table3';
START SLAVE 'gandalf';SET default_master_connection = 'gandalf';
STOP SLAVE;
SET GLOBAL replicate_do_table='database1.table1,database1.table2,database1.table3';
START SLAVE;[mariadb]
...
gandalf.replicate_do_db=database1
saruman.replicate_do_db=database2USE db1;
INSERT INTO db2.tab VALUES (1);