Backup and Recovery using Mariabackup.

A Database Engineer should always be aware of how the database stores valuable data and also to safeguard it. Data may be lost due to a lot of reasons like some critical problems in the hardware, crashes, human mistakes, and more other reasons. That is why we need to take database backups. To make backups easy, a tool is being introduced in MariaDB servers called the Mariabackup tool.

What is Mariabackup?

 Mariabackup is an open-source tool provided by MariaDB for performing physical online backups of InnoDB(hot online is possible), MyRocks, Aria, and MyISAM tables. It was originally forked from Percona Xtrabackup 2.3.8. The feature introduction like InnoDB page compression and TDE in MariaDB break its compatibility with Percona Xtrabackup. It supports both Linux and Windows operating systems.

    Mariabackup was first released in MariaDB 10.1.23 and MariaDB 10.2.7. It was also released as GA in MariaDB 10.1.26 and MariaDB 10.2.10.

Features in Mariabackup:

  • Support Windows Platform ( Open Source Hot backup Solution on Windows ). 
  • As a SST method with MariaDB Cluster ( Galera ).
  • Inbuilt Support for Myrocks Engine
  • Other features inherited from Xtrabackup.

 Architecture:

Possible Backup Types

Full backup:

Full backups create a complete backup of the database server in an empty directory while incremental backups update a previous backup with whatever changes to the data have occurred since the backup.

Incremental backup:

The incremental backup tracks the changes in pages from the last full backup and copies the delta pages.

Partial backup:

 It allows you to choose which databases or tables to backup, as long as the table or partition involved is in an InnoDB file-per-table tablespace ( not in ibdata1 ).

Restoring Individual Tables and Partitions from Full backup.

      It is possible to choose certain tables, databases, or partitions from backup, as long as the table or partition involved is in an InnoDB file-per-table tablespace.  During the preparation stage, –export option can be used to recover the individual tables.

Initiating a Full Backup

        To initiate a Full backup, we need to run the –backup and –target-dir option to backup the database. While taking the full backup the target directory must be empty. Let us try to initiate a full backup over a live MariaDB server

        The following logs a detailed view of what is happening while the backup is in the process.

[root@mydbopslabs204 vagrant]# mariabackup --backup \
>    --target-dir=/home/vagrant/backup/ \
>    --user=mariabackup --password=mypassword
[00] 2020-11-26 07:17:13 Connecting to MySQL server host: localhost, user: mariabackup, password: set, port: not set, socket: not set
[00] 2020-11-26 07:17:13 Using server version 10.4.12-MariaDB
mariabackup based on MariaDB server 10.4.12-MariaDB Linux (x86_64)
[00] 2020-11-26 07:17:13 uses posix_fadvise().
[00] 2020-11-26 07:17:13 cd to /var/lib/mysql/
[00] 2020-11-26 07:17:13 open files limit requested 0, set to 1024
[00] 2020-11-26 07:17:13 mariabackup: using the following InnoDB configuration:
[00] 2020-11-26 07:17:13 innodb_data_home_dir =
[00] 2020-11-26 07:17:13 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2020-11-26 07:17:13 innodb_log_group_home_dir = ./
[00] 2020-11-26 07:17:13 InnoDB: Using Linux native AIO
2020-11-26  7:17:13 0 [Note] InnoDB: Number of pools: 1
[00] 2020-05-26 07:17:13 mariabackup: Generating a list of tablespaces
2020-11-26  7:17:13 0 [Warning] InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
[00] 2020-11-26 07:17:13 >> log scanned up to (27719740831)
[01] 2020-11-26 07:17:13 Copying ibdata1 to 
.
.
.
/home/vagrant/backup/mysql/proc.MYI
[01] 2020-11-26 07:19:48         ...done
[01] 2020-11-26 07:19:48 Copying ./mysql/proc.MYD to /home/vagrant/backup/mysql/proc.MYD
[01] 2020-11-26 07:19:48         ...done
[01] 2020-11-26 07:19:48 Copying ./mysql/procs_priv.frm to /home/vagrant/backup/mysql/procs_priv.frm
[01] 2020-11-26 07:19:48         ...done
[01] 2020-11-26 07:19:48 Copying ./mysql/procs_priv.MYI to /home/vagrant/backup/mysql/procs_priv.MYI
[01] 2020-11-26 07:19:48         ...done
[01] 2020-11-26 07:19:48 Copying ./mysql/procs_priv.MYD to /home/vagrant/backup/mysql/procs_priv.MYD
[01] 2020-11-26 07:19:48         ...done
[01] 2020-11-26 07:19:48 Copying ./mysql/general_log.frm to /home/vagrant/backup/mysql/general_log.frm
[01] 2020-11-26 07:19:48         ...done
[01] 2020-11-26 07:19:48 Copying ./mysql/general_log.CSM to /home/vagrant/backup/mysql/general_log.CSM
[01] 2020-11-26 07:19:48         ...done

 Once the backup is completed the backup dir contains the files which as listed below.

