MySQL Backup Guide 2026: mysqldump, XtraBackup & Point-in-Time Recovery

Key Takeaways

• --single-transaction always: Consistent InnoDB backup without table locks.
• Three backup types: mysqldump (portable SQL), XtraBackup (fast physical), binary logs (point-in-time).
• Test restores regularly: A backup you haven’t tested restoring is not a backup.
• Enable binary logs: Required for point-in-time recovery. Set binlog_format=ROW and expire_logs_days=7.

---

Introduction

Direct Answer: How do I back up MySQL databases on Ubuntu 24.04 in 2026?

For most databases under 50GB: mysqldump -u backup_user -p --single-transaction --routines --triggers --events appdb | gzip > /var/backups/appdb-$(date +%Y%m%d_%H%M).sql.gz. The --single-transaction flag creates a consistent snapshot of InnoDB tables without locking. Automate with a daily cron job: 0 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1. For large databases (50GB+) or minimal backup windows: use Percona XtraBackup (sudo xtrabackup --backup --target-dir=/var/backups/full). For point-in-time recovery: enable binary logs in /etc/mysql/conf.d/binlog.cnf with binlog_format=ROW, back up binlogs alongside full backups, and replay with mysqlbinlog.

---

Part 1: Enable Binary Logs (Prerequisite for PITR)

sudo tee /etc/mysql/conf.d/binlog.cnf << 'EOF'
[mysqld]
server_id        = 1
log_bin          = /var/log/mysql/mysql-bin.log
binlog_format    = ROW          # Required for XtraBackup and PITR
expire_logs_days = 7            # Keep 7 days of binary logs
max_binlog_size  = 256M
sync_binlog      = 1            # Flush to disk on every write (safer)
EOF

sudo systemctl restart mysql
mysql -u root -p -e "SHOW MASTER STATUS\G"

Expected output:

         File: mysql-bin.000001
     Position: 157
 Binlog_Do_DB:

---

Part 2: mysqldump — Logical Backup

# Create dedicated backup user with minimal privileges
sudo mysql << 'SQL'
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup_strong_password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
GRANT EVENT ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
SQL

# Single database backup
mysqldump \
    -u backup -p'backup_strong_password' \
    --single-transaction \     # Consistent InnoDB backup (no table locks)
    --routines \               # Include stored procedures and functions
    --triggers \               # Include triggers
    --events \                 # Include scheduled events
    --hex-blob \               # Safely encode BLOB columns
    appdb | gzip > /var/backups/appdb-$(date +%Y%m%d_%H%M).sql.gz

ls -lh /var/backups/appdb-*.sql.gz | tail -3

Expected output:

-rw-r--r-- 1 root root 847M Apr 30 02:00 appdb-20260430_0200.sql.gz

# All databases backup
mysqldump \
    -u backup -p'backup_strong_password' \
    --all-databases \
    --single-transaction \
    --routines --triggers --events \
    | gzip > /var/backups/all-databases-$(date +%Y%m%d_%H%M).sql.gz

---

Part 3: Restore from mysqldump

# Restore a specific database
gunzip -c /var/backups/appdb-20260430_0200.sql.gz | \
    mysql -u root -p appdb

# Restore all databases
gunzip -c /var/backups/all-databases-20260430_0200.sql.gz | \
    mysql -u root -p

# Verify restore
mysql -u root -p appdb -e "SHOW TABLES; SELECT COUNT(*) FROM users;"

---

Part 4: Percona XtraBackup (Large Databases)

# Install Percona XtraBackup
sudo apt-get install -y percona-xtrabackup-80

# Full physical backup
sudo xtrabackup \
    --backup \
    --user=backup \
    --password='backup_strong_password' \
    --target-dir=/var/backups/xtrabackup-$(date +%Y%m%d)

# Prepare the backup (make it consistent before restore)
sudo xtrabackup --prepare \
    --target-dir=/var/backups/xtrabackup-20260430

echo "Backup ready for restore"

Expected output:

[00] 2026-04-30T02:00:00.000000Z completed OK!
[00] Backup created in directory '/var/backups/xtrabackup-20260430'

