Image
Top

Chicago, IL | p: 773.338.1313

Navigation
Back to Posts

January 11, 2013

Importing large .sql files into phpMyAdmin under MAMP

Ugh. I hate it when I’m trying to make modifications to an existing site and I can’t get the database to import because it’s too large. You end up with a lovely warning message like this:

Screen shot 2013-01-11 at 4.50.43 PM

This happens to me quite often, and I finally found a workaround for this within my local MAMP dev environment.

1. Open up the config.inc.php file within the phpmyadmin dir with your favorite code editor. In your local MAMP environment, it should be located here:

Hard Drive » Applications » MAMP » bin » config.inc.php

2. Do a search for the phrase $cfg[‘UploadDir’] – it’s going to look like this:

$cfg['UploadDir'] = '';

3. Change it to look like this:

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

4. Then, within that phpmyadmin dir, create a new folder & name it upload.

5. Take that large .sql file that you’re trying to import, and put it in that new upload folder.

6. Now, the next time you go to import a database into phpMyAdmin, you’ll see a new dropdown field right below the standard browse area in your “File to Import” section, like this:

Screen shot 2013-01-11 at 5.21.45 PM

Select that dropdown, and your file will show up right there. Hilight the db, and hit “Import” – and you’re golden!

Comments

  1. February 23, 2013

    Reply
    Darcie

    Thanks for putting this on the internet! Just helped me immensely today. Found also that you must choose it from the dropdown; browsing and selecting the file in the approved upload directory doesn’t work. Doing it right, as you explained, does however work.

  2. May 7, 2013

    Reply
    Salvo

    It worked! Got it right first time!
    Thanks so much for a great tip.

  3. May 14, 2013

    Reply
    Judith

    Thank you!! You saved thousands of productive minutes 🙂

  4. June 24, 2013

    Reply
    Julie

    Awesome tip! Thanks a bunch!!

  5. July 14, 2013

    Reply
    Fred Peres

    Beautiful!

  6. July 27, 2013

    Reply
    hd wallpapers

    Wow… thanks dude, your post helped me to import my 100+ MB database which is having a huge wallpapers pics database. I have followed your steps and imported the data successfully.

    Thanks,

  7. August 13, 2013

    Reply
    steffan

    Is this possible with xammp? I have it working on my mamp, but i would like to get it working with xammp as well. Any Suggestions?

    • August 16, 2013

      Reply
      Gregg Tomlinson

      I have never tried this with xammp, sorry. Let me know if you do get it working, and post your results (or a link) – this post actually gets a lot of traffic for people that are working with mamp so I bet it would be helpful to others as well 🙂

  8. October 7, 2013

    Reply
    Rob Golbeck

    Thanks for this! Worked like a charm. 🙂

  9. December 5, 2013

    Reply
    Monique Olivera

    You’re an angel! Thanks

  10. January 6, 2014

    Reply
    Vibhu

    Thanks a lot. It was a great help.

  11. January 14, 2014

    Reply
    Bryant Hernandez

    While looking for this mamp tutorial I found the one for xampp here http://p3php.in/import-large-sql-file-from-phpmyadmin/

    Great tut by the way!

    • January 30, 2014

      Reply
      Gregg Tomlinson

      Ooooh, thanks for posting that xampp import link Bryant! Hopefully Stephan is subscribed to this thread still. And thank you for your kind words about the tutorial 🙂

  12. February 9, 2014

    Reply
    Raghu

    Thank u so much am able to load large SQL file.

  13. March 4, 2014

    Reply
    Alicia St Rose

    I spent hours looking for a way to upload a large file!
    Which I’d come her First!!

    Thank you so much!

  14. March 5, 2014

    Reply
    vishnu-suman choudhary

    Gregg, Your trick saved my time. It worked. Before this I always use some other way like:- I do increase some limit variables size int the php.ini file.

    Today I applied your trick and it worked and I got smile 🙂

    Tons of thanks,
    Vishnu

  15. March 9, 2014

    Reply
    Ben Frain

    Great workaround. Thanks for sharing this. 🙂

  16. March 19, 2014

    Reply
    Devin Columbus

    For anyone that is looking for the “config.inc.php” file in a newer version of MAMP, look in “/Applications/MAMP/bin/phpMyAdmin/”

    • March 20, 2014

      Reply
      Gregg Tomlinson

      Devin: thank you for the comment, and thank you for the pointer! This really helped me out – I upgraded MAMP on both of my sandbox servers over the weekend, and have been struggling with an apc memcache issue in Active Collab ever since. Thanks to your tip I was actually able to better dig thru the issue, come to find out they completely restructured where the php.ini files are kept for the different versions of php that are now available thru MAMP pro admin.

  17. April 11, 2014

    Reply
    Katy

    Thanks a lot, this was exactly what I needed. For me, the config.inc.php file was in Hard Drive » Applications » MAMP » bin » phpmyadmin » config.inc.php

  18. April 30, 2014

    Reply
    Sue

    Thanks so much for this. Saved me HOURS.

  19. May 1, 2014

    Reply
    Dan

    Thanks a lot Gregg! It really helped me out. Amazing how simple the solution is and how you figured it out. Thanks for posting it on the web! Cheers!

  20. June 3, 2014

    Reply
    greenhorse15

    wow…it works for me too

    thanks for sharing

  21. June 24, 2014

    Reply
    robin

    Another happy camper. Thank you!

  22. July 18, 2014

    Reply
    john

    nice

  23. August 5, 2014

    Reply
    Karen

    Yay! Thanks a lot!

  24. September 23, 2014

    Reply
    Prafull

    Great link this solves our problem.

  25. September 28, 2014

    Reply
    Edo

    I got this error: phpMyAdmin – Error
    Wrong permissions on configuration file, should not be world writable!

    How to solve that ?
    Thank You in advance

    • December 10, 2014

      Reply
      Gregg Tomlinson

      Right click that file and set your perms to 644 after you edit, that should take care of the world writable issue.

  26. November 11, 2014

    Reply
    ralph

    thank you! Thank you! Thank you! Your fix worked. Really appreciate this bud.

  27. March 27, 2015

    Reply
    Steve

    Is it too strong to say I love you for writing this up. Thank you so much, I really did not have time too work this out for myself.

    This worked perfectly.

    Thanks again

    • March 27, 2015

      Reply
      Gregg Tomlinson

      Hahahaha, wow! Thanks so much for the compliment Steve, and glad we were able to help you out 🙂

  28. March 30, 2015

    Reply
    Elizabeth

    It’s NOT too strong to say I love you for this post!! You saved my day. It’s stupid to try to upload a file in a local directory and it was taking all day. Now it’s instant. I LOVE YOU!! 🙂

  29. April 12, 2015

    Reply
    KarSho

    Thanks you so much

  30. April 29, 2015

    Reply
    Guy

    Today was a great day after reading this post and successfully importing my troublesomely large database. Can’t thank you enough!!

    • April 29, 2015

      Reply
      Gregg Tomlinson

      So glad we could help! And I’ve been in the same boat before, so I figured someone else would be at some point – so why not share 🙂

  31. April 30, 2015

    Reply
    Gregory McVey

    Excellent!
    Worked perfectly, thanks for the tut.

  32. May 14, 2015

    Reply
    vrwired

    excellent suggestion – it increased my load of same size database at least 5x faster than before I saw this article

  33. May 15, 2015

    Reply
    Rob Gordon

    Gregg – Super post and saved my life as I am working on a clients database that is 2.9GB and needs cleaning out!

  34. June 16, 2015

    Reply
    Alex

    Awesome! Very helpful- thanks for sharing Gregg.

  35. July 9, 2015

    Reply
    JxnGraphix

    Tried several other methods…this one worked with ease! Sweet work-around, thanks!!!

  36. September 25, 2015

    Reply
    lesscro

    Bro’…. (because with this you are my bro’)…

    You make my day… with a drupal sql (1.56 go), i have sucessfull upload (using a lot aof resume) this base to convert it into wordpress (match posts,users,etc…)

    Like all other… love 4 U !!

    Regards

  37. October 20, 2015

    Reply
    min

    Thanks!!!!

  38. October 28, 2015

    Reply
    Sasha-Shae

    I was able to follow the tutorial and get to the part where we can now upload via the checkbox for the new upload field. However, when I went ahead and selected my file (it’s a 49.5MB total size SQL), I got an error on import saying that some packet exceeded max uploaded packet size. Any ideas? I’m pretty much trying to make an exact copy of my live site, to a local environment but not with much success.

    • September 25, 2016

      Reply
      Gregg Tomlinson

      Might need to go into the php.ini file of the version of PHP you are using and edit the following line to increase the maximum size of the file you are trying to upload. Here’s the code you are looking for:


      ; Maximum allowed size for uploaded files.
      upload_max_filesize = 32M

      For me, that was located here: Hard Drive » Applications » MAMP » conf » php5.6.25 » php.ini

      Be sure and shut down then restart all services after making this edit for the changes to take effect.

      Hope that helps!

  39. January 7, 2016

    Reply
    Tamsin

    Thanks so much, simplest tutorial yet…a useful note to add, I found several config.inc.php files in my mamp installation, I edited the one I found in this path: mamp/bin/phpMyAdmin/ and not as specified straight in the bin directory….

    It worked perfectly. I couldn’t search for the full phrase so I just searched for the first part $cfg[‘UploadDir’] as in my config.inc.php file there is a lot of space before the = ”;

    Hugely appreciated. Thanks

    • September 25, 2016

      Reply
      Gregg Tomlinson

      You are quite welcome – and yeah, it’s a pain to search for sometimes, especially using SublimeText – I find it much easier to do searches using Espresso when it comes to things like this.

  40. January 20, 2016

    Reply
    Alan

    This worked, yay! Thanks…

  41. March 4, 2016

    Reply
    Noel

    Thank you!

  42. April 19, 2016

    Reply
    Kelly MacPhee

    You are a genius! Been searching for a solution for ages, thanks so much for this 😀 😀

  43. June 9, 2016

    Reply
    Fernando

    WOW Awesome! thanks

  44. June 11, 2016

    Reply
    Willz

    Thanks! This is much easier than using the terminal (IMO).

    Just a word to those who already have MAMP running; you need to stop and restart the servers before otherwise you’ll get a Error: 1046 SQLSTATE (ER_NO_DB_ERROR) no database selected error. Just in case anyone was having that problem!

    • September 25, 2016

      Reply
      Gregg Tomlinson

      Good point, thank you for your insights on this – I totally missed that on the original post, should have said that before. Thank you for clarifying!

  45. June 14, 2016

    Reply
    jesh

    hi there, I have got to the point where I have made the upload file and it is a visible option to import. I have copied the backup file into the folder but phpmyadmin does not show it and says no file available on server. tried both zip and folder. I am new to this so I appreciate the help

    • September 25, 2016

      Reply
      Gregg Tomlinson

      You’re going to need to completely shut down and then restart MAMP services for this to take effect.

  46. July 4, 2016

    Reply
    Chris

    Much appreciated!!!

  47. July 5, 2016

    Reply
    Derek Mohr

    This is the most helpful thing on the internet.

  48. September 8, 2016

    Reply
    David

    Oh man… Thank you!

  49. September 25, 2016

    Reply
    Terry Atkinson

    Thanks a lot – it worked. Just for the semi illiterate like myself, don’t forget to read that you need to find the file ‘config.inc.php’ inside the phpmyadmin directory which is inside the bin directory. Full path is:

    Hard Drive » Applications » MAMP » bin » phpMyAdmin >> config.inc.php

    • September 25, 2016

      Reply
      Gregg Tomlinson

      Thanks so much – glad it could help! Funny enough, I just updated my localdev and my sandboxes all to MAMP4, and this still applies. Past me just gave future me a big thumbs up 🙂

  50. November 9, 2016

    Reply
    Bob Venk

    Thanks it worked

Newer Comments »

Submit a Comment

Cancel reply

Posted By

Categories

blog