How to Backup a MySQL Database

I’ve never regretted having an extra backup, but I’ve absolutely regretted not having one. Early in my career, a colleague ran a schema migration script against production instead of staging — a simple mix-up between two terminal tabs — and the only reason it wasn’t a disaster was that a full backup from four hours earlier existed. Since then, backups have stopped being a checkbox for me and become a designed system with layers, schedules, and tested recovery paths. Here’s everything I’ve learned about doing that properly in MySQL.

Why a Backup Strategy, Not Just “a Backup”

A single nightly full backup is better than nothing, but a real strategy accounts for: how much data you can afford to lose (Recovery Point Objective, RPO), how quickly you need to be back online (Recovery Time Objective, RTO), storage cost, and the performance impact of backup operations on a live production system. I design around those constraints explicitly rather than just picking a tool and running it on a timer.

Method 1: Logical Backups with mysqldump

mysqldump exports your database as a sequence of SQL statements that can recreate the schema and data when replayed — this is what I call a logical backup.

mysqldump -u root -p --databases mydatabase > mydatabase_backup.sql

For a full-server backup covering every database, users, and privileges:

mysqldump -u root -p --all-databases --routines --triggers --events > full_server_backup.sql

I always include --routines, --triggers, and --events explicitly — they’re not included by default in every version/configuration, and forgetting them means silently losing stored procedures, triggers, or scheduled events in your backup.

Backing Up a Single Table

mysqldump -u root -p mydatabase orders > orders_backup.sql

Compressing the Backup

mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

Consistent Backups for InnoDB (Avoiding Locks)

By default, mysqldump can lock tables during the backup, which is disruptive on a live production system. For InnoDB tables, I use --single-transaction, which takes advantage of MVCC to get a consistent snapshot without blocking other writes:

mysqldump -u root -p --single-transaction --routines --triggers mydatabase > mydatabase_backup.sql

This has become my default for any InnoDB-only database — it’s the difference between a backup that briefly stalls production traffic and one that doesn’t block anything at all.

Method 2: Physical Backups with Percona XtraBackup

For large databases, logical backups become slow both to create and to restore, since restoring means replaying every SQL statement. Physical backups copy the actual data files instead, which is dramatically faster for both backup and restore at scale.

xtrabackup --backup --target-dir=/backups/full_backup/ \
  --user=root --password=yourpassword

XtraBackup can back up InnoDB tables without locking them at all (using the same redo-log-based consistency mechanism InnoDB itself uses for crash recovery), which makes it my preferred method for large, high-traffic production databases where mysqldump‘s overhead — even with --single-transaction — becomes noticeable.

Incremental Backups with XtraBackup

# Full backup (baseline)
xtrabackup --backup --target-dir=/backups/full_backup/

# Incremental backup, capturing only changes since the full backup
xtrabackup --backup --target-dir=/backups/incremental_1/ --incremental-basedir=/backups/full_backup/

Incremental backups dramatically reduce storage and backup-window time for very large databases, at the cost of a more involved restore process (covered in my companion article on restoring backups).

Method 3: Replication as a Backup Layer (Not a Replacement)

I maintain at least one replica server in most production setups, and I take backups from the replica rather than the primary whenever possible — this offloads backup I/O entirely from the server handling live traffic.

-- On the replica, briefly stop replication for a perfectly consistent snapshot
STOP REPLICA;
-- Take the backup here (mysqldump or XtraBackup)
START REPLICA;

I’m careful never to treat replication alone as a backup strategy — a replica faithfully replicates an accidental DROP TABLE just as quickly as it replicates legitimate writes. Replication protects against hardware failure, not against human or application error; a real point-in-time-recoverable backup is still required.

Method 4: Binary Log Backups for Point-in-Time Recovery

Full backups alone only let you recover to the moment the backup was taken. I always back up binary logs continuously as well, so I can replay transactions between the last full backup and any point in time up to the moment of an incident.

