MySQL backup and restore — Complete guide to MySQL database backup strategies in 2026

Database backups are critical for protecting your data against hardware failures, human errors, security breaches and disasters. MySQL offers several backup methods, each with specific use cases and trade-offs. This guide covers logical backups with mysqldump, physical backups with file copying and MySQL Enterprise Backup, binary log backups for point-in-time recovery and automated backup solutions. Understanding these strategies will help you choose the right approach for your infrastructure and recovery requirements.

Understanding MySQL backup types

MySQL supports two primary backup approaches: logical and physical backups. Logical backups export database content as SQL statements that recreate the data, while physical backups copy the actual database files from disk. Each method has distinct advantages and limitations that affect backup speed, storage requirements and restoration flexibility.

Logical backups created with mysqldump are portable across MySQL versions and platforms, making them ideal for migrations and development workflows. Physical backups are faster for large databases and support features like point-in-time recovery when combined with binary logs. The choice depends on your database size, downtime tolerance and recovery objectives.

Backup type	Speed	Size	Flexibility	Best for
Logical (mysqldump)	Slow for large DBs	Compressed output	High portability	Small to medium databases, migrations
Physical (file copy)	Fast	Large disk space	Version-specific	Large databases, fast recovery
Binary logs	Continuous	Moderate	Point-in-time recovery	Mission-critical systems

Logical backups with mysqldump

The mysqldump utility creates logical backups by generating SQL statements to recreate database structures and data. This method works for databases of any size but becomes slower as data volume increases. For production systems, consider using the --single-transaction flag with InnoDB tables to create consistent backups without locking tables.

Basic mysqldump syntax for backing up a single database:

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

For backing up all databases at once:

mysqldump -u root -p --all-databases --single-transaction > all_databases.sql

Key mysqldump options for production backups:

• --single-transaction: Creates consistent backup for InnoDB without locking tables
• --routines: Includes stored procedures and functions
• --triggers: Includes table triggers
• --events: Includes scheduled events
• --master-data=2: Records binary log position (useful for replication)

Restoring from mysqldump backups is straightforward:

mysql -u root -p database_name < backup.sql

Physical backups with file copying

Physical backups involve copying MySQL data directory files directly from disk. This method is significantly faster than mysqldump for large databases but requires stopping MySQL or using specialized tools to ensure consistency. The simplest approach is shutting down MySQL, copying the data directory and restarting the service.

For InnoDB tables, you cannot simply copy files while MySQL is running because InnoDB uses a shared tablespace and transaction logs. MySQL Enterprise Backup and Percona XtraBackup solve this by creating hot backups without downtime. These tools are essential for production environments where stopping the database is not an option.

File-based backup procedure (requires downtime):

systemctl stop mysql
cp -r /var/lib/mysql /backup/mysql-$(date +%Y%m%d)
systemctl start mysql

Physical backups are database version-specific and less portable than logical backups. You cannot restore a MySQL 8.0 physical backup to MySQL 5.7. However, restoration is much faster because you're copying files rather than executing SQL statements. This makes physical backups ideal for disaster recovery scenarios where minimizing downtime is critical.

Using binary logs for point-in-time recovery

Binary logs record all changes to your MySQL databases, enabling point-in-time recovery (PITR) and replication. When combined with full backups, binary logs allow you to restore your database to any specific moment in time. This is crucial for recovering from logical errors like accidentally deleted records or corrupted data.

Enable binary logging in MySQL configuration:

