Migrating MySQL to a new host: dump, import, cut over

Moving a MySQL database to a new host is four jobs: take an inventory of what you are moving, copy the data with a logical dump, recreate what a dump does not carry (users, grants, sometimes definers and time zones), then cut the application over during a short window in which nothing writes to the old server. For databases up to a few gigabytes, the whole move fits in a planned ten to thirty minutes of downtime, and the commands are ordinary mysqldump and mysql. The risk is not in the copy - it is in the things you did not notice until the application was pointed at the new server. This guide is ordered so you notice them first.

The target throughout is MySQL 8.4 LTS. Sources can be MySQL 5.7, 8.0, 8.4, or a server from another family; the differences are covered where they matter.

Take an inventory first#

Run these on the old server before planning anything. They answer the questions that decide how the move goes.

-- Size per database: decides dump method and downtimeSELECT table_schema,       ROUND(SUM(data_length + index_length) / 1024 / 1024) AS mbFROM information_schema.tablesGROUP BY table_schema ORDER BY mb DESC;-- Anything not InnoDB: MyISAM is not covered by --single-transactionSELECT table_schema, table_name, engine FROM information_schema.tablesWHERE engine <> 'InnoDB' AND table_schema NOT IN  ('mysql', 'sys', 'information_schema', 'performance_schema');-- Stored code and its definersSELECT routine_schema, routine_name, routine_type, definer FROM information_schema.routinesWHERE routine_schema = 'appdb';SELECT trigger_name, definer FROM information_schema.triggers WHERE trigger_schema = 'appdb';SELECT table_name, definer FROM information_schema.views WHERE table_schema = 'appdb';SELECT event_name, definer, status FROM information_schema.events WHERE event_schema = 'appdb';-- Version, modes and settings that change behaviourSELECT @@version, @@sql_mode, @@lower_case_table_names, @@time_zone,       @@character_set_server, @@collation_server;

Write down: total size, any non-InnoDB tables (convert them first with ALTER TABLE t ENGINE=InnoDB, or accept a locked dump), every definer that is not the application user, whether events exist and are enabled, and the source's version and sql_mode. Also list every client that connects - not just the main application, but cron jobs, workers, reporting tools, a forgotten admin panel. SELECT user, host, COUNT(*) FROM information_schema.processlist GROUP BY user, host over a day catches most of them; every one needs its connection settings changed at cut-over.

Version and compatibility checks#

A logical dump loads into a newer version far more reliably than a physical copy, and it skips version hops: a 5.7 dump can be loaded directly into 8.4, whereas an in-place upgrade from 5.7 must go through 8.0 first. The usual issues from 5.7:

• Reserved words. MySQL 8 reserved words such as RANK, GROUPS, ROWS, LEAD, LAG, SYSTEM, CUME_DIST and MEMBER. A column named rank loads fine (the dump quotes identifiers) but the application's unquoted queries break. Search the code.
• Zero dates. 0000-00-00 values in DATE and DATETIME columns are rejected under the default 8.x sql_mode. Fix them to NULL on the source first, or load with a session sql_mode that allows them and fix afterwards.
• `GROUP BY` queries. ONLY_FULL_GROUP_BY has been on by default since 5.7, but many old applications switched it off. If the source's sql_mode lacks it, the application's queries may fail on the new server until fixed or until the session mode is set the same way.

Moving from a server of another family, such as an older fork, adds collation names the target does not know (Unknown collation errors), types that MySQL lacks, and syntax for features like sequences or system-versioned tables. Check the dump on a scratch MySQL 8.4 server before planning the real move. MySQL 8.4 LTS: what changed lists the changes from 8.0, and MySQL Shell's util.checkForServerUpgrade() reports them for a specific server.

Dumping the data#

For most databases, one command on a machine that can reach the old server:

$ mysqldump -h old-host -P 3306 -u root -p \    --single-transaction --routines --events --triggers \    --set-gtid-purged=OFF --no-tablespaces --hex-blob \    appdb | zstd -T0 > appdb.sql.zst

Note what is not there: --databases. Without it, the dump contains no CREATE DATABASE or USE statement, so you can load it into a database with a different name. That matters on a hosted server where the database was created for you with its own name. With --databases appdb, the dump would create and switch to appdb regardless.

--set-gtid-purged=OFF and --no-tablespaces stop the two errors that most often break a load by a non-superuser. --hex-blob makes binary columns survive any text handling on the way. Each flag is explained in mysqldump backup and restore.

For databases large enough that a single-threaded dump and load does not fit your window - roughly beyond 20 GB, depending on indexes - MySQL Shell's util.dumpSchemas() and util.loadDump() work in parallel and can strip definers and fix common compatibility problems as they go. loadDump needs local_infile enabled on the target; check that before relying on it.

Users, grants and definers#

A single-database dump carries tables, data, views, routines, triggers and events. It does not carry accounts. Recreate them on the new server rather than dumping the mysql system schema, which is tied to the source version and is not something to load into an 8.4 server.

-- On the old server: print each account and its grantsSHOW CREATE USER 'app'@'%';SHOW GRANTS FOR 'app'@'%';

SHOW CREATE USER prints the account with its password hash, so the account can be recreated with the same password without knowing it - with one exception. Accounts on mysql_native_password carry that plugin's hash, and on MySQL 8.4 the plugin is disabled by default, so the recreated account cannot log in. Set a new password with the default caching_sha2_password instead, which is also a good moment to rotate credentials that may have been sitting in old config files for years. MySQL users and privileges covers recreating accounts and roles properly.

Definers are the other half. Every view, routine, trigger and event names the account that created it, and loading one whose definer is someone other than you needs SET_ANY_DEFINER on 8.4; if the account does not exist on the new server at all, the object also fails when used. The simplest path when loading as an ordinary application user is to strip the definers, which makes you the definer of everything:

