Backup and Restore MariaDB with Mariabackup and Other Methods

MariaDB is a popular open-source relational database management system (RDBMS) that is designed to be a drop-in replacement for MySQL. It was created by the original developers of MySQL after concerns arose over its acquisition by Oracle Corporation.

MariaDB has high compatibility with MySQL and has the same library binary parity and matches the MySQL APIs and commands exactly. This makes the database migration from MySQL to MariaDB easier.

If you are using MariaDB, it is essential for you to know how to make MariaDB backups and restore them. In this article, you are going to understand backup and restore MariaDB with Mariabackup and the other two methods. Then, you may ask, what is Mariabackup?

What is Mariabackup?

Mariabackup, forked from Percona XtraBackup, is an open-source tool created by MariaDB that can be used to perform online backups of InnoDB, MyRocks, Aria, and MyISAM tables in a physical format.

Options:

Note: Go to the Mariabackup options page to learn more.

Features:

• Sets up a replica from a backup.

• Uses encryption and compression tools.

• Restores individual tables and partitions.

• Supports SST method with Glaera Cluster.

• Supports full, partial, and incremental backup and restore.

• Backups and restores tables using Data-at-Rest Encryption, MyRocks storage engine, and InnoDB Page Compression.

• Minimizes locks during backup and provides optimal backup support for all storage engines of MariaDB Enterprise Backup.

Pros:

• Open-source and free to use.

• Provides a high level of data consistency and integrity.

• Easy to set up and use with clear documentation and a user-friendly interface.

• Specifically designed for MariaDB, it is optimized for that particular database management system.

Cons:

• Only supports backup and restore operations for MariaDB.

• Do not support backup tool-based encryption and compact option.

• Requires some knowledge of command-line interfaces and backup procedures.

• Do not support compression and Data-at-Rest Encryption for full backup capability.

• Do not support partial backup of MyRocks data and incremental backup of it will store a full copy.

• May not be suitable for very large databases or for backup operations that need to be performed frequently, as it can consume a significant amount of system resources.

• Be careful not to include typos or use options from newer Mariabackup versions, as the software ignores unknown command-line options without giving an error message.

Install Mariabackup

On Linux:

Install with a package manager and execute as root:

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash

Install with yum/dnf for RHEL, CentOS, Fedora, and other similar Linux distributions:

sudo yum install MariaDB-backup

Install with apt-get for Debian, Ubuntu, and other similar Linux distributions:

sudo apt-get install mariadb-backup

Install with zipper for SLES, OpenSUSE, and other similar Linux distributions:

sudo zypper install MariaDB-backup

On Windows:

1.     Install the MariaDB MSI package.

2.     Click the mariadb-<major>.<minor>.<patch>.msi.

3.     Click Next on the welcome page and accept the license agreement, click Next.

4.     On the Custom Setup page, click Backup utilities to install.

5.     Follow the wizard.

Backup and Restore MariaDB with Mariabackup

Back up the whole database server:

$ mariabackup --backup

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

   --user=mariabackup --password=mypassword

Take an incremental backup (create a full backup first):

$ mariabackup --backup

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

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

   --user=mariabackup --password=mypassword

Prepare consistent backups for recovery:

$ mariabackup --prepare

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

Restore the full database:

a.     Stop the server process and ensure the datadir is empty.

b.     Run –copy-back to keep the original backup files or –move-back to move data to the datadir and the original files are lost.

$ mariabackup --copy-back

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

c.  Start the MariaDB server process.

Restore the changes in the first incremental backup and repeat the step for each remaining one with MariaDB 10.2:

a.     Prepare the base full backup.

$ mariabackup --prepare

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

     --incremental-dir=/var/mariadb/inc1

     b.  Bring the base into sync with changes.

      $ mariabackup --prepare

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

      --incremental-dir=/var/mariadb/inc1

c.     Stop the server process and ensure the datadir is empty.

d.     Run –copy-back to keep the original backup files or –move-back to move data to the datadir and the original files are lost.

$ mariabackup --copy-back

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

Restore incremental backup with MariaDB until 10.1:

a.     Prepare the base full backup.

$ mariabackup --prepare --apply-log-only

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

    b.  Apply increments to the base:

$ mariabackup --prepare --apply-log-only

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

    --incremental-dir=/var/mariadb/inc1

c.     Stop the server process and ensure the datadir is empty.

d.     Run –copy-back to keep the original backup files or –move-back to move data to the datadir and the original files are lost.

$ mariabackup --copy-back

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

Backup and Restore MariaDB with Mysqldump

Mysqldump is a MariaDB built-in backup tool to take the dump of one or more databases for backup or transfer in CSV, other delimited text, or XML format, which can also be used to backup and restore MySQL.

Export all MariaDB databases:

mysqldump -u xx -p -x -A > /data/backup/dbs.sql

Dump several and all databases:

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

shell> mysqldump --all-databases > all_databases.sql

Backup the entire database:

shell> mysqldump db_name > backup-file.sql

Backup single database:

mysqldump --user=xx --password --lock-tables --databases db1 > /data/backup/db1.sql

Backup one table:

mysqldump --user=xx --password --lock-tables db1 table1 > /data/backup/db1_table1.sql

Restore a backup:

mysql -u root -p < backup-file.sql

Backup and Restore MariaDB with A Database Backup Solution

Either way, the processes of backing up and restoring MariaDB are far from intuitive and user-friendly for a novice. Try Vinchin Backup & Recovery which is compatible with 12 virtualizations such as VMware, Hyper-V, Xen, RHEV/oVirt, etc., 2 physical servers, NAS, and 6 databases including Oracle DB, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB.

You can automate database backup with self-defined schedules and choose from full, differential, incremental, and log backups (vary with databases) with one click.

You can enable data deduplication and compression to reduce overall backup size and turn on data encryption with the AES-256 standard for security.

Restore MariaDB to the original or a new database destination and prepare an offsite backup copy in a remote location.

Download the 60-day free trial of the new release v7.0 now:

Easy MariaDB Backup Process:

1.     Go to Physical Backup> Database Backup> Backup and select the database backup agent installed and the backup source.

2.     Select the backup node and storage on the node for the job.

3.     Specify backup strategies as needed.

4.     Confirm and submit.

Easy MariaDB Recovery Process:

1.     Go to Physical Backup> Database Backup> Restore and select the recovery resource from database backups.

2.     Select the target instance.

3.     Restore to the original or a new destination and specify the restore strategies.

4.     Confirm and submit.

Wrap up

You can backup and restore MariaDB with Maiabackup and Mysqldump, but they lack an intuitive GUI and various backup choices as the professional databases backup solution Vinchin Backup & Recovery does. The tool automates data backup, recovery, and worry-free anti-malware measures and technologies to safeguard your MariaDB data overall.