When you want to import a large SQL query in phpMyAdmin, you can run into problems as the default upload size limit is restricted by whatever it is in the php.ini file - and often, you cannot change this or simply don't want to. It tends to default to a measly 2MB.
However, there's a feature that will allow you to FTP your SQL file to an upload directory of your choosing, and then instruct phpMyAdmin to run the query file. Here's how you do it.
- Open up your FTP program, and navigate to the phpmyadmin folder on your server. Inside that folder is a file called config.inc.php - open this up in your trusty text editor.
- While you're still in your FTP program - create a directory inside the phpmyadmin directory called upload:
- Towards the bottom of the config file, you'll notice a potential setting for $cfg['UploadDir']. Change it so that it reads the following:
Don't forget to add the "./" before "upload" - this just tells the program to look in the existing directory (phpmyadmin) for a folder called "upload" - there's no need to change file permissions or anything. - Save it, and then go back to your ftp program, and upload a sql file to that directory.
- When you go to the Import tab in phpMyAdmin, you'll now see a drop-down list of available files within your upload directory. Pick the one you want to run.
- Sit back, have some coffee.
Some Notes
- You can compress the file, which will certainly make your FTP upload time shorter. I found that gzip works best for that. Sometimes a zip file would give me an error.
- Note that you can still run into trouble if your query is HUGE. If your php execution time and/or timeout settings are not big enough in your php.ini file, executing your query can be problematic. However, you can always chop up your data inserts into numerous files, although that can be a pain. (Or, if you have SSH access, you can execute the query file that way.) I encountered this problem while trying to import a big Drupal database. I solved it be emptying (NOT dropping) all the search-related database tables, and then re-indexing the site once all the other data was inserted.
That's it - I was pleased to discover this feature and hope it helps someone else out too!
By Robin January 19, 2012 - 11:50 am
Very nice option for restore big databases.
By Deb January 11, 2012 - 2:33 pm
good info! thanks!
By Johnny December 21, 2011 - 8:55 am
THANK YOU!!!! This was exactly what I was looking for, excellent instructions and it worked like a charm! Glad this was the first page I came across :D
By Peter November 18, 2011 - 12:39 pm
If your file doesn’t appear, and phpmyadmin says that the location couldn’t be accessed, try changing it from ‘./uploads’ to ‘uploads’
By Moving to Moodle 2.0: Pt.2 The Tools and Tweaks | williamstites.net June 10, 2011 - 6:16 pm
[...] (Resource link: http://www.givegoodweb.com/post/82/phpadmin-upload-restriction) [...]
By phpMySQL – große Dateien Importieren | blogging on chilloutsofa April 12, 2011 - 8:33 am
[...] Umgehen lässt sich diese Restriktion durch das Importieren über ein Upload-Verzeichnis. Ein Tutorial hierfür gibt es hier. [...]
By CoolDeep April 6, 2011 - 1:08 am
Thank you so much for one of the most simplest and yet terrifically awesome tip!!!! keep ‘em coming!!!!
By Chris S November 25, 2010 - 11:12 am
+1 here. Super helpful explanations, thank you!
By Tony Boyce September 24, 2010 - 6:25 am
Thank you – thank you – thank you.
Running phpmyadmin on my local server on a Mac. This method saves me a lot of time and head scratching.
Most difficult part was finding my config file which was nested in the ‘libraries’ folder.
Thanks again for sharing.
By Phyllis Ingram September 4, 2010 - 5:23 am
opps, i have a windows machine, should this be ‘..\upload’, that is what I think it should be for windows, but it still didn’t show up, can’t understand that. I am making the changed to the config/config.inc.php file and making the path as shown above ‘..\upload’ but it does not appear as drop down when i hit the inport command button. hmmmm, sure wish i could get this going.
thanks
phyl
By Matt September 3, 2010 - 2:24 pm
Hi Phyl – I’ve covered everything here that I think can go wrong. Short of logging into your server, it’s difficult for me to diagnose from here. If you figure it out, please post your findings here – thanks and good luck!
By Phyllis Ingram September 3, 2010 - 2:11 pm
I followed your instructions and did not forget the ./upload but it does not show up in the import place
any ideas on what i did wrong
phyl
By Abeon June 24, 2010 - 7:31 pm
A very usefull tip!
Saves having to edit the ini file just to upload an sql file…
Very, very usefull, thanks :)
By Copy a WordPress blog to a local machine « footnoteblog February 1, 2010 - 1:27 pm
[...] Quickly import a database into a local computer [...]
By Ken Dreyer July 23, 2009 - 3:58 pm
Thanks for your blog post; I was beating my head against a wall wondering why my .zip file didn’t import! Gzip to the rescue :)
By Matt January 14, 2009 - 12:31 pm
Jorge, you’re likely running into a PHP memory/execution limit that’s set in php.ini (see the last bullet above) – you’re best hope is to trim the query down, or split it into several files. A pain, believe me I know! Or, if you have the ability to up the limits in php.ini, that’s the best solution. But if you’re on a cheap shared hosting plan, there’s little chance of this.
By Jorge January 14, 2009 - 11:21 am
After reloading phpmyadmin, selecting the sql file from the dropdown and click on go, my screen went blank so I don’t know if the process is moving.
Any idea how to fix this? Thanks for the tip.
By djc January 11, 2009 - 1:20 am
THANK YOU! This was by far the simplest explanation for the process!