How To Move a MySQL Database from one server to another Using SCP.

How To Move a MySQL Database from one server to another Using SCP.

Introduction

The Secure Copy (SCP) is a reliable technique used to move databases between two virtual private servers. This method entails copying files obtained via the SSH shell and only takes a few steps to accomplish. However, transferring files between two servers can be a time-consuming activity especially if you are moving huge amounts of data. This tutorial will show you how to utilize the SCP method to transfer your MySQL databases from one VPS or dedicated server to another. Ready? Let’s get started!

Before You Start

To successfully move files in your MySQL databases you require the following:

  • Two virtual private servers; one that is hosting your MySQL database and another where you want to move the files to.
  • You also need the passwords for both servers.

Step 1 –
Backing The Files Up Using The Mysqldump

The mysqldump is a robust utility that helps you to export your data and database structures to SQL dump files. Before you attempt to transfer your files to the new VPS, back them up on the initial server using this utility’s command. To accomplish a MySQL dump, issue the command below:

mysqldump-uroot-p--opt [database name] > [database name].sql

Note:

  • You should replace the placeholder [database name] with the actual name for your database.
  • In the command above we are utilizing a –single-transaction flag to prevent a database lock as we export the files. The –single-transaction flag starts the transaction before running, instead of locking the database. This permits the mysqldump to read the entire database in its current state at the time of this transaction thereby facilitating a consistent dump.
  • The command will also request your root user credentials (username and password). Make sure you submit these details accurately to initiate the process.

Once the dump is completed, you can proceed with the transfer.

Step 2 –
Securing The Backup File

Your data is a valuable asset to your organization. For this reason, you should not leave the database backup files unprotected as it can unintentionally leak or worse, get hacked by people with malicious intentions. Always secure the backup file at the earliest chance. Here we’ll show you how to encrypt and compress the backup file before transferring them to the target location. First, issue the command below to encrypt and compress the backup file:

$ sudo zip --encrypt dump.zipdb.sql

You will be requested to provide your password. Enter your password to initiate the compression.

Step 3 –
Transfering The Backup File

By now, your dump file is protected and compressed. You can move this file securely over your network to a new virtual server. Execute the following SCP command to accomplish this:

scp /path/to/source-file user@host:/path/to/destination-folder/

Step 4 –
Importing The Database

Now, you have the encrypted backup file in the destination server. Before we extract this file, we must first decrypt it.

unzip -P your-password dump.zip

Once the file is decrypted, you can now issue the command below to import it:

mysql -u root -p newdatabase < /path/to/newdatabase.sql

That is it! You file is securely imported on your new server. You can now do away with the original dump file for security and storage reasons.

Step 5 –
Validating The Imported Data

Now that you have your MySQL database on your new server, it always wise to validate it to establish if the correct data was actually moved. To validate the data, issue the query below on the both the new and the old databases, then compare the results.

SELECT
    TABLE_NAME, 
    TABLE_ROWS 
FROM
    `information_schema`.`tables` 
WHERE
    `table_schema` = 'YOUR_DB_NAME';

This query will give you the number of rows on all the tables to provide information on the amount of data hosted in both databases. Besides, you can check the MIN and MAX columns records in the tables. This will help you determine the validity of the data you moved. In addition, before moving the application itself, you can redirect a single application instance to your new database to determine if all is well.

Conclusion

You have successfully and securely moved your MySQL database from the old server to the new server. This process is simple but caution must be taken to prevent data loss or theft.

Check out these top 3 Best web hosting services

Hostinger
AU$4.67 /mo
Starting price
Visit Hostinger
Rating based on expert review
  • User Friendly
    4.7
  • Support
    4.7
  • Features
    4.8
  • Reliability
    4.8
  • Pricing
    4.7
IONOS
AU$1.56 /mo
Starting price
Visit IONOS
Rating based on expert review
  • User Friendly
    4.5
  • Support
    4.0
  • Features
    4.5
  • Reliability
    4.5
  • Pricing
    4.3
Ultahost
AU$4.53 /mo
Starting price
Visit Ultahost
Rating based on expert review
  • User Friendly
    4.3
  • Support
    4.8
  • Features
    4.5
  • Reliability
    4.0
  • Pricing
    4.8
  • Check the recommendations for the best VPS and get a suitable one.

 

How to use phpMyAdmin to develop a website (without MySQL experience)

Brief description A web developer who is not well versed into coding websites f
2 min read
Idan Cohen
Idan Cohen
Marketing Expert

How to Install MySQL on a Windows Web Server Running Apache

This tutorial will show you how to install the MySQL database on a Windows serve
3 min read
Michael Levanduski
Michael Levanduski
Expert Hosting Writer & Tester

How To Install MySQL on Windows

This how-to article teaches you how to install MySQL on Windows.
4 min read
Avi Ilinsky
Avi Ilinsky
Hosting Expert

How to Move Your WordPress Site from Your Local Web Server to Your Live Site

You can always accelerate the development process by developing your WordPress s
4 min read
Max Ostryzhko
Max Ostryzhko
Senior Web Developer, HostAdvice CTO
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.
Click to go to the top of the page
Go To Top