[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1
binlog_format=ROW

After enabling binary logging, MySQL creates sequential log files that capture every data modification. You should regularly archive these logs to separate storage to prevent disk space issues. The typical recovery workflow involves restoring a full backup and then replaying binary logs up to the desired recovery point.

Binary log recovery example:

# Restore full backup
mysql -u root -p < full_backup.sql

# Apply binary logs up to specific time
mysqlbinlog --stop-datetime="2026-01-08 14:30:00" \
  /var/log/mysql/mysql-bin.* | mysql -u root -p

Binary logs grow continuously and require management. Use the expire_logs_days setting to automatically purge old logs, but ensure you've backed them up first. For high-traffic databases, binary logs can consume significant disk space and I/O bandwidth. Monitor your log volume and adjust retention policies accordingly.

Automated backup strategies

Manual backups are prone to human error and inconsistency. Production databases require automated backup schedules that run without intervention and verify backup integrity. You can automate mysqldump using cron jobs, but this approach lacks features like storage management, encryption, notifications and centralized monitoring across multiple databases.

Modern backup tools provide scheduling, multiple storage destinations (S3, Google Drive, FTP), compression, encryption and real-time notifications about backup status. For MySQL databases, MySQL backup tools like Databasus offer a complete solution for both individuals and enterprises, with an intuitive interface for managing backups across multiple databases without writing custom scripts.

Installing Databasus with Docker

Databasus can be installed quickly using Docker or Docker Compose. The Docker installation is straightforward:

docker run -d \
  --name databasus \
  -p 4005:4005 \
  -v ./databasus-data:/databasus-data \
  --restart unless-stopped \
  databasus/databasus:latest

For Docker Compose, create a docker-compose.yml file:

services:
  databasus:
    container_name: databasus
    image: databasus/databasus:latest
    ports:
      - "4005:4005"
    volumes:
      - ./databasus-data:/databasus-data
    restart: unless-stopped

Then start the service:

docker compose up -d

Creating your first MySQL backup

After installation, access the Databasus dashboard at http://localhost:4005 and follow these steps:

1. Add your database: Click "New Database" and enter your MySQL connection details (host, port, username, password, database name)
2. Select storage: Choose where to store backups (local storage, S3, Google Drive, FTP, etc.)
3. Select schedule: Configure backup frequency (hourly, daily, weekly, monthly or custom cron)
4. Create backup: Click "Create backup" and Databasus will validate the connection and start the backup schedule

This approach eliminates the operational overhead of maintaining custom backup scripts and provides visibility into backup health across your entire database infrastructure. Automated verification and alerting ensure you know immediately when backups fail, preventing data loss scenarios where backups silently stopped working weeks ago.

Backup frequency and retention policies

Backup frequency should match your recovery point objective (RPO) — the maximum acceptable data loss in case of failure. A daily backup schedule means you could lose up to 24 hours of data. Critical systems often require hourly backups or continuous binary log archiving to minimize potential data loss.

Retention policies determine how long you keep backups before deletion. Common strategies include keeping daily backups for 7 days, weekly backups for 4 weeks and monthly backups for 12 months. This provides multiple recovery points while managing storage costs. Compliance requirements may mandate longer retention periods for certain data.

Database type	Backup frequency	Retention	Recovery objective
Development	Daily	7 days	24 hours acceptable
Production (low traffic)	Daily + binary logs	30 days	< 1 hour
Production (high traffic)	Hourly + binary logs	90 days	< 15 minutes

Consider the 3-2-1 backup rule: maintain 3 copies of your data, on 2 different storage types, with 1 copy offsite. For MySQL, this might mean keeping one backup on your server, one on network-attached storage and one in cloud storage like S3. This protects against local disasters, hardware failures and ransomware attacks.

Testing backup restoration

Untested backups are worthless. Regular restoration testing ensures your backups are valid and your team knows the recovery procedure. Schedule quarterly or monthly restoration drills where you restore backups to a test environment and verify data integrity. Document the process and measure how long restoration takes.

Common backup failures include:

• Incomplete backups due to timeout or disk space issues
• Corrupt backup files that fail during restoration
• Missing binary logs needed for point-in-time recovery
• Incorrect permissions preventing database access after restoration
• Version incompatibility between backup source and restore target

Automated backup solutions should include verification steps that validate backup integrity immediately after creation. This catches corruption early rather than discovering it during an emergency recovery. Test restores should verify not just that tables are present, but that data is complete and consistent.

Backup security and encryption

Database backups contain sensitive information and must be protected with encryption. Use MySQL's built-in encryption features or encrypt backup files before storing them. AES-256 encryption provides strong protection against unauthorized access. Never store unencrypted database backups in cloud storage or unsecured network locations.

Key security practices for MySQL backups:

• Encrypt backup files with strong encryption (AES-256)
• Store encryption keys separately from backups
• Use secure transfer protocols (SFTP, HTTPS) for offsite backups
• Restrict backup file permissions to essential users only
• Rotate encryption keys periodically
• Audit backup access logs for suspicious activity

Backup credentials should use dedicated MySQL users with minimal required privileges. Create a backup-specific user with SELECT and LOCK TABLES permissions only. Avoid using root credentials in automated backup scripts. Store database passwords securely using environment variables or secret management systems rather than hardcoding them in scripts.

Choosing the right backup strategy

The optimal backup strategy depends on your database size, recovery requirements, available storage and budget. Small databases under 10GB work well with daily mysqldump backups to cloud storage. Larger databases benefit from physical backups combined with binary log archiving for point-in-time recovery. Mission-critical systems require automated backups with instant notifications and verified restoration procedures.

Start with a basic backup plan and improve it as your needs grow. A simple daily mysqldump backup is better than no backup at all. As your database becomes more critical, add features like offsite storage, encryption, hourly schedules and restoration testing. Monitor backup completion times and storage consumption to catch issues before they become problems.

For teams managing multiple MySQL databases, centralized backup management reduces operational complexity and ensures consistent backup practices across all systems. Modern backup tools eliminate the need for custom scripts and provide visibility into backup health across your entire infrastructure. This is especially valuable for organizations where database administration is distributed across multiple team members.

Remember that backups are insurance against disaster. The cost of implementing proper backups is minimal compared to the cost of data loss. Invest time in setting up reliable automated backups, test them regularly and document your recovery procedures. When disaster strikes, your investment will pay for itself many times over.