Importing large files into mysql with phpmyadmin

By David Pratt / Tags: , / 132 Comments / Published: 18-01-10

I’m writing this here because it’s the third time I’ve had this problem, and the third time I’ve forgotten how to fix it! If you ever get hit with the error message:

Phpmyadmin error message

“You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit.”

When trying to import large SQL files into mysql using phpmyadmin, the phpmyadmin documentation offers a few solutions, but I find the easiest method to overcome this is…

Find the config.inc.php file located in the phpmyadmin directory. In my case it is located here:

C:\wamp\apps\phpmyadmin3.2.0.1\config.inc.php

Find the line with $cfg['UploadDir'] on it and update it to:

$cfg['UploadDir'] = 'upload';

Create a directory called ‘upload’ within the phpmyadmin directory.

C:\wamp\apps\phpmyadmin3.2.0.1\upload\

Then place the large sql file that you are trying to import into the new upload directory. Now when you go onto the db import page within phpmyadmin console you will notice a drop down present that wasn’t there before – it contains all of the sql files in the upload directory that you have just created. You can now select this and begin the import.

If you’re not using WAMP on Windows, then I’m sure you’ll be able to adapt this to your environment without too much trouble.

Category: Tech

Tags: ,

Posted: on January 18th, 2010 at 2:55 pm.

Feeds: RSS 2.0

132 Responses to “Importing large files into mysql with phpmyadmin”

sathish January 29th, 2010 at 1:35 pm

Hi,
i have done this in xampp it works, thank you

Lisa Johnston May 4th, 2010 at 9:13 pm

YOU ROCK! Thanks!

James May 13th, 2010 at 2:28 pm

Thanks for sharing, my large database went in without any hassle :)

Jumanji July 24th, 2010 at 3:43 am

thanks working good! cheers!

Aslesha September 13th, 2010 at 12:29 pm

it works
thanks :)

Dipendra September 14th, 2010 at 12:31 pm

Fine it works better

Hittesh September 28th, 2010 at 1:50 pm

Can i do this directly from PhpMyadmin as i have a huge table which i directly import from an excel worksheet.

David Pratt September 28th, 2010 at 8:47 pm

@Hittesh you would need to save the Excel document as a .csv file first. If you do that, then it will work.

Dan October 12th, 2010 at 3:50 pm

Hi Thanks!
it works great I uploaded a 14mb sql file to the new db
great!

moabi November 1st, 2010 at 11:25 pm

thank you so much…!

Bhupendra November 12th, 2010 at 1:02 pm

gr8…..works with xampp also….don’t forget
to add $cfg['ExecTimeLimit'] = 0; in config.inc.php in case of xampp……

crowther November 18th, 2010 at 9:39 am

This worked for my science song database as well. Thanks!

rahul November 19th, 2010 at 12:21 pm

Thanks It works.

Regards

sharfaz November 23rd, 2010 at 6:18 pm

Works like charm in WAMP.. Thanks

Jeff Dolan November 27th, 2010 at 9:03 pm

Thanks for this post! I’m on the second time forgetting myself. :)

ASLok December 16th, 2010 at 7:29 pm

Thanks!! Short and simple – excellent )

Siraj Ali January 18th, 2011 at 8:14 am

Thanks Alot!

Rain January 18th, 2011 at 10:32 am

Hi,
That’s really awesome < it's work!!! :)

Thanks !!

narinder January 19th, 2011 at 2:16 pm

Hi

The directory you set for upload work cannot be reached

Thanks in advance.

Bethel February 26th, 2011 at 2:56 pm

Thanks mate i had not documented it, b4- hence i found myself on the 2nd time forgetting. You rock!!!

javad February 26th, 2011 at 4:30 pm

Hi, Awesome , you saved my life.
More than 2 days i want upload my huge file , but i can’t

Thanks

Alvin March 5th, 2011 at 4:30 pm

thank you it works fine

pradeep March 7th, 2011 at 8:47 am

Thanx ,It works:)

111 March 14th, 2011 at 8:55 pm

For some strange reason I had to restart wamp in a weird fashion for it to work. Create upload directory with file in it, restart wamp, then modify the config file, and all is working. Thanks for the easy fix.

Bethel Goka March 15th, 2011 at 11:37 am

Another option is to split the database using BigDump Splitter, then import it. BigDump Splitter will re-package the files as you import them.

