Ultimate Guide on How to Back Up MariaDB Database

Acronis
Acronis Cyber Protect Cloud
for service providers

Suppose your organization uses MariaDB to manage your existing databases as an alternative to MySQL. In that case, you probably already know that you have many different options for securing and backing up your databases or restoring them if you ever experience an issue. However, it's not always clear what choices you should make to maximize your security and best protect your company's assets.

To help, this article highlights many best practices and specific recommendations for critical parts of your MariaDB configuration. It will also offer a closer look at the best solution to protect mission-critical data — a well-designed combination of these configuration best practices and an application-aware, granular backup and restore system.

What is Mariabackup?

Mariabackup is an open-source tool created by MariaDB to initiate online backup for InnoDB, Aria, MyRocks, and MyISAM tables. The feature was originally forked from Percona XtraBackup 2.3.8 and also provides "hot online" backups (or "hot backups") for InnoB. Mariabackup is available on Windows and Linux.

  • --apply-log — Prepares current backup to restore MariaDB server.
  • --apply-log-only — Used to redo log apply stage.
  • --backup — Initiates MariaDB backup
  • --binlog-info — Specifies the path to retrieve binary log coordinates from the server.
  • --close-files — Used to determine whether to close file handles.
  • --compress — Enables backup compression.
  • --compress-chunk-size — Specifies the operational buffer size for compression threads.
  • --compress-threads — Specifies compression thread numbers.
  • --copy-back — Used for backup restore operations to the data directory.
  • --databases — Specifies table and database backups.

Mariabackup backup features

The MariaDB backup tool has various features to back up multiple databases quickly and efficiently. Users can set up replicas from a backup, encrypt and compress backups, and restore a single database or individual tables and partitions from a backup.

The tool supports the following backup types:

It also supports SST with Galera Cluster. MariaDB backup and restore can recover tables via data-at-rest encryption, InnoDB page compression, and MyRocks storage engine. Moreover, it minimizes locks during backup and ensures robust backup support for all MariaDB Enterprise Backup storage engines.

Pros

  • Free to use (open source)
  • Ensures high data consistency and integrity levels
  • Designed specifically for MariaDB databases
  • Easily set up via clear documentation and interface

Cons

  • Only supports backup and restore functions for MariaDB
  • May consume extensive system resources during backup operations for very large databases
  • Requires a certain level of command-line interface and backup procedure knowledge
  • Does not support Data-at-Rest Encryption and compression for full backups

How do I back up my MariaDB database?

There are three primary options to initiate a MariaDB database backup: Mariabackup, Mysqldump, and a dedicated backup and restore solution.

Mariabackup

Mariabackup can perform a full and incremental backup for the MariaDB database. To initiate a full database backup, you must run Mariabackup via the --backup option combined with the --target-dir option to specify the backup storage location. For full backups, the target directory must be empty or nonexistent (before initiating the backup). To create a full backup, run the following command:

$ mariabackup --backup \

--target-dir=/var/mariadb/backup/ \

--user=mariabackup --password=mypassword

The backup duration and size depend on the database or table volumes you wish to back up. You can abort the backup process as it doesn't modify the database. Mariabackup will write the backup into the target directory. If such a directory doesn't exist, the tool will create one for you. If the target directory exists but isn't empty, the tool will raise an error and abort the process. After you've created a full backup, you can initiate an incremental backup via the following command:

$ mariabackup --backup \

--target-dir=/var/mariadb/inc1/ \

--incremental-basedir=/var/mariadb/backup/ \

--user=mariabackup --password=mypassword

mariadb-dump backup file (mysqldump)

MariaDB provides a built-in backup tool, Mysqldump, to back up or transfer multiple databases in CSV, XML, or other delimited text.

To export all MariaDB databases, use the command: mysqldump -u xx -p -x -A > /data/backup/dbs.sql

To dump multiple databases: shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sq or to dump all databases: shell> mysqldump --all-databases > all_databases.sql

To backup an entire database: shell> mysqldump db_name > backup-file.sql

To back up a single table: mysqldump --user=xx --password --lock-tables db1 table1 > /data/backup/db1_table1.sql

Acronis database backup

