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]
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
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]
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
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
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
--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
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
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
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
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 mysql@lists.mysql.com 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
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
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]
