Skip to main content

mysqldump Best Practices

Database dumps are a core part of WordPress backups. The goal is a dump that is consistent, restorable, and automatable without leaking credentials.

Quick Summary
  • Use --single-transaction --quick for InnoDB (common in WordPress).
  • Include objects you may need: --routines --events --triggers.
  • Compress the dump (SQL compresses extremely well).
  • Avoid inline passwords in commands and scripts.

Prerequisites​

  • A database user with privileges to read the WordPress database.
  • mysqldump installed (often via mysql-client).
check-mysqldump-installed.sh
command -v mysqldump
mysqldump --version

Handle credentials safely​

Avoid these patterns:

  • -p'PASSWORD' or -pPASSWORD in your shell history.
  • Putting database passwords in world-readable scripts.

Prefer:

  • Interactive -p (manual runs).
  • A restricted option file for automation.

Example option file (readable only by root):

create-mysql-option-file.sh
sudo install -m 600 /dev/null /root/.my.cnf
sudo tee /root/.my.cnf >/dev/null <<'EOF'
[client]
user=backup
password=REPLACE_ME
host=127.0.0.1
EOF
warning

Treat /root/.my.cnf as a secret. Restrict permissions and keep it out of backups that go to untrusted storage.

A good default dump command (WordPress)​

This targets consistent reads and compatibility.

mysqldump-wordpress-default.sh
mysqldump \
  --single-transaction \
  --quick \
  --routines --events --triggers \
  --default-character-set=utf8mb4 \
  --hex-blob \
  --column-statistics=0 \
  wordpress > /backups/wp-db.sql

Notes:

  • --single-transaction avoids table locks for InnoDB while remaining consistent.
  • --quick streams rows instead of buffering.
  • --column-statistics=0 improves compatibility with older servers/clients.

SQL dumps compress well. Pick one:

compress-sql-dump-gzip.sh
gzip -9 /backups/wp-db.sql
compress-sql-dump-zstd.sh
zstd -3 -T0 -o /backups/wp-db.sql.zst /backups/wp-db.sql
rm -f /backups/wp-db.sql

Restore workflow (staging first)​

Create a staging database and import.

restore-mysql-from-sql-gz.sh
gunzip -c /backups/wp-db.sql.gz | mysql wordpress_restore
restore-mysql-from-sql-zst.sh
zstd -dc /backups/wp-db.sql.zst | mysql wordpress_restore

Verification​

Verify that the dump is non-empty and contains expected objects.

verify-mysqldump-output.sh
ls -lh /backups/wp-db.sql.*

# quick spot-check for WordPress tables
zstd -dc /backups/wp-db.sql.zst | rg -n 'CREATE TABLE `wp_' | sed -n '1,10p'

Common failure modes​

mysqldump exits but dump is tiny​

Usually:

  • Wrong database name.
  • Privileges missing.
check-databases-and-privileges.sh
mysql -e "SHOW DATABASES;"
mysql -e "SHOW TABLES FROM wordpress;"

Locking or long runtime​

  • Ensure the DB is InnoDB (most WordPress tables are).
  • Use --single-transaction.

Next steps​

  • Automate daily dumps: opt/docker-data/apps/docusaurus/site/docs/server/linux-server/10-backup-disaster-recovery/automating-db-dump-daily.mdx.
  • Encrypt database backups: opt/docker-data/apps/docusaurus/site/docs/server/linux-server/10-backup-disaster-recovery/encrypt-database-backups.mdx.