How to Backup and Restore MariaDB Databases

This brief tutorial shows you how to backup and restore MariaDB databases and data files on Ubuntu 20.04 | 18.04.

MariaDB 10.1 introduced a new backup feature called Mariabackup.

Mariabackup is an an open source tool provided by MariaDB for performing physical online backups of InnoDB, Aria and MyISAM tables. It also enabled “hot online” backups for InnoDB tables.

Mariabackup doesn’t only back up databases, it also backs up many different files in order to perform its backup and restore operations.

To get started with backing up and restoring MariaDB data, follow the steps below:

Install Mariabackup

When you install MariaDB database server, its backup tool isn’t installed automatically.

If you want to implement and backup and restore process with MariaDB, you’ll want to install its backup tool.

Run the commands below to install it on Ubuntu.

sudo apt update
sudo apt-get install mariadb-backup

That should install the tool on Ubuntu.

Backing up MariaDB

Now that the tool is installed, you can run it with different options to perform a full backup and restore of MariaDB databases and data files on Ubuntu and other Linux systems.

First, run the commands below to create a backup directory to store MariaDB backup content.

sudo mkdir ~/mariadb_backup

This should create a backup folder in your home directory: /home/<username>/mariadb_backup.

After creating the backup folder above, simply run the commands below to create a live backup and store in the folder above.

sudo mariabackup --backup --target-dir ~/mariadb_backup -u root

When you run the commands above, it should complete with a success messages.

[00] 2021-01-07 12:28:44 >> log scanned up to (1625621)
 mariabackup: Stopping log copying thread
 [00] 2021-01-07 12:28:44 >> log scanned up to (1625621) [00] 2021-01-07 12:28:44 Executing UNLOCK TABLES
 [00] 2021-01-07 12:28:44 All tables unlocked
 [00] 2021-01-07 12:28:44 Copying ib_buffer_pool to /home/richard/mariadb_backup/ib_buffer_pool
 [00] 2021-01-07 12:28:44         …done
 [00] 2021-01-07 12:28:44 Backup created in directory '/home/richard/mariadb_backup/'
 [00] 2021-01-07 12:28:44 Writing backup-my.cnf
 [00] 2021-01-07 12:28:44         …done
 [00] 2021-01-07 12:28:44 Writing xtrabackup_info
 [00] 2021-01-07 12:28:44         …done
 [00] 2021-01-07 12:28:44 Redo log (from LSN 1625612 to 1625621) was copied.
 [00] 2021-01-07 12:28:44 completed OK!

Run the commands below to list the content of the backup folder.

ls -al ~/mariadb_backup/

It should list something similar to the lines below:

rwxrwxr-x  4 richard richard     4096 Jan  7 12:28 .
 drwxr-xr-x 16 richard richard     4096 Jan  7 09:39 ..
 -rw-r-----  1 root    root       16384 Jan  7 12:28 aria_log.00000001
 -rw-r-----  1 root    root          52 Jan  7 12:28 aria_log_control
 -rw-r-----  1 root    root         324 Jan  7 12:28 backup-my.cnf
 -rw-r-----  1 root    root         972 Jan  7 12:28 ib_buffer_pool
 -rw-r-----  1 root    root    12582912 Jan  7 12:28 ibdata1
 -rw-r-----  1 root    root        2560 Jan  7 12:28 ib_logfile0
 drwx------  2 root    root        4096 Jan  7 12:28 mysql
 drwx------  2 root    root        4096 Jan  7 12:28 performance_schema
 -rw-r-----  1 root    root          77 Jan  7 12:28 xtrabackup_checkpoints
 -rw-r-----  1 root    root         459 Jan  7 12:28 xtrabackup_info

That’s it! You have successfully backup MariaDB.

Restoring MariaDB

After creating a backup copy above, you can create a zipped or tarred copy of the folder and export to another server using rsync or scp if you’re migrating.

If you’re exporting the database to another server, simply run the commands below to create an archive.

You’ll want to use sudo since the database content was added using sudo.

sudo tar -czvf mariadb_backup.tar.gz ~/mariadb_backup

Now send to backup data over to a remote location using rsync or scp.

On the remote host, run the commands below to extract the archive.

tar zxvf mariadb_backup.tar.gz

Next, stop MariaDB service and delete any existing MariaDB data.

sudo systemctl stop mariadb.service
sudo rm -rf /var/lib/mysql/*

Next, prepare the backup file for restoration.

sudo mariabackup --prepare --target-dir ~/mariadb_backup

When you run the commands above, it should give you a success message as shown below:

2021-01-07 12:55:58 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2021-01-07 12:55:58 0 [Note] InnoDB: Completed initialization of buffer pool
2021-01-07 12:55:58 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2021-01-07 12:55:58 0 [Note] InnoDB: The log sequence number 1625452 in the system tablespace does not match the log sequence number 1625612 in the ib_logfiles!
[00] 2021-01-07 12:55:58 Last binlog file , position 0
[00] 2021-01-07 12:55:59 completed OK!

After preparing the database for restoration, simply run the commands below to restore.

sudo mariabackup --copy-back --target-dir ~/mariadb_backup

When the command above is completed, it should display a success message similar to the one below:

01] 2021-01-07 13:21:02 Copying ./mysql/help_category.MYD to /var/lib/mysql/mysql/help_category.MYD
[01] 2021-01-07 13:21:02         ...done
[01] 2021-01-07 13:21:02 Copying ./mysql/columns_priv.frm to /var/lib/mysql/mysql/columns_priv.frm
[01] 2021-01-07 13:21:02         ...done
[01] 2021-01-07 13:21:02 Copying ./mysql/columns_priv.MYI to /var/lib/mysql/mysql/columns_priv.MYI
[01] 2021-01-07 13:21:02         ...done
[01] 2021-01-07 13:21:02 Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
[01] 2021-01-07 13:21:02         ...done
[01] 2021-01-07 13:21:02 Copying ./aria_log.00000001 to /var/lib/mysql/aria_log.00000001
[01] 2021-01-07 13:21:02         ...done
[00] 2021-01-07 13:21:02 completed OK!

Next, run the commands to give MySQL service control of the mysql folder.

sudo chown -R mysql. /var/lib/mysql

Finally start MariaDB service.

sudo systemctl start mariadb.service

That should do it!

Conclusion:

This post showed you how to backup and restore MariaDB databases and data using Mariabackup tool.

If you find any error above, please use the form below to report.

You may also like the post below:

How to Check how much disk space left in Ubuntu – Website for Students

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.