Restrict MySQL replication to row based events

In a follow-up to the work presented on MySQL 8.0.18  where we introduced privilege checks for slave applier threads, in this post we present a new feature to further increase your ability to securely replicate your data: you can now restrict replication streams to row based events only.

In MySQL 8.0.19, a new CHANGE MASTER parameter REQUIRE_ROW_FORMAT is added for replication channels, which makes the channel accept only row-based replication events.
This requirement will increase overall security by:

  • Not allowing the replication of LOAD DATA instructions, as they mean a file will be temporally present on disk where it can be tampered or accessed to reveal data that would be encrypted on insertion.
  • Not allowing the replication of temporary tables and related data, enabling the possibility of having in the master temporary data that might be sensitive, for example unencrypted data used for calculations that will produce encrypted data.
  • Avoid other common issues associate to statement based replication streams as seen in the documentation

These use cases become more relevant when you replicate from servers outside your security boundary, meaning they are outside your control and might be accessed by unknown users.  It then becomes  quite useful in the fan-in use-case, for instance, when you want to aggregate data from several sources you don’t control directly but still want to ensure that no sensitive data is kept on disk unencrypted or is being wrongfully replicated through temporary tables.

Another positive side-effect, for those of you that are already replicating using restricted privileges, is that you no longer need to grant the FILE privilege to the user being configured for this purpose.

Behavior

After enabling this new feature in a replication channel, for all transactions received and applied there are checks being done not allowing any of the following:

  • LOAD DATA events
  • The creation or deletion of temporary tables
  • Most INTVAR, RAND or USER_VAR events as they are associated to statement based replication
  • All data manipulation queries (DML) that were logged for statement based replication

Upon encountering any of these events, replication shall fail and stop.

Configuration

To explicitly make a channel only accept row based replication you will have to fully stop replication. Below you can see the commands to achieve it:

The reason you have to stop the slave and not only its applier is that these checks are done both in the slave receiver and applier threads, hence they will also apply to transactions already received and present in relay logs.

Observability

The Performance Schema tables related to the slave applier status were enhanced to display the status of the new CHANGE MASTER TO … statement option, REQUIRE_ROW_FORMAT:

Changes to mysqlbinlog

Complementing this change we also added a new option for mysqlbinlog.

With this option, you know the output will be valid for a channel running with REQUIRE_ROW_FORMAT = 1.
This option also prevents mysqlbinlog from printing instructions relating to the internal variable: pseudo_thread_id. These, not being needed on row based only streams, would require extra privileges in the replication applier if privilege checks are configured.

Summary

So to secure your server against issues common on statement based replication streams and the events allowed in them, or to simply reduce the privileges needed to configure applier privilege checks in replicas, REQUIRE_ROW_FORMAT is an interesting addition to the MySQL server.

We hope this new feature will allow you to create more secure solutions with the MySQL server. Feel free to test it, and tell us your opinion.

 5,243 total views,  18 views today

About Pedro Gomes

Who am I? I'm a replication developer @ MySQL since 2013, and a fan of all things distributed so it's hard not to love my job. Raised on the distributed lab of Minho's University, home of great academic research on the field, I joined Oracle following this same passion and here I am!

One thought on “Restrict MySQL replication to row based events

Leave a Reply