$ zstd -dc appdb.sql.zst \  | sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' \  | mysql -h new-host -P 30412 -u app -p appdb_new

On RE:NODE, a MySQL server starts with an application database, an application user for it, and a root password, all generated. Load into that database name, point the application at that user, and use root only if the dump needs privileges the application user does not have.

Loading and verifying#

Load into the new server, then prove it worked before any client points at it.

$ zstd -dc appdb.sql.zst | mysql -h new-host -P 30412 -u root -p appdb_new

Check it with numbers, not by eye. information_schema.tables.table_rows is an estimate for InnoDB and differs between servers even when the data is identical; count exactly:

-- Generate exact counts for every table; run the output on both servers and diffSELECT CONCAT('SELECT ''', table_name, ''' AS t, COUNT(*) AS n FROM `',              table_name, '` UNION ALL') AS qFROM information_schema.tablesWHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';

Remove the trailing UNION ALL from the last line, run the query on both sides, and compare. Then compare the things counts miss: the latest row in your busiest tables (SELECT MAX(id), MAX(updated_at) FROM orders), a few known records with accented text to catch character set damage (see utf8mb4 and collations), the presence of routines and events (SHOW PROCEDURE STATUS WHERE Db = DATABASE(), SHOW EVENTS), and that AUTO_INCREMENT values carried over (SHOW CREATE TABLE includes them).

Finally, run the application against the new server - a staging copy of the app with its connection settings changed - and use it. Log in, create something, run the scheduled jobs by hand. This is the step that finds reserved words, sql_mode differences and missing grants, while the old server is still the live one.

Settings that do not travel#

A dump copies data, not server behaviour. These are the differences that make an application act strangely after a move that "worked":

• Time zones. If the application uses named zones (CONVERT_TZ(ts, 'UTC', 'Europe/Berlin') or SET time_zone = 'Europe/Berlin'), the new server needs its time zone tables loaded, or CONVERT_TZ returns NULL and SET time_zone fails with Unknown or incorrect time zone. Test with SELECT CONVERT_TZ('2026-01-01 00:00', 'UTC', 'Europe/Berlin');. Numeric offsets like '+00:00' always work.
• `lower_case_table_names`. A database from a Windows or macOS server, where table names are case-insensitive, can contain queries that refer to Orders and orders interchangeably. On Linux the default is case-sensitive, and the variable cannot be changed after a server is initialised. Fix the queries, not the server.
• `sql_mode`. Compare @@GLOBAL.sql_mode on both. If the old server ran a looser mode, either fix the application or have it set the old mode for its session at connect time, as a stopgap.
• The event scheduler. Events are copied, but they only run if event_scheduler is ON on the new server (the default in 8.x). Check that events you expect to run, do - and that ones you disabled on the old server are disabled on the new one.
• Distance. If the database moves further from the application, every query pays the extra round trip. An application making forty queries per page notices 20 ms of added latency as most of a second. RE:NODE servers are in Germany; keep the application close to the database.

Cutting over#

For an ordinary application the reliable cut-over is a short planned window:

1. Lower the timeout on anything cached. If the application finds the database through a DNS name you control, lower its TTL a day ahead so the change spreads quickly.
2. Stop writes to the old server. Put the application in maintenance mode and stop workers and cron jobs. If you hold root on the old server, SET GLOBAL super_read_only = ON makes certain nothing else writes either - including the forgotten client you did not list.
3. Take the final dump and load it into the new server, replacing the rehearsal copy. Dropping and recreating the target database first avoids mixing in tables from the rehearsal.
4. Verify with the same counts and checks as before. This is fast now, because you scripted it.
5. Change every client's connection settings - host, port, user, password, database name - and start the application, then workers and jobs.
6. Watch the application's errors and the new server's connections for the first hour.

Keep the old server, read-only, for at least a few days. If something turns up, you can compare against it; if something goes badly wrong in the first hour, switching back is a configuration change rather than a restore. For larger databases where the dump and load would not fit a short window, the alternative is to load an initial copy, make the new server a replica of the old one with CHANGE REPLICATION SOURCE TO until it catches up, then cut over in seconds. That needs binary logging and replication privileges on the source, network access between the two, and control of both servers' configuration - worth it for a large busy database, unnecessary for most. Migrations without downtime covers the schema-change side of keeping an application up, and moving a WordPress site specifically is in migrating WordPress to a new host.

FAQ#

How long does it take to migrate a MySQL database?

The copy takes roughly as long as dumping plus loading, and loading is the slow half because every index is rebuilt. A database of 1 GB usually moves in a few minutes; 20 GB can take an hour or more single-threaded. Rehearse it once with real data and time it - that number, plus verification, is your downtime.

Can I migrate without any downtime?

Close to it, with replication: load a copy, replicate changes from the old server until the new one is current, then switch clients over. It needs privileges and configuration access on both servers. For most small applications a planned ten-minute window is simpler and safer.

Do I need to copy the mysql system database?

No, and you should not load one from another version into MySQL 8.4. Recreate the accounts you need with SHOW CREATE USER and SHOW GRANTS output, and reset passwords for any account that used mysql_native_password.

Why does my application say the user does not exist after migrating?

Either the account was not recreated, or its host pattern does not match the application's new address, or it uses mysql_native_password, which is disabled by default on 8.4. If the error mentions a definer instead, a view or routine names an account that does not exist on the new server - strip or recreate the definer.

Can I use phpMyAdmin to move a database?

For small databases, yes: export as SQL from the old server and import on the new one. It runs inside a web request, so large databases hit upload and time limits. phpMyAdmin import and export covers the limits and settings; beyond a few hundred megabytes, use mysqldump and the mysql client.