How to Import/Export a MySQL Database using MySql command line

How to Import/Export a MySQL Database

MySQL command is used to export large database in easiest and fastest way. This example shows you how to export/import a database. It is good to export your data often as a backup.

Export a MySQL Database

      1.     If you are on Windows you will need to open CMD and go to directory where mysql.exe is installed. If you are using WAMP server then this will be usually located in:
C:\wamp\bin\mysql\mysql5.6.17\bin (*note the version of mysql might be different) and then SHIFT+RIGHT CLICK and click on the Open Command Window Here from specified path.

2.     After the first step execute the below command in command prompt.

                   mysqldump -u {DB-USER-NAME} -h {MySQL-SERVER-HOST-NAME} -p {DB-NAME} > {NAME-OF-THE-FILE.sql path}

      3.     You will be promoted for a password, type the password for the respective username and press Enter. Replace the username, password and database_name with your MySQL username, password and database name.

4.     The file test.sql now holds a backup of your database and is ready for download in your computer.
5.     To export a single table from your database you can use the following command:

                 mysqldump -p --user={DB-USER-NAME} {DB-NAME} {TABLE_NAME} > tableName.sql

6.     Again you would need to replace the username, database and tablename with the correct information. Once it is done the table specified would be saved to your computer as tableName.sql.

      Import a MySQL Database

      1.     Locate.Sql file in following specified directory C:\wamp\bin\mysql\mysql5.6.17\bin.

2.     If you are on Windows you will need to open CMD and go to directory where mysql.exe is installed. If you are using WAMP server then this will be usually located in:
C:\wamp\bin\mysql\mysql5.6.17\bin (*note the version of mysql might be different)     and then SHIFT+RIGHT CLICK and click on the Open Command Window Here from specified path. Refer above screen shot.
3.     Next run the command in command prompt:

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

      4.     To import a single table into an existing database you would use the following command

                mysql -p --user={DB-USER-NAME} {DB-NAME} {TABLE_NAME} > tableName.sql