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.
