MySQL: Copy Database From One Server to Another Server (Using mysqldump)

In this post, I am sharing different types of options to copy your MySQL Database from one server to another server.

The migration of data, objects or database is a ubiquitous task for any DBA so as a blogger I can’t skip this kind of topics.

Common and General option:

Server 1: Take the backup in .sql file
– -databases option adds the CREATE DATABASE in dump file, so that you can restore same backup in any other server without creating a blank database.

1	my-shell> mysqldump --databases db_name > mydump.sql

Next, copy mydump.sql file from Server 1 to Server 2.

Server 2: Restore the backup

1	my-shell> mysql < mydump.sql

My favourite option:

Use pipe and copy the database from one server to another, without taking or moving the physical file.

1	mysqldump -uuser -ppassword db_name | mysql -hremoteserver -uremoteuser -premoteserverpassword

For all database:

1	mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserverpassword