How to Back Up and Restore MySQL, PostgreSQL, and MongoDB Databases

To back up a database you export its data with the native dump tool — mysqldump for MySQL, pg_dump for PostgreSQL, and mongodump for MongoDB — and restore it later with mysql, pg_restore, or mongorestore. This guide covers logical and physical backups, point-in-time recovery, compression, scheduled automation, off-site copies in Amazon S3, retention, and how to prove your backups actually restore. Every command below is current for 2026 (MySQL 8.x / 8.4 LTS, PostgreSQL 16/17, MongoDB 7.x/8.x).

At MicroPyramid we have run production database operations for startups and enterprises for 12+ years across 50+ projects, so the patterns here reflect what we actually deploy — not just textbook defaults.

Logical vs physical backups

Before picking a command, know the two backup styles — most teams use both.

Type	What it captures	Tools	Best for
Logical	SQL / BSON statements that recreate schema + data	mysqldump, pg_dump, mongodump	Portability, small-to-medium DBs, cross-version restores, migrations
Physical	Raw on-disk data files + WAL / redo logs	Percona XtraBackup, pg_basebackup, volume snapshots	Very large DBs, fast restores, point-in-time recovery (PITR)

Logical backups are portable across versions and platforms and easy to compress. Physical backups are faster to take and restore on large datasets and are the foundation for point-in-time recovery.

MySQL backup and restore

mysqldump is MySQL's built-in logical backup tool. For InnoDB tables (the default engine) always add --single-transaction so the dump is a consistent snapshot taken without locking the whole database. Stored programs are not dumped by default, so add --routines, --triggers, and --events when you need them.

# Consistent logical backup of one database (InnoDB)
mysqldump -u USER -p \
  --single-transaction --routines --triggers --events \
  mydb > mydb.sql

# Dump every database (includes the mysql schema: users + grants)
mysqldump -u USER -p --all-databases --single-transaction > all.sql

# Compress on the fly
mysqldump -u USER -p --single-transaction mydb | gzip > mydb.sql.gz

# Restore a plain SQL dump (create the database first if needed)
mysql -u USER -p -e "CREATE DATABASE IF NOT EXISTS mydb"
mysql -u USER -p mydb < mydb.sql

# Restore a gzipped dump
gunzip < mydb.sql.gz | mysql -u USER -p mydb

Security: never put the password on the command line (e.g. -pSecret) — it leaks into shell history and ps output. Use -p to be prompted, or store credentials with mysql_config_editor (--login-path) or a ~/.my.cnf file set to chmod 600.

Faster and physical alternatives:

• mydumper / myloader — multi-threaded logical dump and restore, far faster than single-threaded mysqldump on large databases.
• Percona XtraBackup — open-source physical hot backup for InnoDB; near-instant restores, replica seeding, and PITR via binary logs.
• mysqlpump shipped with MySQL 8.0 but is deprecated — prefer mysqldump, mydumper, or the 8.0 CLONE plugin for physical provisioning.

PostgreSQL backup and restore

PostgreSQL ships two logical tools: pg_dump (one database) and pg_dumpall (the whole cluster, including roles and tablespaces — the global objects pg_dump skips). First choose a dump format:

Format	Flag	Restore with	Notes
Plain SQL	-Fp (default)	psql	Human-readable, no parallelism, no selective restore
Custom	-Fc	pg_restore	Compressed, selective restore — the recommended default
Directory	-Fd	pg_restore -j	Parallel dump and restore for large DBs
Tar	-Ft	pg_restore	Archive-friendly

For anything beyond a tiny database, use the custom or directory format with pg_restore — they give you compression, selective table restore, and parallel jobs.

# Custom-format dump of one database (recommended)
pg_dump -U USER -h HOST -Fc mydb > mydb.dump

# Directory format with 4 parallel workers (fast on big DBs)
pg_dump -U USER -h HOST -Fd -j 4 -f mydb_dir mydb

