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?

  • Be cautious when using the slave-read, that may lead the replication lag with writng an avalanche of updating/inserting SQL into master. – liviaerxin Apr 17 '18 at 9:01

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.

| improve this answer | |
  • This sounds like an acknowledgement that it's not necessary to have a dedicated slave for backup. Thanks. – Joshua Pokotilow Jan 28 '16 at 22:26

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.

| improve this answer | |
  • Thanks for the answer. The slave I mentioned is using replication, but then we dump the data periodically from the slave using mysqldump. I don't think that necessarily changes anything about your advice, though. It sounds like you're saying it isn't necessary to have a slave dedicated solely to backup. – Joshua Pokotilow Jan 27 '16 at 14:21

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.