The Large Database Server Transfer Guide

Why size changes the approach

A database transfer that fits comfortably in an hour-long maintenance window at 5GB behaves completely differently at 500GB or 5TB. The methods that work fine for small databases — a `mysqldump` piped straight into the new server, or a simple file copy over SFTP — become impractical once transfer time is measured in many hours rather than minutes. A large database server transfer needs a plan built around three constraints that don’t matter much at small scale: available network bandwidth between source and destination, acceptable downtime for the application, and the time it takes to rebuild indexes and warm caches on the new server once the data has landed. Underestimating any one of these is the most common reason large migrations run long or get abandoned mid-transfer.

Logical dumps vs physical copies

A logical dump (`mysqldump`, `pg_dump`) exports data as SQL statements or a portable archive format, which is flexible and version-independent but slow to both generate and re-import at scale, because the destination has to re-execute every insert and rebuild every index from scratch. A physical copy — moving the actual data files, or using a tool like Percona XtraBackup for MySQL or `pg_basebackup` for PostgreSQL — copies the storage engine’s on-disk representation directly, which is dramatically faster for large databases because there’s no re-parsing or re-indexing involved on restore. The tradeoff is that physical copies generally require matching major versions and often matching architecture between source and destination, while logical dumps can cross version boundaries more safely. For a large database server transfer where source and destination run the same database version, a physical/binary method is almost always the better choice on time grounds alone.

Using replication to avoid a long outage

The single biggest improvement over a basic copy-then-cutover approach is setting up native replication (MySQL replication, PostgreSQL streaming replication, or equivalent) from the old server to the new one before the transfer is “done.” You take an initial physical snapshot to seed the new server, then let replication catch up on everything written since the snapshot was taken — which for a large database server transfer might be gigabytes of changes that accumulated during the many hours the initial copy took. Once replication lag reaches zero, you can schedule the actual cutover for a short window: stop writes on the old server, confirm the new server has caught up completely, repoint the application’s connection string, and resume traffic. This turns a multi-hour outage into a few minutes of downtime, which is the difference that matters most to stakeholders and SLAs.

Moving the data over the network

For the initial bulk copy, raw network throughput is often the real bottleneck, not disk I/O on either end. Compressing the data stream in transit (piping through `gzip` or using a tool with built-in compression) trades CPU time for reduced network transfer time, which is usually a good trade when bandwidth is the constraint and both servers have CPU headroom to spare. Tools like `rsync` are useful for the file-level physical-copy approach because they support resuming an interrupted transfer without starting over — a meaningful advantage when a multi-terabyte transfer over an unreliable link gets cut off partway through. If source and destination are in the same data center or cloud region, a direct high-bandwidth internal network path will always beat routing the transfer over the public internet, so check whether your hosting provider offers a private network option before defaulting to a public-internet copy.

Chunking and parallelism

Splitting a large database server transfer into parallel streams — by table, by schema, or by primary-key range — can significantly reduce wall-clock time versus a single serial dump, provided the network link and both servers’ disks can actually sustain multiple concurrent streams without contention. This matters most for logical dumps, where tools like `mydumper`/`myloader` (for MySQL) or `pg_dump`’s parallel jobs option (for PostgreSQL) can dump and restore multiple tables simultaneously instead of processing them one at a time. It matters less for physical copies, which are already close to sequential disk-throughput limits regardless of parallelism. Test parallelism on a smaller subset first — too many concurrent streams can just as easily saturate a network link and slow everything down as speed it up.

Verifying the transfer

Never treat a large database server transfer as complete until you’ve verified data integrity independently of “the process didn’t throw an error.” Row counts per table are a fast first check but won’t catch subtle corruption or truncated values. Checksumming key tables (MySQL’s `CHECKSUM TABLE`, or a custom hash over a stable column set) on both the source and destination before cutover gives much stronger confidence, and it’s worth spot-checking a handful of records that include edge cases — very long text fields, unusual character encodings, null values in unexpected columns — since these are where lossy conversions tend to surface.

Executing the cutover

Plan the cutover itself as a short, scripted sequence rather than a series of manual steps performed live: put the application into maintenance mode or read-only mode, confirm replication lag is zero, stop the old server’s write path, do a final verification pass, update DNS or connection strings to point at the new server, and bring the application back online. Keep the old server running and replicating (or at least intact and reachable) for a defined rollback window after cutover — a day or two is typical — so that if something unexpected surfaces in production, you have a fast path back rather than needing to reconstruct the old environment from backups under pressure.