Replication filters allow users to configure replication slaves to intentionally skip certain events.
MariaDB provides options that can be used on a replication master to restrict local changes to specific databases from getting written to the binary log, which also determines whether any replication slaves replicate those changes.
The following options are available, and they are evaluated in the order that they are listed below:
binlog_do_dbThe binlog_do_db option allows you to configure a replication master to write statements and transactions affecting databases that match a specified name into its binary log. Since the filtered statements or transactions will not be present in the binary log, its replication slaves will not be able to replicate them.
This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
This option can not be set dynamically.
When setting it on the command-line or in a server option group in an option file, 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:
[mariadb] ... binlog_do_db=db1 binlog_do_db=db2
This will tell the master to do the following:
binlog_ignore_dbThe binlog_ignore_db option allows you to configure a replication master to not write statements and transactions affecting databases that match a specified name into its binary log. Since the filtered statements or transactions will not be present in the binary log, its replication slaves will not be able to replicate them.
This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
This option can not be set dynamically.
When setting it on the command-line or in a server option group in an option file, 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:
[mariadb] ... binlog_ignore_db=db1 binlog_ignore_db=db2
This will tell the master to do the following:
The binlog_ignore_db option is effectively ignored if the binlog_do_db 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 replication slave to filter events replicated in the binary log.
The following options and system variables are available, and they are evaluated in the order that they are listed below:
replicate_rewrite_dbThe replicate_rewrite_db option allows you to configure a replication slave to rewrite database names. It uses the format master_database->slave_database. If a slave encounters a binary log event in which the default database (i.e. the one selected by the USE statement) is master_database, then the slave will apply the event in slave_database instead.
This option will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
This option only affects statements that involve tables. This option does not affect statements involving the database itself, such as CREATE DATABASE, ALTER DATABASE, and DROP DATABASE.
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 replicate_do_table.
This option can not be set dynamically.
When setting it on the command-line or in a server option group in an option file, 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:
[mariadb] ... replicate_rewrite_db=db1->db3 replicate_rewrite_db=db2->db4
This will tell the slave to do the following:
See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.
replicate_do_dbThe replicate_do_db system variable allows you to configure a replication slave to apply statements and transactions affecting databases that match a specified name.
This system variable will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
When setting it dynamically with SET GLOBAL, 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 option group in an option file when the server is started.
When setting it dynamically, the slave threads must be stopped. For example:
STOP SLAVE; SET GLOBAL replicate_do_db='db1,db2'; START SLAVE;
When setting it on the command-line or in a server option group in an option file, 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:
[mariadb] ... replicate_do_db=db1 replicate_do_db=db2
This will tell the slave to do the following:
See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.
replicate_ignore_dbThe replicate_ignore_db system variable allows you to configure a replication slave to ignore statements and transactions affecting databases that match a specified name.
This system variable will not work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
When setting it dynamically with SET GLOBAL, 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 option group in an option file when the server is started.
When setting it dynamically, the slave threads must be stopped. For example:
STOP SLAVE; SET GLOBAL replicate_ignore_db='db1,db2'; START SLAVE;
When setting it on the command-line or in a server option group in an option file, 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:
[mariadb] ... replicate_ignore_db=db1 replicate_ignore_db=db2
This will tell the slave to do the following:
The replicate_ignore_db system variable is effectively ignored if the replicate_do_db system variable is set, so those two system variables should not be set together.
See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.
replicate_do_tableThe replicate_do_table system variable allows you to configure a replication slave 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 statement-based logging. See the Statement-Based Logging section for more information.
When setting it dynamically with SET GLOBAL, 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 option group in an option file when the server is started.
When setting it dynamically, the slave threads must be stopped. For example:
STOP SLAVE; SET GLOBAL replicate_do_table='db1.tab,db2.tab'; START SLAVE;
When setting it on the command-line or in a server option group in an option file, 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:
[mariadb] ... replicate_do_table=db1.tab replicate_do_table=db2.tab
This will tell the slave to do the following:
See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.
replicate_ignore_tableThe replicate_ignore_table system variable allows you to configure a replication slave 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 statement-based logging. See the Statement-Based Logging section for more information.
When setting it dynamically with SET GLOBAL, 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 option group in an option file when the server is started.
When setting it dynamically, the slave threads must be stopped. For example:
STOP SLAVE; SET GLOBAL replicate_ignore_table='db1.tab,db2.tab'; START SLAVE;
When setting it on the command-line or in a server option group in an option file, 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:
[mariadb] ... replicate_ignore_table=db1.tab replicate_ignore_table=db2.tab
This will tell the slave to do the following:
The replicate_ignore_table system variable is effectively ignored if either the replicate_do_table system variable or the replicate_wild_do_table system variable is set, so the replicate_ignore_table system variable should not be used with those two system variables.
See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.
replicate_wild_do_tableThe replicate_wild_do_table system variable allows you to configure a replication slave to apply statements and transactions that affect tables that match a specified wildcard pattern.
The wildcard pattern uses the same semantics as the LIKE operator. This means that the the following characters have a special meaning:
_ - The _ character matches any single character. % - The % character matches zero or more characters. \ - The \ character is used to escape the other special characters in cases where you need the literal character. This system variable will work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
The system variable does filter databases, tables, views and triggers.
The system variable does not filter stored procedures, stored functions, and events. The replicate_do_db 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 CREATE DATABASE, ALTER DATABASE and DROP DATABASE.
When setting it dynamically with SET GLOBAL, 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 option group in an option file when the server is started.
When setting it dynamically, the slave threads must be stopped. For example:
STOP SLAVE; SET GLOBAL replicate_wild_do_table='db%.tab%,app1.%'; START SLAVE;
When setting it on the command-line or in a server option group in an option file, 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:
[mariadb] ... replicate_wild_do_table=db%.tab% replicate_wild_do_table=app1.%
This will tell the slave to do the following:
See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.
replicate_wild_ignore_tableThe replicate_wild_ignore_table system variable allows you to configure a replication slave to ignore statements and transactions that affect tables that match a specified wildcard pattern.
The wildcard pattern uses the same semantics as the LIKE operator. This means that the the following characters have a special meaning:
_ - The _ character matches any single character. % - The % character matches zero or more characters. \ - The \ character is used to escape the other special characters in cases where you need the literal character. This system variable will work with cross-database updates with statement-based logging. See the Statement-Based Logging section for more information.
The system variable does filter databases, tables, views and triggers.
The system variable does not filter stored procedures, stored functions, and events. The replicate_ignore_db 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 CREATE DATABASE, ALTER DATABASE and DROP DATABASE.
When setting it dynamically with SET GLOBAL, 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 option group in an option file when the server is started.
When setting it dynamically, the slave threads must be stopped. For example:
STOP SLAVE; SET GLOBAL replicate_wild_ignore_table='db%.tab%,app1.%'; START SLAVE;
When setting it on the command-line or in a server option group in an option file, 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:
[mariadb] ... replicate_wild_ignore_table=db%.tab% replicate_wild_ignore_table=app1.%
This will tell the slave to do the following:
The replicate_ignore_table system variable is effectively ignored if either the replicate_do_table system variable or the replicate_wild_do_table system variable is set, so the replicate_ignore_table system variable should not be used with those two system variables.
See Configuring Replication Filter Options with Multi-Source Replication for how to configure this system variable with multi-source replication.
How you configure replication filters with multi-source replication depends on whether you are configuring them dynamically or whether you are configuring them in a server option group in an option file.
The usage of dynamic replication filters changes somewhat when multi-source replication 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.
One way to change a replication filter for a multi-source connection is to explicitly specify the name when changing the filter. For example:
STOP SLAVE 'gandalf'; SET GLOBAL gandalf.replicate_do_table='database1.table1,database1.table2,database1.table3'; START SLAVE 'gandalf';
Alternatively, the default connection can be changed by setting the default_master_connection system variable, and then the replication filter can be changed in the usual fashion. For example:
SET default_master_connection = 'gandalf'; STOP SLAVE; SET GLOBAL replicate_do_table='database1.table1,database1.table2,database1.table3'; START SLAVE;
If you are using multi-source replication and if you would like to make this filter persist server restarts by adding it to a server option group in an option file, then the option file can also include the connection name that each filter would apply to. For example:
[mariadb] ... gandalf.replicate_do_db=database1 saruman.replicate_do_db=database2
The CHANGE MASTER statement has a few options that can be used to filter certain types of binary log events.
IGNORE_SERVER_IDSThe IGNORE_SERVER_IDS option for CHANGE MASTER can be used to configure a replication slave to ignore binary log events that originated from certain servers. Filtered binary log events will not get logged to the slave’s relay log, and they will not be applied by the slave.
DO_DOMAIN_IDSThe DO_DOMAIN_IDS option for CHANGE MASTER can be used to configure a replication slave to only apply binary log events if the transaction's GTID is in a specific gtid_domain_id value. Filtered binary log events will not get logged to the slave’s relay log, and they will not be applied by the slave.
IGNORE_DOMAIN_IDSThe IGNORE_DOMAIN_IDS option for CHANGE MASTER can be used to configure a replication slave to ignore binary log events if the transaction's GTID is in a specific gtid_domain_id value. Filtered binary log events will not get logged to the slave’s relay log, and they will not be applied by the slave.
The way that a replication filter is interpreted can depend on the binary log format.
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 USE 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 USE statement). This applies to the following replication filters:
This means that cross-database updates not work with replication filters and statement-based binary logging. For example, if replicate_do_table=db2.tab were set, then the following would not replicate with statement-based binary logging:
USE db1; INSERT INTO db2.tab VALUES (1);
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 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 binary log in statement-based format, even when the binlog_format system variable is set to ROW. This means that the notes mentioned in Statement-Based Logging always apply to DDL.
When using Galera cluster, replication filters should be used with caution. See Configuring MariaDB Galera Cluster: Replication Filters for more details.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/replication-filters/