MySQL DB import/export through the command line

MySQL is an open source database management software that helps users store, organize, and retrieve data. It is a very powerful program with a lot of flexibility—this tutorial will provide the simplest introduction to MySQL.

Moving MySQL database is a frequent necessity. Servers migration/ disk space runs out etc are the main reasons where we need to migrate mysql database. In the case of MySQL database migration, you have many options for accomplishing the task. I’ll discuss three of the most common options ie; migrating our mysql databse through command prompt.

How to export a mysql database using Command Prompt

To start, SSH into the first server (using a tool like PuTTy) as a user with sufficient privileges to work with the server. Execute the following command at the terminal to dump the database to a file:

For Export:

mysqldump -u [user] -p[password] [db_name] | gzip > [filename_to_compress.sql.gz] 

Ex:
mysqldump -u root -proot@123 citydb > citydb.sql  | gzip > citydb.sql.gz

We will be prompted for the password and the export will begin. Note that you should replace each variable in brackets with the proper values and do not include the brackets.

When the export is complete, it’s helpful to compress the file to make it transfer to the new server faster. To do so, we have used the gzip command.

Next, transfer the compressed file to your new server, either by making it available at a web address at the old server and using wget, or by using FTP to download and upload the file.

For Import:

gunzip < [compressed_filename.sql.gz]  | mysql -u [user] -p[password] [databasename] 

Ex:
gunzip < citydb.sql.gz  | mysql -u root -proot@123 citydb 

Note: There is no space between the keyword '-p' and your password.