The dangers of replication filters in MySQL

Last Updated on

Replication icon
Replication

MySQL supports what it calls replication filters. They allow to decide which tables are replicated, and which are not. They can be set on a slave to filter-in tables and databases: the slave 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 in-filters and out-filters. And we can filter specific databases, specific tables, or patterns for table names (for example, all the tables with a certain suffix).

A bit confusing but powerful, right? Unfortunately, this feature is also very dangerous, as it can easily cause inconsistencies (different data) between the master and the slave. This can happen because applications run queries in a wrong way, or because 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.

Glasgow, seen from Queens Park
Glasgow from Queen’s Park.
Westminster’s decisions are replicated with some filters by the national governments,
but is devolution enough?

Some 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 slave 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 slave. 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 slaves at all), they are inherently subject to two important problems:

  • Not logged events cannot be replicated by any slave;
  • 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 slave.
    • 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.
    • 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.

Conclusions

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

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

As usual, I’ll be happy to fix errors and discuss your ideas. I want to thank all the persons who contributed this website with their comments, creating a valuable shared knowledgebase.

Toodle pip,
Federico

Photo credit

Comments (2)

  1. Thanks for this article! I never gave too much tough on the possible problems that you have defined here when using replication filters, because for me they’re still useful. We have a real use case where we only need several tables of a schema from another instance (on another server). The set of tables is specially crafted by me with respect to consistency and table names (with schema name e.g. in format schema.table) are defined in full in REPLICATE_DO_TABLE. The setup had been working properly without any problem for more than 2 years now, until just recently… A new DBA/DevOps or something decided to migrate the old 5.6 master instance, to MySQL 8.0 without telling anyone else. And he migrated the instance on another server (with different connection parameters) leaving the old instance running like nothing at all happened. He only redirected the applications. Since the tables we’re interested in and replicate are not high volume we didn’t notice until a week later, because the replication (that we were of course monitoring) was simply working… 🙂

    1. Hi Georgi, thanks for sharing your experience!
      Good for you. If you define replication filter properly, use default database properly when doing migrations or using STATEMENT, and don’t use stored functions with STATEMENT, replication filters work fine. And yes, they are really useful sometimes. That said, I think that the list of things that *could* go wrong is far too long…
      What surprises me more is the upgrade from MySQL 5.6 to 8.0. Of course in many cases it will not break anything, and hopefully many queries will be faster. But there is always a risk to break something. An upgrade should be planned, communicated to everyone, and then tested properly.
      Cheers,
      Federico

Leave a Reply

Your email address will not be published. Required fields are marked *