Using the MySQL super_read_only system variable

super_read_only-system-variableThis blog post will discuss how to use the MySQL super_read_only system variable.

It is well known that replica servers in a master/slave configuration, to avoid breaking replication due to duplicate keys, missing rows or other similar issues, should not receive write queries. It’s a good practice to set read_only=1 on slave servers to prevent any (accidental) writes. Servers acting as replicas will NOT be in read-only mode automatically by default.

Sadly,  read_only has a historical issue: users with the SUPER privilege can override the setting and could still run DML queries. Since Percona Server 5.6.21 and MySQL 5.7.8, however, you can use the super_read_only feature to extend the read_only  option and apply it to users with SUPER privileges.

Both  super_read_only and  read_only  are disabled by default, and using  super_read_only implies that  read_only  is automatically ON as well. We’ll demonstrate how read_only and super_read only work:

As expected, with the read_only variable enabled, users without SUPER privilege won’t be able to INSERT values, and instead, they will get an ERROR 1290 message:

However, users with SUPER privileges can INSERT values on the table:

Now we will enable super_read_only and try to INSERT data again with both users: