Importing large files through phpMyAdmin on xampp installation

673-phpmyadmin-logo-s-Like it’s described in title in this post I’ll try to describe how to increase the maximum file size for importing database on xampp instalation.

Situation

You installed Xampp and you want to import existing data from other installation or from host. First you need to export the data. In this case I’m using wordpress installation and I want to have copy of my whole site on local install just to have some materials during web development.

Step 1 : Exporting the data

Go to your phpMyAdmin from cPanel or type: localhost/phpmyadmin in your browser if you want to extract from xampp installation. Open database and go to extract, choose “quick” or “custom” and format, sql is preferred. After download is complete send file in .zip format or leave it as is.

Step 2 : Import the data

It’s the best solution to open new, clean database, and import the file in phpMyAdmin. Choose file from local folder and leave all settings as is. That should be it.

But if your file exceeded file size of 2MB (that is default value in xampp installation) than you need to tweak some out. Basically we will just increase the maximum upload file size.

Open your xampp install folder and go to php folder. With some text editor file (notepad++) open the file called php.ini (by type windows describe it as configuration settings) and edit the line (cca. 770, or press ctrl+f  in notepad++ to find it):

post_max_size = 2M

instead 2M, put wanted maximum file size (16MB for example but not higher than 128M), after that go to this line:

max_execution_time = 30

instead 30, increase execution time that’s in seconds (90 for example), also you need to change this line:

max_input_time = 60

instead 60, increase input time in seconds (120 for example)

and top of all you have to change this line:

upload_max_filesize = 2M

instead of 2M, increase to 16M for example.

Save the file, restart apache and mysql in xampp and try to upload again, if you follow correctly you should be able to import files through 16MB (example)

You must pay attention to following:

If the upload_max_filesize is larger than post_max_size, you must increase post_max_size so that it is bigger than upload_max_size.

If the value of post_max_size is larger than memory_limit, you must increase memory_limit so that it is larger than post_max_size.

Advertisements

54 thoughts on “Importing large files through phpMyAdmin on xampp installation

    1. Hy,
      did you restart apache and mysql in xampp? I did this last week with new version of xampp (1.8.3) on windows without problems.
      When you shut down xampp you have to “kill” httpd.exe process in task manager on windows. Then power up xampp again.

  1. Luko, thank you very much for saving me hours of work trying to figure this out!

    btw
    First paragraph in your post. “hole” site should be “whole” site. 😉

    Cheers
    DN

  2. Can’t get it to work–I have 2 .ini files within XAMPP–>PHP. One says ‘development’, one says ‘production’. I’ve made my changes in both files. My dump file is 47MB, so I set the max to 50MB and changed the timeouts. Also killed the process in task manager and re-started. PHP import screen STiLL says max file size 2,048KiB. What the heck am I missing here??

    1. Neither in development or in production. There must be php.ini file. Windows describe it as configuration settings (as all files with .ini extension). Depending on your local settings it may be hidden (rarely) so you have to allow the visibility of hidden files.

      Try to search ../xampp/php folder for php.ini file.

  3. Thanks a lot. After spending almost 2 hours searching online for a solution and editing various php.ini and .htaccess files, and cracking my head trying to upload via Cygwin to Phpmyadmin on Xampp, this tutorial of yours allowed me to upload my large sql file instantly…..life saver!

    1. Don’t really know what to say. I did it successfully with latest version of xampp and win 8.1.
      Server error 500 is “catch-all” error generated by web server (in this case xampp) when communicating with client. Maybe you should try to find error log for further description about it.
      By “breaking some long lines” do you mean when editing php.ini file?

  4. Thank you for the help. I’ve done these steps at least three or four times in the past. But once the time comes to do them again I always find myself forgetting how its done 😉

    1. Set post_max_size to 200M and upload_max_filesize also on 200m.
      On max_input_time and max_execution_time you can set value to default on -1 which means unlimited.

    1. Did you restart mysql and apache process in xampp application? And did you “kill” the httpd procces in task manager?
      Btw. Which version of windows and xampp are you using?

  5. You have really helped a Brother with this! Keep informing us about new developments. I wish You All Favours

    1. Hy,

      I don’t have experience with such large files but you can try one of the following solutions (not tested):
      1. Set up php.in file with this setings: post_max_size = 3500M upload_max_filesize = 3500M max_execution_time = 0 max_input_time = 0 memory_limit = 4000M
      2. Try to split sql file in several smaller files.
      3. Try to upload through shell like described here: http://www.nex.be/press/2012/04/12/import-large-sql-files-in-xampp/

      Anyway, leave comment here if you succeed or not.

      1. I APPLIED 1 && 3 METHOD by using thesemethods i couldnot met my goal .now i try to apply this solution first split the sql file in several files then import them.
        after this i discuss with u about results thnx for help

      2. sir when i follow the above methods it donot import my file complete and it shows this paragraph on screen
        Script timeout passed, if you want to finish import, please resubmit same file and import will resume

      3. Try to change values for: max_execution_time and max_input_time to -1 or some large number like 10000, to avoid breaking upload. Also, increase memory_limit to something larger than post_max_size.

  6. It’s not working for us. We use Windows 7 64-bit and xampp control panel v3.2.2 (for windows 32 bit, because we couldn’t find for windows 64 bit)
    We tried imputing higher limits as suggested above, and even no limits. Importing data page still shows Max: 2,048KiB
    We even tried splitting dump files. We uploaded data structure first and we got message “No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration.” We then uploaded the first of the split dump files and we also got errors. We don’t know what to do now… Any other suggestions? Thanks.

    1. I just tested it with: Windows 7 pro SP1 64-bit and xampp 3.2.2 with php 7.0.2. Everything is working like it should. Did you restart apache and mysql services after editing php.ini file?

      1. Thanks for quick reply Luko. We followed instructions step by step and we even turned computer off and went to bed (we read somewhere else that it could take time for xampp to read the changes in php.ini) This morning all is the same. We use PHP 5.5.30
        Thanks.

      1. In the end we were able to do it. After installing and uninstalling XAMPP many times because we had on and off problems accessing Apache. To tell you the truth I don’t even know why your solution would work also on and off (maybe related to al the ons and off…) But in the end all is good. Thanks!

  7. Hi , I m trying to import sql database that is already builted , I want to import , I hv changed the limits as u said bt still m having the error that “you probably trying to upload too large file” I change in php.in , php.in.dist, php4. Cong

  8. hi
    i made all the changes as u suggested in config file of php. i want to upload a 500 mb file. it showing error in script.

    1. What kind of error? If it’s script timeout passed … then try setting unlimited value in ..xampp\phpMyAdmin\libraries\config.default.php . It should look something like this:
      /**
      * maximum execution time in seconds (0 for no limit)
      *
      * @global integer $cfg['ExecTimeLimit']
      */
      $cfg['ExecTimeLimit'] = 0;

  9. Hi Mate,

    Thanks for all the details.
    I am able to process the file but its only copying first row from the CSV when I push it using Format ‘CSV using Data Load’, however it gets updated properly if I import using Format ‘CSV’, any idea why it is not taking all the rows when I push it using Data Load. It is the exact same file with same formatting.

    Regards,
    Kunal

    1. Although I don’t have experience with importing tables via CSV I don’t know what do you mean by Format “CSV using Data Load” ? Options for format in phpmyadmin import is: SQL, CSV, XML, ESRI Shape file, MediaWiki table and OpenDocument Spreadsheet.
      With CSV import possible issues my occur with columns separator or when first row contains column names…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s