The order of steps that keeps a site up
Migrating a database is not "export-then-import" — it is a sequence with checkpoints. Change the order and the site either goes down for the whole migration or keeps writing to a database that is about to disappear.
- Put the site into read-only mode or warn users about maintenance — records written during the export will not end up in the dump.
- Take a dump on the old server with
mysqldumpand a consistency flag. - Transfer the file to the new server and import it into an empty database.
- Check the database on the new server through the
hostsfile, without touching the domain's DNS. - Only after a successful check switch the domain and update the site configuration.
Export: mysqldump with the right flags
Database under 1 GB
mysqldump -u user -p --single-transaction --routines --triggers database_name > dump.sql--single-transaction takes a snapshot without locking tables (important for InnoDB), --routines and --triggers add stored procedures and triggers that a plain dump skips.
Database over 5 GB
mysqldump -u user -p --single-transaction --quick --compress database_name | gzip > dump.sql.gz--quick reads rows one at a time instead of loading the whole table into memory, --compress compresses traffic between the client and the MySQL server, and the outer gzip shrinks the file itself by 5-10x.
Only specific tables
mysqldump -u user -p database_name table1 table2 > partial.sqlTransferring the dump to the new server
Over scp
scp dump.sql.gz root@new-server-ip:/root/Directly over SSH, no file on disk
mysqldump -u user -p --single-transaction database_name | gzip | ssh root@new-server-ip "gunzip | mysql -u user -p database_name"This pipeline saves disk space on both servers and time: there is no need to wait for the file to be fully written before the transfer can start.
Import on the new server
gunzip < dump.sql.gz | mysql -u user -p database_nameComparing transfer methods
| Method | Practical limit | Site downtime | When to use |
|---|---|---|---|
| phpMyAdmin (file export/import) | up to 50-100 MB | minutes | small sites, no SSH access |
| mysqldump to a file + scp | up to a few GB | minutes to hours | SSH available, moderate database size |
| Direct pipe over SSH | limited only by link speed | minutes to hours | large databases, little free disk space |
Checking the new server before switching DNS
Before changing DNS, check the site on the new IP by remapping the domain only on your own computer.
echo "203.0.113.10 example.com www.example.com" | sudo tee -a /etc/hostsAfter that, open the site in a browser as usual — it will request the domain but get a response from the new server. You can also check without editing hosts, by passing the Host header directly:
curl -H "Host: example.com" http://203.0.113.10/Do not forget to remove the line from /etc/hosts after the check, or you will keep seeing the old version even after the real DNS switch.
Common import errors
| Message | Cause and fix |
|---|---|
| ERROR 1044 (42000): Access denied for user | The MySQL user has no rights on the target database — grant it ALL privileges on that database |
| ERROR 2006 (HY000): MySQL server has gone away | The dump contains a row larger than max_allowed_packet — raise the setting in my.cnf on the new server |
| ERROR 1046 (3D000): No database selected | The dump has no USE database_name — create the database first and import with the name specified explicitly |
| Unknown collation: 'utf8mb4_0900_ai_ci' | The MySQL version on the new server is older — either upgrade the server or replace the collation name in the dump with a supported one |
Rollback if something goes wrong
- Do not delete the old database and old server until the new one has run for at least a full day under real load.
- If the site behaves unstably after the DNS switch, restore the old A records — they take effect as the TTL expires.
- Fix the problem on the new server at a calm pace, then repeat the migration.
When this approach does not fit
For databases over 20-30 GB, a plain mysqldump takes hours and keeps the site down the whole time. In that case use master-slave replication for the initial sync without stopping the site, and reserve mysqldump only for a final check of a small number of changed rows.
Checking the result
- Compare the table count:
SHOW TABLES;on the old and new server must return the same list. - Compare row counts in key tables:
SELECT COUNT(*) FROM orders;and the same for other important tables. - Run
mysqlcheck -u user -p --all-databaseson the new server — it checks table integrity. - Open the site through the hosts override and check key pages: login, checkout, the admin panel.
A detailed checklist for checking a site after migration is in the article post-migration check, and a safe rollback procedure is in migration rollback plan.
Frequently asked questions
Do you have to fully stop the site during a database transfer, or is read-only mode enough?
Read-only mode is usually enough: the key is that no new records get written while the dump is being taken, or they will miss the export and be lost after the switch. A full stop is rarely necessary — only when the site actively writes to nearly every table and read-only alone cannot prevent that.
What should you do if taking the dump takes too long and disrupts the live database?
The consistent-snapshot flag avoids locking tables during the dump, so the export usually does not block concurrent queries, and a long runtime more often signals a large data volume. When the database is so large that even this dump stretches into hours, switching to replication for the initial sync makes more sense, keeping the dump only for a final comparison.
Can you transfer only part of the database's tables instead of the whole database?
Yes, mysqldump can export individual tables by name without touching the rest of the database, handy when moving a single site module or restoring specific data after a mistake. Relationships between tables are worth checking separately: if the moved table references data that does not yet exist on the new server, those foreign keys will need adding after the import.
What should you do if the row counts in the new server's tables don't match the old server's after the transfer?
Start by comparing the table list and the row counts of key tables on both servers — a mismatch usually means some data was written to the old database after the dump was taken, while the site still accepted requests. If the old server is still running, take a fresh dump and repeat the import, without deleting the old database.