Scott Elkin April 8th, 2011 at 1:04 am

Gave you a shout out on my blog…great work. This saved my bacon today!. So much faster!

hasan habib April 12th, 2011 at 6:35 am

thanks! its works.
but one thing. in xampp, config.inc.php don’t content the line,
$cfg['UploadDir'] = ‘upload’;

so open the config.inc.php using wordpad,
if u don’t find the line just type it
$cfg['UploadDir'] = ‘upload’;
before ?>, then save and restart the service.
thanks again

John April 25th, 2011 at 8:21 pm

Graciaaaaaaaaaasssssss thanks! its works.

Mark May 7th, 2011 at 12:14 am

YOU ARE A GENUIS!

Ady May 9th, 2011 at 5:39 am

It’s Work, but I must modify max execution time to 3600 seconds before. Thanks.

php May 20th, 2011 at 8:27 pm

I tried uploading but I am getting this message: “Script timeout passed, if you want to finish import, please resubmit same file and import will resume.”
And when I resubmit it, it is doing the same thing again and displaying the error message again.
My dump size is 1.2GB.

php May 20th, 2011 at 11:33 pm

I changed the variable $cfg['ExecTimeLimit'] from 300 to 0 in config.default.php file which went thru fine and did not show time out message again.

If some tables are huge, we can import them seperately too. I like to do it because I can see that its uploaded successfully instead of all the table names together.

Filip June 7th, 2011 at 4:30 pm

It doesn’t work for me… I don’t get that ,,new” drop down… Any ideas?

Miky June 8th, 2011 at 11:16 am

Worked in a snap….Awesome…..:)…Thanks

John Q June 13th, 2011 at 10:12 pm

Thanks, my DB was only 8mb and it wouldn’t work no matter what I did until I found your solution.

rob ganly June 28th, 2011 at 12:48 pm

good stuff- i haven’t developed on windows for a while but currently am. the command line stuff isn’t as clean as on linux so this is a handy way to avoid it! rob ganly

Gersh July 1st, 2011 at 5:59 pm

Thanks that was useful and thanks hasan for this tip (didnt even need to restart just refresh the import page):

in xampp, config.inc.php don’t content the line,
$cfg['UploadDir'] = ‘upload’;

so open the config.inc.php using wordpad,
if u don’t find the line just type it
$cfg['UploadDir'] = ‘upload’;
before ?>, then save and restart the service.

Lardi July 4th, 2011 at 7:35 pm

Thanks man
It’s work with me

Nahyan August 1st, 2011 at 9:33 am

Love You… :p :D
Its working Great… :-)

Neha Rana August 3rd, 2011 at 12:01 pm

thanx yarr,,god bless ya

Dave August 7th, 2011 at 11:08 pm

Dude you are the best! This worked out really well for me
Thanks

Eumer August 8th, 2011 at 4:34 am

Thanx a lot.
But it can be done at remote server?
how can “config.ini.php” be reached?

isho August 9th, 2011 at 4:48 pm

Ur intelligent

Najeeb August 21st, 2011 at 8:40 am

thanks this is the best it work very nice

Shaikh Mahbubur Rahaman August 21st, 2011 at 10:06 pm

i need to import a cvs with 440mb

vas August 23rd, 2011 at 6:47 pm

for me it says “The directory you set for upload work cannot be reach” in xamp, please give some solution for that

nurzunairah August 24th, 2011 at 3:30 am

really help..thanks

Tylor Durden September 21st, 2011 at 1:43 pm

if it doesn’t work, you can use ems sql manager for mysql. Before use that you have to change your max_upload_size and than import your sql query or file. I think its easy way to import something in phpmyadmin.

Falguni September 29th, 2011 at 8:16 pm

This is really helpfull,and very easy to follow

Dalip September 30th, 2011 at 10:37 am

I have done as mentioned but its says upload directory cannot be reached…
any solutions

Dalip September 30th, 2011 at 10:38 am

This is on xampp

Shohel October 11th, 2011 at 11:11 pm

Hello David,
Thank you very very much, this is the best solution to me regarding this problem. I hope this type of interesting solution from you in future.

Pleasant October 18th, 2011 at 9:06 am