[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_expire_logs_seconds = 604800  ; keep 7 days

# Copy binary logs to backup storage regularly, in addition to full backups
rsync -av /var/log/mysql/mysql-bin.* /backups/binlogs/

Backup Architecture Diagram

flowchart TD
    A[Production MySQL Server] --> B[Replica Server]
    B --> C[Nightly Full Backup - mysqldump or XtraBackup]
    A --> D[Continuous Binary Log Archiving]
    C --> E[Offsite/Cloud Storage]
    D --> E
    E --> F[Tested Restore Drill - Quarterly]

Automating Backups with Cron

#!/bin/bash
# backup_mysql.sh
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups"

mysqldump --single-transaction --routines --triggers --events \
  -u backup_user -p"$(cat /etc/mysql/backup_password)" mydatabase \
  | gzip > "$BACKUP_DIR/mydatabase_$DATE.sql.gz"

# Remove backups older than 14 days
find "$BACKUP_DIR" -name "mydatabase_*.sql.gz" -mtime +14 -delete

# crontab entry: run daily at 2 AM
0 2 * * * /usr/local/bin/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1

I always store the backup password in a restricted-permission file or a secrets manager, never directly in a script or crontab entry, and I log every run’s success/failure to somewhere I actually monitor.

Choosing Backup Frequency and Retention

I calibrate this per system based on actual RPO/RTO requirements agreed with stakeholders, not a one-size-fits-all default.

Real-World DBA Scenarios

• Pre-migration safety net: I always take a fresh full backup immediately before any schema migration or major deployment, regardless of the regular backup schedule.
• Compliance requirements: regulated industries (finance, healthcare) often mandate specific retention periods and encrypted, offsite backup storage — I design retention policy around the strictest applicable requirement.
• Multi-region disaster recovery: replicating backups to a geographically separate region/cloud provider so a regional outage doesn’t take out both the primary database and its only backup copy.
• Cost-conscious storage tiers: moving older backups to cheaper cold storage (like S3 Glacier) once they age past the “likely to be needed quickly” window, while keeping recent backups on fast-access storage.

Security Considerations

• Backup files often contain the same sensitive data as the live database — they need equivalent encryption at rest, access controls, and audit logging, not lighter treatment just because they’re “just backups.”
• I encrypt backup files (gpg or a cloud provider’s server-side encryption) both in transit to storage and at rest.
• Backup credentials get their own dedicated, least-privilege MySQL account (SELECT, LOCK TABLES, RELOAD, REPLICATION CLIENT — nothing more) rather than reusing an administrative account.

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongBackupPass!2026';
GRANT SELECT, LOCK TABLES, RELOAD, PROCESS, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';

Common Mistakes

• Backing up only the data directory files while MySQL is running, without a proper hot-backup tool, resulting in inconsistent, unusable backup files.
• Forgetting --routines, --triggers, and --events in mysqldump, silently losing critical database objects.
• Storing backups on the same physical server/disk as the production database, so a single disk failure destroys both the data and its backup.
• Never actually testing a restore, discovering the backup is corrupted or incomplete only during a real emergency (see my companion article on restoring backups for why this is non-negotiable).
• Ignoring binary log backups, leaving no way to recover to a point in time between full backups.

Troubleshooting Table

FAQs

What’s the difference between a logical and a physical backup? A logical backup (mysqldump) exports data as SQL statements; a physical backup (XtraBackup) copies the actual database files directly, which is much faster to restore for large databases.

Does mysqldump lock my tables during backup? By default it can, but adding --single-transaction avoids locking for InnoDB tables by using a consistent MVCC snapshot instead.

Is replication a substitute for backups? No — replication protects against hardware failure but faithfully replicates human errors like accidental deletes; you still need real point-in-time-recoverable backups.

How long should I retain backups? It depends on your RPO/RTO requirements and any compliance obligations — I typically recommend at least 14-30 days for standard applications and longer for regulated/financial data.

Should backups be stored on the same server as the database? No — always store backups on separate storage, ideally in a different physical location or cloud region, so a single failure can’t destroy both the live data and its backup.

Interview Questions

1. What’s the difference between logical and physical MySQL backups, and when would you choose each?
2. Why is --single-transaction important when using mysqldump on InnoDB tables?
3. How does binary log archiving enable point-in-time recovery?
4. Why shouldn’t replication alone be considered a backup strategy?
5. What privileges should a dedicated backup account have, following least privilege?
6. How would you design a backup retention policy for a financial application versus a low-traffic internal tool?
7. What’s the benefit of taking backups from a replica instead of the primary server?

Optimization Tips

• Use --single-transaction with mysqldump for InnoDB to avoid locking production traffic during backup.
• Prefer physical backups (XtraBackup) over logical (mysqldump) for very large databases to minimize both backup and restore time.
• Take backups from a replica rather than the primary to offload I/O from the server handling live traffic.
• Compress backups immediately (gzip or built-in compression flags) to save storage and transfer time, especially for offsite/cloud storage.
• Automate retention cleanup so old backups don’t silently consume all available disk space.

Summary and Key Takeaways

A solid MySQL backup strategy layers logical backups (mysqldump) or physical backups (XtraBackup) for full recoverability, continuous binary log archiving for point-in-time recovery, and replication to reduce backup impact on production traffic — with clear retention policies calibrated to actual business RPO/RTO requirements. None of this matters, though, unless it’s actually tested: a backup that’s never been restored is only a hypothesis. The single practice that’s protected me the most over the years is treating backups as a designed, monitored, regularly-drilled system rather than a cron job I set up once and forgot about.

References

• MySQL 8.0 Reference Manual — Backup and Recovery
• MySQL 8.0 Reference Manual — mysqldump — A Database Backup Program
• MySQL 8.0 Reference Manual — The Binary Log
• Percona XtraBackup Documentation