Showing entries 1 to 2
Displaying posts with tag: read_only (reset)
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 …

[Read more]
MySQL Replication 102

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

  • server-id – Replication will not work without this correctly set and unique
  • log-bin – Pre-requisite for working replication
  • log-bin-index
  • max_binlog_size
[Read more]
Showing entries 1 to 2