# Plain SQL, gzip-compressed
pg_dump -U USER -h HOST mydb | gzip > mydb.sql.gz

# Cluster-wide globals: roles, grants, tablespaces
pg_dumpall -U USER -h HOST --globals-only > globals.sql

# Restore a custom / directory dump into a fresh database
createdb -U USER -h HOST mydb
pg_restore -U USER -h HOST -d mydb --no-owner -j 4 mydb.dump

# Restore a plain SQL dump
psql -U USER -h HOST -d mydb < mydb.sql
gunzip < mydb.sql.gz | psql -U USER -h HOST -d mydb

# Load globals first when rebuilding a whole cluster
psql -U USER -h HOST -f globals.sql

Point-in-time recovery (PITR). Production PostgreSQL needs more than nightly dumps. Take a physical base backup with pg_basebackup and continuously archive write-ahead log (WAL) segments; you can then restore the base backup and replay WAL up to any moment — ideal for undoing an accidental DROP TABLE a few minutes ago.

# One-time physical base backup (compressed tar)
pg_basebackup -U REPLUSER -h HOST -D /backups/base -Ft -z -P

# Enable WAL archiving in postgresql.conf, then reload:
#   wal_level = replica
#   archive_mode = on
#   archive_command = 'aws s3 cp %p s3://my-bucket/wal/%f'

MongoDB backup and restore

mongodump writes a binary BSON snapshot and mongorestore reads it back — this is the right tool for full-fidelity disaster recovery because BSON preserves every data type. mongoexport / mongoimport produce JSON or CSV instead, handy for moving a single collection between systems but lossy on types, so don't rely on them for DR. mongodump works against standalone servers and replica sets; for a consistent snapshot of a sharded cluster use Atlas or coordinated filesystem snapshots.

# Full BSON dump of one database, gzip-compressed
mongodump --uri="mongodb://USER:PASS@HOST:27017" \
  --db=mydb --gzip --out=/backups/mongo

# Replica-set point-in-time snapshot via the oplog
mongodump --uri="mongodb://HOST:27017" --db=mydb \
  --oplog --gzip --out=/backups/mongo

# Export one collection to JSON (interchange, not DR)
mongoexport --uri="mongodb://HOST:27017" --db=mydb \
  --collection=orders --out=orders.json

# Restore a gzipped BSON dump
mongorestore --uri="mongodb://USER:PASS@HOST:27017" \
  --gzip /backups/mongo

# Restore a single collection (--drop replaces existing data)
mongorestore --uri="mongodb://HOST:27017" --db=mydb \
  --collection=orders --drop /backups/mongo/mydb/orders.bson.gz

# Import JSON back into a collection
mongoimport --uri="mongodb://HOST:27017" --db=mydb \
  --collection=orders --file=orders.json

MongoDB Atlas (the managed service) provides continuous cloud-side backups with point-in-time restore plus scheduled snapshots, so you rarely run mongodump by hand there. On self-hosted replica sets, dump from a secondary to spare the primary, and use --oplog for a consistent point-in-time snapshot. If you need help operating a cluster, see our MongoDB development services.

Automate backups with cron

A backup you have to remember to run is a backup you don't have. Wrap the dump command in a small shell script that timestamps the file, pushes a copy off-site, and prunes old files, then schedule it with cron.

#!/usr/bin/env bash
# /usr/local/bin/pg_backup.sh — nightly PostgreSQL backup to S3
set -euo pipefail

DB="mydb"
STAMP="$(date +%F_%H%M)"
FILE="/backups/${DB}_${STAMP}.dump"
BUCKET="s3://my-company-backups/postgres"

# 1. Dump (custom format, compressed). Credentials live in ~/.pgpass
pg_dump -U backup -h localhost -Fc "$DB" > "$FILE"

# 2. Copy off-site to S3 with server-side encryption
aws s3 cp "$FILE" "$BUCKET/" --sse aws:kms

# 3. Local retention: keep the last 7 days
find /backups -name "${DB}_*.dump" -mtime +7 -delete

