Click here to Skip to main content
14,690,218 members
Articles » Database » Database » MySQL
Tip/Trick
Posted 4 Dec 2018

Tagged as

Stats

5K views
4 bookmarked

How to Transfer a MySQL Database between Two Servers using SCP

Rate me:
Please Sign up or sign in to vote.
4.27/5 (6 votes)
4 Dec 2018CPOL
A guide to transfer your MySQL databases between two servers using the SCP method

Introduction

Transferring MySQL database between servers can be accomplished using SCP (Secure Copy). It is a secured method in copying file(s) between servers since it uses the same authentication and security as the Secure Shell (SSH) Protocol. SCP encrypts both the file and any passwords exchanged while transferring the MySQL database.

Prerequisites

Before we start, you’ll need to have two different servers installed with MySQL of the same version. In this tutorial, we’ll be using CentOS Linux as the operating system. Make sure to have enough free space on both servers to hold the database dump file and the imported database.

Tutorial

The following steps below will guide you to transfer your MySQL databases between two servers using the SCP method.

Step 1 - Perform MySQL Dump

Before transferring the database to the new server, we first need to perform the mysqldump command.

You can dump all your MySQL databases to a single file using this:

mysqldump -u root –p –all-databases > [dump_file.sql]

or if you want to dump a single database, you can use:

mysqldump -u root –p [database_name] > [dump_file.sql]
  • [database_name]: The name of the database you want to take backup.
  • [dump_file.sql]: The name of the dump file you want to generate.

If the database you’re transferring is running or in use, you can stop the mysql using the systemctl command as shown:

systemctl stop mysql

After the dump is performed, you can now transfer the database to the new server.

Step 2 – Transfer the MySQL Database Dump File to the New Server

In this step, we are using the SCP command to transfer the database dump file to the new server.

scp [database name].sql [username]@[servername]:[database path]

Step 3 - Import the Database

Once the MySQL dump file has been transferred to the new server, you can import the database into MySQL:

mysql -u root -p [name of new database] < /path/to/newdatabase.sql

Step 4 – Verify the Database Imported

Once the import is completed, you can verify the databases on both servers on the mysql shell using these commands:

mysql -u user -p
show databases;

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Renz Ladroma
Philippines Philippines
From the beautiful islands of the Philippines. I like words, queries and scripts.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Jay Bardeleben6-Dec-18 11:31
professionalJay Bardeleben6-Dec-18 11:31 
QuestionGood foundations, one suggestion Pin
Member 139108636-Dec-18 9:37
MemberMember 139108636-Dec-18 9:37 
Praisenice article Pin
brown smithdd4-Dec-18 9:51
Memberbrown smithdd4-Dec-18 9:51 
GeneralRe: nice article Pin
Renz Ladroma4-Dec-18 16:47
MemberRenz Ladroma4-Dec-18 16:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.