# New Approach for Backups

**URL:** <https://forums.percona.com/t/new-approach-for-backups/525>\
**Category:** Other MySQL® Questions\
**Created:** [November 15, 2007, 2:40pm UTC](https://forums.percona.com/t/new-approach-for-backups/525 "2007-11-15T14:40:48Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![RHAT](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@RHAT](https://forums.percona.com/u/RHAT)\
**Post date:** [November 15, 2007, 2:40pm UTC](https://forums.percona.com/t/new-approach-for-backups/525/1 "2007-11-15T14:40:48Z")

</div>

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](http://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]

What do you think? Will it work?

---

<div class="post-metadata">

**Author:** ![scoundrel](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@scoundrel](https://forums.percona.com/u/scoundrel)\
**Post date:** [November 16, 2007, 12:49am UTC](https://forums.percona.com/t/new-approach-for-backups/525/2 "2007-11-16T00:49:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![RHAT](https://avatars.discourse-cdn.com/v4/letter/r/e9c0ed/32.png) [@RHAT](https://forums.percona.com/u/RHAT)\
**Post date:** [November 18, 2007, 10:13am UTC](https://forums.percona.com/t/new-approach-for-backups/525/3 "2007-11-18T10:13:50Z")

</div>

Hi,

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?

thanks

---

<div class="post-metadata">

**Author:** ![scoundrel](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@scoundrel](https://forums.percona.com/u/scoundrel)\
**Post date:** [November 18, 2007, 10:35am UTC](https://forums.percona.com/t/new-approach-for-backups/525/4 "2007-11-18T10:35:49Z")

</div>

| [B]RHAT wrote on Sun, 18 November 2007 10:43[/B] |
| 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.

---

<div class="post-metadata">

**Author:** ![stark](https://avatars.discourse-cdn.com/v4/letter/s/dbc845/32.png) [@stark](https://forums.percona.com/u/stark)\
**Post date:** [November 20, 2007, 4:33am UTC](https://forums.percona.com/t/new-approach-for-backups/525/5 "2007-11-20T04:33:37Z")

</div>

You can even stop Mysql on the slave completly and do a binary backup.