How to perform a MariaDB backup to secure your data

Backups are essential for the secure operation of any database. A system failure, problematic update or security breach can permanently delete data. In this tutorial, you’ll learn how to protect your MariaDB databases and how to perform a complete MariaDB backup.

What are the two types of MariaDB backups?

MariaDB distinguishes between two types of backups:

• Logical backups store data in an SQL-executable format. These backups contain instructions like CREATE TABLE and INSERT INTO, which can restore both the structure and contents of the database. Logical backups are ideal for transferring data between systems, versions or even different database types.
• Physical backups copy files and directories directly from the file system. This method is faster and more efficient, especially for large datasets. However, it is only reliable when servers, storage formats and MariaDB versions are identical.

For many use cases, combining both methods works best — such as using logical dumps for individual data or tables and physical backups for full and quick restores.

What options are available for creating a MariaDB backup?

MariaDB provides several tools for backing up data. Depending on your needs, data volume and system setup, different methods can be used.

mariadb-dump

mariadb-dump is the most widely used MariaDB backup tool. It creates logical backups and stores data as an SQL file. This file can later be imported using the MariaDB client.

Advantages:

• Works on any system with MariaDB client
• Backs up tables, individual or entire databases
• Compatible with other MariaDB and MySQL versions

Disadvantages:

• Large datasets can significantly increase backup and recovery times
• Performance spikes may occur during the backup process

mariadb-backup

mariadb-backup secures data physically. Based on Percona XtraBackup, it has been customized for MariaDB. It supports both encryption and compression.

Advantages:

• Fast and efficient for large datasets
• Supports live backups (hot backup)
• Ideal for production systems using InnoDB tables

Disadvantages:

• Works only with InnoDB (not with MyISAM tables)
• Requires more setup and effort during restoration

Additional options

• mariadb-hotcopy: An older tool for MyISAM tables (no longer maintained)
• LVM snapshots: System-wide backup at the filesystem level. Very fast, but complex
• dbForge Studio: A graphical solution for Windows systems with a user-friendly interface

How to perform a MariaDB backup with mariadb-dump

You’ll need access to the database server and a user account with backup permissions (for example, root). Open a terminal or SSH connection and make sure the mariadb-dump command is installed and available in your path.

Back up all databases

Run the following command:

mariadb-dump -u root -p --all-databases --result-file=/backups/backup_all.sql

Explanation:

• -u root: Specifies the root user.
• -p: Prompts for a password.
• --all-databases: Backs up all existing databases.
• --result-file: Specifies the file where the backup will be saved.

This backup includes all databases including user management, triggers, views and system tables.

Back up a specific database

The following command --databases shop_db backs up only the specified database.

mariadb-dump -u root -p --databases shop_db --result-file=/backups/shop_db.sql

If you don’t want the CREATE DATABASE statement in the dump, use this command instead:

mariadb-dump -u root -p shop_db --result-file=/backups/shop_db.sql

In this case, you need to create the database manually during restoration.

Back up multiple databases

To back up multiple databases, list them after the --databases option. This will generate a SQL file with instructions for each of the databases you’ve listed.

mariadb-dump -u root -p --databases db1 db2 db3 --result-file=/backups/multiple.sql

Back up individual tables

Specify the database and the tables you want to back up in the command. This is useful for targeted exports, such as when migrating individual tables.

mariadb-dump -u root -p db_name table1 table2 --result-file=tables.sql

Possible options for mariadb-dump

Automate backups with Cron

You can automate daily backups using a CronJob. To do this, open the Cron table:

crontab -e

Insert this line to create a backup daily at 3 AM, for example:

0 3 *** mariadb-dump -u root -p password --all-databases --result-file=/backups/backup-$(date +\%F).sql

For security reasons, do not store passwords directly in the Cron table. Instead, create a configuration file .my.cnf in the home directory:

[client]
user=root
password=password

Set the access rights:

chmod 600 ~/.my.cnf

Then use this variant in CronJobs without the password:

mariadb-dump --all-databases --result-file=/backups/backup-$(date +\%F).sql

Use mariadb-dump for flexible, system-independent backups and smaller datasets. For production systems with large datasets, mariadb-backup is recommended. Regularly back up and test your backups with restorations. Only a verified backup can reliably protect against data loss.