Should a MySQL slave used for backup also be used to service read-only requests in a production environment?

Posted on

Question :

Based on best practices, I’m wondering if we should have at least one slave that’s used solely as a backup server, without servicing any forward-facing requests from users.

For example, if we have a master server with a single slave, and that slave is used to back up data via mysqldump a few times a day, are there any compelling reasons not to use that same slave to calculate some quick-to-load (<10 seconds) analytics for a handful of customers in production?

Answer :

How long does the backup take?
How much does it “lock up” the Slave?
What time of day is the backup done?
What time of day are analytics performed?

The answers to those questions will tell you “Will the backup interfere too much with the analytics queries?” The answer to that is the answer to your original question.

Not a proper slave because you are not using mysql replication but anyway it’s very common to have an slave or more than one to reduce the load in the master server.
Make really sure that its only used for read queries.

It is a good idea to use a slave for backups, especially if backups cause problems with operations on the master. Reading from the slave shouldn’t present any issues – a lot of people do it as a way to lighten the load on the master. Check that the slave is set to read-only and be aware that long-running SELECTs may cause replication lag and possibly interfere with mysqldump.

Leave a Reply

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