| 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?
|
|
|
| Re: New Approach for Backups [message #2208 is a reply to message #2207 ] |
Fri, 16 November 2007 01:19  |
|
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.
Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|
|
| Re: New Approach for Backups [message #2216 is a reply to message #2215 ] |
Sun, 18 November 2007 11:05 |
|
| RHAT wrote on Sun, 18 November 2007 10:43 |
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.
Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|
| Re: New Approach for Backups [message #2223 is a reply to message #2207 ] |
Tue, 20 November 2007 05:03 |
stark
Messages: 11
Registered: July 2007 |
Junior Member |
|
|
|
You can even stop Mysql on the slave completly and do a binary backup.
|
|
|
Members
Search
Help
Register
Login
Home
