Import and Export MySQL Database

There are many ways you can Export and then Import MySQL on other server like phpMyAdmin and MySQL WorkBench. But most efficient way (though not much user friendly) is command line option. This is helpful when you don’t have other option to transfer database but you have SSH access to the server and you want total control of the whole process.

First connect server from where you want to Export the database using SSH (use tool like PuTTy on Windows or Terminal on Linux and Mac). Execute following command to dump the database to a file.

mysqldump -u [username] -p [database_name] > [db_dump.sql]

You will be prompted for password and the export will begin. When export is complete then it is recommended that you compress the file for transfer. To compress run below command.

tar zcf db_dump.tar.gz db_dump.sql

Next transfer the compressed file to new server. Connect SSH session to new server and then decompress using below command.

tar zxf db_dump.tar.gz

This will place the original SQL file in the same folder. Now Import using following command.

mysql -u [username] -p [database_name] < [db_dump.sql]

Bingo! Import is done!

In case server where Import has to be done is accessible by the source server and connectivity between two is stable. Following commands can be executed to directly transfer data.

mysqladmin -h [destination_host] create [database_name] -u [username] -p

mysqldump [database_name] | mysql -h [destination_host] [database_name] -u [username] -p