DEV Community

Cover image for MariaDB backup and restore — Complete guide to MariaDB database backup strategies
Piter Adyson
Piter Adyson

Posted on

MariaDB backup and restore — Complete guide to MariaDB database backup strategies

#mariadb #database

Database backups are your safety net against data loss, hardware failures and human errors. MariaDB, being a critical component of many production systems, requires a solid backup strategy that balances reliability, performance and recovery speed. This guide will walk you through the most effective MariaDB backup strategies, from simple mysqldump commands to advanced physical backups with Mariabackup.

MariaDB backup and restore

Understanding MariaDB backup types

MariaDB offers two primary backup approaches, each with distinct characteristics. Logical backups export data as SQL statements, while physical backups copy the actual database files. Understanding these differences helps you choose the right strategy for your infrastructure.

Logical vs physical backups

Backup Type Speed Storage Size Flexibility Hot Backup Best For
Logical (mysqldump) Slow Larger High No (read-only) Small databases, cross-platform
Physical (Mariabackup) Fast Smaller Low Yes Large databases, minimal downtime

Logical backups are portable and human-readable, making them ideal for moving data between different MariaDB versions or platforms. Physical backups are faster and support hot backups, but require identical MariaDB versions for restoration. For production systems with databases larger than 10GB, physical backups typically provide better performance.

Backup with mysqldump

The mysqldump utility is the most common tool for MariaDB backups. It generates SQL statements that recreate your database structure and data. While not the fastest option, mysqldump works reliably across all MariaDB versions and requires no additional tools.

Basic mysqldump syntax

Create a complete database backup with this command:

mysqldump -u username -p database_name > backup.sql
Enter fullscreen mode Exit fullscreen mode

For multiple databases:

mysqldump -u username -p --databases db1 db2 db3 > backup.sql
Enter fullscreen mode Exit fullscreen mode

For all databases including system databases:

mysqldump -u username -p --all-databases > backup.sql
Enter fullscreen mode Exit fullscreen mode

Important mysqldump options

Here are the most useful options for production backups:

  • --single-transaction: Creates consistent snapshot without locking tables (InnoDB only)
  • --quick: Retrieves rows one at a time instead of loading entire result set
  • --skip-lock-tables: Prevents table locking during backup
  • --routines: Includes stored procedures and functions
  • --triggers: Includes triggers in the backup
  • --events: Includes scheduled events

Production-ready backup command:

mysqldump -u backup_user -p \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  --databases production_db > backup_$(date +%Y%m%d_%H%M%S).sql
Enter fullscreen mode Exit fullscreen mode

Restoring from mysqldump

Restore a backup by redirecting the SQL file to the mysql client:

mysql -u username -p database_name < backup.sql
Enter fullscreen mode Exit fullscreen mode

For multiple databases or full system restore:

mysql -u username -p < backup.sql
Enter fullscreen mode Exit fullscreen mode

The restore process executes each SQL statement sequentially, which can take considerable time for large databases. Always test restoration procedures on a non-production system first to verify backup integrity and estimate recovery time.

Physical backups with Mariabackup

Mariabackup is MariaDB's enterprise-grade physical backup tool, designed for hot backups with minimal performance impact. It creates consistent snapshots while your database remains online and accessible to applications.

Installing Mariabackup

Mariabackup comes with MariaDB Server packages:

Debian/Ubuntu:

sudo apt-get install mariadb-backup
Enter fullscreen mode Exit fullscreen mode

CentOS/RHEL:

sudo yum install MariaDB-backup
Enter fullscreen mode Exit fullscreen mode

Docker:

docker exec mariadb mariabackup --version
Enter fullscreen mode Exit fullscreen mode

Creating backups with Mariabackup

Basic backup command:

mariabackup --backup \
  --target-dir=/backup/full-$(date +%Y%m%d) \
  --user=backup_user \
  --password=your_password
Enter fullscreen mode Exit fullscreen mode

After creating the backup, you must prepare it before restoration:

