There is an interesting feature in MySQL 5.7+: the global variable super_read_only. MySQL 5.6 and before only had the read_only global variable which is not preventing a user with SUPER privilege to write to the database. With super_read_only, those users cannot write anymore. But this feature comes with a surprise.
TL&DR: changing super_read_only to ON also sets read_only to ON and setting
The biggest concern with a slave is to ensure your data is consistent with the master! End of story!
3 of the biggest things I see when dealing with out-of-sync slaves:
Of course, if you have a slave, definitely use the --read-only option.
However, SUPER users can still write on slaves with --read-only, so blindly granting SUPER to all users just to save a little time when creating users won’t help. I’d suggest to use SUPER as sparingly as possible (not to mention it’s good for security also).
And the use of --slave-skip-errors=… is generally just a quick fix to avoid errors on the slave, but a sure fire way to end up with inconsistent …
[Read more]One of the most asked questions is how to setup MySQL replication. The MySQL Reference Manual provides a good Replication How To as a starting guide on MySQL Replication 101.
MySQL replication has many uses including read scalability, backups, failover, online maintenance, upgrade testing and verification, software upgrades, alternative data or structure for performance queries and even benchmarking to name the popular uses.
When reviewing an installation of MySQL replication I use the following as part of my checklist of steps used for confirming your replication environment is operational.
Master Variables