How to migrate databases

Quick answer: For MySQL/MariaDB use mysqldump (with --single-transaction for InnoDB) and import on the target; for PostgreSQL use pg_dump/pg_restore. Compress dumps for transfer, verify row counts after import, and for big or busy databases do a full copy early plus a final delta inside a short write freeze.

Overview

Databases are the part of a migration where "almost identical" is not good enough — a half-imported table or lost day of rows hurts more than any config quirk. The tools are mature; what needs planning is consistency (no writes mid-dump) and size (transfer and import time for large datasets).

Before you start

• Database credentials on both servers and matching (or newer) engine versions on the target.
• Enough free disk on both sides for the dump file — check with df -h.
• An idea of database size: SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024) AS mb FROM information_schema.tables GROUP BY table_schema;

Step-by-step guide

1. Dump (MySQL/MariaDB):

   mysqldump --single-transaction --routines --triggers dbname | gzip > dbname.sql.gz

2. Transfer: rsync -a dbname.sql.gz user@NEW_IP:/root/
3. Import on the target:

   gunzip -c dbname.sql.gz | mysql -u user -p dbname

4. PostgreSQL equivalent: pg_dump -Fc dbname > dbname.dump then pg_restore -d dbname dbname.dump.
5. Verify: compare table counts and a few row counts on both sides; test the application against the new database.
6. For the cutover: repeat the dump/import during the write freeze so the final state moves — for very large databases consider dumping only changed tables.

Common issues

• Dumping without --single-transaction: on a live InnoDB database this risks an inconsistent snapshot — always include it.
• Charset surprises: keep utf8mb4 end-to-end; a latin1 detour corrupts emoji and Cyrillic text.
• Import much slower than dump: normal — indexes rebuild on import. For huge datasets import inside screen/tmux so an SSH drop does not kill it.
• Users and grants: dumps move data, not database users — re-create users and GRANTs on the target.

When to contact support

Databases in the hundreds of GB, replication setups, or minimal-freeze requirements are exactly the cases to hand to a free assisted migration — describe sizes and your tolerance window in the ticket.

Frequently asked questions

Why use --single-transaction with mysqldump?

It takes a consistent InnoDB snapshot while the application keeps writing, so the dump is not a mix of old and new rows. Without it a live-site dump can be silently inconsistent.

How do I move a database that is hundreds of GB?

Do the full dump and import early, days before cutover, then move only the delta during the freeze: changed tables, or a fresh dump if the write volume is small enough.

Why is importing so much slower than dumping?

The server rebuilds indexes and applies constraints for every row on import. It is normal; run big imports inside screen or tmux so a dropped SSH session cannot interrupt them.

Related articles

• How to migrate from another VPS provider
• How to migrate WooCommerce without losing orders
• How to avoid downtime during migration
• How to restore a website from backup

Ready to move? Order a VPS at Cloud2Y →