How to Import 1GB .sql file to WAMP/phpmyadmin

Question

I want to import over 1GB size sql file to MySQL database in localhost WAMP/phpmyadmin. But phpmyadmin UI doesn't allow to import such big file.

What are the possible ways to do that such as any SQL query to import .sql file ?

Thanks

Screenplay

Is there any particular reason that forces you to use PHPmyadmin? – Daemon

no any special reason but I'm working/testing on that – Screenplay

I suggest you to try some proper MySQL manager software, like MySQL workbench (free) or SQLyog community edition (free) – Daemon

I've tried with the SQLyog. Thats cool. Thanks – Screenplay

see video here youtu.be/eTpVItabrjY – Offhand

Answer 1

I suspect you will be able to import 1 GB file through phpmyadmin But you can try by increasing the following value in php.ini and restart the wamp.

post_max_size=1280M
upload_max_filesize=1280M
max_execution_time = 300 //increase time as per your server requirement. 

You can also try below command from command prompt, your path may be different as per your MySQL installation.

C:\wamp\bin\mysql\mysql5.5.24\bin\mysql.exe -u root -p db_name < C:\some_path\your_sql_file.sql

You should increase the max_allowed_packet of mysql in my.ini to avoid MySQL server gone away error, something like this

max_allowed_packet = 100M

Silky

Increase also the PHP max script time, because the default is 30 seconds and I guess that you can't upload and run a 1GB file in that time period – Daemon

Command path for Wamp Version 2.2 seems to be slightly different, this works for me: C:\wamp\bin\mysql\mysql5.5.24\bin\mysql.exe -u root -p < db_file.sql – Dwarfish

I tried modifying the value in php.ini file and restarted XAMPP, but its still showing the same limit as 2MB, any ideas? – Recognizance

@Recognizance if you have made the changes as per the suggestion than I suggest you to restart apache and check the value again – Silky

I restarted it, but its still the same. – Recognizance

I'm getting : "Mysql has gone away". – Skysail

@JoseManuelAbarcaRodríguez increase max_allowed_packet of mysql in my.ini – Silky

My database was 30Mb and max_allowed_packet was 1M, I changed it to 100M and it worked. Upvoted! – Skysail

Answer 2

Step 1: Find the config.inc.php file located in the phpmyadmin directory. In my case it is located here:

C:\wamp\apps\phpmyadmin3.4.5\config.inc.php 

Note: phymyadmin3.4.5 folder name is different in different version of wamp

Step 2: Find the line with $cfg['UploadDir'] on it and update it to:

$cfg['UploadDir'] = 'upload';

Step 3: Create a directory called ‘upload’ within the phpmyadmin directory.

C:\wamp\apps\phpmyadmin3.2.0.1\upload\

Step 4: Copy and paste the large sql file into upload directory which you want importing to phymyadmin

Step 5: Select sql file from drop down list from phymyadmin to import.

Dobson

Consider styling abit more like step 1, Note etc – Crumpton

I personally find this solution to be a lot better and easier for beginners. – Guzman

This works, great solution. However, my config.inc.php didn't include $cfg['UploadDir'] but I found it in the config.sample.inc and copied it over. – Strawboard

Really it works!!!. i tried 2 days and finally got this solution. A perfect solution – Avouch

Answer 3

The values indicated by Ram Sharma might need to be changed in Wamp alias configuration files instead.

In <wamp_dir>/alias/phpmyadmin.conf, in the <Directory> section:

  php_admin_value upload_max_filesize 1280M
  php_admin_value post_max_size 1280M
  php_admin_value max_execution_time 1800

Sikh

A vote from me, this one wast the answer fro me also (WAMPStack) – Skyjack

Newer version of WAMP 3.0.6 (as of 1/17/17) requires this method on top of modifying PHP.ini file. – Typewrite

Answer 4

Make sure to check the phpMyAdmin config file as well! On newer WAMP applications it is set to 128Mb by default. Even if you update php.ini to desired values you still need to update the phpmyadmin.conf!

Sample path: C:\wamp64\alias\phpmyadmin.conf

Or edit through your WAMP icon by: ->Apache -> Alias directories -> phpMyAdmin

Viticulture

Good work for me – Anadem

Answer 5

I also faced the same problem and, strangely enough, changing the values in php.ini did not work for me. But I found out one more solution that worked for me.

