RE: LVM-snapshot + mysqldump -- is this a reasonable backup
On Mon, 2005-09-19 at 12:15 -0400, George Herson wrote: Dear Jim, Re: your post at http://lists.mysql.com/mysql/189058, why bother creating the mysqldump if you already have the snapshot? Why not just backup the snapshot? (I'd have hit reply online but didn't see a Reply button and didn't want to bother with joining the forum yet.) George, I think it's ok to post to the list even if one is not not a subscribed member -- perhaps someone will correct me if this is wrong. Unless you have a personal message, or have been specifically asked to communicate something offlist, the benefit of posting to the list is that everybody gets to learn (and ask). Now, to respond to your question.. The output of mysqldump *is* the backup. If the db goes away, it can be restored with (something like) mysql dumpfile.2005-09-01. The dump operation is run periodically, and some number of back versions can be kept around (or offloaded) for archival value. Anyway, that expresses my intention. It is admittedly and intentionally a simple-minded backup strategy -- I am hopeful that more experienced list contributors will critique the idea or suggest improvements. ..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
On Mon, 2005-09-19 at 13:14 -0400, George Herson wrote: James G. Sack (jim) wrote: On Mon, 2005-09-19 at 12:15 -0400, George Herson wrote: Dear Jim, Re: your post at http://lists.mysql.com/mysql/189058, why bother creating the mysqldump if you already have the snapshot? Why not just backup the snapshot? [...] The output of mysqldump *is* the backup. If the db goes away, it can be restored with (something like) mysql dumpfile.2005-09-01. The dump operation is run periodically, and some number of back versions can be kept around (or offloaded) for archival value. Yes, but can't you also save your snapshot instead, then, when/if you want, restore it, 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 ?? .. George, LVM snapshots are generally intended to be short-lived -- they give you a virtual copy of the underlying (origin) filesystem (as it existed at one point in time), which occupies only a fraction of the original storage space because it only deals with changes that occur to the origin fs over the lifetime of the snapshot. The snapshot point-in-time concept allows running some slow process (typically, maybe, writing to a tape device) without needing to be concerned that the data is changing while you are copying it. The database does need to be made consistent (locked) at the point of taking the snapshot, but creating the snapshot only takes seconds. So the database may be write-unlocked after only a minor disruption, instead of during the entire backup operation. If the snapshot were kept around indefinitely, it would eventually need perhaps as much storage as the origin. So typically one deletes a snapshot after using it for a staging operation such as above. A further consideration is that the snapshot adds overhead to every disk operation, so that's another incentive to minimize the snapshot lifetime. ..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?
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]
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+innodb
On Mon, 2005-08-15 at 20:01 -0700, Patrick Michael Kane wrote: Did you ever get any responses on how to get innodb to a consistent on-disk state for LVM snapshotting? FYI, under LVM2 read/write snapshots are allowed. I never got any replies. If you look closely into my rambling posting, you will see that I am aware of the LVM2 option, and that I solicit feed back on whether it's a good idea to mount snapshots r/w. Regards, ..jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb engine breaks snapshot-based backup strategy
Am I doing something wrong, or does the innodb engine design preclude loading a server with a readonly database snapshot? I'm talking about 4.1.x (or maybe 4.x) and linux lvm snapshots (lvm2) specifically about MySQL-4.1.12 (mysql-4.1.12-2.FC4.1.x86_64.rpm) tested on an AMD-64 + FC4 (linux 2.6.11 (or .12, maybe) kludges used debug build (--with-debug=full) variation of the rpm I believe a common backup strategy (works for myisam) is the following: -- -flush tables with read lock -lvmcreate -s (snapshot) -unlock tables -mount snapshot (readonly) -mysqld_safe --defaults-file=path-to/my.cnf2 --err-log=/tmp/elog2 where my.cnf2 specifies the snapshot mount point as the datadir -mysqldump --defaults-file=path-to/my.cnf2 --databases --opt testdb testdb.dump (..time passes..) Then after completion of the dump, umount and lvremove the snapshot --- This seems to me to be a reasonably useful recipe -- does anyone have issues with this statement? For innodb I have made what I think is appropriate adjustments to the config and procedure, but cannot get it to work. The first problem is that mysqld fails to start because it cannot open ibdata1 (..) for read-write (on the ro snapshot mount). I can get some encouragement of sorta-almost getting it to work by: -kludging the code in innobase/os/os0file.c to open the data files with O_RDONLY and changing the locking to F_RDLCK. -copying the ib_logfile[01] files to r/w disk and adjusting the innodb_log* config After the above, I can actually get the cnf2-server to load and even respond to some simple select queries. I did no extensive testing, but instead tried to run mysqldump. It seems to proceed nicely -- but after a while fails with .. InnoDB: Warning: we did not need to do crash recovery, but log scan InnoDB: progressed past the checkpoint lsn 0 93795910 up to lsn 0 93795920 050811 9:53:24 InnoDB: Error: Write to file /mnt/scratch/mysql/ibdata1 failed at offset 0 1048576. InnoDB: 16384 bytes should have been written, only -1 were written. .. 050811 9:53:24 InnoDB: Assertion failure in thread 46912496362752 in file fil0fil.c line 3924 InnoDB: Failing assertion: ret InnoDB: We intentionally generate a memory trap. .. server terminates Now I didn't try to debug mysqldump any further, because it seems there may be pervasive assumptions that the ibdata files are writable (I guess stemming from the multi-user concurrency, or something like that). Shame though, eh? Q? Perhaps there are some further mods similar to the flavor of my kludges, that allow innodb to run in a fully-readonly mode? Q? Perhaps the opt_readonly is aready part of the solution? Q? Perhaps there's a better way (preferably w/o buying InnoDB Hot Backup). Q? I do kinda feel that innodb (any database) really ought to allow a readonly mode. Am I unreasonable? SIDE ISSUE ** It *is* possible to get to get the dump to work by mounting the snapshot read-write! No kludging, no moving of the logfiles required. Q? Am I wrong to be squeamish about mounting lvm snapshots read-write? Q? Has anybody done such -- and verified that the dumps are valid? Q? Even if that works and is safe, am I out-of line asking for readonly? - Thanks all, for your forbearance! ...jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
