Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
are there any special reasons why you would need to backup the slave every 30 minutes? –  KookieMonster Nov 19 '13 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 '13 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. –  KookieMonster Nov 20 '13 at 7:06
add comment

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.

Max.

share|improve this answer
    
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 '13 at 11:56
    
Replication occures at "commit" on master. –  Maxime FOUILLEUL Nov 19 '13 at 13:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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