Overcoming file size limits when uploading SQL files to phpMyAdmin

Ref: https://www.interspire.com/support/kb/questions/851/Overcoming+file+size+limits+when+uploading+SQL+files+to+phpMyAdmin

If you find that you are unable to upload your backed-up SQL file to phpMyAdmin because the file is too large (even after zipping it) there are four options you could try:

1) Using a better compression algorithm

Zip is fairly good but there are better alternatives. GZip and BZip2 are good choices that should give you considerably lower file sizes (and phpMyAdmin understands them too). If you need a utility that can compress to gzip or bzip you could try 7zip.


2) Increasing the maximum file size phpMyAdmin will accept

If the limitation you are running into is a limit on the size of file uploads on your system, you may be able to increase this limit. By default it's usually 2M or 8M. To increase it you need to change PHP settings. You can change these settings either system-wide (if you have permission) or locally. The settings to change (with their typical defaults are):

post_max_size = 8M
upload_max_filesize = 2M

You must ensure that post_max_size is the same or larger than upload_max_filesize.

• These settings can be changed globally by changing them in your php.ini file (its location varies depending on your system). After changing the settings, remember to restart your web server.

• You may be able to change these settings locally if you're using Apache by putting a ".htaccess" file in phpMyAdmin's directory. The contents of the file should be:

php_value post_max_size 20M

php_value upload_max_filesize 20M

Change '20M' to however large you want it to be. You shouldn't need to restart the web server for these changes to take effect, but if you start getting Internal Server Errors (500) then this probably means your host does not allow you to change these settings and you need to remove those settings.

3) Splitting up the file

This is quite tricky, but you could split the file into smaller pieces, compress each piece, and then load them in one after the other. You may need to be familiar with SQL to do this safely, and editing large files can be problematic depending on your system speed and text editor.


4) Importing the SQL file another way

If you have a Unix/Linux server and have SSH access to it then you can use the `mysql' command line program to import the file like so:

mysql -u myuser -p mydatabase < myfile.sql

This will prompt you for the password for `myuser' and then import the SQL from myfile.sql. Obviously you will need to have uploaded myfile.sql through FTP or similar. Ideally you would compress myfile.sql, upload it, uncompress it and then import using the command above.