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.
Best Answer
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.