? Migrating a MySQL database usually takes a few simple steps, but it generally takes longer because the amount of data you are moving can be large.

?

?

?

The following steps will walk you through how to export a MySQL database from the old server, harden it for security; and then copy it and import it to the new server to keep the data intact.

?

Export the MySQL database to a dump file

?

Oracle provides a tool called mysqldump that allows you to easily export the database structure and its data to a dump file in SQL. You can use the following command:

?

1.mysqldump -u root -p --opt [database name] > [database name].sql?

?

However, please note the following:

?

We can use the --single-transaction flag to prevent the database from locking up while exporting data. This enables you to continue updating data on the old database while exporting it to a dump file. Note, however, that data that is updated after the export process has begun will not be imported into the dump file.

?

Be sure to replace [database name] with your actual database name before running this command.

?

Please enter your own username and corresponding password, and make sure that the user has the permissions required to back up the database.

?

Securely Hardening Backup Files

?

In most cases, data is an organization's most important asset. Therefore, we don't want the various backups of the database to be exposed to unprotected servers, as this could result in a wrongful disclosure or even a worse situation such as theft by hackers.

?

So what you can usually try is to compress, encrypt the file, and then delete the original file. On Linux, use the following command to encrypt a compressed file:

?

1.zip --encrypt dump.zip db.sql?

?

You will be prompted for a password before the zipping begins.

?

Transferring backup files

?

So far, we've got an encrypted dump file. Let's transfer it to the new server over the network using the SCP command:

?

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

?

Importing a MySQL dump to a new server

?

With the above step, we have transferred the backup file to the new server, let's decrypt and extract it below:

?

1.unzip -P your-password dump.zip?

?

For storage space and security reasons, remember to delete the corresponding dump file once the file has been imported.

?

You can use the following command to import a file:

?

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

?

Validate imported data on new server

?

Now that we have imported the database on the new server, we need a way to validate that the data actually exists and make sure nothing is missing.

?

I would recommend that you run the following query on both the old and new databases and compare the results obtained.

?

This query counts the number of rows in all the tables to show the amount of data in the old and new databases.

?

1. SELECT ?

2.TABLE_NAME, ?

3.TABLE_ROWS ?

?

4.FROM ?

`

5.information_schema`. `tables` ?

?

6.WHERE ?

`

7.table_schema` = 'YOUR_DB_NAME'; ?

?

Additionally, I recommend that you check the MIN and MAX records for the numeric columns in each table to make sure that the data itself is valid, rather than just looking at the total amount of data (although that's the only value that the query can read). Another option available for testing is to export the database from the new server as a SQL dump file and compare it to the SQL dump file from the old server.

?

Additionally, before an application is migrated, I recommend that you redirect an instance of the application to the new database to confirm that everything is working properly.

?

Another export and import option

?

We've put this option last because we really don't recommend that you use it.

?

This method is very easy to implement because it uses a single command to export, transfer, and import the data from the dump file into the new database all at once.

?

The downside is that if the network link is broken, you need to restart it.

?

As such, we don't think it's recommended, especially in large databases, where it can be very inapplicable.

?

Of course, if you have to try it, you can use the following command:

?

1.mysqldump -u root -pPassword --all-databases | ssh user@new_host.host.com 'cat - | mysql -u root -pPassword'?

?

Important note

?

Please make sure that you have the same official release of MySQL server installed at both the old and new locations. Otherwise, you will need to follow the upgrade instructions on the MySQL website for harmonization (see (/doc/refman/5.7/en/upgrading.html).

?

Make sure you have enough space on the old server to save the dump file and the zip file (there should be space for db_size x 2).

?

Make sure you have enough space on the new server to store the encrypted and decrypted dump files and to be able to import them into the database (there should be space for db_size x 3).

?

If you've ever considered just moving the datadir from one database to another, I'd suggest that you're better off not doing that. Otherwise, you'll be messing with the internal structure of the database, and you'll be setting yourself up for possible problems in the future.

?

In the new server configuration, do not forget to configure important flags such as innodb_log_file_size. This is because if you forget to update the configuration according to the specifications of the new server, it is likely to lead to serious performance issues.

?

In many cases, the initial purpose of upgrading to a new database server is generally to improve query performance. If such upgrades do not result in the desired improvements, then you should consider optimizing SQL queries beyond just upgrading the hardware

In many cases, the original intent of upgrading to a new database server is to improve query performance.