Opening a large .SQL file.

[Cancel Man]

I've got a .SQL file that is 2.11GB (2,271,290,797 bytes), and im having so much trouble opening it, firstly i tried phpmyadmin, which should be associated with SQL files, it said the file was too large, then i tried opening it with Microsoft Notepad, still too large i wasn't expecting much with it being Microsoft and all. Then i tried Notepad++ which has always been reliable to me, still too large. How would one open this file? I've done quick google but nothings come up.
Thanks for any help in advance.

[MentaL]

[yjlasher22]

weird... a quick google came up with dozen of solutions for this. most of it requires you buy stuff, but here's one i found.

With linux shell (over ssh for example) you can easilly chop it in pieces with for example following command:

Lets assume your sql file is example.sql. With this command the sql file will be choped to 50m pieces. CD to the directory where the sql file is located and execute following command:

split -b 50m example.sql example_

You will get files example_aa , example_ab and so on.

now you can open any of those files, edit and save. When you are done you can combine those files with a command like for example:

cat example_[aa-bb] > example.sql

[Cancel Man]

Um, i'll try that once i get back on my desktop. But can i use a Linux shell code in Vista?
I'd rather not "buy" one as i'm trying to keep this computer as legit as possible. And if i was to split each file to say 100mb each wouldn't that mean for me to be able to read the end result i'd need to c+p each into a new file? How can it read that but not open it?
I'm not exactly very tech savvy ;)

[Ron]

Just wait for it to load everything?

2.1 gb of text is a lot of text... It needs a while to load.

[TheAJ]

If you have access to editing php.ini, change the upload size limit to 2.1gb then later change it back after you execute the sql file, which will most likely take hours - days

Or you can use Navicat, it doesn't load the whole file at once, it loads while it executes, took me 15 minutes or so to execute 10mb of sql data from navicat, on a quadcore, 6gb ram. Sadly navicat doesn't support multicores so more likely if you have a quadcore or a dualcore your cpu will be stuck at 25% (quad) or 50% (dual)

[KidFlow]

I tried opening 100MB + .SQL and I crashed. Wow good luck aswell.

[Anujan]

If you have access to editing php.ini, change the upload size limit to 2.1gb then later change it back after you execute the sql file, which will most likely take hours - days

Or you can use Navicat, it doesn't load the whole file at once, it loads while it executes, took me 15 minutes or so to execute 10mb of sql data from navicat, on a quadcore, 6gb ram. Sadly navicat does support multicores so more likely if you have a quadcore or a dualcore your cpu will be stuck at 25% (quad) or 50% (dual)

Interesting...
Never knew that.
Splitting the file would be your best option.

[yjlasher22]

Um, i'll try that once i get back on my desktop. But can i use a Linux shell code in Vista?
I'd rather not "buy" one as i'm trying to keep this computer as legit as possible. And if i was to split each file to say 100mb each wouldn't that mean for me to be able to read the end result i'd need to c+p each into a new file? How can it read that but not open it?
I'm not exactly very tech savvy ;)

no... you would not be able to use the linux shell in windows, unless you get virtual... whats that word, nah, emulator like... virtualbox? however, i do believe that you would be able to open the split files in windows.

[khubab1]

Hi, Kindly read very usefull article for huge sql data uploading at http://www.nobleatom.com

[cypher]

neah just use the command line:

1. go to run
2. type mysql -u root -p (if it`s added on path variable, if not you`ll need to specify complete path to the executable)
3. enter your password
4. use (database name here, if you didn`t create it yet type: create database db_name_here;)
5. type source (path to your .sql file, example c:\a.sql)

[omen]

if cyphers suggestion doesnt work - which can happen.

mysql -u root -p -h localhost databasename < C:\larger-sql-file.sql

mysql = mysql command, for linux it should work just in shell if your shell has loaded variables. Windows you would need to cd to where mysql.exe is.

-u = username flag
root = your root username
-p = password flag
-h = hostname flag
localhost = the host that has mysql running
databasename = database name
< = import
C:\larger-sql-file.sql = your file, this would be different in linux.

this command should prompt you for your root's password

depending on your ram, cpu, mysql allocated ram and optimizations it will take some time.

[lewis-smiffy]

Use big dump, that's how I dumped the db of my forum.