On Slave Usage

Posted in: MySQL, Technical Track

Slaves can be used for:

  1. Horizontal read scalability — take the load off a master database by spreading reads to a replicated slave.


  • Disaster recovery — some disasters, such as a hardware failure, can be solved by having a slave ready to propagate to a master. This technique also works to make offline changes to a master/slave pair without having database downtime (see below).



  • Consistent Backups — without disrupting production usage, a slave can be used to take a consistent backup by stopping the database and copying the database files. This is a free and uncomplicated way to get consistent backups (innodb hot backup is not free, and using a snapshotting tool (such as LVM’s snapshot capability) can be complex. Not everyone wants to manage snapshots.)


Be careful when using a slave for more than one purpose. Using a slave for more than one purpose can be done, but carefully. For example:

If one slave instance serves the needs of 1 and 2 — Where do the reads go when the slave is promoted to a master (or taken offline for maintenance)?

If one slave instance serves the needs of 1 and 3 — can the read slave be down for the length of time it takes to take a backup? If not, this solution is not appropriate.

If one slave instance serves the needs of 2 and 3 — Where does the backup get taken from when the slave is promoted to master (or taken offline for maintenance)?

There are certainly workarounds. We either create a propagation procedure (whether for maintenance or in an emergency) or assess an existing one, to cover these cases to make sure any dual-use of a slave does not leave a client hanging. We regularly test whether slaves are in sync, to ensure accuracy no matter what purposes replication slaves are used for. We also go through actual slave-to-master propagations. This can be useful as a drill, and can also be useful to avoid downtime.

Avoiding Downtime with Replication

In many cases, using replication can avoid downtime during an offline database change (for example, an OPTIMIZE TABLE to defragment a table). To implement this, the slave is taken out of service, the change performed on the slave, the slave is put back into service and propagated to be the master. Then the previous master is taken offline, the change applied to the previous master, and the previous master can return to service (either as a slave of the current master, or by propagating the previous master to be the current master.


Interested in working with Sheeri? Schedule a tech call.

3 Comments. Leave new

All good uses for a slave, but I think it’s important to point out that all of those options depend on replication working properly. The backups, in particular. They are only as good as replication. While replication generally works reasonably well, there’s plenty that can go wrong when the limitations and capabilities in replication are not addressed.

For instance, filtering databases can cause problems when using cross-database updates (or updates without first running the “USE ” command). “INSERT|UPDATE … SELECT” can also cause problems.

For multi-master’s, it’s important to note that using auto_increment offsets won’t always save you if you send writes to both servers, such as when you might be using UNIQUE keys.

These limitations can generally be overcome, and, when they are, replication tends to work very well. Thought I’d put my $0.02 about them though since not everyone who might read this post might be thinking about them.


Tim — yes, definitely. I started to allude to that kind of stuff by saying “We regularly test whether slaves are in sync, to ensure accuracy no matter what purposes replication slaves are used for.”

Using existing data in DML is also bad as you point out — but row-based replication has changed that problem.

As you point out, using the “replicate-do” and “replicate-ignore” statements have their problems. I think it’s particularly important to note that using “binlog-do” and “binlog-ignore” mean that you don’t log information that you may be required to (or you may want to).

In my opinion it’s better to log everything and just apply what you want, than to log selectively and apply everything.

There’s a lot to replication; this post was just meant to be a warning if people are using slaves for more than one purpose, they should make sure they’re not going to get stuck if they haven’t thought everything through.



Other good use for slaves:
1. Testing MySQL version upgrade:
Upgrading the MySQL version on the slave is relatively safe. It may (though not necessarily) reflect any incompatibilities between your application and the new version. So if the slave upgraded, and replication continues without issues, it’s relatively safe to upgrade the master.

2. Testing index impact and performance:
To verify schema improvements, one can add/change indexing on tables on the slave, testing previous slow queries on the new schema. If successful, these changes can be propagated to the master as described.

3. I usually require more than one slave. In such cases where it is possible, a particular slave is marked as “a replacement for the master” in case of disaster. This particular slave has the exact same design and settings as the master.
The other slave, however, can be utilized for backups, may have more relaxed settings, etc.
This second slave may also be forced to lag behind the master (e.g. using maatkit’s mk-slave-delay) to also provide recovery in case of a “drop database” statement.


Leave a Reply

Your email address will not be published. Required fields are marked *