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:
[I]
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.[/I]
It could work, but only in rare cases when your database has not so write-intensive workload. Then your low-end backup server would be able to keep up with replication stream. But in really many cases when your slave is huge machine with pretty large innodb_buffer_pool, your low end server would not be able to keep up with replication w/o horrible lags (replicated statements are executed with one thread only, buffer pool is small, cpu is slower).
So, you can try this approach, but it would be useful in too small subset of cases. Especially weird here is that on your low end machine when you’ll perform backups your slave lag would grow and then it could take too much time to catch up with replication.
So, I’d stick with LVM-snapshot based backups made from some powerful slave.
ok, I think in my case, my approach would work
because I have 90% read access.
However, all the time I read about the “LVM method”
I ask myself, what happens to inserts/updates durinng
the write lock? Do they throw an exception or are they just
delayed?
Hi,
However, all the time I read about the "LVM method"
I ask myself, what happens to inserts/updates durinng
the write lock? Do they throw an exception or are they just
delayed?
thanks
When you do LVM backups, DB is locked (write queries are delayed) only for a second or even fraction of second while you’re creating your snapshot. Then, when snapshot is created, your db is available for writes.