• Click your Wamp server icon -> MySQL -> MySQL console
• Once MySQL console is open. Enter your MySQL password and enter these commands:

1. use user_database_name
2. source path/to/your/sql/path/filename.sql

If you still have problems, watch this video.

Ferule

I find this a good alternative for quick importing, but I do have a single follow-up question: What are the advantages of this technique over the one using a custom upload folder? I understand that you don't have to change a config file for this (,at least I didn't,) but is there a speed/reliability advantage? – Ostia

@Ostia You can import any locations file on the fly without having to edit a config file. You are directly using the mysql CLI which is configured to support larger files and packets (though this method wont work for 1GB+ files as default values are not enough). – Betrothed

Answer 6

What are the possible ways to do that such as any SQL query to import .sql file ?

Try this

 mysql -u<user> -p<password> <database name> < /path/to/dump.sql

assuming dump.sql is your 1 GB dump file

Monied

Answer 7

A phpMyAdmin feature called UploadDir permits to upload your file via another mechanism, then importing it from the server's file system. See http://docs.phpmyadmin.net/en/latest/faq.html#i-cannot-upload-big-dump-files-memory-http-or-timeout-problems.

Copyright

Answer 8

If you will try to load such a large file through phpmyadmin then you would need to change upload_file_size in php.ini to your requirements and then after uploading you will have to revert it back. What will happen? If you would like to load a 3GB file. You will have to change those parameters in php.ini again.

The best solution to solve this issue to open command prompt in windows.

Find path of wamp mysql directory.

Usually, it is C:/wamp64/bin/mysql/mysqlversion/bin/mysql.exe

Execute mysql -u root

You will be in mysql command prompt

Switch database with use command.

mysql> use database_name
mysql> source [file_path]

In case of Windows, here is the example.

mysql> source C:/sqls/sql1GB.sql

That's it. If you will have a database over 10GB or 1000GB. This method will still work for you.

Masera

Answer 9

Before importing just make sure you have max_allowed_pack value set some thing large else you will get an error: Error 2006 MySQL server gone away.

Then try the command: mysql -u root -p database_name < file.sql

Menial

Answer 10

You can do it in following ways;

1. You can go to control panel/cpanel and add host % It means now the database server can be accessed from your local machine. Now you can install and use MySQL Administrator or Navicat to import and export database with out using PHP-Myadmin, I used it several times to upload 200 MB to 500 MB of data with no issues

2. Use gzip, bzip2 compressions for exporting and importing. I am using PEA ZIP software (free) in Windows. Try to avoid Winrar and Winzip

3. Use MySQL Splitter that splits up the sql file into several parts. In my personal suggestion, Not recommended

4. Using PHP INI setting (dynamically change the max upload and max execution time) as already mentioned by other friends is fruitful but not always.

Listel

Answer 11

I suggest you'd definitely use mysql command prompt that would be faster option. because there are limitation in phpmyadmin UI and browsers itself for process request

following are steps to use mysql command line.

doesnt matter if you user xampp/wamp/mamp

find mysql directory in xampp/wamp/mamp in your system directory

search for bin folder path is system dir/(xampp/wamp)/mysql/bin

now open command prompt i'll refer to windows

please change directory in command line and point to path_to_mysql_bin

then user following command

"mysql-u root -p -h localhost" press enter system will ask for password press enter again

finally you're in

please use command "use Database_name" to point to specific database and you're good to go

if you want to upload database in to for ex. temp table

then follow these steps

use temp;
source path_to_sql_file_where_db_is_store_in_your_system.sql;

this will upload sql file database in temp db

if you didnt get any part from this then please pm me i'll definitely help you out.

Mitesh

Demirep

Answer 12

In Your Case with Xammp it not work. To Slove this problem In Xammp? Just make database to Zip file and upload that's it will work in xammp. if the size is too large it will show you time out error but submit the same zip folder again and after resubmitting will continue from position when it Force stop.

Pulsation

Answer 13

Go to c:/wamp/apps/phpadmin3.5.2 Make a new subfolder called ‘upload’ Edit config.inc.php to find and update this line: $cfg[‘UploadDir’] = ‘upload’ Now when you import a database, you will give a drop-down list in web server upload directory with all the files in this directory. Chose the file you want and you are done.

Backsaw