RE: LVM-snapshot + mysqldump -- is this a reasonable backup strategy?
quoting Alan Williamson [EMAIL PROTECTED] .. This recipe is intended to minimize the impact on ongoing database operations by inhibiting writes only during a relatively speedy operation (creating a snapshot). The long dump operation can ... This seems to be a rather long winded way of doing this. Why not replicate the database and therefore not have to bring it down ever. I wrote a blog entry about this very thing, and had some interesting comments back on http://blog.spikesource.com/mysql_hotbackup.htm Hope this helps, Thanks for the input. It does seem more complicated than it ought to be, doesn't it? The reason for trying to do it that way (eg: flush tables with read lock, create snapshot, unlock mount snapshot run 2nd mysqld, mysqldump, kill 2nd mysqld, umount snap, remove snap, whew!) is that I have only one server, and believe that the snapshot was invented just for such a purpose -- namely: quickly capture a point-in- time image of the database for a leisurely backup. In more words, locking is deemed required to achieve a consistent db state for a backup operation, but the lock need only be held during the short time required to create the snapshot. Then the much longer backup (dump) operation can proceed by reading from the (effectively static) db contained in the snapshot. Your suggestion is appreciated, Alan, but if I have only one server, it seems that my question still remains: Am I missing something? Is there something silly or unwise about this strategy? Do I have misconceptions about making the database consistent in preparation for backup? ..jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LVM-snapshot + mysqldump -- is this a reasonable backup strategy?
This recipe is intended to minimize the impact on ongoing database operations by inhibiting writes only during a relatively speedy operation (creating a snapshot). The long dump operation can ... This seems to be a rather long winded way of doing this. Why not replicate the database and therefore not have to bring it down ever. I wrote a blog entry about this very thing, and had some interesting comments back on http://blog.spikesource.com/mysql_hotbackup.htm Hope this helps, alan -- Alan Williamson, Technology Evangelist SpikeSource Inc. Daily OS News @ http://compiledby.spikesource.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LVM-snapshot + mysqldump -- is this a reasonable backup strategy?
This recipe is intended to minimize the impact on ongoing database operations by inhibiting writes only during a relatively speedy operation (creating a snapshot). The long dump operation can then be performed on the (stable) snapshot, without interfering with ongoing use of the live database. 1. effectively quiesce and stabilize the database via flush tables with read lock 2. while writes are locked-out, make an LVM snapshot of the filesystem containing the db 3. after snapshot creation finishes, release the write-lockout via unlock tables 4. mount the snapshot 5. load a second database server daemon accessing the db within the snapshot (with a suitable alternate my.cnf file) 6. perform mysqldump operation on the snapshot-db 7. cleanup (unload second db server, unmount and delete snapshot) So what monsters lurk within this backup strategy? ..jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LVM-snapshot + mysqldump -- is this a reasonable backup strategy?
Hello. A few months ago you've been writing about issues with InnoDB when using a similar recipe. Please, share your experience of how you've solved that problems. James G. Sack (jim) [EMAIL PROTECTED] wrote: This recipe is intended to minimize the impact on ongoing database operations by inhibiting writes only during a relatively speedy operation (creating a snapshot). The long dump operation can then be performed on the (stable) snapshot, without interfering with ongoing use of the live database. 1. effectively quiesce and stabilize the database via flush tables with read lock 2. while writes are locked-out, make an LVM snapshot of the filesystem containing the db 3. after snapshot creation finishes, release the write-lockout via unlock tables 4. mount the snapshot 5. load a second database server daemon accessing the db within the snapshot (with a suitable alternate my.cnf file) 6. perform mysqldump operation on the snapshot-db 7. cleanup (unload second db server, unmount and delete snapshot) So what monsters lurk within this backup strategy? ..jim -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
