import large MySQL file phpmyadmin

I am working on IP Address Host Website Optimization as previously I was using only one table and inserting all the information related to IP Address in Single Table but I just notices that I am repeating un-necessary data in every row and it was increasing my database size rapidly. I download IP Address Host MySQL database and tried to restore / import in phpmyadmin on XAMP / Local Server but it gives me an error “You probably tried to upload a file that is too large.” I know it’s a known behavior of phpmyadmin / MySQL due to its default limitations setup in configuration file but I thought to write it down for people who do not like to remember things like me J

Solution:

Navigate to you php.ini file (for XAMP: C:\xampp\php or the location you installed your php) and open it in notepad or your favorite editor like notepad++.

  • Find and Replace
    • Default : post_max_size=8M
    • New: post_max_size=2048M - I need to restore 2 GB database.
  • Find and Replace
    • Default : upload_max_filesize=512M
    • New: upload_max_filesize=2048M
  • Find and Replace
    • Default : max_execution_time=1800
    • New : max_execution_time=1800

Navigate to \mysql\bin location to edit MySQL file “my.ini” and

  • Find and Replace
    • Default : innodb_log_file_size = 5M
    • New : innodb_log_file_size = 1024M
  • Find and replace
    • Default : innodb_log_buffer_size = 8M
    • New : innodb_log_buffer_size = 1024M

Please restart your Apache Server Services for PHP and MySQL Server Service for MySQL configuration changes to take place.

Important: innodb_log_buffer_size , default settings and configurations as per MySQL documentations says that it refresh on every second and we mostly do not required to increase its value as much higher but in my scenario, it was required as my server was throwing an error for this as well.

I believe, we have done everything and it must be a working solution for all of you in any kind of solutions required. Please do not forget to change the size based on your requirements. I have done this based on my maximum requirements.

.

Get Socially Engaged with IP Address and Host Team !