MySQL database migration: a step-by-step and secure method

Introduction

Migrating a MySQL database is a critical operation that affects data integrity, application stability, and rollback capabilities. We will discuss how to use mysqldump, migrate users, preserve data integrity, and take into account the nuances of different MySQL versions.

When is migration necessary?

• Moving to a new hosting provider or server.
• Updating the MySQL version.
• Configuring replication or load balancing.
• Consolidating databases.

Risks and how to minimize them

• Version incompatibility: differences in sql_mode, default_authentication_plugin, encodings (utf8mb4_unicode_ci vs utf8mb4_unicode_520_ci).
• Loss of privileges: users are stored separately and must be transferred manually.
• Application downtime when migrating without replication.
• Inconsistent dump: –single-transaction only works with InnoDB; FLUSH TABLES WITH READ LOCK is required for MyISAM.

Step-by-step migration plan

Step 1. Create a dump

Main keyword (1): MySQL database migration.

Create a dump with logic preservation:

				
					mysqldump -u root -p --single-transaction --routines --triggers --events olddb > dump.sql
				
			

• –single-transaction reduces locking (works only for InnoDB).
• –routines, –triggers, –events preserve procedures, triggers, and event scheduler.

Step 2. Transfer the dump

Transfer via SCP or rsync over a secure channel. Check the file integrity:

				
					sha256sum dump.sql
				
			

Step 3. Creating the database

On the new server: 

				
					mysql -u root -p -e "CREATE DATABASE newdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
				
			

Note: utf8mb4_unicode_ci may differ from utf8mb4_unicode_520_ci in MySQL 8.0.

Step 4. Import the dump 

				
					mysql -u root -p newdb 
				
			

Step 5. Transfer users and permissions

• Execute SHOW GRANTS FOR ‘user’@’host’; and transfer CREATE USER/GRANT.
• In MySQL 8.0, you can use mysqlpump –users.
• Consider default_authentication_plugin (in 8.0, this is caching_sha2_password).

Step 6. Integrity check

• Compare the number of rows in the tables.
• Check the checksums (pt-table-checksum).
• Test key SQL queries.

Step 7. Minimize downtime

For critical systems, use replication:

1. Configure the new server as a slave.
2. Wait for synchronization.
3. Switch master → slave without downtime.

Checklist before switching

Rollback

• Create a backup copy of the old server.
• To roll back, you can bring up the old server as a slave and switch back.

Conclusion

Migrating a MySQL database is safe if you take into account the versions, use dumps, and check integrity. Replication will help avoid downtime, and backups will allow you to quickly roll back. Migrating a MySQL database requires a careful approach to permissions, collations, and settings.

Table of Contents