MySQL & PostgreSQL Backup Failures — Why Exit Code 0 Lies

Every developer eventually faces The Moment — a production database gets corrupted, a junior engineer runs DELETE without a WHERE clause, or a cloud disk silently fails. The difference between a bad afternoon and a company-ending catastrophe is whether you had a solid backup strategy before it happened. Backup and restore isn't a nice-to-have; it's the insurance policy your entire application depends on.

The problem is that most tutorials show you a command and call it a day. They don't explain that there are fundamentally different types of backups — logical vs. physical — and that choosing the wrong one for your situation can mean either a 6-hour restore window when you need 20 minutes, or a backup file that's completely unusable. They don't explain why pg_dump and mysqldump exist as separate tools with different philosophies, or when you'd reach for something else entirely.

By the end of this article you'll be able to: create logical and physical backups for both MySQL and PostgreSQL, write an automated backup script that you can drop into a cron job today, restore from a backup under pressure, and know exactly which approach to use for which scenario. Let's build that safety net.

Logical vs Physical Backups — The First Decision

Before you write a single backup script, you need to decide which type of backup fits your workload. Logical backups produce SQL statements or delimited text. Physical backups copy the raw database files.

Logical backups (mysqldump, pg_dump) are the Swiss Army knife of backups. They produce portable archives that you can restore on a different MySQL/PostgreSQL version, different architecture, or even different database engine with minor tweaks. They're ideal for small databases (<50 GB), schema-only backups, or when you need to migrate between environments. The cost: they are slow. A 500 GB MySQL dump can take hours, and restore takes even longer because every row must go through the SQL parser and re-index.

Physical backups (XtraBackup, pg_basebackup) copy raw data files at the filesystem level. They're fast — a 2 TB database can be backed up in under 30 minutes. They support incremental backups and have minimal database impact during operation. The trade-off: they're tightly coupled to the database version, storage engine, and operating system. You cannot restore an InnoDB physical backup into MyISAM, or a PostgreSQL 16 physical backup into version 15 without pg_upgrade.

The reality: in production, you need both. Logical backups for granular restore (single table, specific rows), and physical backups for full database recovery speed. You also need continuous archiving of transaction logs (binary logs for MySQL, WAL for PostgreSQL) for point-in-time recovery — no backup is complete without them.

#!/bin/bash
# io.thecodeforge.backup_decision —— choose backup strategy
DB_SIZE_GB=$(du -sb /var/lib/mysql/io_thecodeforge | awk '{print $1/1073741824}')
if [ $DB_SIZE_GB -lt 50 ]; then
  echo "Use mysqldump or pg_dump for logical backup"
else
  echo "Use XtraBackup or pg_basebackup for physical backup"
fi
# Always enable binary log / WAL archiving regardless of size
mysql -e "SELECT @@log_bin;" | grep -q 1 || echo "Enable log_bin in my.cnf"

mysqldump: Options That Matter in Production

mysqldump is ubiquitous, but most engineers use it with defaults that are dangerous in production. The default mode locks tables with a global read lock, blocking writes for the entire dump duration. On a busy database, that's a full write outage.

Critical options: - --single-transaction: For InnoDB tables, starts a transaction using MVCC to get a consistent snapshot without locking writes. This is the most important flag for production MySQL. Do not dump a live InnoDB database without it. - --routines --events --triggers: By default, mysqldump does NOT include stored procedures, events, or triggers unless you explicitly add these flags. Missing them means your restore will lose application logic. - --master-data=2: Records the binary log file and position at dump time, essential for replication setup or point-in-time recovery. Also enables --flush-logs implicitly. - --compress: Compresses the output on the fly, saving disk space and network bandwidth without needing a separate gzip step. - --opt: A shortcut for several optimizations (disable foreign key checks, extended inserts) — but it's enabled by default. Good for restore speed, but the extended inserts can cause memory issues on restricted test instances.

Performance consideration: A dump of a 200 GB InnoDB database will take about 30–60 minutes on modern hardware. During that time, the database buffer pool might be flushed by the full table scan. Monitor SHOW ENGINE INNODB STATUS for pending disk reads. For databases over 500 GB, use mydumper (parallel logical dump) or switch to physical backup.

#!/bin/bash
# io.thecodeforge.mysql_backup — production-safe MySQL logical backup
BACKUP_DIR="/backup/mysql/$(date +%Y%m%d)"
mkdir -p "$BACKUP_DIR"
mysqldump \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  --master-data=2 \
  --compress \
  --all-databases \
  > "$BACKUP_DIR/full_dump.sql.gz"

# Verify dump integrity (minimal parse test)
mysql -e "SELECT 1" # check connectivity first
zcat "$BACKUP_DIR/full_dump.sql.gz" | tail -n +22 | head -c 10000 | mysql test_verify 2>&1 || echo "Backup corruption detected!"

# Copy binary logs since last dump
mysql -e "SHOW BINARY LOGS;" | awk 'NR>1{print $1}' | while read log; do
  mysqlbinlog /var/lib/mysql/$log > "$BACKUP_DIR/$log.sql"
done

pg_dump: What Seniors Know About PostgreSQL Backups

PostgreSQL's logical backup tool pg_dump is more flexible than mysqldump but has its own sharp edges. The key difference: pg_dump runs in a READ COMMITTED isolation level by default, not REPEATABLE READ. That means if another transaction modifies data during the dump, you may get an inconsistent snapshot.

Critical flags: - -j N (parallel jobs): Speeds up dump by using N concurrent workers, each dumping a separate table. The default is 1. For databases with large tables, use -j 4 to cut dump time by 60–70%. But beware: parallel dumps put more load on the server. - --schema-only / --data-only: Useful when you need to preserve structure separately from data, e.g., for change management. - --format=directory: Outputs each table as a separate file inside a directory. This enables parallel restore with pg_restore -j. It also compresses each file individually, so you can restore single tables. - --no-owner / --no-privileges: Essential when restoring on a different server where the PostgreSQL user may not exist. - --snapshot: Use a previously exported snapshot ID for consistent backup across multiple dumps (e.g., schema + data).

Point-in-time recovery prerequisite: pg_dump alone cannot give you PITR. You must combine it with continuous WAL archiving via archive_mode = on and archive_command. The base backup (e.g., pg_basebackup) plus WAL segments allows restoring to any second.

#!/bin/bash
# io.thecodeforge.pg_backup — production Postgres logical backup with parallel workers
DB_NAME="io_thecodeforge"
BACKUP_DIR="/backup/pg/$(date +%Y%m%d)"
mkdir -p "$BACKUP_DIR"

# Parallel dump (4 jobs, directory format)
pg_dump -h localhost -U backup_user -j 4 -Fd -f "$BACKUP_DIR/dump" "$DB_NAME"

# Verify the dump by trying to restore a single table to a temp DB
createdb verify_backup
pg_restore -d verify_backup -t "settings" "$BACKUP_DIR/dump" 2>&1 || echo "Restore test failed"
dropdb verify_backup

