The risks of replication filters in MariaDB and MySQL

Last updated on 28 Aprile 2021

MariaDB and MySQL support what they calls replication filters. This feature allows us to determine which tables are replicated, and which are not. Filters can be set on a replica to filter-in tables and databases: the replica will not replicate everything from the master, but only the specified databases and tables. Or they can filter-out tables and databases: the specified databases and tables will be ignored, everything else will be replicated. Or we can use a combination of inclusive filters and exclusive filters. And we can filter specific databases, specific tables, or regular expressions for table names (for example, to include all the tables with a certain prefix).

A bit confusing but powerful. Unfortunately, this feature is also very dangerous, as it can easily cause inconsistencies (different data) between the master and the replica. This can happen if applications run queries in a wrong way, or if a DBA runs a migration without following certain rules.

Inconsistent data is per se bad news. But keep also in mind that, if MySQL detects an inconsistency, replication will break. MySQL detects inconsistencies, for example, and it tries to replicate a row deletion but the target row doesn’t exist anymore.

Before listing the risks themselves, let me clarify a couple of concepts that some of my readers may not familiar with.

Some MariaDB/MySQL concepts

Let me explain a couple of MySQL concepts first, because they’re important to understand the risks of replication filters.

I want this article to be understandable for non-expert MySQL users. But if you know everything about binary log formats and you know what default database means, just skip to How replication filters can cause inconsistencies and Binary log filters, below.

Statement replication

All the risks only affect the replication of events from the master, in the  STATEMENT  format. Events are logged as  STATEMENT  if:

• binlog_format = STATEMENT ;
• binlog_format = MIXED (for non-deterministic statements);
• If a user with the  SUPER  privilege changes the  binlog_format , maybe just temporarily and at session level;
  • This could also happen in a stored procedure created by a superuser;
• DDL statements ( CREATE TABLE ,  ALTER TABLE ,  DROP TABLE …) are always logged as  STATEMENT .

For more details, see Setting The Binary Log Format, in MySQL documentation.

Default database

A default database is the currently selected database. If there is none, you’ll need to write queries in this form:

SELECT * FROM my_database.my_table;

Using a default database means to write something like this:

USE my_database;
SELECT * FROM my_table;

When writing an application, usually one specified a database when establishing the connection to MySQL, instead of running  USE .

How replication filters can cause inconsistencies

• When no default database is selected, database-level filters won’t work.
• When a database is selected but we are writing to a table into a different database, database-level filters won’t work.
• replicate_do_table  doesn’t prevent changes made in stored functions. One could object that functions should not write to tables, but that is not always true. A function could treat table data as static variables. Here’s an example.
• Replication breaks when a single statement modifies both a filtered-in table and a filtered-out table. (multi-table  DELETE , multi-table  UPDATE ).

The last risk is particularly tricky if you use patterns to exclude (or include) table names. For example, you may add a filter to ignore all tables starting with  old_ . But after some months/years a new table called  old_index  may be created, and you may want to replicate it. At that point, it’s very easy to make a mistake.

Binary log filters

Replication filters are set on the replica to decide which events – sent by the master – will be replicated. Binary log filters are set on the master to decide which events should be written in the binary log. Filtered-out events are not logged and not sent to any replica. They are twin options: identical, with a different prefix ( replicate_  or  binlog_ ).

Binary log filters are subject to the same limitations/risks as replication filters. But, while they sound like an optimisation (because events that we don’t want to replicate are not logged by the master and not seen by the replicas at all), they are inherently subject to two important problems:

• Not logged events cannot be replicated by any replica;
• Using the binary log as an incremental backup will not restore any non-logged change;
  • A way to undo a destructive change is to restore the latest backup and re-apply the changes from the binary log, except for the one we want to undo. But if the binary log doesn’t include all changes made to the data, this will not be possible.

Recommendations for a safe replication

My recommendations about the use of these features are:

• Don’t use binary log filters at all.
• Filtering out the  mysql  system database normally does not harm. This is desirable to avoid replicating master users and permissions on the replica.
  • The only case when this is dangerous is when we write into the  mysql  database manually, instead of using statements like  CREATE USER .
• sys  database should be different for every MySQL version. It is a good idea to filter it out.
• Filtering out  information_schema  or  performance_schema  has no effect, simply don’t.
• Sometimes we really want to avoid replicate some regular databases. And using replication filters is the only way. But at least, we should follow some rules strictly.
  • Use  ROW  binary log format.
    • pt-table-checksum  and  pt-table-sync  require that we temporarily use  STATEMENT . Yes, this is a problem.
  • Make sure that applications select a default database and never write to a non-default database.
  • If you do migrations manually, make sure to properly use  USE .

See also

Related courses

The articles on Federico-Razzoli.com share a lot of technical knowledge for free, in the hope that they can help solving specific problems and raise the level of technical culture. However, if you work with database technologies, as an administrator or as a developer, you may need to build more organic knowledge and skills. Here are some courses related to the subject of this article.

• MariaDB Administration Essentials or MySQL Administration Essentials
• MariaDB Replication or MySQL Replication
• Galera Cluster

Conclusions

We discussed the risks of replication filters and binary log filters, and I offered my recommendations to make your replication safe.

A MariaDB Health Check or a MySQL Health Check may help you find out if you are using replication filters properly.

Did you notice any mistake? Do you have different opinions? Do you have ideas to share? Please comment!

Federico Razzoli