I would like to implement the following backup solution as highlgihted on the mysql site:

To use replication as a backup solution, replicate data from the master to a slave, and then back up the data slave. The slave can be paused and shut down without affecting the running operation of the master, so you can produce an effective snapshot of “live” data that would otherwise require the master to be shut down.

I will have the master -> slave replication running every 5 minutes and then perform a full backup of the slave every 30 minutes

I understand that when the slave is backed up this is a database dump which will require all tables to be locked. Therefore the master wont be able to replicate to the salve, will this result in any errors on the master side? What actually happens in this case? Will the replication fall over if master cant replicate to the slave or will it handle gracefully?

I can accept if the master cant replicate when slave is backed up as the replication is quite frequent. i would just like to know what happens if the master is trying to replicate and the slave is locked.

  • are there any special reasons why you would need to backup the slave every 30 minutes? Nov 19, 2013 at 11:27
  • @KookieMonster - there will be lots of data transactions so we need to rollback to 30 mins if the master db ever falls over. Is 30 mins overkill?
    – Santiago
    Nov 19, 2013 at 11:53
  • the MySQL specialists may answer this better than me, but I have yet to find a database that needs a full back up every half hour. As you say, this looks overkill. Nov 20, 2013 at 7:06

1 Answer 1


Operations and issues on slaves will not impact the Master. I dont't understand your concept of "the master -> slave replication running every 5 minutes". All transactions on Master will immediatelty be replicate on slaves.

If your tables are InnoDB, your dump will not lock any tables (with the option --single-transaction) so you can have normal activity on master and slave too.

To backup your slave you can also stop the replication, shutdown your MySQL (Slave) and copy your datafiles or use a hot backup solution like Percona XtraBackup.


  • i wasnt aware that the transactions happen immediately (which is good to know). I will try to implement the backup solution mentioned (shutdown and copy).
    – Santiago
    Nov 19, 2013 at 11:56
  • Replication occures at "commit" on master. Nov 19, 2013 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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