Import mysql database using command line in Linux

Upload mysql database command :

If you want to import a mysql database using command line, here is the command.

$  mysql -h host-name -u username -p'password' "database_name" < "path/of/the/sql/file"

where 
hostname is name of your host. If you upload on your system it can be localhost or 127.0.0.1. If you would upload on server it would be IP of the server.
username is username of mysql user
password is password of mysql user, Remember there is no space between -p & single quote (')
database_name is name of your database in which you want to upload the sql file

Examples:

a) If you want to upload file on your localhost, the command would be (if root has no password)

$ mysql -h 127.0.0.1 -u root "database_name" < /path/of/the/sql/file

b) If you want to upload file on other system of same LAN, the command would be

$ mysql -h 192.168.1.7 -u john -p'johnathan' "webpro" < /home/user/documents/webpro.sql

where 192.168.1.7 - IP of other system of same LAN
john is username of mysql user
johnathan is his password
webpro is database name

NOTE :
Please create database first before running above command otherwise it would give error "ERROR 1049 (42000): Unknown database 'webpro'"

Increase import file size limit in phpmyadmin

Upload large size sql files in phpmyadmin :

1) Open file php.ini

2) Search parameters upload_max_filesize.

3) Increase the file size limit for the parameter.

upload_max_filesize=512M

Now you can upload sql files up to 512 MB in phpmyadmin.
In this example I have set limit 512 MB, you can set according to your requirement.

4) Increase file size limit for post_max_size and memory_limit too.
     It should be equal or greater than upload_max_filesize

post_max_size=1024M
memory_limit=1024M

5) Restart Apache.

There is a instruction in php.ini that "Maximum amount of memory a script may consume (128MB)" but when you will increase the memory_limit size greater than 128M, you will see that you are not getting any error and large file has been uploaded successfully.

NOTE :
The first things to check are the values of upload_max_filesize, memory_limit and post_max_size in the php.ini configuration file. All of these three settings limit the maximum size of data that can be submitted and handled by PHP. post_max_size and memory_limit need to be equal or larger than upload_max_filesize.

Be careful to set memory_limit size. If it exceeds the limit, the phpmyadmin does not start. It shows blank page.

If you are not able to upload small size sql file (in-kb/mb) because of change in  memory limit. Make it again 128 MB and Restart Apache


Solution :2
If you are still facing problem while uploading large size sql files, upload them using command line. To see this method, click here .

You should see this post too. Click here.

phpmyadmin error : #1153 - Got a packet bigger than 'max_allowed_packet' bytes

 phpmyadmin- Got a packet bigger than 'max_allowed_packet' bytes

 


Error in phpmyadmin while uploading sql file :

1) Open file my.cnf 
path of the file : /opt/lampp/etc/my.cnf

2) Set value max_allowed_packet=128M under attribute [mysqld] 
    If parameter is not there, add it under attribute [mysqld] .

[mysqld]
max_allowed_packet=128M

3) Restart XAMPP.