This link has been bookmarked by 24 people . It was first bookmarked on 15 Jul 2007, by Peter Murray.
-
03 Dec 11
-
16 Jun 11
-
14 Apr 11
-
24 Nov 10
-
25 Aug 10
-
05 Jul 09
-
Why snapshot based MySQL backups are great ?
There are number of reasons:
Almost Hot backup In most cases you can perform this type of backup while your application is running. No need to shut down server, make it read only or anything like it.
Support for all local disk based storage engines It works with MyISAM and Innodb and BDB, It also should work with Solid, PrimeXT and Falcon storage engines.
Fast Backup You simply do file copy in the binary form so it is hard to beat in speed.
Low Overhead It is simply file copy so overhead to the server is minimal.
Easy to Integrate Do you want to compress backup ? Backup it to tape, FTP or any network backup software - it is easy as you just need to copy files.
Fast Recovery Recovery time is as fast as putting data back and standard MySQL crash recovery, and it can be reduced even further. More on this later.
Free No extra commercial tools as Innodb Hot Backup are required to perform backup.
-
Are there any downsides ?
Need to have snapshot campatibility - this is obvious one.
May need root access In some organizations DBA and System Administrator are different people from different departmnents which might not like to trade access rights between each other.
Hard to predict downtime I mentioned this solution is often hot backup, but bad thing it is hard to estimate when it is hot and when it is not - FLUSH TABLES WITH READ LOCK may take quite a while to complete on systems with long queries.
Problems with data on multiple volumes If you have logs on separate devices or just your database spanning across multiple volumes you will be in trouble as you will not get consistent snapshot across all the database. Some systems may be able to do atomic snapshot of many volumes.
-
Lets speak a bit about how LVM and snapshotting in general works. Really there are different implementations but the sake of them is to provide you with volume which consistently matches state of the volume at the time storage is created. In LVM it is implementeed as copy on write. Special storage area allocated on device where old version of changed pages are stored. You can think about it as about simplified form of versioning like in Innodb if it is closer to you.
-
There are two types of snapshots - some of them are read-only while others can be read-write. read-only snapshots may sound good enough as you're only going to read data anyway, but in reality read-write snapshots have number of benefits. First no extra handling is needed for journaling file sytems - you can simply do journal recovery on snapshot. With read-only snapshot you need to make sure filesystem synchronizes device before snapshot is taken so no journal replay is needed.
The other benefit of read-write snapshot is you can actually start MySQL Server on it and perform recovery, check tables or do whatever else you might need to do to ensure your backup is consistent. Backing up database which was already corrupted is very nasty problem you want to avoid.
-
The catch here is FLUSH TABLES WITH READ LOCK actually waits for all statements to complete, even selects. So be careful if you have any long running queries. If you're using only Innodb tables and do not need to synchronize binary log position with backup you can skip this step.
-
No it is not safe because logs and tablespace will be out of sync. Note, even if no statements can be run after FLUSH TABLES WITH READ LOCK Innodb continues to do things in background – purging, insert buffer merge, flushing dirty pages etc.
You’re lucky it worked but I would not rely on it.
-
-
15 Jun 09
-
08 Nov 08
-
23 Jul 08
Eric HammondInstant database backups are fun!
LVM MySQL backups replication snapshots sql databases tutorials master slave esh-anvilon
-
18 Jul 08
-
05 Jun 08
-
04 Mar 08
-
30 Nov 07
-
24 Oct 07
-
15 Jul 07
-
09 Jun 07
-
05 Mar 07
-
03 Oct 06
-
22 Aug 06
Would you like to comment?
Join Diigo for a free account, or sign in if you are already a member.