How to do a MariaDB Restore

Question: There is a cronjob backing up MariaDB databases through the mariabackup utility. 

mariabackup --defaults-file=/my_dir/.backup-my.cnf --backup --parallel=$PARALLEL --no-timestamp --target-dir=$BKUP_DIR/daily

Answer: Here is a basic structure of how you could restore MariaDB server . This approach is not for recovering a single database - that is for another post. These steps will recover all the databases in the MariaDB instances

There are loads of different options when recovering and all factors should be considered when doing a restore.  Note , in step 4 about emptying the data dir , I always recommend to take a copy in case of rollback

Step 1 : Prepare the Backup for the Restore

When the initial backup is taken , and the data files are created in the target directory - they are not consistent for a point-in-time restore. If there is no preparation the db engine recognizes the file inconsistency  and creates a crash in the recovery

The --prepare option assists in making the data files consistent and ready to restore.    If this step has already completed and you attempt to to execute a message appears indicating current status .

mariabackup based on MariaDB server 10.3.17-MariaDB Linux (x86_64)
[00] 2021-10-01 17:18:18 cd to <my_backup_directory>
[00] 2021-10-01 17:18:18 This target seems to be already prepared.

Example:

--execute as mysql

$ mariabackup --prepare   --target-dir=<the backup path>

Once you've prepared the backup for restoration , restore the backup using either the --copy-back or the --move-back options. The --copy-back option allows you to keep the original backup files. The --move-back option actually moves the backup files to the datadir, so the original backup files are lost.

I’d recommend using –copy-back  option.

Step 2 : Get variable information from MariaDB

show global variables like 'datadir';

+---------------+----------------------+

| Variable_name | Value                |

+---------------+----------------------+

| datadir       | /my_data_dir/ |

+---------------+----------------------+

Step 3 :  Stop the MariaDB Server process.

               systemctl stop mysql

Step 4: Then, ensure that the datadir is empty.

When you clear the datadir ensure there are no files or folders in the directory , as the mariadbbackup will fail .

Make a copy of the data_dir contents. 

Step 5: Run Mariabackup with one of the options mentioned above:

Note: the target-dir , is the source directory for the backup files

$ mariabackup --copy-back   --target-dir=<target_data_dir>

Step 6 : Then, you may need to fix the file permissions. (OPTIONAL )

Note: Only use this step if there are permission issues

When Mariabackup restores a database, it preserves the file and directory privileges of the backup. However, it writes the files to disk as the user and group restoring the database. 

So , depending on how you backed\restored , there may be a requirement to adjust the directory permissions

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

Step 7: Start MariaDB service

systemctl start mysql

Step 8: Check the databases