UPDATE: As of MySQL 5.7.8 there is super_read_only so use this instead of this trick.

It’s always been an annoyance that  read_only in MySQL actually means “read only apart from those with the SUPER priv”. Now I know it’s best practice not to give this permission out to users but sometimes we are stuck with the choices others have made.

We now have another option in the form of tx_read_only which was introduced in MariaDB 10 and MySQL 5.6.5. If we set this variable to ON then any writes will be rejected with…

ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

This also applies to users with the SUPER priv. A user with the SUPER priv can still perform some action entitled by that privileges, i.e. SET GLOBAL var, PURGE BINARY LOGS, CHANGE MASTER etc. Check your specific use case. Unfortunately this also prevents replication working. We can get around this restriction using the init_slave variable and the following SQL…

SET SESSION TRANSACTION READ WRITE;

Now the replication thread will work! This all goes to hell if a user logs in and executes

SET SESSION TRANSACTION READ WRITE;

You may be able to check the performance_schema to see if you have any users doing this…

SELECT *
FROM performance_schema.`events_statements_summary_by_digest`
WHERE DIGEST_TEXT LIKE '%TRANSACTION%';

It may be possible to override this using init_connect if you have any clients executing this by default as part of the connection setup (I need to test this). It would also go a good idea to keep using read_only as well. The my.cnf settings for this would be…

transaction_read_only = 1
read_only = 1
init_slave "SET SESSION TRANSACTION READ WRITE;"

tx_read_only doesn’t seem to work in the cnf file for some reason. This is an inconsistency they may fix in a future version. There’s obviously still holes in this approach but I’m hopeful this will make my setup a little more resilient.