How to import large sql file in phpmyadmin

I want to import a sql file of approx 12 mb. But its causing problem while loading. Is there any way to upload it without splitting the sql file ?

Try to import it from mysql console as per the taste of your OS.

mysql -u {DB-USER-NAME} -p {DB-NAME} < {db.file.sql path}

or if it's on a remote server use the -h flag to specify the host.

mysql -u {DB-USER-NAME} -h {MySQL-SERVER-HOST-NAME} -p {DB-NAME} < {db.file.sql path}

3 things you have to do:

in php.ini of your php installation (note: depending if you want it for CLI, apache, or nginx, find the right php.ini to manipulate)

post_max_size=500M

upload_max_filesize=500M

memory_limit=900M

or set other values.

Restart/reload apache if you have apache installed or php-fpm for nginx if you use nginx.

Remote server?

increase max_execution_time as well, as it will take time to upload the file.

NGINX installation?

you will have to add: client_max_body_size 912M; in /etc/nginx/nginx.conf to the http{...} block

Edit the config.inc.php file located in the phpmyadmin directory. In my case it is located at C:\wamp\apps\phpmyadmin3.2.0.1\config.inc.php.

Find the line with $cfg['UploadDir'] on it and update it to $cfg['UploadDir'] = 'upload';

Then, create a directory called ‘upload’ within the phpmyadmin directory (for me, at C:\wamp\apps\phpmyadmin3.2.0.1\upload\).

Then place the large SQL file that you are trying to import into the new upload directory. Now when you go onto the db import page within phpmyadmin console you will notice a drop down present that wasn’t there before – it contains all of the sql files in the upload directory that you have just created. You can now select this and begin the import.

If you’re not using WAMP on Windows, then I’m sure you’ll be able to adapt this to your environment without too much trouble.

Reference : http://daipratt.co.uk/importing-large-files-into-mysql-with-phpmyadmin/comment-page-4/

Solution for LINUX USERS (run with sudo)

Create 'upload' and 'save' directories:

mkdir /etc/phpmyadmin/upload
mkdir /etc/phpmyadmin/save
chmod a+w /etc/phpmyadmin/upload
chmod a+w /etc/phpmyadmin/save

Then edit phpmyadmin's config file:

gedit /etc/phpmyadmin/config.inc.php

Finally add absolute path for both 'upload' and 'save' directories:

$cfg['UploadDir'] = '/etc/phpmyadmin/upload';
$cfg['SaveDir'] = '/etc/phpmyadmin/save';

Now, just drop files on /etc/phpmyadmin/upload folder and then you'll be able to select them from phpmyadmin.

Hope this help.

Just one line and you are done (make sure mysql command is available as global or just go to mysql installation folder and enter into bin folder)

mysql -u database_user_name -p -D database_name < complete_file_path_with_file_name_and_extension 

Here

• u stands for User
• p stands for Password
• D stands for Database

---DON'T FORGET TO ADD < SIGN AFTER DATABASE NAME---

Complete file path with name and extension can be like

c:\folder_name\"folder name"\sql_file.sql

---IF YOUR FOLDER AND FILE NAME CONTAINS SPACE THAN BIND THEM USING DOUBLE QUOTE---

Tip and Note: You can write your password after -p but this is not recommended because it will show to others who are watching your screen at that time, if you don't write there it will ask you when you will execute command by pressing enter.