mariabackup --prepare --target-dir=/backup/full-20260111
Enter fullscreen mode Exit fullscreen mode

The preparation phase applies transaction logs and makes the backup consistent. Skip this step and your backup won't restore properly.

Incremental backups

Incremental backups save only the changes since the last backup, reducing storage requirements and backup time:

Full backup:

mariabackup --backup \
  --target-dir=/backup/full \
  --user=backup_user \
  --password=your_password
Enter fullscreen mode Exit fullscreen mode

Incremental backup:

mariabackup --backup \
  --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full \
  --user=backup_user \
  --password=your_password
Enter fullscreen mode Exit fullscreen mode

Preparing incremental backups:

mariabackup --prepare --target-dir=/backup/full
mariabackup --prepare --target-dir=/backup/full --incremental-dir=/backup/inc1
Enter fullscreen mode Exit fullscreen mode

Restoring from Mariabackup

Stop MariaDB, remove the old data directory and copy the backup:

sudo systemctl stop mariadb
sudo rm -rf /var/lib/mysql/*
sudo mariabackup --copy-back --target-dir=/backup/full
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mariadb
Enter fullscreen mode Exit fullscreen mode

The copy-back operation restores all database files to their original locations. Verify the restoration by connecting to MariaDB and checking your databases and tables.

Automated backups with Databasus

For teams and production systems, manual backups introduce risk through human error and inconsistency. Databasus is a free, open source backup management tool that automates MariaDB backups with scheduled runs, multiple storage destinations and team notifications.

Setting up Databasus

Install Databasus using Docker:

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

Or with Docker Compose:

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

Access the web interface at http://localhost:4005 and create an account.

Creating automated backups

Navigate to "New Database" and configure your MariaDB connection:

  1. Add database connection: Enter your MariaDB host, port, username, password and database name
  2. Select storage destination: Choose from local storage, S3, Google Drive, Dropbox, FTP or other supported storages
  3. Configure schedule: Set hourly, daily, weekly, monthly or cron-based backup intervals
  4. Add notifications (optional): Configure Slack, Discord, Telegram or email notifications for backup status
  5. Save and activate: Databasus validates your settings and starts the backup schedule

Databasus handles compression, encryption and retention policies automatically, removing the complexity of backup management while providing enterprise-grade reliability.

Binary log backups

Binary logs record all database changes and enable point-in-time recovery. While not a complete backup solution, binary logs complement full backups by allowing recovery to any specific moment between backup runs.

Enabling binary logging

Edit /etc/mysql/mariadb.conf.d/50-server.cnf:

[mysqld]
log_bin = /var/log/mysql/mariadb-bin
expire_logs_days = 7
max_binlog_size = 100M
Enter fullscreen mode Exit fullscreen mode

Restart MariaDB:

sudo systemctl restart mariadb
Enter fullscreen mode Exit fullscreen mode

Backing up binary logs

MariaDB automatically rotates binary logs based on size and retention settings. Archive old logs before deletion:

mysqlbinlog mariadb-bin.000001 > binlog_backup.sql
Enter fullscreen mode Exit fullscreen mode

For multiple log files:

mysqlbinlog mariadb-bin.000001 mariadb-bin.000002 mariadb-bin.000003 > binlog_backup.sql
Enter fullscreen mode Exit fullscreen mode

Point-in-time recovery

Restore your base backup, then apply binary logs up to the desired point:

mysql -u root -p < full_backup.sql
mysqlbinlog mariadb-bin.000001 mariadb-bin.000002 | mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

Stop at a specific timestamp:

mysqlbinlog --stop-datetime="2026-01-11 14:30:00" mariadb-bin.000002 | mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

This technique recovers data up to seconds before a disaster, making it essential for financial applications and systems with strict data loss requirements.

Backup strategy recommendations

Database Size Primary Method Frequency Retention Additional
< 1GB mysqldump Daily 30 days Weekly offsite
1-10GB mysqldump Daily 14 days Binary logs + weekly offsite
10-100GB Mariabackup Daily 7 days Binary logs + incremental
> 100GB Mariabackup Daily full + hourly incremental 7 days full, 3 days incremental Binary logs + offsite

Choose your backup frequency based on how much data you can afford to lose. A daily backup means potentially losing 24 hours of data. If that's unacceptable, increase frequency or implement binary log archival for point-in-time recovery.

The 3-2-1 backup rule

Always follow the 3-2-1 backup principle:

  • 3 copies of your data: production database, local backup and offsite backup
  • 2 different media types: local disk and cloud storage
  • 1 offsite location: different physical location from your primary data center

This strategy protects against hardware failure, data center disasters and ransomware attacks. Cloud storage services like AWS S3, Google Cloud Storage or Backblaze B2 provide cost-effective offsite backup destinations.

Testing backup restoration

Backups are worthless if they don't restore. Test your restoration procedure regularly to verify backup integrity and train your team.

Regular restoration tests

Schedule quarterly restoration tests:

  1. Select a random backup from the previous month
  2. Restore to a test environment
  3. Verify database integrity with consistency checks
  4. Measure restoration time and document the process
  5. Update runbooks based on findings

Run consistency checks after restoration:

mysqlcheck -u root -p --all-databases --check
Enter fullscreen mode Exit fullscreen mode

Measuring recovery objectives

Document your recovery metrics:

  • RTO (Recovery Time Objective): Maximum acceptable downtime during recovery
  • RPO (Recovery Point Objective): Maximum acceptable data loss measured in time

If your RTO is 1 hour, your restoration process must complete within 60 minutes. If your RPO is 15 minutes, you need backup frequency or binary logs that capture changes every 15 minutes.

Backup security and encryption

Backups contain sensitive data and require protection equivalent to your production database. Implement encryption for backup files and secure storage access.

Encrypting mysqldump backups

Encrypt backup files immediately after creation:

mysqldump -u backup_user -p production_db | \
  gzip | \
  openssl enc -aes-256-cbc -salt -pbkdf2 -out backup_encrypted.sql.gz.enc
Enter fullscreen mode Exit fullscreen mode

Decrypt during restoration:

openssl enc -aes-256-cbc -d -pbkdf2 -in backup_encrypted.sql.gz.enc | \
  gunzip | \
  mysql -u root -p production_db
Enter fullscreen mode Exit fullscreen mode

Mariabackup encryption

Enable encryption in Mariabackup:

mariabackup --backup \
  --target-dir=/backup/encrypted \
  --encrypt=AES256 \
  --encrypt-key-file=/etc/mysql/backup.key \
  --user=backup_user \
  --password=your_password
Enter fullscreen mode Exit fullscreen mode

Store encryption keys separately from backups. If an attacker gains access to your backups but not the encryption keys, your data remains protected.

Common backup mistakes to avoid

Even experienced administrators make backup mistakes that lead to data loss. Learn from common errors:

  • Not testing restores: A backup you can't restore is useless
  • Storing backups on the same server: Hardware failure destroys both database and backups
  • Ignoring backup logs: Failed backups go unnoticed until you need them
  • No backup verification: Corrupted backups discovered during emergencies
  • Insufficient retention: Deleting backups before corruption is discovered
  • No documentation: Team members can't restore during emergencies

Set up monitoring alerts for backup failures. Use backup verification tools to detect corruption early. Document restoration procedures and train team members on emergency recovery.

Conclusion

A robust MariaDB backup strategy combines multiple techniques to balance recovery speed, storage efficiency and data protection. Start with mysqldump for simplicity, graduate to Mariabackup for performance and add binary logging for point-in-time recovery as your requirements grow.

Remember that backups are insurance against the unexpected. Invest time in testing restoration procedures, automate backup processes to eliminate human error and always maintain offsite copies. When disaster strikes, your preparation determines whether you experience minor inconvenience or catastrophic data loss.

Top comments (0)