Importing large files into mysql with phpmyadmin

By David Pratt / Tags: , / 187 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

187 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

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..

BitBug May 7th, 2012 at 3:30 am

Thank you! That hack kicks ass!

swathi May 7th, 2012 at 11:10 am

I got this problem and struggled for 5 hrs. I just fixed with ur idea in 5 mins. awsome.
Thank u so much

Aline May 10th, 2012 at 3:25 am

Awesome, that is exactly what I’ve been trying to do! Thanks so much!

Elkbir May 12th, 2012 at 12:12 pm

Thank you very much. Thas big helpfull from u.

P.Maheswary May 16th, 2012 at 8:23 am

Thank you very much it works correctly in WAMP..

Raj Keshwani May 19th, 2012 at 11:37 am

Hurray!!! This works!! Thanks

Boris May 22nd, 2012 at 1:07 pm

Thanks man. This was really helpful!

regina May 28th, 2012 at 10:13 am

hi guys this work

Surya May 30th, 2012 at 7:51 am

thanks its working fine……..

Sujith Divakar May 30th, 2012 at 8:21 am

In wamp its worked perfectly for me.. hanks a lot mate..

Jimmy M June 1st, 2012 at 12:50 pm

Thanks a lot mate. Works fine for me in WAMP 2.0

Tim Norton June 2nd, 2012 at 1:08 pm

Thank you

Why don’t they do this by default

kibiki June 3rd, 2012 at 6:16 pm

it works but not for bigest file

mmm June 5th, 2012 at 8:42 pm

i love you :)

Rohit June 11th, 2012 at 3:22 pm

Thanks a ton..!!!!!

Sam June 15th, 2012 at 9:03 am

what about max execution time out? I cannot import it while time is out.

stephen June 25th, 2012 at 6:22 pm

Just wanted to say YOU ARE A LEGEND!!!!!!

Velmurugan June 30th, 2012 at 8:13 am

Thanks friend it’s very useful to me…

Nagarajan July 11th, 2012 at 9:08 am

hi all,
can any one explain in detail how to solve this problem in xampp since i am an fresher in php..and i meet the same error…please..

Nagarajan July 11th, 2012 at 9:09 am

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

Kevin July 12th, 2012 at 4:04 pm

many thanks!

kani July 26th, 2012 at 12:36 pm

i had some prblm while importing the database in phpmyadmin ,i think that prblm in storage engine

John August 7th, 2012 at 2:39 pm

thanks man, worked for xampp as well with adding what the above comments said.. simply adding the following to the xampp/phpMyAdmin/config.inc.php file

$cfg['UploadDir'] = ‘upload’;
$cfg['ExecTimeLimit'] = 0;

Robb September 4th, 2012 at 5:18 pm

@john thanks man that worked…

it worked thanks for suggestion

Michael Marshall September 4th, 2012 at 7:28 pm

This is a gravedig post but oh well.
I have tried what you said but I get a “Fatal Error” while uploading.
I am working with a 150mb+ .sql file here!

Kalim September 16th, 2012 at 5:46 pm

Great, its work in XAMP too. Thanks

orangorangan September 19th, 2012 at 11:18 am

ur tips is way more human than the documentation dude!

kiran September 20th, 2012 at 8:22 am

Thanks a lot !! :)

Bogdan October 1st, 2012 at 1:44 pm

great man …worked like a charm, thanks and keep up the good work :)

Syed Kamran October 4th, 2012 at 12:29 pm

Pure skills..

Evelyn October 9th, 2012 at 9:52 am

Great, that works very fine!
Thank you for sharing :)

Amber October 15th, 2012 at 7:47 pm

ummm . . wowww !! thank you so much !!

Jason October 16th, 2012 at 7:28 pm

Worked perfectly…..brilliant!!!!

Amitabh October 17th, 2012 at 5:52 pm

Many Many thanks for your valuable points

Dwain October 25th, 2012 at 8:07 pm

It worked. Thank you very much!

ariessandi October 27th, 2012 at 6:09 am

COOL………THANX MAN….
IS VERI USEFULL FOR ME..
THANXXXX

atik November 14th, 2012 at 1:02 am

thank you very much, realy usefull, it work 100%

Tamawy December 2nd, 2012 at 2:26 am

Thnx man, It works. But I am trying to upload a 27 mb sql file. It take too many time to perform. If there is any faster way tell me please. Thank you

rudy December 7th, 2012 at 12:47 pm

this is very useful information and I followed your instructions step by step.
in myPHPadmin the drop down radiobox appeared but still the system gives the same error message: “No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration. See FAQ 1.16.”

any tips?

and my filesize is only 2,7 MB

Urmila December 11th, 2012 at 1:01 pm

this method is really rocks……..

thank you so much!!!!!!

Chandra December 13th, 2012 at 12:54 pm

Hi, Nice solution . but when i try to upload a 200 MB sql file into the system then it is not working fine .. i also changed the max allowed packet data value to check if this will work but still it doesnt ..

Aussie December 15th, 2012 at 4:45 am

Works great, on wampserver 2.2 – save plenty of time, thanks mate

Deepika Patel January 24th, 2013 at 6:49 am

Thanks! it works..

roja March 15th, 2013 at 9:42 pm

HOW TO RESOLVE THIS PROBLEM…

When i try to upload very large file (about to 480MB) of file i got this error message…

plz anyone help me in this regard……

Error

SQL query: DocumentationEdit Edit

SELECT `comment`
FROM `phpmyadmin`.`pma_column_info`
WHERE db_name = ‘BharatCine’
AND table_name = ”
AND column_name = ‘(db_comment)’

MySQL said: Documentation
#1100 – Table ‘pma_column_info’ was not locked with LOCK TABLES

Irfan March 19th, 2013 at 8:41 am

Thanks its is very nice, easy and simple method.
Works perfect.

bauer March 22nd, 2013 at 12:54 pm

thanks a lot :)

Drew April 13th, 2013 at 11:08 am

This is pure gold. Works perfectly on MAMP when nothing else did. Thanks so much.

Kumaresh May 8th, 2013 at 4:06 am

God bless you !! I’m the DBA here @ http://www.20overs.com and I was trying to upload a 110 MB data file. Tried several things, none of them worked. This is my first stint at MySQL and phpMyAdmin. Your advice worked like a charm !! Thanks …
For those of you who received “The directory you set for upload work cannot be reached” error, try ‘/upload’ instead of ‘upload’ and use LOAD DATA INFILE ‘/upload/filename’ … to load the data into your table.

Nithin June 8th, 2013 at 7:49 pm

You can use command line to do the same thing. Just do the following command in the cmd line

C:/path till upload directory> mysql -h hostname -u username -p databasename > dbfile.sql

Remember you shud have your database, host and user created before doing this.

Thanks!

Regginald June 9th, 2013 at 12:41 pm

Ran into the error below when I tried it on XAMPP for Linux:

“Existing configuration file (./config.inc.php) is not readable.”

I fixed it as shown below:

1. Stop XAMPP/ LAMPP
sudo /opt/lampp/lampp stop
2. Navigate to the phpmyadmin folder
cd /opt/lampp/phpmyadmin
3 Change the permissions for the config file:
sudo chmod 644 config.inc.php
4 Start XAMPP/LAMMP
sudo /opt/lampp/lampp stop

Hope this helps someone also facing this error!

Thanks Dave :)

Leave a reply