A MySQL-to-MySQL migration is moving a database from one server (or version, or topology) to another. The three paths are mysqldump + mysql import (small, simple, slow), logical replication (medium, online, complex), or a managed migration tool (large, online, expensive). The team that runs a single dev-to-staging move uses mysqldump; the team that runs a continuous multi-region sync uses logical replication.
Table of contents
- Path 1: mysqldump + import (the dev/staging move)
- Path 2: logical replication (the online move)
- Path 3: managed migration tool
- Schema-and-data-only vs. full migration
- How this fits the rest of the stack
- FAQ
Path 1: mysqldump + import (the dev/staging move)
On the source server:
mysqldump -u root -p --single-transaction --routines --triggers myapp > myapp.sql
--single-transaction is the key flag - it wraps the dump in a transaction so the dump is consistent (no half-written tables). --routines and --triggers include stored procedures and triggers; they are off by default.
Copy the file to the target, then import:
mysql -u root -p myapp < myapp.sql
The team that runs this for a 1 GB dev database has a 30-second window. The team that runs it for a 100 GB prod database does not - this path is offline and locks the source for the duration of the dump.
Path 2: logical replication (the online move)
MySQL’s built-in replication streams binlog events from a primary to one or more replicas. The team that uses this for a migration sets up a replica on the new server, lets it catch up, then promotes it to primary and points the application at it.
-- On the source: enable binlog and set a server-id
-- In my.cnf:
-- [mysqld]
-- server-id = 1
-- log_bin = /var/log/mysql/mysql-bin.log
Then on the replica: CHANGE MASTER TO MASTER_HOST='source', MASTER_USER='repl', MASTER_PASSWORD='...', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4; followed by START SLAVE;.
The catch-up time depends on write volume and replica capacity. The team that has a busy prod database plans the cutover for low-traffic hours and verifies Seconds_Behind_Master is 0 before promoting.
Path 3: managed migration tool
For cloud-to-cloud moves (AWS RDS to Aurora, DigitalOcean to AWS, etc.) the path is a managed tool. AWS DMS, Google Database Migration Service, and the in-cloud migration wizards in DigitalOcean, Linode, and others all support MySQL-to-MySQL.
These tools handle the binlog stream, the cutover coordination, and the rollback. The team that uses one for a one-time move accepts the cost in exchange for not hand-rolling replication.
Schema-and-data-only vs. full migration
Sometimes the move is just the data, not the schema. The team that already has the target schema (because they are promoting dev to staging, or the schema is the same) uses:
mysqldump -u root -p --no-create-info --single-transaction myapp > data.sql
--no-create-info skips the CREATE TABLE statements. The team that uses this for a content-only seed is the team that already ran the migrations on the target.
The reverse - schema only, no data - is mysqldump --no-data. The team that ships a migration-only path (Django migrations, Rails migrations, etc.) does not need the data portion.
FAQ
Do I need to lock the source during the dump?
Only if you do not use --single-transaction. With that flag, mysqldump wraps the whole dump in a transaction so the source is consistent and reads are not blocked. Without it, the dump is row-by-row and can be inconsistent if writes happen mid-dump.
How long does the migration take?
For mysqldump + import: roughly the file size on the wire plus the import time. For 1 GB on a fast network, expect a minute. For 100 GB, expect hours. For replication-based moves, the catch-up time depends on write throughput and replica capacity.
Can I move between MySQL versions?
Yes - mysqldump output is forward-compatible. The team that moves from 5.7 to 8.0 uses mysqldump + import. The reverse (8.0 to 5.7) is not supported.
What about character set issues?
Make sure both servers use the same default character set (utf8mb4 is the modern default). If the source is latin1 and the target is utf8mb4, the import will silently convert. The team that finds ????? in their data after a migration has a character set mismatch.
Should I run the migration during low traffic?
For mysqldump + import, yes - the source is read-locked for the duration of the dump. For replication-based moves, the cutover is the only brief lock; the rest of the migration is online.
How this fits the rest of the stack
For a sense of what the full project costs before it commits, the RunxBuild hosting calculator shows the line items together. The API, the database, the storage, the worker, the bandwidth - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers.
Useful related references: