Image
Top
Navigation
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. 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. Salvo

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

  3. Judith

    Thank you!! You saved thousands of productive minutes ๐Ÿ™‚

    • Thanks! So glad someone else got some use out of this info ๐Ÿ™‚

  4. Julie

    Awesome tip! Thanks a bunch!!

  5. Beautiful!

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

    • 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. Thanks for this! Worked like a charm. ๐Ÿ™‚

  9. You’re an angel! Thanks

  10. Vibhu

    Thanks a lot. It was a great help.

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

    • 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. Thank u so much am able to load large SQL file.

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

    Thank you so much!

  14. 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. Great workaround. Thanks for sharing this. ๐Ÿ™‚

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

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

    Thanks so much for this. Saved me HOURS.

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

    wow…it works for me too

    thanks for sharing

  21. robin

    Another happy camper. Thank you!

  22. john

    nice

  23. Karen

    Yay! Thanks a lot!

  24. Prafull

    Great link this solves our problem.

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

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

  26. ralph

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

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

    • Hahahaha, wow! Thanks so much for the compliment Steve, and glad we were able to help you out ๐Ÿ™‚

  28. 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. Thanks you so much

  30. Guy

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

    • 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. Excellent!
    Worked perfectly, thanks for the tut.

  32. vrwired

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

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

  34. Awesome! Very helpful- thanks for sharing Gregg.

  35. JxnGraphix

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

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

    Thanks!!!!

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

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

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

    This worked, yay! Thanks…

  41. Noel

    Thank you!

  42. Kelly MacPhee

    You are a genius! Been searching for a solution for ages, thanks so much for this ๐Ÿ˜€ ๐Ÿ˜€

  43. WOW Awesome! thanks

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

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

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

  46. Much appreciated!!!

  47. This is the most helpful thing on the internet.

  48. David

    Oh man… Thank you!

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

    • 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 ๐Ÿ™‚

Submit a Comment

Posted By

Categories

blog