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:
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.
Did you know we have a MySQL-specific tips and tricks guide? Download our MySQL tips and tricks guide now.
We offer on-site and remote MySQL replication training. Inquire today about training services.