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_0900_ai_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_0900_ai_ci in MySQL 8.0.
Step 4. Import the dump
mysql -u root -p newdb < dump.sql
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:
- Configure the new server as a slave.
- Wait for synchronization.
- Switch master → slave without downtime.
Checklist before switching
Unit | What to check |
Versions | MySQL/MariaDB compatibility |
Encoding | Make sure utf8mb4 and collations are correct |
sql_mode | Compare sql_mode |
Rights | Check GRANT |
Plugins | default_authentication_plugin |
Replication | Check lag |
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.
