Working with Large SQL Dumps

[Anduril66]

I have a large (1.5 gb) Sql dump of an article accessory. I would like to work on the SQL and remove most of the articles before I upload it to my webserver. What is the best way to edit a file you cannot open because it is bigger than your memory. Also, is there any utilities you can use to edit a SQL dump without uploading the SQL to a webserver?

Thanks

[redwine]

you install sql server locally and use SQL queries to delete rows instead of editing sql dump file. This is how i manage 10GB database. I dont make sql dump as it takes too long. I just gzip and copy the mysql database folder to my remote backup server.

[kogepan]

Im having the same problem. Since i purchased an article database as well..

[Anduril66]

Yeah I bought that 277 000 article database and I am trying to extract the finance articles, but I'm pretty new to databases.

Thanks for the help redwine. I installed Apache and MySQL on my Windows desktop machine. But right now, I only have access to the SQL dump and not the database, because I downloaded the SQL dump from another user. I'm having trouble creating the database. I tried renaming the dump "articles" and I put "articles" in my mysql bin directory, and I tried "source articles;" but I got a "Failed to open file, error: 2", and I'm not really sure what I'm doing.

[kogepan]

right now im trying to split the file then manually insert it into mysql we'll seee what happen :)

[YIAM]

I installed Apache and MySQL on my Windows desktop machine. But right now, I only have access to the SQL dump and not the database, because I downloaded the SQL dump from another user. I'm having trouble creating the database.

I will suggest you do download WAMP (http://www.wampserver.com/). Its includes MySQL, Apache, PHP and also phpMyAdmin.

With phpMyAdmin you can do it in minutes.

[Anduril66]

Oh yeah, I did download WAMP and tried to Import the SQL, but the operation broke because the file was so large. Do I have to split the dump?

[adam1987]

I used this to help upload a big sql, maybe it could help you. http://www.ozerov.de/bigdump.php

[kogepan]

ya i tried that file it worked like a charm until my host gave me this max_questions resources error.... im outa luck.

[adam1987]

yea i had that trouble before , in the end i had to open the sql file in notepad and split it into about 5

[Clive]

yea i had that trouble before , in the end i had to open the sql file in notepad and split it into about 5Were you actually able to open the sql? How big was it? Those with the 277,000 articles sql will not be that lucky to use this advice since the file is larger than what Notepad can open. the BigDump script has helped me..

[phantomddl]

there is an easier way, dont waste your time with it
first download this: http://www.ozerov.de/bigdump.zip
then edit the script (database settings)
then upload the sql into ftp, and chmod the folder 777
then go to bigdump.php you will see sql file there, click import
thats all :)

edit: oops sorry, i didnt see, it was given

[kogepan]

yea i had that trouble before , in the end i had to open the sql file in notepad and split it into about 5

i cant even open the sql sadly. :(

[Clive]

i cant even open the sql sadly. :(http://www.ozerov.de/bigdump.zip can help, just give it a try!

[ashiezai]

http://www.ozerov.de/bigdump.zip can help, just give it a try!

Please read the messages above you before you post.

[Clive]

Please read the messages above you before you post.Did that. I even suggested this option myself a few posts above.
Only it looks like the guy is unsure which to try, so I insisted by sending him there again, repeating myself and others..
Simply because it's a working solution and is worth taking a look at.

[everypcneed]

For those of you having issues opening large sql dumps try downloading PSPad Editor from http://www.pspad.com/en/download.php

I never had an issue opening an sql dump file with this editor to date and I have had to work with some latge sql dumps.

[ShiftChip]

I also have the same DB

I used: JASP (Just Another file Split Program)
http://www.freedownloadscenter.com/Utilities/File_Splitting_Utilities/JASP___Just_Another_File_Split_Program.html
I'm sure there is better file split programs tho...

[technoguy]

if you want to open your file then use ltfviewer to view your file.

[nQQb]

thank you technoguy, your post was helpful to me.

[einsteinsboi]

This article might help also in opening large sql dumps.

http://codingpad.maryspad.com/2007/06/19/working-with-a-sql-dump-file-a-newbie-tutorial/

It shows you how to do it from the MySQL command line, works great for large sql dump files.

[xxKillswitch]

BigDump is definetly the way to go. I've had to restore a several hundred MB Vbulletin database and without BigDump it just wasn't working. Even trying to copy/paste sections of the SQL would have taken hours (done it before).