# Archive WAL segments since last base backup
rsync -avz /var/lib/postgresql/16/archive/* "$BACKUP_DIR/wal/"

Automating Backups: The Cron Script That Won't Fail Silently

Most backup strategies fail not because of bad tools, but because the automation is brittle. A cron job that silently stops running, or a script that returns exit code 0 even when the backup file is empty, is worse than no backup — it gives false confidence.

Build a robust automated backup pipeline: 1. Pre-checks: Before starting a backup, verify disk space (df -h) is above threshold, database is reachable (mysqladmin ping or pg_isready), and no other backup is running (lock file). 2. Backup execution: Use the commands from earlier sections with proper flags. Redirect stdout/stderr to a log file. 3. Post-check: Immediately verify the backup by attempting a partial restore on a temporary database. For logical dumps, run the first 1000 lines through the parser. For physical backups, check the last modified timestamp and file size. 4. Rotation & retention: Keep daily backups for 7 days, weekly for 4 weeks, monthly for 12 months. Delete old backups automatically, but keep the latest full backup in a separate location. 5. Alerting: If the backup fails or the verification step fails, send an alert to PagerDuty, email, or Slack. Do not rely on cron's built-in mail — it's often not configured.

The cron schedule: For most workloads, run a full backup daily during off-peak hours (e.g., 2 AM). For MySQL with high transaction volume, stream binary logs every 5 minutes to a remote server. For PostgreSQL, configure archive_timeout = 60 in postgresql.conf to archive WAL segments at least every minute.

Pro tip: Use a tool like autobackup.py or a simple bash script with error handling. Avoid complex frameworks unless you have a dedicated team to maintain them.

# io.thecodeforge.cron — database backup scheduling
# Daily full backup at 2:00 AM
0 2 * * * root /opt/scripts/mysql_backup_prod.sh > /var/log/db_backup.log 2>&1 || /usr/local/bin/critical_alert "MySQL backup failed"
# Verify yesterday's backup integrity at 3:00 AM (staggered)
30 3 * * * root /opt/scripts/verify_backup.sh yesterday > /var/log/db_verify.log 2>&1 || /usr/local/bin/critical_alert "Backup verification failed"
# Healthcheck: email if no backup file newer than 26 hours
0 * * * * root /opt/scripts/check_backup_age.sh /backup/mysql/ 26 || /usr/local/bin/critical_alert "Backup age exceeded threshold"

Restore Under Pressure: The Procedure That Saves Your Career

Restores happen under pressure. The system is down, stakeholders are watching, and your hands are shaking. A well-documented restore procedure is your lifeline. The number one rule: never restore directly onto the production server unless you have no other choice. Always restore to a separate instance first to validate the backup.

Restore steps for logical backup: 1. Spin up a clean instance with the same version and configuration. 2. Transfer the backup file (if not already local). 3. For MySQL: mysql -u root -p < full_dump.sql or using a compressed file: zcat full_dump.sql.gz | mysql -u root -p. 4. For PostgreSQL: pg_restore -d new_db -j 4 -Fd backup_directory or for plain SQL: psql -d new_db -f backup.sql. 5. Verify the data: run select count(*) on critical tables, test application queries. 6. If success, point application to new database (or promote restored instance).

Restore steps for physical backup (XtraBackup): 1. Prepare the backup: xtrabackup --prepare --target-dir=/backup/xtra. 2. Copy the prepared backup to the MySQL data directory. 3. Start MySQL; check error log. 4. Apply binary logs from the dump position to the desired point in time.

Restore steps for physical backup (pg_basebackup): 1. Ensure the base backup directory is in place. 2. Edit postgresql.auto.conf or recovery.conf to set restore_command to fetch WAL from archive. 3. Create a recovery target if needed: recovery_target_time '2026-04-22 14:30:00'. 4. Start PostgreSQL; it will automatically replay WAL until the target. 5. Run SELECT pg_is_in_recovery(); to confirm it's up.

Test your restores routinely. Schedule a monthly restore drill where you restore the latest backup to a test environment and run a script that validates row counts from a known checkpoint. Without testing, you're gambling.

#!/bin/bash
# io.thecodeforge.restore_drill — monthly restore validation
set -euo pipefail

RESTORE_DIR="/tmp/restore_drill_$(date +%s)"
mkdir -p "$RESTORE_DIR"

cd "$RESTORE_DIR"
# Fetch latest backup from remote storage
aws s3 cp s3://backups/mysql/latest/full_dump.sql.gz .

# Prepare test database
mysql -e "CREATE DATABASE IF NOT EXISTS restore_test;"
mysql -e "DROP DATABASE IF EXISTS restore_test;"
mysql -e "CREATE DATABASE restore_test;"

# Restore
zcat full_dump.sql.gz | mysql -u root restore_test 2>&1 | tee restore.log || echo "Restore failed"

# Verify
ROWS=$(mysql -u root -e "SELECT COUNT(*) AS total_rows FROM restore_test.io_thecodeforge.users;" 2>/dev/null | tail -1)
if [ "$ROWS" -gt 1000 ]; then
  echo "Restore validation passed: $ROWS rows restored."
else
  echo "Restore validation FAILED: expected >1000 rows, got $ROWS"
  exit 1
fi

# Cleanup
mysql -e "DROP DATABASE restore_test;"
rm -rf "$RESTORE_DIR"