1

It'd be really nice for disaster recovery to be able to set this up. We are doing snapshots, but this seems like it'd be a really nice backup to have. For example, I'd like to always keep a replicated slave 30 minutes behind our master, so that we could have a sort of live backup all the time.

This is a secondary replicated server, as ypercube points out -- if you only have a single replicated server, running it 30 minutes behind would mean you'd potentially lose 30 minutes of data in the case of the master crashing.

Although I suspect the relevant binlogs for those 30 minutes of binlog events would actually be stored on the slave (and just not processed until it was 30 minutes behind), so I'm thinking that I wouldn't actually, even if this were the only slave.

I'll be doing some testing to confirm this behavior, as it is relevant for some potential use-cases for us. I'll update this later.

  • Seems like you want to lose 30 minutes of data, if anything goes wrong with the master. Why? – ypercubeᵀᴹ Dec 14 '15 at 21:40
  • What version of MySQL are you using ? – RolandoMySQLDBA Dec 14 '15 at 21:59
  • 1
    @ypercube you wouldn't lose the data. It's persisted to the slave's relay log immediately, as standard async replication (as OP has correctly speculated), but the slave sql_thread waits to execute the events until they are MASTER_DELAY seconds in the past. The idea is to provide a recovery window to avoid data loss due to queries that ran on the master at the hands of someone whose fingers are asking to be cut off. The option is poorly named. SLAVE_SQL_DELAY would have been a more intuitive name. – Michael - sqlbot Dec 15 '15 at 3:20
1

If you really want a live slave 30 minutes behind its master then use the following

CHANGE MASTER TO ...
MASTER_DELAY = 1800;

This will setup replication 30 minutes behind

See the CHANGE MASTER TO Documentation

In light of ypercube's comment, you should do the following:

Setup two slaves - Slave1 with 30 minute delay - Slave2 with no delay (Run mysqldump --single-transaction for point-in-time snaphosts)

  • MASTER_DELAY requires MySQL 5.6. – G-Nugget Dec 14 '15 at 21:58
2

For any version pt-slave-delay Is what you're looking for. Note this is a 3rd party tool that continuously runs with stop/start slave, to keep it around the delay you want.

If you have 5.6 or above you can use the native built in slave delay

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.