The importance of setting MySQL slaves to read-only

Ask any MySQL Database Administrator or database-savvy Developer how smooth their last MySQL master/slave swap went, and unless they live in a perfect world, they likely encountered a hiccup or two.  The actual reconfiguration of replication probably went well, as “CHANGE MASTER TO” is hard to mess up if you’re being careful.   But controlling application behavior isn’t always easy, and can lead to attempted writes to the now-demoted master, which has likely just become a production slave.  For instance, applications that use DNS for database connectivity can cache the DNS entry that should normally point them to the correct MySQL master.  Sometimes, a configuration file is overlooked and the correct read/write host pairs are not updated correctly.  Often enough, a Database Administrator needs to perform an emergency master/slave swap and doesn’t have control over the behavior of the application(s) the database serves.  A clean architecture involving automation can help avoid these issues all-together, but in a typical environment, MySQL master/slave swaps are a common and manual process.

To help Database Administrators feel confident about data integrity through a late-night master/slave swap, regardless of potentially mis-behaving applications and clients, it’s important to remember the “read-only” global variable.  The setting can be toggled on/off at runtime, and except for users who have the SUPER privilege, MySQL will forcefully deny any changes to data unless those changes are requested by a replication thread.

However, keep in mind:

  • Many application users, especially at young start-ups, will have GRANT ALL privileges which include SUPER, thereby rendering the read-only setting useless.
  • Use of automation (such as with puppet or chef and a template-generated my.cnf file) is critical to avoid human error.  Appropriate monitoring around the read-only setting is a huge plus.
  • Even when used consistently, the read-only setting must be switched on and off at necessary times.  For example, during a master/slave swap, you’ll want to set the “old” master to read-only=on BEFORE you set the “new” master to read-only=off.  Otherwise, there’s a solid chance cached DNS (or some other unexpected behavior) will cause writes to both servers, even if during a small window of opportunity.

No matter what hiccup avoidance technique(s) are used (our favorite is eating granulated sugar), it is very important to maintain data integrity at all costs.  No one wants to replay binary logs and migrate misplaced data.  To optimize your next master/slave swap, utilize the read-only setting of MySQL consistently, and be sure to avoid having application users with the SUPER privilege.









Share Button

Tags: ,

No comments yet.

Leave a Reply