Trying to open a big sql script
Hi all, I have backed up a database using mysqldump and have a .sql script that is over 2GB in size. I am trying to open this file to view it and make some changes. I have not been able to find a program that can open this file. Does anyone have any suggestions as to a program that can do this? Thanks in advance -- View this message in context: http://www.nabble.com/Trying-to-open-a-big-sql-script-tf3606302.html#a10075570 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to open a big sql script
On Thursday 19 April 2007 11:43:34 molemenacer wrote: Hi all, I have backed up a database using mysqldump and have a .sql script that is over 2GB in size. I am trying to open this file to view it and make some changes. I have not been able to find a program that can open this file. Does anyone have any suggestions as to a program that can do this? You need an editor that will only load the current view of the file into memory. I'm not sure that such a beast exists, other than stream editors such as sed or perl. Can the changes you need to make be done with stream editing (simple changes like changing a word or two are very easy with stream editor)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to open a big sql script
That's a much larger file than most any text editor would work with, in my experience. I'd give BBEdit on the Mac a try if nothing else, but my expectations would not be too high. For examining and altering a file that large I'd try grep, awk, sed, perl, etc. Barring that, one thing you might do is use perl or another scripting language (or perhaps some utility software) to read the file in 100 MB or so chunks and write out to a series of smaller files. Edit the smaller files, then use shell command to cat them all back into one bigger file. HTH, Dan On 4/19/07, molemenacer [EMAIL PROTECTED] wrote: Hi all, I have backed up a database using mysqldump and have a .sql script that is over 2GB in size. I am trying to open this file to view it and make some changes. I have not been able to find a program that can open this file. Does anyone have any suggestions as to a program that can do this? Thanks in advance -- View this message in context: http://www.nabble.com/Trying-to-open-a-big-sql-script-tf3606302.html#a10075570 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to open a big sql script
On Thu, April 19, 2007 12:48, Duncan Hill wrote: On Thursday 19 April 2007 11:43:34 molemenacer wrote: Hi all, I have backed up a database using mysqldump and have a .sql script that is over 2GB in size. I am trying to open this file to view it and make some changes. I have not been able to find a program that can open this file. Does anyone have any suggestions as to a program that can do this? You need an editor that will only load the current view of the file into memory. I'm not sure that such a beast exists, other than stream editors such as sed or perl. Can the changes you need to make be done with stream editing (simple changes like changing a word or two are very easy with stream editor)? Also, replace from your mysql-installation could be used,if you only need to replace a-string with b-string :) -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql and disk space left
Hi all, I wonder what is the impact of the disk space left for a mysql DB (MyIsam, Linux 2.6, Ext3, RAID5). I mean, I there a kind of limit to not cross to limit the performances impact ? Cheers, Manuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem on millions of records in one table?
He, Ming Xin PSE NKG wrote: Hi, Pipes Is it reliable to use MySQL 5.1 in a commercial product now since it is still a beta version? Hmmm. Probably depends on what you are doing with it... But, in general, it's fairly sta ble at this point but, like all beta software, cannot be considered a production version. Cheers, Jay -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 18, 2007 10:04 PM To: Brent Baisley Cc: He, Ming Xin PSE NKG; mysql@lists.mysql.com Subject: Re: Problem on millions of records in one table? Brent Baisley wrote: It all depends on how complicated your data and searches are. I've got tables that add 2-3 million per day and I don't have performance problems. Although we only retain at most 500 millions records, not a full years worth. That said, you can get horrible performance out of mysql with tables as small as 100,000 records if you don't structure your queries correctly or use a good table structure. If I know the tables are going to grow quickly and I don't need the entire dataset all the time, I'll use merge tables. This makes it easy to remove old data easily from the default table set. Hi! Have you tried out the new partitioning features of MySQL 5.1 to do this? Would be cool if you had some performance numbers comparing the older MERGE table method with the newer partitioning... Cheers! Jay - Original Message - From: He, Ming Xin PSE NKG [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 17, 2007 11:03 PM Subject: Problem on millions of records in one table? Hi,all The number of the records in one table increase constantly. As evaluated, the amount would increase to at least 30 millions within one year. So we worry about whether mysql could handle such a big amount of records with good performance. Or need we some other solutions to avoid this problem ,such as using Partition, dividing a big table and etc. Any help or idea would be greatly appreciated. Best Regards mingxin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to open a big sql script
It might be a little late for this advice but mysqldump has some useful options for this kind of thing. When I use it to create full snapshots, I use a script which generates a separate schema file per table/view and keep the data in one or more per table for data. naming conventions keep keep the files grouped. the little known --where option is handy for the later. As for editors, I have used vi frequently in the past to edit larger-than-RAM files but never anything that big. On 4/19/07, Mogens Melander [EMAIL PROTECTED] wrote: On Thu, April 19, 2007 12:48, Duncan Hill wrote: On Thursday 19 April 2007 11:43:34 molemenacer wrote: Hi all, I have backed up a database using mysqldump and have a .sql script that is over 2GB in size. I am trying to open this file to view it and make some changes. I have not been able to find a program that can open this file. Does anyone have any suggestions as to a program that can do this? You need an editor that will only load the current view of the file into memory. I'm not sure that such a beast exists, other than stream editors such as sed or perl. Can the changes you need to make be done with stream editing (simple changes like changing a word or two are very easy with stream editor)? Also, replace from your mysql-installation could be used,if you only need to replace a-string with b-string :) -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to open a big sql script
I am trying to change all the names of the database from mthosp to another name, is this possible? Dan Buettner-2 wrote: That's a much larger file than most any text editor would work with, in my experience. I'd give BBEdit on the Mac a try if nothing else, but my expectations would not be too high. For examining and altering a file that large I'd try grep, awk, sed, perl, etc. Barring that, one thing you might do is use perl or another scripting language (or perhaps some utility software) to read the file in 100 MB or so chunks and write out to a series of smaller files. Edit the smaller files, then use shell command to cat them all back into one bigger file. HTH, Dan On 4/19/07, molemenacer [EMAIL PROTECTED] wrote: Hi all, I have backed up a database using mysqldump and have a .sql script that is over 2GB in size. I am trying to open this file to view it and make some changes. I have not been able to find a program that can open this file. Does anyone have any suggestions as to a program that can do this? Thanks in advance -- View this message in context: http://www.nabble.com/Trying-to-open-a-big-sql-script-tf3606302.html#a10075570 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Trying-to-open-a-big-sql-script-tf3606302.html#a10078655 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to open a big sql script
On Thursday 19 April 2007 15:53:54 molemenacer wrote: I am trying to change all the names of the database from mthosp to another name, is this possible? Assuming you mean tables, not database (as mysqldump doesn't store the database name in the dump file [or at least never has for me]): sed -e 's/mthosp/another_name/' source.sql dest.sql mysql rename table mthosp_1 to another_name_1, mthosp_2 to another_name_2 (Check the manual for syntax) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to open a big sql script
Something like this should do the trick: ~#replace mthosp newname org-file.sql new-file.sql -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Thu, April 19, 2007 16:53, molemenacer wrote: I am trying to change all the names of the database from mthosp to another name, is this possible? Dan Buettner-2 wrote: That's a much larger file than most any text editor would work with, in my experience. I'd give BBEdit on the Mac a try if nothing else, but my expectations would not be too high. For examining and altering a file that large I'd try grep, awk, sed, perl, etc. Barring that, one thing you might do is use perl or another scripting language (or perhaps some utility software) to read the file in 100 MB or so chunks and write out to a series of smaller files. Edit the smaller files, then use shell command to cat them all back into one bigger file. HTH, Dan On 4/19/07, molemenacer [EMAIL PROTECTED] wrote: Hi all, I have backed up a database using mysqldump and have a .sql script that is over 2GB in size. I am trying to open this file to view it and make some changes. I have not been able to find a program that can open this file. Does anyone have any suggestions as to a program that can do this? Thanks in advance -- View this message in context: http://www.nabble.com/Trying-to-open-a-big-sql-script-tf3606302.html#a10075570 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Trying-to-open-a-big-sql-script-tf3606302.html#a10078655 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help please: SELECT in binlog?
We recently switched to mysql5 and while we were at it we also changed our logs from text to bin as suggested by the migration script we had (probably created by debian people). Now I unfortunately had to reconstruct what had happened during a faulty run of our application and I could not get any SELECT statement from the log!? The usual search engine run didnt bring up anything useful, so my questions are: 1) Are the selects somwhere in the binlogs and I just have not found the right voodoo to make the come out? 2) If they are not there by default, can I configure mysqld to store SELECTs in a binlog? 3) If not, is the old text log all I can go back to? thanks for reading, Fionn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help please: SELECT in binlog?
Fionn Behrens wrote: We recently switched to mysql5 and while we were at it we also changed our logs from text to bin as suggested by the migration script we had (probably created by debian people). Now I unfortunately had to reconstruct what had happened during a faulty run of our application and I could not get any SELECT statement from the log!? The usual search engine run didnt bring up anything useful, so my questions are: 1) Are the selects somwhere in the binlogs and I just have not found the right voodoo to make the come out? No, no selects. Only commands that change data are replicated, AFAIK. 2) If they are not there by default, can I configure mysqld to store SELECTs in a binlog? Not that I know of. 3) If not, is the old text log all I can go back to? You can have both, AFAIK. The general query log keeps all queries, including SELECTs. Binlog only has data-modifying queries. Cheers, jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help please: SELECT in binlog?
On Do, 2007-04-19 at 13:57 -0400, Jay Pipes wrote: You can have both, AFAIK. The general query log keeps all queries, including SELECTs. Binlog only has data-modifying queries. Thanks very much for your answer. Maybe the fact that binlogs apparently are quite different from normal text logs should be clearly mentioned somewhere in the docs. Especially the mysqlbinlog manpage might be a good place to mention that SELECT statements can not be restored with it. kind regards, Fionn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help please: SELECT in binlog?
Fionn Behrens wrote: On Do, 2007-04-19 at 13:57 -0400, Jay Pipes wrote: You can have both, AFAIK. The general query log keeps all queries, including SELECTs. Binlog only has data-modifying queries. Thanks very much for your answer. Maybe the fact that binlogs apparently are quite different from normal text logs should be clearly mentioned somewhere in the docs. Especially the mysqlbinlog manpage might be a good place to mention that SELECT statements can not be restored with it. I think it's already fairly clearly stated that the binlog is only for replaying queries which would have modified the database. After all, why would you want to re-run a select query? From the first paragraph of the manual page describing the binlog: http://dev.mysql.com/doc/refman/5.0/en/binary-log.html The binary log contains all statements that update data or potentially could have updated it (for example, a DELETE which matched no rows). Statements are stored in the form of events that describe the modifications. I agree that the mysqlbinlog manpage doesn't clearly state it, but it does refer you to the section of the documentation about the binlog which does. Cheers Dave P -- David Precious http://blog.preshweb.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
character_set_xxx
Hi, How can I change character_set_xxx variables in MySQL 4.1.x in Linux? Regards, Behrang -- Behrang Saeedzadeh http://www.jroller.com/page/behrangsa http://my.opera.com/behrangsa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trying to open a big sql script
If you are under windows, you should try Textpad (http://www.textpad.com/) In any case, a hex editor will do what you're looking for. Hope to help, Andy -Original Message- From: molemenacer [mailto:[EMAIL PROTECTED] Sent: Thursday, April 19, 2007 6:44 AM To: mysql@lists.mysql.com Subject: Trying to open a big sql script Hi all, I have backed up a database using mysqldump and have a .sql script that is over 2GB in size. I am trying to open this file to view it and make some changes. I have not been able to find a program that can open this file. Does anyone have any suggestions as to a program that can do this? Thanks in advance -- View this message in context: http://www.nabble.com/Trying-to-open-a-big-sql-script-tf3606302.html#a10 075570 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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]