It still takes forever to upload a 4.5 MB CSV file. :(

Ira October 18th, 2011 at 7:40 pm

Thanks Man. This has been a major headache for quite some time!

Ira October 18th, 2011 at 7:43 pm

Dalip: First you need to restart the service, and make sure you have files in the upload folder, or the drop down probably wont show. It works for me in XAMPP.

prasad October 24th, 2011 at 9:39 am

really good and useful.thanx and keep it up.

Neets November 8th, 2011 at 11:17 am

Hi David,
Really a gr8 job done :) !!!
Keep up the good work..!!!

kumar November 11th, 2011 at 10:51 am

I am using wamp on windows.This method doesn’t provide an option of drop down in import sectiion..Please correct me if i am wrong…

Fidelis November 11th, 2011 at 7:38 pm

This is really great. I was able to finish up my work within minutes. Thanks a lot.

Yannis December 1st, 2011 at 5:21 pm

Sorry but didn’t work for me! My SQL file is 175MB. I have tried almost everything but i can’t import the file anyway.

yoro December 4th, 2011 at 4:21 pm

Thanks, finally got it to work after increasing max_allowed_packet to 2M in my.ini

Ravi December 9th, 2011 at 9:43 am

Very Very very heartily thanks! its works.
but one thing. in xampp, config.inc.php don’t content the line,
$cfg['UploadDir'] = ‘upload’;

so open the config.inc.php using wordpad,
if u don’t find the line just type it
$cfg['UploadDir'] = ‘upload’;
before ?>, then save and restart the service.
thanks again

Jonny Parkes December 9th, 2011 at 12:20 pm

Legend! Worked great for XAMPP!

John Levesque December 9th, 2011 at 5:50 pm

Thank You!! I’ve tried breaking up large dump files and doing that way, but your method is so much better.

Prashant December 19th, 2011 at 3:28 pm

You rock buddy

Nir December 28th, 2011 at 3:29 pm

Great great great!
But it takes forever to upload a 6.5 GB database

Suhas January 15th, 2012 at 12:25 pm

Thanks dear…

You helped me lot…. :)

nathan January 16th, 2012 at 10:27 am

Very nice and easy

teo January 19th, 2012 at 11:00 am

Doesn’t work in xampp.
There’s no $cfg['UploadDir'] and even if you add it, no dropdown appears when uploading in phpmyadmin nor any option to choose a file on the server.

maani January 26th, 2012 at 11:35 am

Great, that works excellently for me.
Thanks.

Juan January 26th, 2012 at 11:27 pm

awesome work, I run a lamp stack but due to you tutorial I was able to get my large file uploaded.

also thanks to

wiseguy
http://mywiseguys.com/topic/2271-importing-large-files-with-phpmyadmin/

Trupti January 27th, 2012 at 3:32 pm

Excellent solution !!!

Thanks a Lot …

Joel Milne January 28th, 2012 at 2:15 am

This was such a help to me today! Although my script is still running, I think its working now. At least when it does timeout, it starts back up again!

Malik January 30th, 2012 at 6:33 am

You are so Genius, You saved me.

azhar February 6th, 2012 at 10:02 pm

thanks, it saved my hours

جامعة تشرين February 11th, 2012 at 5:55 am

many thanx

Ian Price February 16th, 2012 at 1:22 pm

Thank you!

Nousheen February 16th, 2012 at 4:15 pm

Brilliant Man You r savior! thanks

matt February 18th, 2012 at 5:47 am

this is honestly the best tip i have seen all this year. ill be writing about this for sure. thank you so much.

Hans February 19th, 2012 at 2:57 am

The Master at Work!
So simple yet everyone else had complicated solutions with explanations that Einstein would not understand.
Solution simple yet effective!!
Explanation, step by step the way something should be explained!!
Thank you very much!!

thisworks February 22nd, 2012 at 6:04 pm

Make sure you change both *”post_max_size”* and *”upload_max_filesize”* in your “php.ini” (which is located where your “php.exe” is).

The following example allows you to upload and import 128MB sql files:

post_max_size=128M
upload_max_filesize=128M

Restart Apache and you’re all set.

Nitin February 23rd, 2012 at 7:07 am

Thanks for Your help…Works Superbly..

Vanessa Colina February 27th, 2012 at 6:01 am

Worked with MAMP, I had to close the app and relaunch. Starting the server wasn’t enough. After re-opening the app, it worked.

Thanks! :)

edys March 5th, 2012 at 7:36 am

Thanks so much… my problems solved

Jeremy Flatt March 7th, 2012 at 1:05 am