# crontab -e  — run every day at 02:30
30 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

Keep secrets out of the script: use ~/.pgpass for PostgreSQL, ~/.my.cnf / --login-path for MySQL, and an environment-variable connection URI for MongoDB, each at chmod 600. For long-term retention, S3 lifecycle rules can transition old dumps to cheaper storage classes and expire them automatically, while versioning and Object Lock protect against deletion and ransomware. If you run on AWS, our AWS consulting and cloud migration teams set up encrypted, versioned backup buckets with lifecycle policies as standard.

The 3-2-1 backup strategy

The industry-standard rule — and the one we apply on client systems:

• 3 copies of your data (one primary + two backups).
• 2 different storage media or locations.
• 1 copy off-site (a different region or provider).

A modern extension is 3-2-1-1-0: add 1 offline or immutable copy (Object Lock / write-once storage to survive ransomware) and 0 verified errors from regular restore tests.

Practice	Why it matters
Test restores on a schedule	An untested backup is a hope — most failures only surface on restore
Encrypt at rest and in transit	TLS to the database, --sse / KMS on S3, encrypted volumes for local dumps
Automate and monitor	Cron the job and alert on failure or a missed run
Set retention	Daily for ~2 weeks, weekly for a few months, monthly for a year — tune to compliance
Document recovery	Write down the exact restore steps and your RTO / RPO targets

Always test your restores. Periodically spin up a throwaway instance, restore the latest dump, and run a few sanity queries. A backup that has never been restored cannot be trusted.

Frequently Asked Questions

How often should I back up my database?

Match the frequency to how much data you can afford to lose — your recovery point objective (RPO). A common baseline is nightly full logical backups plus continuous WAL or oplog archiving for point-in-time recovery, which caps worst-case loss at seconds to minutes rather than a whole day. High-write systems back up more often.

What is the difference between logical and physical backups?

A logical backup (mysqldump, pg_dump, mongodump) exports statements or BSON that recreate your data and is portable across versions and platforms. A physical backup (Percona XtraBackup, pg_basebackup, volume snapshots) copies the raw data files, restores far faster on large databases, and underpins point-in-time recovery. Many production setups use both.

How do I take a consistent backup without downtime?

Use each engine's online-consistent options: mysqldump --single-transaction for InnoDB, pg_dump (which always runs inside a consistent MVCC snapshot), and mongodump --oplog on a replica set. These produce a coherent snapshot while the database keeps serving traffic, so no maintenance window is needed.

Can I restore a backup to a newer database version?

Logical dumps generally restore to the same or a newer major version, which is why pg_dump / mysqldump / mongodump are the go-to tools for upgrades and migrations. Physical backups are tied to the exact major version and platform. Always restore into a test instance first to catch deprecated syntax.

How should I store backups off-site?

Copy dumps to object storage such as Amazon S3 with aws s3 cp, enable server-side encryption (SSE-KMS), turn on versioning and Object Lock for immutability, and use lifecycle rules to tier or expire old files. Keeping at least one copy in a different region or account satisfies the off-site leg of the 3-2-1 rule.

Why do my backups need to be tested?

Because backups fail silently — a truncated dump, a missing role or grant, an unreadable archive, or a wrong flag usually only shows up when you try to restore during an outage. Schedule regular restore drills into a disposable instance and verify row counts and key queries so recovery is proven, not assumed.

Should I put the database password in the backup command?

No. Passwords on the command line leak into shell history and process listings. Use ~/.my.cnf or --login-path for MySQL, ~/.pgpass for PostgreSQL, and an environment-variable connection URI for MongoDB — each file set to chmod 600.

Need a hand with database operations?

Backups, restores, replication, and zero-downtime upgrades are everyday work for us. Across 12+ years and 50+ projects we have run production database migrations and ongoing maintenance for MySQL, PostgreSQL, and MongoDB. The patterns above are the same ones we deploy for clients — automated, encrypted, off-site, and restore-tested.