How to Import and export databases in mysql using terminal


Today I will be detailing about how to export/import large database file in mysql through terminal.

Export From a Database
To export a database from mysql trough terminal.

1- open console.
2- issue below command

mysqldump -u <username> -p <application_db>  >  <appdb>.sql

e.g. mysqldump -uroot -p application_database  >  application.sql

Import To Database

There are two ways to do the same.

First Method

Assuming you’re on a Linux or Windows console:

1- Open console
2- issue below command

mysql -u <username> -p <database_name> < <filepath/filename.sql>

e.g  mysql -uroot -p application_db   < /abhishek/application_database.sql

It will prompt for password and do the job, You may see an idle screen while import is being done there.
There will be no status displayed untill it reaches to end of script.

You may also provide the password directly in the same line as below.

mysql -u <username> -p<dbuserpassword> <database_name> < <filepath/filename.sql>

e.g  mysql -uroot -pabhishek  application_db   < /abhishek/application_database.sql

It will work exactly similar to above provided it will not ask for password, as we have already provided in command.

Second Method

1- Open console
2- start mysql interactive mode as below

mysql -u<database_username> -p

e.g. mysql -uroot -p

It will prompt for password, provide password of mysql user and proceed to step 3.

3- select database in which file will be imported. (Assuming database name is application_db)

use <database_name>

use application_db

4- issue below command to import. (assuming here test.sql is our file to import)

source  <path_to_sqlfile_test.sql>

e.g. source  /path_to_sqlfile/test.sql

It will show the status too, while import script is running there. In this second approach seems more better than the first one as user is aware of its current progress of import.

Leave a Reply

Your email address will not be published. Required fields are marked *