[root@mydbopslabs204 vagrant]# ls /home/vagrant/backup/
aria_log.00000001    backup-my.cnf   ibdata1      my    mydbops              performance_schema    TalkingData   xtrabackup_info  aria_log_control   cluster        ib_buffer_pool   ib_logfile0   mydb   mysql    xtrabackup_checkpoints     sys                                  

Initiating an Incremental backup

I have made a few changes to the databases. Now let us perform an incremental backup to capture the new changes to the databases –incremental-basedir option emphasis the path of the full backup which is done earlier.

[root@mydbopslabs204 vagrant]#  mariabackup --backup \
>    --target-dir=/home/vagrant/inc1/ \
>    --incremental-basedir=/home/vagrant/backup/ \
>    --user=mariabackup --password=mypassword
[00] 2020-11-26 07:31:12 Connecting to MySQL server host: localhost, user: mariabackup, password: set, port: not set, socket: not set
[00] 2020-11-26 07:31:12 Using server version 10.4.12-MariaDB
mariabackup based on MariaDB server 10.4.12-MariaDB Linux (x86_64)
[00] 2020-11-26 07:31:12 incremental backup from 27719740822 is enabled.
[00] 2020-11-26 07:31:12 uses posix_fadvise().
[00] 2020-11-26 07:31:12 cd to /var/lib/mysql/
[00] 2020-11-26 07:31:12 open files limit requested 0, set to 1024
[00] 2020-11-26 07:31:12 mariabackup: using the following InnoDB configuration:
[00] 2020-11-26 07:31:12 innodb_data_home_dir =
[00] 2020-11-26 07:31:12 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2020-11-26 07:31:12 innodb_log_group_home_dir = ./
[00] 2020-11-26 07:31:12 InnoDB: Using Linux native AIO
2020-11-26  7:31:12 0 [Note] InnoDB: Number of pools: 1
[00] 2020-05-26 07:31:12 mariabackup: Generating a list of tablespaces
2020-11-26  7:31:12 0 [Warning] InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
[00] 2020-05-26 07:31:12 >> log scanned up to (27719740831)
.
.
.
mariabackup: Stopping log copying thread.[00] 2020-05-26 07:31:33 >> log scanned up to (27719740831)

[00] 2020-11-26 07:31:33 >> log scanned up to (27719740831)
[00] 2020-11-26 07:31:33 Executing BACKUP STAGE END
[00] 2020-11-26 07:31:33 All tables unlocked
[00] 2020-11-26 07:31:33 Copying ib_buffer_pool to /home/vagrant/inc1/ib_buffer_pool
[00] 2020-11-26 07:31:33         ...done
[00] 2020-11-26 07:31:33 Backup created in directory '/home/vagrant/inc1/'
[00] 2020-11-26 07:31:33 Writing backup-my.cnf
[00] 2020-11-26 07:31:33         ...done
[00] 2020-11-26 07:31:33 Writing xtrabackup_info
[00] 2020-11-26 07:31:33         ...done
[00] 2020-11-26 07:31:33 Redo log (from LSN 27719740822 to 27719740831) was copied.
[00] 2020-11-26 07:31:33 completed OK!

We can also perform further new incremental backups, with a change in –target-directory option and –incremental-basedir to the previous incremental backup location. For example,

[root@mydbopslabs204 vagrant]# mariabackup --backup \
>    --target-dir=/home/vagrant/inc2/ \
>    --incremental-basedir=/home/vagrant/inc1/ \
>    --user=mariabackup --password=mypassword

