MySQL Performance: MySQL => New Approach for Backups

New Approach for Backups [message #2207]

Thu, 15 November 2007 15:10

RHAT
Messages: 3
Registered: November 2007

Junior Member

Hi,

I'm currently in the process of designing a backup strategy for my environment. My env is currently a Master/Slave cluster running innodb.

I have read about the following approaches:

* Use a write lock and a FS which allows snapshots
* Use MySQLdump
* Use some commercial tool for innodb hot backup

However, I have "found" an approach that has to
my best knowledge not been discussed on mysqlperformanceblog.com.
And here it is:

Use a low-end machine as another slave, when a backup is due, stop the replication on exactly this machine.
Flush tables with write lock and conduct the backup.
Then restart the replication -- the slave will catch
up, even if the backup takes hours.

This has one main advantage:
There is no need to write lock the master or
slave which are in live operation.

I have not yet tried it, but the below statement in
the MySQL docs seem to support it:

mysql> STOP SLAVE IO_THREAD;

Stopping the SQL thread can be useful if you want to perform a backup or other task on a slave that only processes events from the master. The IO thread will continue to be read from the master, but not executed, which will make it easier for the slave to catch up when you start slave operations again.

What do you think? Will it work?

Report message to a moderator