Import large sql databases with phpMyAdmin (increase size limit) 2


How to overcome the default import 2MB limit in SQL with phpMyAdmin.


Start with your development environment:

In case you run it on Windows (Windows Web Development Environment with Windows, Apache, MySQL, and PHP) it can be found in C:\Program Files\PHP5 or under an alternate name with the PHP version attached i.e. \bin\php\php5.5.12\.


For LAMP, which is the short name for open-source software: Linux operating system, Apache HTTP server, MySQL database and PHP programming language.

With Apache2 and PHP5 installed you need to make three changes in the file
sudo gedit /etc/php5/apache2/php.ini


If you run XAMPP, then probably you will find the php.ini file in the .xamppapachebinphp.ini file.


Let’s see the steps required:

1. Search for the entry post_max_size = 2M
post_max_size

2. Insert a larger number than the size of your database (i.e. if you have an 8MB database in this case you can increase it to 64M
3. Edit the memory_limit
4. Give a larger number than the one given to post_max_size i.e. if you insert 64M to post_max_size; you need to enter a higher figure to the memory_limit i.e. 128M.

memory_limit

5. Edit the entry upload_max_filesize with a value smaller than the post_max_size i.e. 64M or less.

upload_max_filesize


In summary, starting from bigger values the order should go like this

– post_max_size > db size
– memory_limit > post_max_size
– upload_max_filesize

import large sql database

*This post is intended to help users import their databases into local installations. In web hosting, things differ from host to host. A good practice is to check your phpMyAdmin limit (on my Namecheap web host shared server the PHPMyAdmin limit is set to 50MB) located in your phpMyAdmin and selecting the tab Import. If your database is larger than the limit of your web host, you should message them for arrangements.




Read more:


Leave a comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Import large sql databases with phpMyAdmin (increase size limit)

  • Alan OldStudent

    I am trying to duplicate the Occupy Tacoma (occupytacoma [dot] org) website locally on my wamp server, and I looked for the php.ini files. There are 2, in 2 places, with somewhat different values. I haven’t changed anything yet.

    What do you make of that? Should both be edited? Should the values match? If it makes any difference, I have several WordPress installations in my localhost.

    Here are the details:
    C:\wamp\bin\php\php5.3.9\php.ini
    memory_limit = 128M
    post_max_size = 8M
    upload_max_filesize = 2M

    C:\wamp\bin\apache\Apache2.2.21\bin\php.ini
    memory_limit = 128M
    post_max_size = 8M
    upload_max_filesize = 40M

    Regards,
    Alan OldStudent