We at YodiYil believe in trying new ideas and sharing our learning, data and results. We proactively document and share our resources in the form of tutorials for anyone to use or contribute to.

At some point most of us end up in a situation where we need to migrate large MySQL database from one Ubuntu server to another without having to locally download the MySQL data files or running complicated migration tools.

Our GPS Tracking and IOT services have components that utilize MySQL on Ubuntu servers and quite some time we require to transfer databases from one server to another for upgrades or maintenance.

It is a simple and easy 3 step process.

Step 1 - Backup the Source Database

First create a backup of the database on the source server.

mysqldump -u root -p --opt databasename > databasename.sql

Once the dump is completed and ready, transfer the dump to destination server

Step 2 - Transfer database dump to destination server

We use SFTP to copy the database file between Ubuntu servers. You can also use SCP instead.

The steps to connect to destination server using sftp and transferring the dump file is as following.

Navigate to the directory where the mysqldump file is stored.

sftp user@destinationserver (replace this with your destination servers user name and IP or domain)

sftp> cd target/directory (navigate to the destination servers directory where you want to copy the file to)

sftp> put databasename.sql (use the file name to transfer single file, or the command below to copy all files from source directory)

sftp> put *

Step 3 - Import MySQL database dump

Once the data transfer is complete, import the database dump into the new MySQL server.

mysql -u root -p databasename < /target/directory/databasename.sql