Looking for Slave Consistency: Say Yes to –read-only and No to SUPER and –slave-skip-errors


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:

  1. Many users do not use the –read-only option on their slaves.
  2. Some of those who do often have numerous users with SUPER who can still perform writes.
  3. Many users simply use –slave-skip-errors=… to avoid common errors.

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 data. Please don’t just use this blindly, but rather figure out why you are receiving such errors and resolve them the correct way. (We’re happy to assist if you need help with this.) In conclusion, if the consistency of the data on your slave matters to you/your business/your customers, and/or if you’ve already had problems with inconsistent data, then add –read-only, remove –slave-skip-errors=…, and remove SUPER from as many users that use the slave as possible. That will be a terrific start. If you are really serious about having a “read only” slave, and you use InnoDB, then as of MySQL 5.6 and MariaDB 10.0, there is also the innodb_read_only option, which has the following effect:

  • No user will be able to modify. Only DCL (like create user/grant/revoke etc) commands will work for the root user.

Note that when both –read-only and –innodb-read-only are given, then –innodb-read-only takes effect. There is more information on this variable, and how to set up read only InnoDB on the following two pages: https://blogs.oracle.com/MySQL/entry/innodb_read_only_mode http://dev.mysql.com/../innodb-read-only-instance.html Along this same line, and in addition to InnoDB, MariaDB 10.0 also has a spider_read_only_mode option which will make spider tables read only as well. Hope this helps.