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.
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
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,
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.
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
%20vs%20systemctl%20stop%20(2).png)
.svg)