# Restore (stop MySQL first)
sudo systemctl stop mysql
sudo rm -rf /var/lib/mysql/*
sudo xtrabackup --copy-back --target-dir=/var/backups/xtrabackup-20260430
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql
mysql -u root -p -e "SHOW DATABASES;"

---

Part 5: Point-in-Time Recovery

# Scenario: database corrupted at 14:30:00 — recover to 14:29:00

# Step 1: Restore last full backup (before 14:30)
gunzip -c /var/backups/appdb-20260430_0200.sql.gz | mysql -u root -p appdb

# Step 2: Find the binary log position just before corruption
sudo mysqlbinlog \
    --start-datetime="2026-04-30 02:00:00" \
    --stop-datetime="2026-04-30 14:29:00" \
    /var/log/mysql/mysql-bin.000001 | \
    mysql -u root -p appdb

echo "Point-in-time recovery complete — restored to 14:29:00"

---

Part 6: Automated Backup Script

sudo tee /usr/local/bin/mysql-backup.sh << 'SCRIPT'
#!/bin/bash
set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
DB_USER="backup"
DB_PASS="backup_strong_password"
RETAIN_DAYS=7
LOG="/var/log/mysql-backup.log"

log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG"; }

mkdir -p "$BACKUP_DIR"
DATE=$(date +%Y%m%d_%H%M)

log "Starting MySQL backup"

# Dump all databases
mysqldump -u "$DB_USER" -p"$DB_PASS" \
    --all-databases --single-transaction \
    --routines --triggers --events | \
    gzip > "${BACKUP_DIR}/all-${DATE}.sql.gz"

SIZE=$(du -sh "${BACKUP_DIR}/all-${DATE}.sql.gz" | cut -f1)
log "Backup complete: all-${DATE}.sql.gz (${SIZE})"

# Rotate old backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +${RETAIN_DAYS} -delete
KEPT=$(ls "$BACKUP_DIR"/*.sql.gz 2>/dev/null | wc -l)
log "Retained ${KEPT} backup(s). Deleted files older than ${RETAIN_DAYS} days."
SCRIPT

sudo chmod +x /usr/local/bin/mysql-backup.sh

# Schedule daily at 2:00 AM
(crontab -l 2>/dev/null; echo "0 2 * * * /usr/local/bin/mysql-backup.sh") | crontab -

# Test the script
sudo /usr/local/bin/mysql-backup.sh
ls -lh /var/backups/mysql/

---

Conclusion

MySQL is now backed up with automated daily mysqldump snapshots, binary logs enabled for point-in-time recovery, and a rotation policy keeping 7 days of history. Test the restore procedure monthly — a backup you haven’t restored from is an assumption, not a guarantee.

---

People Also Ask

How often should I back up MySQL?

At minimum: daily full backups with 7-day retention, binary logs enabled for PITR. For higher RPO/RTO requirements: backup every 4–6 hours. The frequency should match your tolerance for data loss — if losing 24 hours of data is acceptable (low-traffic blog), daily is fine. If losing 1 hour is unacceptable (e-commerce), set up continuous binary log shipping to a replica or S3-compatible storage.

---

Part 7: Backup Validation and Consistency Checks

A backup is only useful if it can be restored. Always validate backups immediately after creation.

For mysqldump, run a restore test to a temporary database:

mysql -u root -p -e "CREATE DATABASE backup_test;"
gunzip -c /var/backups/appdb-20260502_0200.sql.gz | mysql -u root -p backup_test
mysql -u root -p -e "SELECT COUNT(*) FROM backup_test.users;"

For XtraBackup, use the --prepare phase and run it on a separate host or VM to verify file integrity without disturbing production.

xtrabackup --prepare --target-dir=/var/backups/xtrabackup-20260502

If the prepare step fails, the backup is not usable. Capture the error output and fix the backup configuration before proceeding.

---

Part 8: Point-in-Time Recovery in Detail

Point-in-time recovery (PITR) is the difference between a backup and a recovery strategy. With binary logs enabled, you can restore the database to the exact moment before an incident.

The workflow is:

1. Restore the last full backup.
2. Apply binary log events from the backup time to the target recovery time.
3. Stop before the failure or user error occurs.

Example recovery steps:

# Restore the full backup first
mysql -u root -p appdb < /var/backups/appdb-20260502_0200.sql

# Apply binary logs through 14:29:59
mysqlbinlog --stop-datetime="2026-05-02 14:29:59" /var/log/mysql/mysql-bin.000001 | mysql -u root -p appdb

If your binlogs are compressed, add --read-from-remote-server or decompress them first. The key is to keep expire_logs_days long enough to cover your RPO and the time it takes to discover the problem.

---

Part 9: Retention Policies and Catalog Management

Retention policy defines how long backups remain available. For a sovereign environment, use multiple retention tiers:

• daily full backups retained for 14 days
• weekly full backups retained for 12 weeks
• monthly backups retained for 12 months
• binary logs retained for the longest recovery period you want to support

Automate catalog pruning with timestamped directories and an audit log. Example structure:

/var/backups/mysql/weekly/2026-05-02/
/var/backups/mysql/daily/2026-05-03/
/var/backups/mysql/monthly/2026-05/

A retention script should delete expired files safely and log removed backups.

---

Part 10: Encrypting Backups at Rest and in Motion

Backups contain production data and are a high-value target. Encrypt them at rest and when moving them offsite.

For file encryption with OpenSSL:

gzip -c /var/backups/appdb-20260502_0200.sql |     openssl enc -aes-256-cbc -salt -pbkdf2 -pass pass:"SuperSecurePassphrase" > /var/backups/encrypted/appdb-20260502_0200.sql.gz.enc

For XtraBackup repositories, use filesystem-level encryption or encrypt the repository directory before archiving.

# On Linux, use LUKS for directory-level encryption
sudo cryptsetup luksFormat /dev/sdb1
sudo cryptsetup open /dev/sdb1 secure-backups
mkfs.ext4 /dev/mapper/secure-backups
mount /dev/mapper/secure-backups /mnt/secure-backups

Ensure key material is stored separately from the backup media. If an encrypted backup and its key are both lost, the data is unrecoverable.

---

Part 11: Offsite and Air-Gapped Backup Workflows

A sovereign backup strategy should include at least one offsite copy. For truly isolated environments, use air-gapped media with strong checksums.

Example offsite workflow:

1. Create the backup locally.
2. Encrypt with a strong passphrase or GPG key.
3. Transfer to an offsite host or removable media using rsync --checksum.
4. Verify the checksum after transfer.

sha256sum /var/backups/encrypted/appdb-20260502_0200.sql.gz.enc > /var/backups/checksums.txt
rsync -avP /var/backups/encrypted/appdb-20260502_0200.sql.gz.enc remote:/backup-store/
ssh remote "sha256sum -c /backup-store/appdb-20260502_0200.sha256"

Air-gapped backups should be written to a dedicated USB or tape device, stored in a secure location, and audited periodically.

---

Part 12: Disaster Recovery Runbooks and Playbooks

A backup guide is incomplete without a recovery playbook. Create one that covers common recovery scenarios:

• corrupted table after a failed migration
• deleted application data by accident
• ransomware event requiring restore from a known good backup
• full cluster failure requiring complete recovery

A simple runbook should include commands, expected outputs, and validation steps:

1. Identify the latest safe backup.
2. Restore the backup to a separate host.
3. Run validation queries.
4. Promote the restored database.
5. Update application configuration and DNS if needed.

Maintain this runbook in a version-controlled document that operators can access without a production database connection.

---

Part 13: Monitoring, Alerts, and Operational Visibility

Your backup system should alert you when it fails. Build monitoring around three signals:

• backup completion status
• backup size and growth trends
• binary log availability and retention

Example monitor checks:

# Check the most recent backup age
find /var/backups/mysql/daily -maxdepth 1 -name "*.sql.gz" -printf '%T@ %p
' | sort -n | tail -1

If the latest backup is older than 36 hours, trigger an alert. If binary logs are missing for the retention window, alert immediately.

Use a local monitoring agent or a simple cron health script to post messages to chat, email, or a local dashboard.

---

Part 14: Backups in Containerized MySQL Deployments

If MySQL runs in a Docker or Podman container, backup strategy changes slightly. Use volume mounts for data and capture backups outside the container.

Example Docker backup:

docker exec mysql-container mysqldump -u backup -p'backup_strong_password'     --single-transaction --routines --triggers --events appdb | gzip > /var/backups/appdb-$(date +%Y%m%d_%H%M).sql.gz

For XtraBackup in a containerized environment, mount the MySQL data directory and backup target as volumes and run xtrabackup inside the container.

Keep the container image minimal and the backup tools separate from the production runtime. This reduces the risk of backup operations affecting transactional performance.

---

Part 15: High Availability and Backup Coordination

Backups should integrate with your MySQL high availability topology. For read replicas, prefer backups from a replica when possible to reduce load on the primary.

If using MySQL Group Replication or InnoDB Cluster, designate one node for backups and keep the backup user in sync across the cluster.

When restoring in an HA environment, rebuild the cluster from the restored data, then rejoin replicas and apply any necessary group replication configuration. Document the cluster bootstrap procedure in your disaster recovery documentation.

---

Part 16: Backup Best Practices Checklist

• Full backups are automated and consistent.
• Binary logs are enabled and retained for the recovery window.
• Backups are encrypted at rest and in transit.
• Backup restores are tested regularly.
• Retention policies are documented and enforced.
• Offsite or air-gapped copies exist.
• Backup failures trigger alerts.
• Backup users have minimal privileges.
• Backup metadata and checksums are archived.
• Disaster recovery runbooks are current and accessible.

Part 17: Backup Performance and Throughput Tuning

Backups can become a bottleneck if they compete with production I/O. Tune the backup pipeline for speed and minimal impact.

For mysqldump:

• Use --single-transaction to avoid locking.
• Use --quick to stream rows rather than buffering them in memory.
• Use --max_allowed_packet with care for large binary columns.

For XtraBackup:

• Use --parallel to speed file copy operations.
• Exclude unnecessary directories from the backup target.
• Use a fast backup disk separate from the MySQL data disk if possible.

Example XtraBackup options:

xtrabackup --backup --target-dir=/var/backups/xtrabackup-20260502 --parallel=4

A well-tuned backup system preserves production performance while still meeting recovery goals.

---

Part 18: Backup Storage Tiers and Local Caching

Use a tiered backup architecture:

• Tier 1: local high-performance backup disk for daily operations
• Tier 2: encrypted network-attached storage or private object store for weekly backups
• Tier 3: air-gapped removable media or offsite vault for monthly archives

This tiering balances recovery speed with long-term durability. Keep local backups for quick restores and move older snapshots to more secure storage.

---

Part 19: Recover Time Objectives (RTO) and Backup Planning

Your backup strategy should support the recovery objectives your organization sets.

• If RTO is measured in minutes, use physical backups and standby replicas.
• If RTO is measured in hours, mysqldump may be acceptable for smaller databases.
• If RTO is flexible but data loss must be minimal, enable binary logs and plan for PITR.

Match backup frequency to the acceptable data loss window. For example, if one hour of data loss is acceptable, keep binary logs for at least 24 hours and perform daily full backups.

---

Part 20: Backup Key Lifecycle Management

Encryption keys are as important as the backups themselves. Establish a key lifecycle that includes generation, rotation, storage, and retirement.

• Generate keys using a secure local hardware module or a trusted key management system.
• Store keys separately from the backup data.
• Rotate keys on a regular schedule or after any detected exposure.
• Retire old keys only after confirming all backups encrypted with them are no longer needed.

Use a manifest to record which key encrypted each backup. Without that record, you may be unable to decrypt older backups when needed.

---

Part 21: Backup Metadata and Catalog Design

Keep a backup catalog so you can find the right restore set quickly. Include metadata such as:

• backup type (full, incremental, differential)
• target database
• server hostname
• retention tier
• encryption status
• checksum
• software version

A simple manifest file might look like this:

{
  "backup_id": "20260502-0200",
  "type": "full",
  "database": "appdb",
  "mysql_version": "9.0.1",
  "encrypted": true,
  "checksum": "sha256:..."
}

This metadata saves time during incident response and helps justify recovery decisions.

---

Part 22: Backup Recovery Drills and Continuous Improvement

Regular recovery exercises are the most reliable way to prove your backups work.

Run a quarterly drill that includes:

• restoring the latest backup to an isolated host
• applying binary logs or WAL segments for PITR
• validating application queries
• timing the restore process and comparing to target RTO
• documenting discoveries and process improvements

After each drill, update the backup playbook with lessons learned. The drill is also an opportunity to test access to encrypted backups and offline archives.

---

Part 23: Backup Governance, Audit, and Policy

Governance turns backup operations from a task into a discipline. Document policies such as:

• who may approve backup retention changes
• who may access backup encryption keys
• what data classes require permanent retention
• how long audit logs are retained
• how backup failures are escalated

Review these policies at least annually. For sovereign environments, policies should be local, explicit, and aligned with your overall security posture.

---

Part 24: Backup Troubleshooting and Common Issues

Problem: mysqldump runs too slowly

Use --single-transaction --quick --skip-lock-tables and consider using a replica when possible.

Problem: xtrabackup fails during backup

Check innodb_flush_log_at_trx_commit and ensure the backup user has RELOAD and LOCK TABLES privileges.

Problem: binary logs are missing

Verify log_bin and expire_logs_days, and make sure the backup archive process has not removed them prematurely.

Problem: restore verification fails

Always restore to a test host first. If the backup is corrupt, stop and fix the backup pipeline before relying on it again.

---

Part 25: Final Backup Readiness Checklist

• Backups are created and verified daily.
• Binary logs are enabled and archived successfully.
• Encrypted copies exist offsite.
• Restore drills are performed on a schedule.
• Backup metadata is recorded for every file.
• Encryption keys are managed separately and rotated.
• Retention policies are defined and enforced.
• Backup users have minimal privileges.
• Monitoring alerts trigger on failures and stale backups.
• Recovery runbooks are accessible and up to date.

Part 26: Scheduling Strategies for Steady Backup Windows

Choose backup windows that minimize conflict with peak traffic. For low-traffic applications, daily backups at 02:00 are common. For busier workloads, consider:

• incremental backups every 4 hours
• full backups on weekends
• snapshot backups during known maintenance windows

A staggered schedule reduces I/O concentration and avoids overwhelming the host. Record the schedule in the backup playbook and make sure operators know when backups run.

---

Part 27: Handling Legacy Data and Schema Changes

When your schema changes, ensure backups remain consistent across versions. Use a migration-aware backup strategy:

• take a full backup before schema changes
• create a restore point in your backup metadata
• perform a restore test after the migration

If you discover a migration rollback is necessary, your backups should allow recovery to the point before the migration began. Keep a separate pre-migration backup for this use case.

---

Part 28: Local Backup Retention and Compliance

Different data classes may have different retention requirements. For example:

• transactional logs: 7 days
• application data backups: 30 days
• audit data backups: 1 year

Document these policies and encode them in your retention script. Backup retention is not arbitrary — it should reflect business requirements and regulatory obligations.

---

Part 29: Backup Storage Access Controls

Backups should be as restricted as the production data they contain. Use file permissions and access controls:

chmod 700 /var/backups/mysql
chown root:backup /var/backups/mysql

Only backup operators and recovery engineers should have access. If using a shared repository, separate backup storage from regular user storage and monitor access logs.

---

Part 30: Restoring from Compressed and Encrypted Backups

A common restore failure happens when the compression or encryption pipeline is not tested. Document commands for each format:

# gzip restore
gunzip -c backup.sql.gz | mysql -u root -p appdb

# encrypted restore
openssl enc -d -aes-256-cbc -pbkdf2 -in backup.sql.gz.enc | gunzip | mysql -u root -p appdb

If the backup is encrypted with GPG, keep the public key available for verification and the private key secure on the recovery host.

---

Part 31: Backup Metrics and Capacity Planning

Track these metrics:

• backup size growth rate
• storage utilization
• average restore time
• backup success rate
• time to detect backup failure

Use these metrics to plan capacity and justify additional backup storage or faster media. If backup size grows unexpectedly, investigate schema or data changes.

---

Part 32: Continuous Improvement through Runbook Reviews

Backup procedures should be reviewed after every incident. If a recovery takes longer than expected, update the runbook. If a backup fails, document the cause and the fix.

A strong backup program treats every failure as a learning opportunity and codifies the improvement in the next runbook revision.

Part 33: Recovery Time and Confidence Targets

Define both a recovery time objective (RTO) and a recovery confidence objective. RTO answers how quickly you need to recover. Recovery confidence asks how sure you are that the restore is valid.

Achieving confidence means running restore tests, verifying data, and updating recovery documentation after every change. A backup program with an RTO but no validation is incomplete.

Part 34: Backup Recovery Confidence Rating

Assign a confidence rating to each backup type and restore path. Example:

• full mysqldump restore: high confidence for small databases
• XtraBackup restore: high confidence for large InnoDB stores
• binary-log replay: medium confidence if log sequence is verified

Record the confidence level in the backup catalog so operators know which recovery path to trust under pressure.

Part 35: Backup Restore Playbook Snippet

A restore playbook should include a concise command sequence and validation steps. For example:

1. locate the backup file: ls -lh /var/backups/mysql/appdb-*.sql.gz
2. decrypt if needed
3. restore to a test database: gunzip -c ... | mysql -u root -p appdb_test
4. verify row counts and key queries
5. document the result and retention details

Include this snippet directly in your recovery documentation so operators can find it quickly during an incident.

Part 36: Backup Confidence Statements

For each backup type, maintain a short confidence statement. For example: “mysqldump backups are verified daily and test-restored monthly.” These statements help operators choose the right recovery path quickly.

Part 37: Backup Playbook Health Check

Include a simple health-check note in your backup playbook. If the latest backup is older than expected or the restore test has not run, escalate immediately. This quick triage step saves hours in an incident.

Part 38: Final Backup Readiness Reminder

Keep the recovery commands, encryption keys, and validation queries all in one trusted playbook so the team can restore quickly and confidently.

Further Reading

• How to Install MySQL 9 on Ubuntu 24.04 — MySQL installation
• MySQL User Management 2026 — backup user with minimal privileges
• Restic Backup on Ubuntu 24.04 — also back up the SQL dump files

Tested on: Ubuntu 24.04 LTS (Hetzner CX32). MySQL 9.0.1, XtraBackup 8.0.35. Last verified: April 30, 2026.