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]
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]
Seeking Backup Strategy
Hello, After many months of preparation, I am finally going to go live with a project I have created. It is your basic e-commerce site, where I need to make sure I have a current backup, specifically on the orders placed, etc. I am going to run the mysql server on a PC possibly running XP. (Small startup, and Tomcat is running there). I am considering buying a tape drive, and somehow backing up the database periodically throughout the day. I am sure there are some best practices for this. Could anyone enlighten me as to how to back this up, and how often. Up until now, I have always done database exports and imports, so I could use any info. Sincerely Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Backup Strategy
Scott Purcell wrote: Hello, After many months of preparation, I am finally going to go live with a project I have created. It is your basic e-commerce site, where I need to make sure I have a current backup, specifically on the orders placed, etc. I am going to run the mysql server on a PC possibly running XP. (Small startup, and Tomcat is running there). I am considering buying a tape drive, and somehow backing up the database periodically throughout the day. I am sure there are some best practices for this. Could anyone enlighten me as to how to back this up, and how often. Up until now, I have always done database exports and imports, so I could use any info. Sincerely Scott I use MySQL dump to dump the contents of the databases at several intervals during the day (before work, during first break, lunch, last break, end of the day), zip the .sql files, and distribute them to two other holding machines. The last dump of the day is put to tape. We aren't 24x7, and during the off times there are no users active (on break/out to lunch) during the dumping/zipping/copying. I haven't noticed any problems with this setup, however, YMMV. -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup strategy
On Tue, 04 May 2004 14:44:26 -0700 Ron Gilbert [EMAIL PROTECTED] wrote: Is there a better way to be doing this given the huge amount of binary data I have? You may wish to also look into replication, which is a cinch to setup with MySQL. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup strategy
You may wish to also look into replication, which is a cinch to setup with MySQL. Unfortunately replication does not handle point in time recovery. This is usually required to happen when someone accidentally drops a table or deletes too many rows from the database inadvertently. Under replication these changes will be dutifully applied to the replica. One mechanism would be to mirror the data disks, raid-1. This would provide the necessary reliability, but again will not account for user mistakes. Best bet is to utilize one of the backup strategies to make a copy of the data in a reasonable fashion. And this may also require replication so the actual backup may happen from the replica without unduly effecting the primary. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup strategy
On Tue, May 04, 2004 at 02:44:26PM -0700, Ron Gilbert wrote: I am wondering what the best backup strategy is for my database. The database is used to store a very large number of binary files, ranging from a few K to 20MB's. The database stores thousands of these files. I can not put this data on the file server, it needs to be in the database. Currently the database is about 1.7GB's and will grow over time to 4GB or higher. I created 20 identical tables to hold the binary data. I was worried about the 4GB/Tables limit, so figured I would spread it out over several tables, also there is no a single point of failure for loosing all my data. To do nightly backups (I don't need anything more frequent), I copy the whole database directory to another HD on the same server, then the files that changed are rsync'd to another server. One of the reason that I store the data in several tables is so only the tables that changed need to be rsync'd to the other machine. It is not on a local net, so it can take a while to do. In any given day, only 10 or so binary files are added, so not a lot changes from day to day, but it can be one some days When I move to 4.1 and start using InnoDB tables (or should I), will the same technique of copying the whole directory and sync'ing only that tables that changed still work? Nope. Is there a better way to be doing this given the huge amount of binary data I have? I'd consider enabling the binary log and backing it up. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup strategy
I am wondering what the best backup strategy is for my database. The database is used to store a very large number of binary files, ranging from a few K to 20MB's. The database stores thousands of these files. I can not put this data on the file server, it needs to be in the database. Currently the database is about 1.7GB's and will grow over time to 4GB or higher. I created 20 identical tables to hold the binary data. I was worried about the 4GB/Tables limit, so figured I would spread it out over several tables, also there is no a single point of failure for loosing all my data. To do nightly backups (I don't need anything more frequent), I copy the whole database directory to another HD on the same server, then the files that changed are rsync'd to another server. One of the reason that I store the data in several tables is so only the tables that changed need to be rsync'd to the other machine. It is not on a local net, so it can take a while to do. In any given day, only 10 or so binary files are added, so not a lot changes from day to day, but it can be one some days When I move to 4.1 and start using InnoDB tables (or should I), will the same technique of copying the whole directory and sync'ing only that tables that changed still work? Is there a better way to be doing this given the huge amount of binary data I have? I am running MySQL v3.23.58, but will probably be upgrading to 4.1 if it makes sense. This is running on RH9, but will be moved to a FreeBSD server in the near future. Thanks, Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
