Why MySQL Backups Matter More Than Most People Realise

Every MySQL database holds something irreplaceable. Customer records, order history, content, application settings, and the relationships between all of those pieces live in tables that grow larger and more critical by the month. Hardware fails. Software has bugs. Accidental deletes happen. Without a tested backup, a single mistake or hardware fault can wipe out months or years of work.

mysqldump is the standard tool for backing up MySQL databases. It ships with MySQL, works on Linux, Windows, and macOS, and produces a plain SQL text file containing everything needed to rebuild a database from scratch. Understanding how to use it properly, and when to look at alternatives, is a fundamental skill for anyone running a MySQL-backed application in production.

The Basics of Mysqldump

The simplest backup command dumps an entire database to a SQL file. Open a terminal and run:

mysqldump -u root -p mydatabase > backup.sql

This connects to MySQL as the root user, dumps the database named mydatabase, and writes the output to backup.sql. You are prompted for the password interactively. The resulting file contains CREATE DATABASE and USE statements, followed by CREATE TABLE and INSERT statements for every table and row.

To restore from this backup, pass the SQL file through the MySQL client:

mysql -u root -p mydatabase < backup.sql

For large databases, compressing the backup during creation saves both disk space and transfer time. Pipe the output through gzip and include a date stamp in the filename:

mysqldump -u root -p mydatabase | gzip > backup_$(date +%Y%m%d).sql.gz

Including the date makes it straightforward to identify the right file when you have multiple backups in a directory and need to restore a specific point in time.

Backing Up All Databases on a Server

When you need to capture everything on the server, use the --all-databases flag. This produces a single dump file containing every database:

mysqldump -u root -p --all-databases > full_backup_$(date +%Y%m%d).sql

Restoring a full backup requires MySQL root privileges. The restore command recreates every database and overwrites existing data with the contents of the backup:

mysql -u root -p < full_backup_20260520.sql

Always confirm you have the correct file before running a full restore. There is no confirmation prompt, and the operation is destructive.

Selective Backups: Single Tables and Specific Databases

Large databases can take significant time and disk space to dump in full. Sometimes you only need a specific table or a subset of databases.

To dump only selected tables from a database, list them after the database name:

mysqldump -u root -p mydatabase users orders products > mydatabase_tables.sql

This captures only the users, orders, and products tables from mydatabase, which is useful when you need to restore a single corrupted table without touching the rest of the database.

To back up multiple specific databases, use the --databases flag:

mysqldump -u root -p --databases mydatabase1 mydatabase2 > two_databases.sql

This flag includes the CREATE DATABASE statements for each database, making the dump self-contained and easier to restore on a fresh MySQL installation.

Point-in-Time Recovery with Binary Logs

A standard mysqldump captures the database state at the moment the command starts running. Any changes made during the dump itself are not included. For production systems where data loss between backups is unacceptable, binary logging provides a continuous record of every change.

Enable binary logging in the MySQL configuration file:

log_bin = /var/log/mysql/mysql-bin
expire_logs_days = 7
max_binlog_size = 100M

After restarting MySQL with this configuration, every INSERT, UPDATE, DELETE, and DDL statement is written to the binary log. Combining a daily mysqldump with binary log backups gives you point-in-time recovery capability. You can restore to any moment between the last full backup and the present.

To restore to a specific time, first restore the most recent full backup, then replay the binary logs up to the target timestamp. The mysqlbinlog command reads the binary log and converts it back to SQL statements:

mysqlbinlog --stop-datetime="2026-05-20 14:30:00" /var/log/mysql/mysql-bin.000001 | mysql -u root -p

For more complex scenarios involving multiple binary log files, you may need to specify the starting position as well. Always test point-in-time recovery in a staging environment before relying on it in production. You can find more detail on this process in a dedicated MySQL point-in-time backup and recovery guide.

Mysqldump Options That Matter in Production

The default mysqldump output works fine for development, but production backups need additional options to ensure consistency and completeness.

--single-transaction creates a consistent backup without locking tables. It works by issuing a START TRANSACTION statement, which is supported by InnoDB, the default storage engine in MySQL 5.5 and later. This means the backup reflects a single moment in time without interrupting active database connections:

mysqldump -u root -p --single-transaction mydatabase > backup.sql

--master-data writes the binary log filename and position to the dump file. This information is essential for setting up replication or performing point-in-time recovery:

mysqldump -u root -p --single-transaction --master-data=2 mydatabase > backup.sql

The value 2 writes the binary log position as a comment in the dump file rather than executing it directly, which is safer for most use cases.

--routines and --triggers include stored procedures, functions, and triggers in the dump. These are easy to forget but are critical if your application relies on them:

mysqldump -u root -p \
  --single-transaction \
  --master-data=2 \
  --routines \
  --triggers \
  --events \
  mydatabase > backup.sql

A production backup command for an InnoDB database typically combines --single-transaction with --master-data=2, --routines, --triggers, and --events to capture everything the application depends on.

Compressing and Encrypting Backups

Compress backups immediately to save space, particularly for larger databases. The gzip compression ratio for SQL dumps is typically good because the files contain a lot of repeated text:

mysqldump -u root -p --single-transaction mydatabase | gzip > backup_$(date +%Y%m%d).sql.gz

For additional security, especially when storing backups off-server or in the cloud, encrypt the compressed file before uploading:

mysqldump -u root -p --single-transaction mydatabase | gzip | gpg --encrypt --recipient '[email protected]' > backup_$(date +%Y%m%d).sql.gz.gpg

Store the decryption key separately from the backup itself. If both the encrypted backup and the key are on the same compromised server, the encryption provides no real protection.

Automating Backups with Cron

Backups that depend on someone running a command manually eventually get missed. The most reliable backup strategy automates everything from the start. Add an entry to the crontab to run a backup script daily:

