MySQL 101: Using super_read_only

MySQL 101 super_read_onlyAs many of you may remember, Percona added the super_read_only feature way back in Percona Server for MySQL 5.6.21, based on work done by WebScaleSQL. This feature eventually found its way into the Community branch of MySQL starting with 5.7.8, and it works the same in both cases. While this is now old news, over the last year I’ve had a couple of inquiries from clients around super_read_only usage in MySQL, and how it works in practice. While the usage of super_read_only is not complex, there is a small caveat that occasionally leads to some confusion around its use. As such, I thought it may be a good idea to write a quick blog post explaining this feature a bit more, and expanding on how it interacts with read_only.

What is super_read_only?

For those unfamiliar, what is super_read_only? Prior to its introduction, MySQL had the option to set a node to read_only, preventing everyone except those with the SUPER privilege from writing to the database. Most often used for replica nodes, it was a good step in preventing someone from inadvertently updating a replica manually without going through the primary node and letting replication threads handle the distribution. This, of course, could break replication due to duplicate keys, missing rows, or other issues as a result of the inconsistency between the datasets.

Using super_read_only takes this one step further, behaving identically to read_only while also blocking those with SUPER privileges from writing to the database as well. While at first glance this may seem like a stop-gap measure in lieu of better and more restrictive user permissions, it has proven very handy in production environments to add a further layer of protection to replica nodes, and helping to prevent human error from causing unexpected downtime.

One Hand Washes The Other

The inquiries I referenced earlier comes with its use, and not realizing (or forgetting) that read_only and super_read_only are linked. The key thing to keep in mind is that enabling super_read_only implies regular read_only as well.

  • Setting read_only = OFF also sets super_read_only = OFF.
  • Setting super_read_only = ON also sets read_only = ON.
  • All other changes to either of these variables have no effect on the other.

This behavior does seem logical, as when disabling read_only you probably also want to disable super_read_only, and vice-versa, when enabling super_read_only you probably also want to enable read_only. While this linked behavior is documented, there are no warnings or notes in MySQL itself alerting you to this when you make a change to one or the other. This can lead to some head-scratching, as a change to one variable changes the other in lockstep.

Final Considerations

There are a few other implications to keep in mind for read_only that apply to super_read_only as well – for instance, operations on temporary tables are allowed no matter how these variables are set. Any OPTIMIZE TABLE or ANALYZE TABLE operations are also allowed since the purpose of read_only / super_read_only is to prevent changes to the table structure, but not to table metadata such as index statistics. Finally, these settings will need to be manually disabled (or scripted via automation) if there is ever an instance where the replica needs to be promoted to primary status.

Percona Distribution for MySQL: An Enterprise-Grade MySQL Solution, for Free

Share this post

Leave a Reply