Prevent non-replication writes to MySQL slave?

We have some MySQL database servers set up with row-based replication, for performance. The software writes to the master, and reads from either the master or the slave. Everything's working great, for the most part.

It's my understanding that MySQL will allow writes to the slave, even though it knows it's a MySQL slave. Ideally, I'd kind of like to close this, so even if somebody writes some bad code that gets a read-connection and does an UPDATE, it will throw an error rather than put data on the slave.

Is there a way to do this in MySQL? Obviously we'd like to make this impossible from our software, as well, but like a firewall on our servers, I'd like to be as defensive as possible.

Thanks!

Enable the read-only option in my.cnf. It can also be specified as a flag on the command line using --read-only with mysqld.

As an alternative to setting read_only=1 (e.g. when there are other scratchpad/reporting/development databases on the slave instance), I sometimes strip all privileges other than SELECT from all users to the DB that I am replicating.

That is, after running the GRANT command on the master, I run the REVOKE command on the slave.

As of MySQL 5.7.8, there is now a super_read_only option, which prevents even SUPER users from performing client updates. It does not disrupt the replication process. As with other settings, it can be set:

• in command line format (--super_read_only=ON),
• as a variable in my.cnf (super_read_only=1), or
• from the client prompt (SET GLOBAL super_read_only = 1;).

Note that:

• Enabling super_read_only implicitly enables read_only
• Disabling read_only implicitly disables super_read_only

Some caveats:

• Neither read_only nor super_read_only will prevent operations on temporary tables.
• They will not prevent metadata operations like ANALYZE TABLE and OPTIMIZE TABLE.
• Bugs for certain queries with super_read_only enabled have been reported.

Reference: https://www.percona.com/blog/2016/09/27/using-the-super_read_only-system-variable/

As the first post somewhat suggests, you do it with permissions. Read-only option does not work for super users as an FYI and its also not really a workable solution for a slave where you want to prevent writes. You need to prevent writes with user/database/table permissions. For one, the replication user still must be able to write to the slave to keep it in sync with the master. Better way to control writes is you need to revoke options that allow writes (ie- inserts, creates, etc.) for the user in question that should be doing reads only on the slave.