Re: backup questions
Hi, Option 2 will not work. InnoDB has background threads that continue to change data even when the database is "quiet". This is a simplification. The details are too complicated to write in this thread, but there's an entire chapter on this topic in the book I'm writing right now, High Performance MySQL 2nd Edition :-) Suffice to say you *can* take backups by copying files with InnoDB, but you have to know what you're doing. You should use an LVM snapshot to get a consistent point-in-time snapshot of the files. You might try using mylvmbackup. Baron On Jan 28, 2008 8:43 AM, Michaël de Groot <[EMAIL PROTECTED]> wrote: > Hi Alex, > > I've used this method to start a replication slave without using MySQLdump to > get the data from one machine to another. > Option 1 works for sure, > Options 3 and 4 do not work for sure (if a .MYI, .MYD or ibdatax file is > changed while you are copying, you get a broken file on the other end). > I'm not sure about option 2, but I think it should work. > > Best regards, > > Michael > > > -Oorspronkelijk bericht- > > Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Namens Alex K > > Verzonden: maandag 28 januari 2008 14:34 > > Aan: MySQL General List > > Onderwerp: backup questions > > > > > Hi guys, > > > > Is it safe to perform a backup of a database by copying its mysql > > files if the database is: > > > > 1) MyISAM and the database is not being used (mysql server turned off)? > > > > 2) InnoDB and and the database is not being used? > > > > 3) MyISAM and the database is being used? > > > > 4) InnoDB and and the database is being used? > > > > I know I can use mysqhotcopy or mysqldump but I'm just curious if it's > > possible to perform a backup by simply copying the files over in the > > conditions described above. > > > > Thank you so much, > > > > Alex > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: backup questions
Hi Alex, I've used this method to start a replication slave without using MySQLdump to get the data from one machine to another. Option 1 works for sure, Options 3 and 4 do not work for sure (if a .MYI, .MYD or ibdatax file is changed while you are copying, you get a broken file on the other end). I'm not sure about option 2, but I think it should work. Best regards, Michael > -Oorspronkelijk bericht- > Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Namens Alex K > Verzonden: maandag 28 januari 2008 14:34 > Aan: MySQL General List > Onderwerp: backup questions > > Hi guys, > > Is it safe to perform a backup of a database by copying its mysql > files if the database is: > > 1) MyISAM and the database is not being used (mysql server turned off)? > > 2) InnoDB and and the database is not being used? > > 3) MyISAM and the database is being used? > > 4) InnoDB and and the database is being used? > > I know I can use mysqhotcopy or mysqldump but I'm just curious if it's > possible to perform a backup by simply copying the files over in the > conditions described above. > > Thank you so much, > > Alex > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backup questions
Hi guys, Is it safe to perform a backup of a database by copying its mysql files if the database is: 1) MyISAM and the database is not being used (mysql server turned off)? 2) InnoDB and and the database is not being used? 3) MyISAM and the database is being used? 4) InnoDB and and the database is being used? I know I can use mysqhotcopy or mysqldump but I'm just curious if it's possible to perform a backup by simply copying the files over in the conditions described above. Thank you so much, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
Its the same program, just Improved :-) People hate things they do not understand. You're probably right, and if I had memorized all the commands to be able to edit text, and had gotten used to it, I may like it. I've just gotten used to free-format editing w/o having to enter any commands to do it. Yeah, you'll probably be better sticking with your old program. Don't even look at GVIM, its VIM with a Graphical Interface, and that is VI Improved with a lot of features. So, you wouldn't like it. I downloaded and installed it, since it was pretty small. You're right, it's pretty much VI for Windows in a GUI. Probably not something that I'll use that often. What can I say?! Its a taste thing! But the fact that you had problems with your editor may indicate you'll have more problems in the future. VI is in every distro of Linux I've ever used so far, so, it was best to learn it in order to admin my servers in a better way, porting it for Windows saved me a lot of work (learning a new, win tool). Besides, what simple, fast and reliable editor you know can: VI is kind of like the old edlin program in DOS. I really HATED that one too. Talk about a bad editor, but that was a bad editor. You had to edit line-by-line. At least with VI, you can edit the whole thing at one time, and go back and forth. It's really a safe editor too, you have to do something to change the text, which is kind of good. I use Linux very infrequently, but I can always rely on VI to allow me to edit a configuration file, or whatever. 1) Automatically backup files. 2) Auto-ident code. 3) Highlight code from at least 100 languages (including SQL, C, PHP, Java, HTML). 4) Show you differences between files (oh, that helped me a lot with my.cnf) 5) Keep versions of old edited files 6) Remember the position where you left editing the file 7) Line numbering, jump to, copy paste visual or command, delete lines by number, delete multiple and much more editing features. 8) Much more stuff I don't use/know Multi-Edit does all of these things that you mentioned above, and much more! That's one of the reasons I like it so much. I've never really ran into a problem with this before, and it's possible that it's just a setting in my editor some where. There are SO MANY settings. Anyway, resolved for now, and I'll never throw away old faithful Multi-Edit (unless something better comes along). Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backup questions
GVIM is a great transition from vi to more 'modern' (read slower and cumbersome!) editors. It allows all the normal vi commands, but also 'standard' Windoze features like Ctrl-C Ctrl-V for copy and paste, drag and drop selection etc. Like all good editors, it takes time to get the most out of it, but at least it is usable out of the box (unlike vi!) Another one I like is ConTEXT, from www.context.cx. Not sure if its got a max line length, though. Quentin -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Tuesday, 4 July 2006 8:44 a.m. To: mysql@lists.mysql.com Subject: Re: Backup questions > There's GVIM for Windows, its the same program of Linux, I use it when > there's no way but using Windows. Get it at www.vim.org ! Its a blast > having the same (powerful, easy, fast and reliable) tool in windows > and linux. But you'll find it kinda hard to learn at first, because of > the "command mode". If it's anything like the "VI" editor in Linux, then I don't even want to think about it. I've used the VI editor several times, and I HATE IT! VIM sounds like the same sort of thing if it's got a "command mode". I've found a way to backup so that it wraps the lines correctly now, so I can use my old editor, which I'm used to. I may look into GVIM, though, just for grins-and-giggles. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
On 7/3/06, Jesse <[EMAIL PROTECTED]> wrote: > There's GVIM for Windows, its the same program of Linux, I use it when > there's no way but using Windows. Get it at www.vim.org ! Its a blast > having the same (powerful, easy, fast and reliable) tool in windows > and linux. But you'll find it kinda hard to learn at first, because of > the "command mode". If it's anything like the "VI" editor in Linux, then I don't even want to think about it. I've used the VI editor several times, and I HATE IT! VIM sounds like the same sort of thing if it's got a "command mode". Its the same program, just Improved :-) People hate things they do not understand. I've found a way to backup so that it wraps the lines correctly now, so I can use my old editor, which I'm used to. I may look into GVIM, though, just for grins-and-giggles. Yeah, you'll probably be better sticking with your old program. Don't even look at GVIM, its VIM with a Graphical Interface, and that is VI Improved with a lot of features. So, you wouldn't like it. What can I say?! Its a taste thing! But the fact that you had problems with your editor may indicate you'll have more problems in the future. VI is in every distro of Linux I've ever used so far, so, it was best to learn it in order to admin my servers in a better way, porting it for Windows saved me a lot of work (learning a new, win tool). Besides, what simple, fast and reliable editor you know can: 1) Automatically backup files. 2) Auto-ident code. 3) Highlight code from at least 100 languages (including SQL, C, PHP, Java, HTML). 4) Show you differences between files (oh, that helped me a lot with my.cnf) 5) Keep versions of old edited files 6) Remember the position where you left editing the file 7) Line numbering, jump to, copy paste visual or command, delete lines by number, delete multiple and much more editing features. 8) Much more stuff I don't use/know OK, I'll stop VIM stuff here, I just love it, but I'm pretty sure I can't convince most people, but I like to try. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
Oh, OK. I use MySQL Administrator frequently, but didn't realize that it could selectively restore. I don't like to use it for backups, because it doesn't get --routines and --triggers, and also doesn't backup in the format that I use. But I'll consider that. Thanks, Jesse - Original Message - From: "Brad Jahnke" <[EMAIL PROTECTED]> To: "Jesse" <[EMAIL PROTECTED]>; "MySQL List" Sent: Monday, July 03, 2006 11:06 AM Subject: Re: Backup questions 2) sometimes, I like to copy just a single table or so out of the backup file, and restore just that. You might want to try out MySQL Administrator which can often be used to restore backups from mysqldump. It can _selectively_ restore tables from a backup file. http://dev.mysql.com/doc/administrator/en/mysql-administrator-restore.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
There's GVIM for Windows, its the same program of Linux, I use it when there's no way but using Windows. Get it at www.vim.org ! Its a blast having the same (powerful, easy, fast and reliable) tool in windows and linux. But you'll find it kinda hard to learn at first, because of the "command mode". If it's anything like the "VI" editor in Linux, then I don't even want to think about it. I've used the VI editor several times, and I HATE IT! VIM sounds like the same sort of thing if it's got a "command mode". I've found a way to backup so that it wraps the lines correctly now, so I can use my old editor, which I'm used to. I may look into GVIM, though, just for grins-and-giggles. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
On 7/3/06, Jesse <[EMAIL PROTECTED]> wrote: > --routines, -R > and > --triggers Thanks, these did the trick, and it's put my procedures and triggers into the back up file. However, it has commented them out so that they will not be created if I do a restore to a new database. Not sure why... Security reasons maybe, this would make the dump not compatible with older versions of mysql that do not support triggers, procedures or functions. I guess there must be a way for mysqldump to uncomment this. > Change Editor ;-) > I personally use VIM and never had such problems. I've been using Multi-Edit for years, and have been very happy with it. VIM sounds like a Linux editor?? I'm working in Windows XP. There's GVIM for Windows, its the same program of Linux, I use it when there's no way but using Windows. Get it at www.vim.org ! Its a blast having the same (powerful, easy, fast and reliable) tool in windows and linux. But you'll find it kinda hard to learn at first, because of the "command mode". > --extended-insert (-e) will put multiple VALUES() statments in a > single line. Disable this feature (it is default for --opt) and will > get a single INSERT for each row. I thought this may be the way I needed to go, but wasn't sure. I will give this a try and see if it works. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
> 2) sometimes, I like to copy just a single table or so out of the backup > file, and restore just that. You might want to try out MySQL Administrator which can often be used to restore backups from mysqldump. It can _selectively_ restore tables from a backup file. http://dev.mysql.com/doc/administrator/en/mysql-administrator-restore.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
--routines, -R and --triggers Thanks, these did the trick, and it's put my procedures and triggers into the back up file. However, it has commented them out so that they will not be created if I do a restore to a new database. Not sure why... Change Editor ;-) I personally use VIM and never had such problems. I've been using Multi-Edit for years, and have been very happy with it. VIM sounds like a Linux editor?? I'm working in Windows XP. --extended-insert (-e) will put multiple VALUES() statments in a single line. Disable this feature (it is default for --opt) and will get a single INSERT for each row. I thought this may be the way I needed to go, but wasn't sure. I will give this a try and see if it works. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
Jesse wrote: Funny, i've never seen one that does? What system/editor are you using? Multi-Edit version 8.0i. This is an older version of the editor. Maybe a newer one wouldn't, but for the most part, it does a very good job for me. Ok never heard of multiedit... if your system is windows, try to use EditPlus. Under any *nix variant, ofcourse just use vim. try to use the --max_allowed_packet= option - afaik mysqldump will create lines as long as "max_allowed_packet" so if you set this to 8k you should get nice short lines This didn't seem to make any difference in the length of the line. Even if it did, I have a feeling it would go to the 8K column, and just chop it right there, the same way it is at the 16384th column. in that case you need to throw that editor as far as way as you can and never ever use it again ;) Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
Funny, i've never seen one that does? What system/editor are you using? Multi-Edit version 8.0i. This is an older version of the editor. Maybe a newer one wouldn't, but for the most part, it does a very good job for me. try to use the --max_allowed_packet= option - afaik mysqldump will create lines as long as "max_allowed_packet" so if you set this to 8k you should get nice short lines This didn't seem to make any difference in the length of the line. Even if it did, I have a feeling it would go to the 8K column, and just chop it right there, the same way it is at the 16384th column. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
On 7/3/06, Jesse <[EMAIL PROTECTED]> wrote: I'm trying to determine the best way to back up my MySQL databases in such a way that they can be easily restored, flexible, and complete. I've experimented with physically copying the files (with disastrous results). MySQLDump seems to be the way to go, but I've got two problems with that which I don't know how to overcome: 1) MySQLDump does not appear to get Procedures, Functions, and Triggers. What I'm doing right now for this is manually copying those to another file which I've got, then if I have to restore a database, I have to manually add the procedures, functions, and triggers back into the database. There are options called: --routines, -R and --triggers But you need latest version of MySQL Client tools. 2) sometimes, I like to copy just a single table or so out of the backup file, and restore just that. That's why I really like the way MySQLDump works, because it creates SQL code that I can simply run to re-create and restore the records. Problems is that a table/create/insert is one continuous line, with no line breaks, So, if I've got a table with 100,000 records, I cannot copy that table out, because my editor forces a hard line break at column position 16384, which, of course, corrupts the restore. I don't know if there are other text editors that will not do this, or even better, if there is a way to add a hard line break after each inserted record. I realize this will increase the size of the backup file, but to me, it's worth it. Change Editor ;-) I personally use VIM and never had such problems. Ok, now, to the point: --extended-insert (-e) will put multiple VALUES() statments in a single line. Disable this feature (it is default for --opt) and will get a single INSERT for each row. Any ideas? Yes! Windows: Mysql Manual - Mysqldump section Linux: man mysqldump It's all there... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup questions
Jesse wrote: my editor forces a hard line break at column position 16384, which, of course, corrupts the restore. I don't know if there are other text editors that will not do this, Funny, i've never seen one that does? What system/editor are you using? or even better, if there is a way to add a hard line break after each inserted record. I realize this will increase the size of the backup file, but to me, it's worth it. try to use the --max_allowed_packet= option - afaik mysqldump will create lines as long as "max_allowed_packet" so if you set this to 8k you should get nice short lines Any ideas? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup questions
I'm trying to determine the best way to back up my MySQL databases in such a way that they can be easily restored, flexible, and complete. I've experimented with physically copying the files (with disastrous results). MySQLDump seems to be the way to go, but I've got two problems with that which I don't know how to overcome: 1) MySQLDump does not appear to get Procedures, Functions, and Triggers. What I'm doing right now for this is manually copying those to another file which I've got, then if I have to restore a database, I have to manually add the procedures, functions, and triggers back into the database. 2) sometimes, I like to copy just a single table or so out of the backup file, and restore just that. That's why I really like the way MySQLDump works, because it creates SQL code that I can simply run to re-create and restore the records. Problems is that a table/create/insert is one continuous line, with no line breaks, So, if I've got a table with 100,000 records, I cannot copy that table out, because my editor forces a hard line break at column position 16384, which, of course, corrupts the restore. I don't know if there are other text editors that will not do this, or even better, if there is a way to add a hard line break after each inserted record. I realize this will increase the size of the backup file, but to me, it's worth it. Any ideas? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnodB Hot Backup Questions
David, - Original Message - From: ""David Griffiths"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, May 14, 2004 8:05 PM Subject: Re: InnodB Hot Backup Questions > Sorry - haven't had a chance to respond till now. > > > > So "restore" == "apply-log", but one works on any computer, and the > other > > > only works on the computer that it's node locked to. > > > --apply-log works also in any computer regardless of the hostname or the > > license expiration date. > > > I'm running ibbackup on an unlicenced machine. The manual states, > > "ibbackup --apply-log /home/pekka/.backup-my.cnf" the manual is already updated for ibbackup-2.0, while there is no --apply-log option at all in ibbackup-1.40 :(. In 1.40 you must use --restore. Please use ./ibbackup --help to look at the available options in 1.40. ibbackup-2.0.0 has now been declared stable on Linux, and on Linux we are only shipping 2.0.0 from now on. > Here's the output with --apply-log (some sensitive info stripped) > > --- > mydb2:/data # ./ibbackup --apply-log ./my2.cnf > InnoDB Hot Backup version 1.40; Copyright 2003 Innobase Oy > License xxx is granted to > (--restore works in any computer regardless of the hostname) > Licensed for use in a computer whose hostname is 'hrdb1' > Expires 2005-6-1 (year-month-day) at 00:00 > See http://www.innodb.com for further information > Type ibbackup --license for detailed license terms, --help for help > > --- Error: the hostname of this computer is 'mydb2'. I guess ibbackup interprets that your first my.cnf file is called '--apply-log', it does not understand it is an option (of 2.0)! Then it refuses to run because the hostname of the computer does not match. ... > David. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnodB Hot Backup Questions
Sorry - haven't had a chance to respond till now. > > So "restore" == "apply-log", but one works on any computer, and the other > > only works on the computer that it's node locked to. > --apply-log works also in any computer regardless of the hostname or the > license expiration date. I'm running ibbackup on an unlicenced machine. The manual states, "ibbackup --apply-log /home/pekka/.backup-my.cnf" Here's the output with --apply-log (some sensitive info stripped) --- mydb2:/data # ./ibbackup --apply-log ./my2.cnf InnoDB Hot Backup version 1.40; Copyright 2003 Innobase Oy License xxx is granted to (--restore works in any computer regardless of the hostname) Licensed for use in a computer whose hostname is 'hrdb1' Expires 2005-6-1 (year-month-day) at 00:00 See http://www.innodb.com for further information Type ibbackup --license for detailed license terms, --help for help --- Error: the hostname of this computer is 'mydb2'. Please contact [EMAIL PROTECTED] for a license renewal. If I run with --restore, however (same directory, same files, 30 seconds after the above command was run), mydb2:/data # ./ibbackup --restore ./my2.cnf InnoDB Hot Backup version 1.40; Copyright 2003 Innobase Oy License xxx is granted to (--restore works in any computer regardless of the hostname) Licensed for use in a computer whose hostname is 'hrdb1' Expires 2005-6-1 (year-month-day) at 00:00 See http://www.innodb.com for further information Type ibbackup --license for detailed license terms, --help for help Contents of ./my2.cnf: innodb_data_home_dir got value /data innodb_data_file_path got value ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata 4:100M:autoextend innodb_log_group_home_dir got value /data innodb_log_files_in_group got value 2 innodb_log_file_size got value 104857600 ibbackup: We were able to parse ibbackup_logfile up to ibbackup: lsn 5 2076170601 040514 9:51:43 ibbackup: Full backup restoration successful! - Am I doing something wrong with regards to --apply-log? > Looks like you have taken the backup with ibbackup-2.0, but are trying to > run --apply-log with ibbackup-1.40. You should use the same or later > ibbackup version to run --apply-log. Maybe you have forgotten to replace > your old ibbackup-1.40 binary with the new ibbackup-2.0 binary, and the > innobackup script is using the old binary? That's a possibility - we were using a demo-ibbackup for a month or two, before ordering the full version - I guess the demo was beta, and the licenced version was 1.4. There is no version # in the name of the file, and I didn't think to check the version by running with the --help flag. I tried with a more recent backup, and it worked (as you can see above). > That is safer. It can reveal corruption. > > > Or is it only done if you need > > to use the backup? > > --apply-log is needed to use the backup. But I recommend doing it > immediately after you have taken the backup. > > In short, the logic is this: > > 1. Take a backup. > 2. Run --apply-log to make the backup a 'consistent snapshot'. > 3. Use the backup. Ok - thx - that makes sense. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnodB Hot Backup Questions
David, - Original Message - From: ""David Griffiths"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, May 13, 2004 7:50 AM Subject: InnodB Hot Backup Questions > I'm hoping someone on the list has some experience with the tool > (specifically, restoring a backup), as I'm stumped. > > First, "--apply-log" seems to only work on the host that ibbackup is > lode-locked to. This doesn't make sense. Running the tool dumpes out some > text, and part of that text is, > > "(--restore works in any computer regardless of the hostname)" > > The manual says, > > "Therefore, the option --apply-log was added as a synonym for --restore in > the version 1.40 of ibbackup, and the option name --restore will become > deprecated." > > So "restore" == "apply-log", but one works on any computer, and the other > only works on the computer that it's node locked to. --apply-log works also in any computer regardless of the hostname or the license expiration date. > Am I missing something obvious? And what happens after version 1.4? No one > is able to apply the binary log file if they need to test the backup on a > different server? > > Second, the manual has a bunch of inconsistencies w/regards to the "my.cnf" > file you are supposed to use to apply the log. > > Section 3 says, > > We run ibbackup to roll forward the data files so that they correspond to > the same log sequence number: > > $ ibbackup --apply-log /home/pekka/.backup-my.cnf > > Note the usage of the backup-my.cnf file; this is the file that ibbackup > uses to figure out how to actually perform the backup, as opposed to the > my.cnf file that is used to actually configure the MySQL server. > > but Section 5.3 says, > > "innobackup --apply-log /home/pekka/.my.cnf > /home/pekka/script/backups/2004-02-03_13-27-09" > > Notice that the my.cnf file appears to be the normal config file for mysqld > (farther down the page, you find out that the file is infact a copy of the > my.cnf file). There is the backup-my.cnf file in the directory with the data > files, and it's not used. The above line uses the PERL interface, and thus > the ibbackup->innobackup. Section 5.3 is about a the innobackup Perl script. It has different usage than the ibbackup binary. > Which .cnf file is supposed to be used? The Server config file (my.cnf) or In fact, the my.cnf file parameter in the innobackup Perl script is not used at all! I have asked Pekka to remove the extra parameter, because it confuses users. > the ibbackup config file (you can name it whatever you want)? Note that both > seem to do exactly the same thing; I guess the issue here is more clarity > than anything else. > > > Finally, > > When I run --restore (apply-log doesn't work, remember?) I get, > > 040512 21:38:12 ibbackup: ibbackup_logfile's creation parameters: > ibbackup: start lsn 5 1804179456, end lsn 5 1804179476, > ibbackup: start checkpoint 5 1804179476 > ibbackup: Error: backed up log file segment has a wrong magic n:o 542632761. > ibbackup: Run 'ibbackup --help' for help. #define BACK_UP_LOG_END_MAGIC_N 98435789 /* The log end is new format if we store info of possible --include regexp option when the backup was taken */ #define BACK_UP_LOG_END_NEW_FORMAT_MAGIC_N 542632761 Looks like you have taken the backup with ibbackup-2.0, but are trying to run --apply-log with ibbackup-1.40. You should use the same or later ibbackup version to run --apply-log. Maybe you have forgotten to replace your old ibbackup-1.40 binary with the new ibbackup-2.0 binary, and the innobackup script is using the old binary? > There is no troubleshooting section in the manual to explain the error, and > ibbackup --help - it's focus is on the backup, not the restore. Googling on > the message returns no pages. > > > Unfort, support for the tool costs much more than the tool itself, and is > basically 10 emails a year. Support costs 590 euros per year, the tool 390 euros per year. > Can anyone provide some insight? The whole apply-log thing is confusing - > should it be done immediately after a backup? That is safer. It can reveal corruption. > Or is it only done if you need > to use the backup? --apply-log is needed to use the backup. But I recommend doing it immediately after you have taken the backup. In short, the logic is this: 1. Take a backup. 2. Run --apply-log to make the backup a 'consistent snapshot'. 3. Use the backup. > Thx, > David Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnodB Hot Backup Questions
I'm hoping someone on the list has some experience with the tool (specifically, restoring a backup), as I'm stumped. First, "--apply-log" seems to only work on the host that ibbackup is lode-locked to. This doesn't make sense. Running the tool dumpes out some text, and part of that text is, "(--restore works in any computer regardless of the hostname)" The manual says, "Therefore, the option --apply-log was added as a synonym for --restore in the version 1.40 of ibbackup, and the option name --restore will become deprecated." So "restore" == "apply-log", but one works on any computer, and the other only works on the computer that it's node locked to. Am I missing something obvious? And what happens after version 1.4? No one is able to apply the binary log file if they need to test the backup on a different server? Second, the manual has a bunch of inconsistencies w/regards to the "my.cnf" file you are supposed to use to apply the log. Section 3 says, We run ibbackup to roll forward the data files so that they correspond to the same log sequence number: $ ibbackup --apply-log /home/pekka/.backup-my.cnf Note the usage of the backup-my.cnf file; this is the file that ibbackup uses to figure out how to actually perform the backup, as opposed to the my.cnf file that is used to actually configure the MySQL server. but Section 5.3 says, "innobackup --apply-log /home/pekka/.my.cnf /home/pekka/script/backups/2004-02-03_13-27-09" Notice that the my.cnf file appears to be the normal config file for mysqld (farther down the page, you find out that the file is infact a copy of the my.cnf file). There is the backup-my.cnf file in the directory with the data files, and it's not used. The above line uses the PERL interface, and thus the ibbackup->innobackup. Which .cnf file is supposed to be used? The Server config file (my.cnf) or the ibbackup config file (you can name it whatever you want)? Note that both seem to do exactly the same thing; I guess the issue here is more clarity than anything else. Finally, When I run --restore (apply-log doesn't work, remember?) I get, 040512 21:38:12 ibbackup: ibbackup_logfile's creation parameters: ibbackup: start lsn 5 1804179456, end lsn 5 1804179476, ibbackup: start checkpoint 5 1804179476 ibbackup: Error: backed up log file segment has a wrong magic n:o 542632761. ibbackup: Run 'ibbackup --help' for help. There is no troubleshooting section in the manual to explain the error, and ibbackup --help - it's focus is on the backup, not the restore. Googling on the message returns no pages. Unfort, support for the tool costs much more than the tool itself, and is basically 10 emails a year. Can anyone provide some insight? The whole apply-log thing is confusing - should it be done immediately after a backup? Or is it only done if you need to use the backup? Thx, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup Questions
Can anyone help me with some questions about backup scripts?
I've written a bash script to take database-level backups of each of the
databases in our copy of MySQL. We are running MySQL 4.0.15 on Linux
Mandrake 9.1 and using a mix of MyISAM and InnoDB databases. This is my script,
with the userid and password faked for obvious reasons:
#--
#!/bin/bash
#This script makes a separate database-level backup of each of the current
MySQL databases and#deletes backups older than a certain number of
days.#This script is normally invoked via a cron job so that it runs once
per day in the middle of the night.#The crontab entry looks like this:#0
3 * * * sh /home/foo/MySQL/backup.bash > /home/foo/MySQL/backup.out
2>&1
DEBUG=1; #DEBUG switchUSERID="foo"; #The userid to use for creating the
backupPASSWORD="bar"; #The password to use for creating the
backupBACKUP_TIMESTAMP=`/bin/date +%Y%m%d"-"%H%M%S`; #The timestamp
(MMDD-HHMMSS) of the backupBACKUP_PATH="/home/foo/MySQL/backup"; #The
directory into which the backup will be
writtenNUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of
backups to keep
#Display the non-secret values used in this run.if test $DEBUG -eq
1then echo "BACKUP VALUES:"; echo "
Backup timestamp is" $BACKUP_TIMESTAMP; echo " Backup path
is" $BACKUP_PATH; echo " Number of daily backups to keep
=" $NUMBER_OF_DAILY_BACKUPS_TO_KEEP; fi
#For each database currently in MySQL, take a database-level backup, then
list any backups older than a certain number of days, then delete these old
backupsfor ONE_DBNAME in `echo show databases | mysql -s -u $USERID
-p$PASSWORD`do echo echo "Backing up
database" $ONE_DBNAME; /usr/bin/mysqldump --opt --verbose
-u${USERID} -p${PASSWORD} ${ONE_DBNAME} -r
${BACKUP_PATH}"/"${ONE_DBNAME}"."${BACKUP_TIMESTAMP}".sql" echo
">> Deleting these old backups for this database..."
/usr/bin/find ${BACKUP_PATH} -mtime +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name
$ONE_DBNAME'*' -print; #display old backups (if any)
/usr/bin/find ${BACKUP_PATH} -mtime +$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name
$ONE_DBNAME'*' -exec rm '{}' ';'; #delete old backups (if any)done
#-
The script seems to work just fine at the moment but I'm concerned that it
isn't equipped to handle errors very well. Unfortunately, I'm not very clear on
exactly what sort of errors *can* occur when doing backups, let alone how they
can be handled. Can anyone tell me from their own experience what errors we are
likely to encounter in doing mysqldumps and how we can recognize them when they
happen? Also, since I'm pretty new to bash, I'd appreciate some hints on how to
catch errors with a bash script. For instance, if we encounter a full disk while
writing the backup, what sort of error will we get and how will bash know what
the error was so that it can tell the user of the script?
In a nutshell, I'd like to generate a simple email in any case where the
backup doesn't work correctly and send it to the appropriate administrators so
that they can take action. The first action they would take is to look at the
output from the script (which is redirected to a file) so the email should
contain the name of that file.
Rhino---rhino1 AT sympatico DOT ca"If you want the best
seat in the house, you'll have to move the cat."
