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!
THANK YOU! This was by far the simplest explanation for the process!
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.
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.
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 :)