0 3 * * * /root/scripts/mysql_backup.sh

This runs the backup script at 3 AM every day. Choose a time when database activity is low to reduce the impact on production users.

A practical backup script that maintains a rolling seven-day retention window looks like this:

#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
DB_NAME="mydatabase"
DB_USER="root"
DB_PASS="password"
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"

mysqldump -u "$DB_USER" -p"$DB_PASS" \
  --single-transaction \
  --master-data=2 \
  --routines \
  --triggers \
  --events \
  "$DB_NAME" | gzip > "$BACKUP_DIR/${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql.gz"

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $BACKUP_DIR/${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql.gz"

The script creates a timestamped, compressed backup, then automatically deletes any backup files older than the retention period. Set the retention period based on your recovery point objective and available storage. A seven-day retention is common for daily backups, but some systems need longer windows.

Restoring a Database from Backup

Restoring is straightforward but must be done carefully. Always verify the backup in a test environment before touching production.

To restore a compressed backup, decompress it and pipe it through the MySQL client:

gunzip < backup_20260520.sql.gz | mysql -u root -p mydatabase

A safer approach restores to a differently named database first, so you can verify the data before promoting it to production:

gunzip < backup_20260520.sql.gz | mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mydatabase_restore"
gunzip < backup_20260520.sql.gz | mysql -u root -p mydatabase_restore

Check the row counts, verify specific records you know should be present, and confirm the restored database looks correct before switching production traffic to it.

Verifying Backup Integrity

A backup that cannot be restored is not a backup. This is a common oversight. Verify every backup can be restored, even if only to a temporary database on a staging server. The verification process also confirms that the backup script is running correctly and that the files being created are actually valid.

A straightforward verification script looks like this:

gunzip < backup_20260520.sql.gz | mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS backup_test"
gunzip < backup_20260520.sql.gz | mysql -u root -p backup_test
mysql -u root -p -e "SHOW TABLES FROM backup_test"

If the database has a known row count or specific records you can check, verify those after restoration. Compare the restored database against what you expect to see. Automating this verification process means problems get caught before a real restore is needed. A more complete approach to testing your disaster recovery procedures is covered in a separate guide on verifying backups and testing disaster recovery.

When Mysqldump Is Not Enough: Large Databases

mysqldump is single-threaded, which means it processes one table at a time. For databases larger than 50 GB, it can take hours to complete and generate an enormous SQL file that is slow to restore. MySQL Shell provides parallel dump and load utilities that use multiple threads to speed up the process significantly:

mysqlsh --dump -u root -p --destination=user@localhost:3306 --destination ~/backups/mysql --threads=8

For very large production databases, physical backups using tools like MySQL Enterprise Backup or Percona XtraBackup back up the actual data files rather than exporting SQL statements. This is faster to create and faster to restore, but requires more careful handling and storage engine compatibility.

The choice between logical backups (mysqldump) and physical backups depends on database size, recovery time requirements, and available tools. Many production environments use a combination: daily logical backups for point-in-time capability and weekly physical backups for faster full restores.

Off-Site and Cloud Backup Strategies

A backup stored on the same server as the database does not protect against hardware failure, ransomware, or accidental deletion. Copying backups to a different location is an essential part of any serious backup strategy. Integrate this into the backup script or run it as a separate step after the local backup completes.

# After creating local backup
rclone copy "$BACKUP_DIR/${DB_NAME}_$(date +%Y%m%d).sql.gz" \
  remote:bucket-name/mysql-backups/

# Or with AWS CLI
aws s3 cp "$BACKUP_DIR/${DB_NAME}_$(date +%Y%m%d).sql.gz" \
  s3://your-bucket/mysql-backups/

Encrypt files before uploading unless your cloud storage supports server-side encryption with keys you control. Always test the restore process from the off-site location to confirm it works correctly. Cloud storage options and what to look for in a cloud backup solution are covered in more detail in a guide on cloud backup solutions.

Backup Retention and Rotation Strategy

How long you keep backups depends on your recovery point objective, regulatory requirements, and available storage. A common rotation strategy combines daily, weekly, and monthly backups:

  • Daily backups: Keep for seven to fourteen days to handle recent accidental deletions or errors.
  • Weekly backups: Keep for four to six weeks to support recovery from problems not caught within the daily window.
  • Monthly backups: Keep for three to twelve months depending on business needs and any compliance requirements.

The retention script shown earlier implements a simple rolling window. More complex rotation schemes can be implemented with additional scripting or backup management software.

Common Backup Mistakes and How to Avoid Them

Several mistakes appear repeatedly when reviewing backup strategies. Knowing what to avoid saves time and reduces risk.

Storing backups on the same disk as the database is one of the most common errors. If the disk fails, you lose both the database and the backup simultaneously. Always store backups on separate storage, ideally on a different server or in cloud storage.

Forgetting to back up binary logs means losing the ability to do point-in-time recovery. Enable binary logging on any production MySQL server and include the binary log position in your dumps using --master-data=2.

Not testing restores is the mistake that bites hardest. A backup that has never been restored is an untested assumption. Schedule regular restore tests, at least quarterly, and document the results.

Using root credentials in scripts is convenient but risky. Create a dedicated MySQL user with only the privileges needed for backups:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

This limits what a compromised script can do if the credentials are exposed.

Putting It All Together

A reliable MySQL backup strategy combines several layers. Start with daily automated backups using mysqldump with the appropriate production flags. Enable binary logging to support point-in-time recovery. Store backups on separate storage, ideally off-site or in cloud storage. Test restores regularly to confirm everything works as expected.

The specific tools and retention periods matter less than having a consistent, tested process that runs automatically. Review your current backup setup and identify any gaps. A small amount of planning now can prevent significant problems later.