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 DATAinstructions, 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.
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:
- The creation or deletion of temporary tables
USER_VARevents 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.
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:
mysql> STOP SLAVE FOR CHANNEL 'channel_1';
mysql> CHANGE MASTER TO REQUIRE_ROW_FORMAT = 1 FOR CHANNEL 'channel_1';
mysql> START SLAVE FOR CHANNEL 'channel_1';
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.
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:
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO REQUIRE_ROW_FORMAT = 1;
mysql> START SLAVE;
mysql> SELECT Require_row_format FROM performance_schema.replication_applier_configuration;
| REQUIRE_ROW_FORMAT |
| YES |
1 row in set (0.00 sec)
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.
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.