[mysql] Can't import database through phpmyadmin file size too large

I have been trying to import Database through phpMyAdmin. My database file is a.sql and it's size is 1.2 GB I am trying to import this on local and phpMyAdmin is saying:

You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit.

Please help I really need this to work.

This question is related to mysql database apache phpmyadmin wampserver

The answer is


for ubuntu 14.04 use this:

  • 1- open php.ini using gedit or nano:
  • sudo gedit /etc/php5/apache2/php.ini
  • 2- find 'upload_max_size' then replace this single line with these lines:
  • max_execution_time = 259200 max_input_time = 259200 memory_limit = 1000M upload_max_filesize = 750M post_max_size = 750M
  • 3- save php.ini and close editor
  • 4- restart apache2:
  • sudo service apache2 restart
  • 5- now open
  • localhost://phpmyadmin/
  • in browser and uplaod your database.sql


    You can also try compressing (zipping) the file. It sometimes works for me if I can't get to php.ini for phpmyadmin. If zipping reduces the file size below the maximum file size, it just might work!


    You have three options:

    • Use another way to get the file on the server, and use a mysql client on the server
    • Use another client to connect to the server and load the data
    • Change your PHP settings to allow such huge files. Don't forget to increment the execution time as well.

    Here is what I've done:

    1. Gone to my XAMPP installed directoy C:\xampp\php
    2. Open the "Configuration Settings" file named php.ini using your text editor, preferably you can also you Notepad for Windows machine.
    3. Somewhere nearby on line no 886, please update it to upload_max_filesize = 128M
    4. Similarly on line no 735 post_max_size = 128M
    5. On line no 442 max_execution_time = 300
    6. On line no 452 max_input_time = 60
    7. Restart your Apache from either XAMPP Control Panel or using CMD in Windows https://stackoverflow.com/a/6723485/969092

    Done!


    1. Set the below values in php.ini file (C:\xampp\php\)

      • max_execution_time = 0
      • max_input_time=259200
      • memory_limit = 1000M
      • upload_max_filesize = 750M
      • post_max_size = 750M
    2. Open config.default file(C:\xampp\phpMyAdmin\libraries\config.default) and set the value as below:

      • $cfg['ExecTimeLimit'] = 0;
    3. Then open the config.inc file(C:\xampp\phpMyAdmin\config.inc). and paste below line:

      • $cfg['UploadDir'] = 'upload';
    4. Go to phpMyAdmin(C:\xampp\phpMyAdmin) folder and create folder called upload and paste your database to newly created upload folder (don't need to zip)

    5. Lastly, go to phpMyAdmin and upload your db (Please select your database in drop-down)

    upload image

    1. That's all baby ! :)

    *It takes lot of time.In my db(266mb) takes 50min to upload. So be patient ! *


    Use command line :

    mysql.exe -u USERNAME -p PASSWORD DATABASENAME < MYDATABASE.sql
    

    where MYDATABASE.sql is your sql file.


    If you do not want to change the settings or play with command line. There is option to compress the file and upload in phpMyAdmin. It should bring down the size considerably.


    You no need to edit php.ini or any thing. I suggest best thing as Just use MySQL WorkBench.

    JUST FOLLOW THE STEPS.

    Install MySQL WorkBench 6.0

    And In "Navigation panel"(Left side) there is option call 'Data import' under "MANAGEMENT". Click that and [follow steps below]

    1. click Import Self-Contained File and choose your SQL file
    2. Go to My Document and create folder call "dump"[simple].
    3. now you ready to upload file. Click IMPORT Button on down.

    If you are using MySQL in Xampp then do the steps below.

    Find the following in XAMPP control panel>Apach-Config> PHP (php.ini) file

    • post_max_size = 8M

    • upload_max_filesize = 2M

    • max_execution_time = 30
    • ut_time = 60enter code here memory_limit = 8M

    And change their sizes according to your need. I'm using these values

    post_max_size = 30M
    upload_max_filesize = 30M
    max_execution_time = 4500
    max_input_time = 4500
    memory_limit = 850M
    

    to import big database into phpmyadmin there are two ways 1 increase file execution size from php.ini 2 use command line to import big database.


    For Upload large size data in using phpmyadmin Do following steps.

    1. Open php.ini file from C:\wamp\bin\apache\Apache2.4.4\bin Update following lines
      max_execution_time = 259200
      max_input_time = 259200
      memory_limit = 1000M
      upload_max_filesize = 750M
      post_max_size = 750M
      than after restart wamp server or restart all services Now Upload data using import function in phymyadmin. Apply second step if till not upload data.
    2. open config.default.php file in c:\wamp\apps\phpmyadmin4.0.4\libraries (Open this file accoring to phpmyadmin version)
      Find $cfg['ExecTimeLimit'] = 300;Replace to $cfg['ExecTimeLimit'] = 0;
      Now you can upload data.

    You can also upload large size database using MySQL Console as below.

    1. Click on WampServer Icon -> MySQL -> MySQL Consol
    2. Enter your database password like root in popup
    3. Select database name for insert data by writing command USE DATABASENAME
    4. Then load source sql file as SOURCE C:\FOLDER\database.sql
    5. Press enter for insert data.

    Note: You can't load a compressed database file e.g. database.sql.zip or database.sql.gz, you have to extract it first. Otherwise the console will just crash.


    Another option that nobody here has mentioned yet is to do a staggered load of the database using a tool like BigDump to work around the limit. It's a simple PHP script that loads a chunk of the database at a time before restarting itself and moving on the the next chunk.


    Examples related to mysql

    Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

    Examples related to database

    Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

    Examples related to apache

    Enable PHP Apache2 Switch php versions on commandline ubuntu 16.04 Laravel: PDOException: could not find driver How to deploy a React App on Apache web server Apache POI error loading XSSFWorkbook class How to enable directory listing in apache web server Job for httpd.service failed because the control process exited with error code. See "systemctl status httpd.service" and "journalctl -xe" for details How to enable php7 module in apache? java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient The program can't start because api-ms-win-crt-runtime-l1-1-0.dll is missing while starting Apache server on my computer

    Examples related to phpmyadmin

    phpMyAdmin on MySQL 8.0 phpmyadmin - count(): Parameter must be an array or an object that implements Countable Error loading MySQLdb Module 'Did you install mysqlclient or MySQL-python?' phpMyAdmin ERROR: mysqli_real_connect(): (HY000/1045): Access denied for user 'pma'@'localhost' (using password: NO) phpMyAdmin access denied for user 'root'@'localhost' (using password: NO) mysqli_real_connect(): (HY000/2002): No such file or directory How to create a foreign key in phpmyadmin #1292 - Incorrect date value: '0000-00-00' MySQL error - #1932 - Table 'phpmyadmin.pma user config' doesn't exist in engine phpMyAdmin Error: The mbstring extension is missing. Please check your PHP configuration

    Examples related to wampserver

    How to upgrade safely php version in wamp server How to enable local network users to access my WAMP sites? Project Links do not work on Wamp Server WAMP Cannot access on local network 403 Forbidden Apache won't start in wamp WampServer orange icon How can I access localhost from another computer in the same network? Forbidden: You don't have permission to access / on this server, WAMP Error Wampserver icon not going green fully, mysql services not starting up? How to change the URL from "localhost" to something else, on a local system using wampserver?