Export, Import MySQL db between different servers through Linux command line

You should have

Database name, Database Username, Database Password and command line access to your server

Export database to local file system, for example /var/www/html

cd /var/www/html
mysqldump --add-drop-table -u dbuser -p dbname > dbname.sql

Enter the password when asked to and it should save dbname.sql file in your local file system, for example, /var/www/html here.

Once the file is saved download it using FTP or other relevant method and upload to new server using relevant method.

On new server if you already have database created you can take a backup using the same method as above and then delete and recreate the database as follow:

mysql -u newdbuser -p newdbname

Enter the password when asked to and it will enter you to mysql console such as:

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

In the mysql console drop previously existed database and recreate new one

DROP DATABASE dbname;
CREATE DATABASE dbname;

Exist the mysql console and then ssh to the webroot of new server. Now import the file exported in very first step

mysql -u newdbuser -p newdbname< dbname.sql

Enter the password when asked to and it should import the database to your database named newdbname.

Leave a Reply