Preparing the Backup:

The backup has to be prepared before restoring it because the data files that Mariabackup creates in the target directory are not point-in-time consistent, given that the data files are copied at different times during the backup operation. If you try to restore from these files, InnoDB notices the inconsistencies and crashes to protect you from corruption.              

    Using the –prepare option In MariaDB 10.1, we would also have to use the –apply-log-only option.In MariaDB 10.2 and later, the option –apply-log-only can be ignored. Use the following command.

[root@mydbopslabs204 vagrant]# mariabackup --prepare    --target-dir=/home/vagrant/backup    --incremental-dir=/home/vagrant/inc1
mariabackup based on MariaDB server 10.4.12-MariaDB Linux (x86_64)
[00] 2020-11-26 07:42:58 incremental backup from 27719740822 is enabled.
[00] 2020-11-26 07:42:58 cd to /home/vagrant/backup/
[00] 2020-11-26 07:42:58 This target seems to be already prepared.
[00] 2020-11-26 07:42:58 mariabackup: using the following InnoDB configuration for recovery:
[00] 2020-11-26 07:42:58 innodb_data_home_dir = .
[00] 2020-11-26 07:42:58 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2020-11-26 07:42:58 innodb_log_group_home_dir = /home/vagrant/inc1/
[00] 2020-11-26 07:42:58 InnoDB: Using Linux native AIO
[00] 2020-11-26 07:42:58 mariabackup: Generating a list of tablespaces
.
.
.
[01] 2020-11-26 07:42:59         ...done
[01] 2020-11-26 07:42:59 Copying /home/vagrant/inc1/hars/db.opt to ./hars/db.opt
[01] 2020-11-26 07:42:59         ...done
[01] 2020-11-26 07:42:59 Copying /home/vagrant/inc1/cluster/db.opt to ./cluster/db.opt
[01] 2020-11-26 07:42:59         ...done
[01] 2020-11-26 07:42:59 Copying /home/vagrant/inc1//aria_log.00000001 to ./aria_log.00000001
[01] 2020-11-26 07:42:59         ...done
[01] 2020-05-26 07:42:59 Copying /home/vagrant/inc1//aria_log_control to ./aria_log_control
[01] 2020-11-26 07:42:59         ...done
[00] 2020-11-26 07:42:59 Copying /home/vagrant/inc1//xtrabackup_info to ./xtrabackup_info
[00] 2020-11-26 07:42:59         ...done
[00] 2020-11-26 07:42:59 completed OK!

Restoring the Backup

After the preparation process is done, we are ready to restore the data using –copy-back and –move-back option.The –copy-back option allows us to keep the original backup files. The –move-back option moves the backup files to the datadir, so that original backup files are lost.          

    First we have to stop the MariaDB server process, then ensure that the data dir and related innodb dir is empty before executing the following commands.

[root@mydbopslabs204 vagrant]# mariabackup --copy-back \
   --target-dir=/var/mariadb/backup/
                                or
[root@mydbopslabs204 vagrant]# mariabackup --move-back \
   --target-dir=/var/mariadb/backup/

After restoring a backup ,you may need to fix the file permissions. , you need to adjust the ownership of the data directory to match the user and group for the MariaDB Server

For example,

[root@mydbopslabs204 vagrant]# chown -R mysql:mysql /var/lib/mysql/

Finally start the MariaDB process.

In this blog we have seen about basic full backup & incremental backup using the Mariabackup , preparing the same and restoring it back.

4 thoughts on “Backup and Recovery using Mariabackup.

    1. Hi Mike,I have add syntax for restore an individual tables below
      mariabackup –prepare –export \
      –target-dir=/var/mariadb/backup/ \
      –user=mariabackup –password=mypassword

      Like

  1. Instead of the –move-back,

    if I do -apply-log and then the backup is consistent.
    Then move/scp the backup files to the destination directory, will it work
    or
    thsi is something different with your restoration method ?

    Like

  2. It works well too if you are aware of all the destination directories ( iblogfile , ibdata1, and other tablespaces). It is a good practice to use –move-back as it reads the MySQL config file and do the database operations.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s