Acronis Cyber Protect Cloud has a dedicated agent for MySQL / MariaDB backups. Follow the steps below to perform backups on a machine in a consistent state:

  • For Windows, make sure the MySQL Tools folder is in Windows`%PATH% on the machine. Ensure the local connection is allowed for the MySQL client without explicitly passing credentials on the backed-up machine.
  • Download and unpack scripts to execute before and after snapshot creation.

Scripts for Linux / Scripts for Windows

The scripts will lock tables via the "FLUSH TABLES WITH READ LOCK;" command and unlock them after Acronis completes the snapshot.

  • Place config.sh into the scripts' working directory.
  • Apply the scripts

When backing up a physical machine, specify the scripts in the "Pre-post data capture commands" section of the backup plan options. When backing up a virtual machine (VMware) at the hypervisor level (agentless backup), you need to place the scripts in specific folders on the virtual machine. You can find directory directions listed in this article.

How to restore MariaDB database backup?

There's a critical step to complete before successfully restoring a MariaDB backup — Preparation.

Prepare the backup file for restoration

Data files created in the target directory by MariaDB aren't point-in-time consistent. Given that the information is copied at different times during the backup process, it needs to be brought up-to-date to ensure a successful restore. If you attempt to restore it without preparation, InnoDB identifies the inconsistencies and crashes to protect the system from corruption. To prepare a backup for recovery, you must ensure it's consistent via the --prepare option.

$ mariabackup --prepare \

--target-dir=/var/mariadb/backup/

Restore MariaDB database backup

Once you have a consistent backup (meaning preparation is complete), you can restore MariaDB data using either the --move-back or the --copy-back options. Remember that the --move-back option moves the backup to the datadir, meaning the original backup files will be lost.

  1. Stop the MariaDB Server process.
  2. Ensure that the datadir is empty.
  3. Run MariaDB with one of the two options discussed above:

$ mariabackup --copy-back \

--target-dir=/var/mariadb/backup/

  1. Fix file permissions.

When you restore a single database via Mariabackup, it preserves privileges (both file and directory) for the backup data. However, it will write the backup to disk as the user and group restoring the database. So, after a MariaDB or SQL restore, you might need to specify the data directory's owner to match the user/group. Typically, mysql for both. You can recursively change file ownership to the mysql user/group via the command:

$ chown -R mysql:mysql /var/lib/mysql/

What is the difference between logical and physical Mariadb backup?

Essentially, logical backups contain the necessary SQL statements to restore backups — e.g., CREATE TABLE, CREATE DATABASE, INSERT while physical backups are created by copying individual files and directories. Below, we will outline the primary differences between physical and logical backups.

  • Logical backups are more flexible

Logical backups can be restored on different MariaDB versions, hardware configurations or DBMS. On the other hand, you can't import physical backups on severely different hardware, different DBMS, or even a different MariaDB version.

  • You can perform logical backups on different levels

Logical backups can be performed on the database and table levels. On the other hand, physical backups assign databases to the directory and file levels. In the InnoDB storage engine (and MyISAM), each table in the database has an equivalent set of files. In versions prior to MariaDB 5.5, InnoDB files have a default number of stored tables, meaning you can't back up by table.

  • Logical backups are larger than a corresponding physical backup.
  • Logical backups take more time to backup and restore than a corresponding physical backup.
  • Logical backups exclude log backups and configuration files from the backup process.

MariaDB: Multiple databases backup tools

You can use several backup and restore options to secure MariaDB data.

Mariadb-backup

As discussed, Mariadb-backup is a fork of Percona XtraBackup. It supports data-at-rest encryption and MariaDB 10.1 compression.

mariadb-dump file

mariadb-dump (a newer version of mysqldump) performs logical backups. It's an excellent option for a relatively small data size, offering the most flexible backup and restore options. The mariadb-dump file can dump contents into SQL, CSV or XML formats to be imported into another database. You can export the file into other MariaDB database versions, MySQL, or another DBMS, as discussed. (the latter is valid if there are no DBMS-specific statements in the dump)

mariadb-dump can dump triggers and multiple tables as they're a part of the table definition. Nonetheless, it needs extra parameters to recreate stored procedures, events, and views. (e.g., --routines, --events)

InnoDB logical backups

The buffer pool is a critical InnoDB component for optimizing MariaDB. The buffer pool gathers information and indexes via table scans to store in its memory. If InnoDB volumes don't fit in the memory, the buffer pool must contain the most frequently accessed data. (with an argument that the last accessed data is also a candidate for the buffer pool) If you don't present InnoDB with proper configuration files, the feature may copy the complete table contents during table scans into the buffer pool. And as InnoDB performs a logical backup, it always implies full table scans.

How to export databases via MySQLdump?

Mysqldump is a console utility that easily exports databases to an SQL file. (text-based) You will need a database name and credentials for an account with at least a medium access level. (for example, you can use a full-read-only access account) To export the SQL dump, use this command:

$ mysqldump -u username -p database_name > data-dump.sql

The command won't produce visual output, but you can check the transaction log and data-dump.sql contents to ensure a legitimate SQL dump file.

Configuration options and considerations

There are many different configuration options to consider as you set up and maintain MariaDB. The following sections will discuss authentication, passwords, networking, Docker image usage, and additional considerations for future upgrades.

  • Authenticating without passwords

If your organization prefers to let users authenticate without passwords, you have a few options depending on your operating system (Linus or Windows).

1.       Linux: In Linux, use MariaDB's Unix_socket authentication plugin. This enables users to log in to MariaDB via the Unix socket, but only if the user's name matches the system user. Additionally, the root user is effectively configured to use the Unix socket authentication by default, which prevents the accidental granting of root access to outside connections via a listening port.

2.       Windows: In Windows environments, you can use the named pipe plugin, which provides similar functionality for Windows-based deployments — it allows users to use operating system credentials when connecting to MariaDB on Windows.

  • Multiple authentication methods

MariaDB allows for multiple authentication methods. This provides greater flexibility where users can authenticate using a Unix socket or, if that doesn't work, use a password.

  • Password complexity plugin

Additionally, you can install the cracklib-password-check plugin to ensure users create complex, non-trivial passwords at all times — an essential step in maximizing security.  

  • Thoughts on storing passwords

If you must store passwords, we suggest you follow these specific best practices:

  • Never store passwords in clear text.
  • Make sure server passwords are encrypted. Note that the default in MariaDB is sha 1, which is no longer considered secure.
  • Additionally, ed_25519 is not set by default but is highly recommended.
  • Secure MariaDB networking

If your deployment setup allows it, it may be better to run skip-networking on the server with a Unix socket whenever possible. Yet, if this is not possible, use specific hostnames for users to limit externally accepted IP addresses. Note that only connections coming from 192.168.1.10 will be accepted.

  • Create application-specific users

One scenario to note: The default deployment allows the application root access. Not only is this not necessary, but it's not a good idea since it may lead to vulnerabilities in the future. For example, in the case of a security bug in the application, the whole database server can be compromised. Instead, it would be best to create dedicated users and only grant rights as needed using the least privilege principle.

  • In the case of an untrusted network

In the case that you're dealing with an untrusted network, make sure that client-server communications are encrypted. This requires SSL certificates for the server to be generated and configured (use the CREATE USER command for information on the specified certificates.). All of this helps you connect locally without SSL required. At the same time, make sure to require SSL when connecting from an outside host.

  • MariaDB as a Docker image

MariaDB is also shipped as a Docker image. This means there are many ways to set up the server, including sharing the Unix socket via a volume or exposing the TCP/IP port (3306). For useful environment variables, you can use MARIADB_ROOT_PASSWORD/MYSQL_ROOT_ PASSWORD.

MariaDB upgrades

With MariaDB, backward compatibility is maintained across all versions. Features are rarely dropped; when they are, it's usually related to conflicts with the MySQL standard. You must ensure the MariaDB system is updated. This is important because MariaDB releases security fixes as soon as possible after Oracle's MySQL release (usually 1–2 days), so applying updates will ensure you have access to all these patches as quickly as possible.

What is the best solution to back up and restore MariaDB?

The best solution for protecting your valuable data and databases is a partnership between application best practices and an application-aware, granular backup and restore solution. Acronis delivers a single-pass backup to completely restore MariaDB and MySQL workloads and granular recovery of MySQL / MariaDB databases and tables if corruption occurs. The solution performs backups at the server, VM, database, or individual table levels. This becomes a significant advantage for users running multiple websites or applications simultaneously.

Hosting and cloud service providers will also appreciate that the Acronis Cyber Protect Cloud solution is fully configurable and deployable via the control panels and automation systems they use now — cPanel, Plesk, DirectAdmin, WHMCS and more. Combining cybersecurity, data protection, and backup yields better protection requiring fewer resources — critical to avoiding breaches and ensuring safe and malware-free recoveries. To learn more about the advantages of using Acronis Cyber Cloud as an MSP platform, visit our solutions for service providers page today.

About Acronis

Acronis is a Swiss company, founded in Singapore. Celebrating two decades of innovation, Acronis has more than 1,800 employees in 45 locations. The Acronis Cyber Protect Cloud solution is available in 26 languages in over 150 countries and is used by 20,000 service providers to protect over 750,000 businesses.