phpMyAdmin: Bypass File Upload Size Limit

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.

  1. 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.
  2. While you’re still in your FTP program – create a directory inside the phpmyadmin directory called upload:
    phpmyadmin-trans.png
  3. Towards the bottom of the config file, you’ll notice a potential setting for $cfg['UploadDir']. Change it so that it reads the following:
    phpmyadmin-config.png
    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.
  4. Save it, and then go back to your ftp program, and upload a sql file to that directory.
  5. 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.
    phpmyadmin2.png
  6. 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!

19 thoughts on “phpMyAdmin: Bypass File Upload Size Limit

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

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

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

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

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

  6. If your file doesn’t appear, and phpmyadmin says that the location couldn’t be accessed, try changing it from ‘./uploads’ to ‘uploads’

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>