How to perform a MariaDB backup to secure your data

Backups are essential for the secure operation of any database. A system failure, prob­lem­at­ic update or security breach can per­ma­nent­ly 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 dis­tin­guish­es between two types of backups:

• Logical backups store data in an SQL-ex­e­cutable format. These backups contain in­struc­tions like CREATE TABLE and INSERT INTO, which can restore both the structure and contents of the database. Logical backups are ideal for trans­fer­ring data between systems, versions or even different database types.
• Physical backups copy files and di­rec­to­ries directly from the file system. This method is faster and more efficient, es­pe­cial­ly 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 in­di­vid­ual 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.

Ad­van­tages:

• Works on any system with MariaDB client
• Backs up tables, in­di­vid­ual or entire databases
• Com­pat­i­ble with other MariaDB and MySQL versions

Dis­ad­van­tages:

• Large datasets can sig­nif­i­cant­ly increase backup and recovery times
• Per­for­mance spikes may occur during the backup process

mariadb-backup

mariadb-backup secures data phys­i­cal­ly. Based on Percona Xtra­Back­up, it has been cus­tomized for MariaDB. It supports both en­cryp­tion and com­pres­sion.

Ad­van­tages:

• Fast and efficient for large datasets
• Supports live backups (hot backup)
• Ideal for pro­duc­tion systems using InnoDB tables

Dis­ad­van­tages:

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

Ad­di­tion­al options

• mariadb-hotcopy: An older tool for MyISAM tables (no longer main­tained)
• LVM snapshots: System-wide backup at the filesys­tem 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 per­mis­sions (for example, root). Open a terminal or SSH con­nec­tion 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

Ex­pla­na­tion:

• -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 man­age­ment, 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 restora­tion.

Back up multiple databases

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

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

Back up in­di­vid­ual 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 in­di­vid­ual 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 con­fig­u­ra­tion 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-in­de­pen­dent backups and smaller datasets. For pro­duc­tion systems with large datasets, mariadb-backup is rec­om­mend­ed. Regularly back up and test your backups with restora­tions. Only a verified backup can reliably protect against data loss.