Having problems… It took me an hour to locate my phpmyadmin folder in the root… once I finally found the file i looked but couldn’t find the line you specified…

Jeremy Flatt March 7th, 2012 at 1:06 am

I have also can’t my post max size and upload upload size but no luck

sharath March 11th, 2012 at 10:08 pm

simply brilliant….thanks a lot

Lucas Souza March 13th, 2012 at 4:46 am

Oh, you spared me several minutes. I was about to start to split the file into others to upload one at a time.
Thank you!

Robert March 14th, 2012 at 10:28 am

Tnx!! Works fine and saved me loads of time.

Renne March 14th, 2012 at 12:07 pm

Can’t believe I had to do something like this to upload a mere 2mb database. Thanks man.

murahmotor March 16th, 2012 at 3:32 am

great!!!!! you solve my big problem. thank you very much GBU

Jasmine March 17th, 2012 at 3:01 pm

Thank u so much it is very useful

alex March 20th, 2012 at 2:44 pm

thanks, its done….!!!!

Khurram Ali March 22nd, 2012 at 6:36 am

getting this error
The directory you set for upload work cannot be reached

azz March 22nd, 2012 at 11:03 am

thank you so much

Harmen Meijer March 22nd, 2012 at 4:01 pm

Wow, 110 MB loaded without a problem!
thanks.

Madhury March 25th, 2012 at 9:45 pm

thank u so much…

Haja Peer Mohamed H March 26th, 2012 at 6:13 am

Thanks, it worked. I was trying for this the last few days… Thank you again

John March 26th, 2012 at 10:32 am

Thanks a lot, u saved me a great deal of time.

Rizwan March 27th, 2012 at 10:52 am

good job, works nice in WAMP. Thanks

Alan N March 30th, 2012 at 5:25 pm

Thanks for sharing. So simple when you know how. Brilliant!

Nathaniel April 2nd, 2012 at 4:11 am

I couldn’t figure out how to do this until I came across your site. Thank you so much!

K.G.B April 2nd, 2012 at 2:18 pm

This problem occurred: “The directory you set for upload work cannot be reached”
what should I do?

FOFO April 3rd, 2012 at 8:08 pm

Thank You .. It Works Fine

bipin April 4th, 2012 at 11:09 am

this is great and worik for mee good thanks so much realy

sanjay April 4th, 2012 at 1:06 pm

Thanks for this
It really helped a lot

Williamdoors April 5th, 2012 at 7:16 am

You so rock! I owe you a beer or 10

Ayoub April 9th, 2012 at 6:25 pm

Thank you so much. Easy and clean

dave April 12th, 2012 at 3:14 pm

thanks, worked a treat.

Gopal Sharma April 19th, 2012 at 11:29 am

Sir thanks alot your idea is stil working … But i get some problem … Sir when i import my large sql file then it shows ” #1046 – No database selected ” this type of problem plz sir help me i will have to submit my project on tomorrow so sir plz help me…….. what i do give me some sussaction sir i m waiting of your responce thanks alot sir u r great sir :)

Gopal Sharma April 19th, 2012 at 11:42 am

Thanks sir i got my ans. thanks alot sir .

Roman April 22nd, 2012 at 5:35 pm

Is there a solution if the config file is not available?

Mamoun April 26th, 2012 at 5:17 am

Seriously, You are so rock

Eric Cohen April 27th, 2012 at 2:42 pm

THANK YOU SO MUCH…

That helps a lot.

dexi April 30th, 2012 at 9:38 am

Its a good one!!! thanks

adhizzz April 30th, 2012 at 12:55 pm

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 24 bytes) in C:\wamp\apps\phpmyadmin3.4.9\libraries\import.lib.php on line 532…

i try many time but still error..what the problem..anyone can help me

ferdinand May 2nd, 2012 at 9:49 am

the solution u gave was the best… thanks your agreat help. keep up the good works and continue your advocacy in helping programmer like me .

sen May 3rd, 2012 at 7:15 am

I got this error my data size is 2.95 GB plz helpme to fix it “Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 262156 bytes) in C:\xampp\phpMyAdmin\libraries\import\csv.php on line 161″

Sovann MY May 3rd, 2012 at 6:28 pm

Anybody help me how can i Create a directory called ‘upload’ within the phpmyadmin directory?

Looking forward to hearing from you soon.

Jaimin May 6th, 2012 at 6:35 am

Thanks Yaar..

Leave a reply

Related Posts