Skip to main content

Database Backup and Migration

Database backups and migrations are high-risk operations: one wrong target can overwrite production data. Use a repeatable workflow: export (consistent dump), compress, transfer, restore into the correct database, and verify. For WordPress, the safest default is a logical dump with mysqldump using --single-transaction (for InnoDB) so you can back up a live site without long table locks.

Quick Summary

For live WordPress sites: mysqldump --single-transaction --quick wordpress | gzip > wordpress.sql.gz, then restore with gzip -dc wordpress.sql.gz | mysql wordpress and verify tables.

warning

Restore steps can overwrite data. Always confirm the target host and database name before importing.

Workflow at a Glance​

Prerequisites​

  • Credentials for a database user with dump/restore privileges.
  • mysqldump and mysql client installed.
  • Enough disk space to hold the dump (plus compression overhead).

Verify tooling:

verify-mysql-tools.sh
mysqldump --version
mysql --version
gzip --version

Core Commands​

mysqldump-basic-shape.sh
mysqldump [OPTIONS] -u USER -p DATABASE > dump.sql
mysql-import-shape.sh
mysql -u USER -p DATABASE < dump.sql

Key Flags (WordPress-Friendly Defaults)​

FlagWhy it mattersExample
--single-transactionConsistent dump for InnoDB without long locksmysqldump --single-transaction ...
--quickStream rows to reduce memory usagemysqldump --quick ...
--routines --triggersInclude stored routines/triggers if presentmysqldump --routines --triggers ...
--set-gtid-purged=OFFAvoid GTID statements (MySQL feature)Use when needed for MySQL 5.7+
note

Most WordPress databases use InnoDB. If you have MyISAM tables, you may need different locking behavior.

Examples (Commands + Expected Output)​

Output varies

Successful dumps and imports often produce no output. Verify by checking the output file and running sanity queries.

Create a consistent WordPress dump (file output)​

dump-wordpress-db.sql.sh
mysqldump -u wpuser -p \
  --single-transaction --quick \
  --routines --triggers \
  wordpress > wordpress.sql

Expected output:

example-output-mysqldump-file.txt
(no output on success)

Use case: Create a migration-ready dump.

Confirm the dump file exists and has a reasonable size​

check-dump-file-size.sh
ls -lh wordpress.sql

Expected output:

example-output-ls-dump.txt
-rw-r--r-- 1 root root 1.2G Mar  1 10:55 wordpress.sql

Use case: Catch empty/failed dumps early.

Compress the dump​

compress-db-dump-gzip.sh
gzip -9 wordpress.sql

Expected output:

example-output-gzip.txt
(no output on success)

Use case: Reduce transfer/storage cost.

Dump and compress in one step​

dump-and-compress-stream.sh
mysqldump -u wpuser -p --single-transaction --quick wordpress | gzip -9 > wordpress.sql.gz

Expected output:

example-output-dump-stream.txt
(no output on success)

Use case: Faster workflow with less temporary disk usage.

Transfer the dump to another server​

scp-db-dump.sh
scp wordpress.sql.gz user@destination-vps:/tmp/

Expected output:

example-output-scp.txt
wordpress.sql.gz                                    100%  220M  12.0MB/s   00:18

Use case: Migrate the database between servers.

Restore into the target database​

restore-db-from-gzip.sh
gzip -dc wordpress.sql.gz | mysql -u root -p wordpress

Expected output:

example-output-restore.txt
(no output on success)

Use case: Import into the correct database on the destination.

Verify tables exist after restore​

verify-restored-tables.sh
mysql -u wpuser -p -D wordpress -e "SHOW TABLES;"

Expected output:

example-output-show-tables-after-restore.txt
Tables_in_wordpress
wp_options
wp_posts
wp_postmeta
...

Use case: Fast sanity check after import.

WordPress Migration Checklist​

  1. Confirm the correct database name and credentials on source and destination.
  2. Take a dump with --single-transaction --quick.
  3. Compress and transfer the dump.
  4. Create the destination database (if needed).
  5. Import the dump.
  6. Verify table count and a few key rows (siteurl, home, users).

WordPress VPS Use Cases​

ScenarioRecommended approachNotes
Pre-update backupDump + gzipKeep last-known-good DB state
Full server migrationDump + transfer + restoreVerify before DNS cutover
Disaster recovery testRestore into stagingProves backups are usable

Troubleshooting​

ProblemLikely causeFix
Dump file is tinyWrong database or credentialsVerify DB name and run SHOW TABLES;
Import fails with permission errorsPrivileges missingGrant required permissions or import as admin
Site breaks after restoreURL mismatchUpdate siteurl/home carefully; verify serialized data handling

Best Practices​

  • Always keep the dump file name dated (e.g., wordpress-2026-03-01.sql.gz).
  • Use --single-transaction for live InnoDB workloads.
  • Verify backups by restoring into staging regularly.
Cheat Sheet
db-backup-migration-cheat-sheet.sh
mysqldump -u wpuser -p --single-transaction --quick wordpress > wordpress.sql
gzip -9 wordpress.sql
mysqldump -u wpuser -p --single-transaction --quick wordpress | gzip -9 > wordpress.sql.gz
scp wordpress.sql.gz user@destination:/tmp/
gzip -dc wordpress.sql.gz | mysql -u root -p wordpress
mysql -u wpuser -p -D wordpress -e "SHOW TABLES;"

What's Next​