Database Migration and Data Import/Export

When you move a database to another server or upgrade to a new version, the safest approach is a logical migration: dump the data to SQL or CSV, then load it into the fresh database. It travels well across versions and machines, and every step is visible and verifiable. The examples below use MySQL/MariaDB and PostgreSQL on Ubuntu/Debian.

Logical Dump

Use mysqldump for MySQL/MariaDB and pgdump for PostgreSQL. Always state the character set explicitly so nothing gets mangled:

# MySQL: --single-transaction gives a consistent snapshot without locking (InnoDB)
mysqldump --single-transaction --default-character-set=utf8mb4 \
  -u root -p mydb | gzip > mydb.sql.gz

# PostgreSQL: -Fc is the custom compressed format, restorable in parallel
pg_dump -Fc -U postgres mydb > mydb.dump

Load into the new database:

gunzip < mydb.sql.gz | mysql -u root -p mydb_new
pg_restore -j 4 -U postgres -d mydb_new mydb.dump

Cross-Version Gotchas

• Character set: Older databases often use utf8 (which is really only 3 bytes). Standardize on utf8mb4, or you'll silently drop emoji and some CJK text. Create the target with CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4unicodeci;.
• Timezone: Confirm both machines agree — SELECT @@global.timezone; — or store everything in UTC. TIMESTAMP shifts with the session timezone; DATETIME does not.
• Storage engine: Make sure the target stays on InnoDB and doesn't fall back to MyISAM (no transactions, not crash-safe).
• SQL mode: Newer MySQL ships with strict mode on. Legacy zero-dates like 0000-00-00 and over-length values will now error out, so adjust sqlmode if you must load old data.

Large Tables: Batching and Direct Pipes

Big tables don't need to touch disk. Pipe straight from source to target to save space and one round of IO:

mysqldump --single-transaction --default-character-set=utf8mb4 mydb bigtable \
  | ssh user@newhost "mysql -u root -pPASS mydb_new"

For truly huge tables, dump one table at a time or split by primary-key ranges (--where="id BETWEEN 1 AND 1000000") and run several passes to ease memory and network pressure.

CSV Import/Export

For bulk single-table data, CSV is fastest. PostgreSQL's COPY and MySQL's LOAD DATA use a server-side bulk path that far outruns row-by-row INSERT:

-- MySQL (requires local_infile enabled)
LOAD DATA LOCAL INFILE 'users.csv' INTO TABLE users
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n' IGNORE 1 LINES;

-- PostgreSQL
\copy users FROM 'users.csv' WITH (FORMAT csv, HEADER true);

Reconcile Before and After

Never eyeball it — verify. Compare row counts table by table:

SELECT COUNT(*) FROM users;

For critical tables, also check a checksum such as SUM() over a money column. Matching row counts are necessary but not sufficient, so sample-compare a few core tables at the business level.

Downtime Window and Read-Only Cutover

To lose zero data, writes that land after the dump instant must not be dropped:

• Announce a maintenance / read-only window ahead of time and pick an off-peak hour.
• Flip the old database to read-only (MySQL SET GLOBAL readonly = ON;) to block new writes.
• Dump → load → reconcile row counts.
• Point the app at the new database, confirm reads and writes work, then reopen traffic.

> Risk reminders: rehearse the full run against the new database first; before piping mysqldump straight into a production target, triple-check the target database name so you don't overwrite live data; and keep credentials out of the command line (they land in shell history) — use /.my.cnf or /.pgpass instead.

Summary

A logical migration is three steps: dump, load, reconcile. The real traps are declaring utf8mb4, aligning timezones, and keeping the storage engine. Pipe or batch large tables, bulk-load CSV with LOAD DATA/COPY, freeze the old database behind a read-only window on cutover, and confirm parity with row counts and checksums before you reopen traffic.