Mariadb Incremental Backup Strategy

install mariadb-back

sudo apt-get install mariadb-backup 

Creating Local Backup

1. Backup Entire databases

   sudo mariadb-backup --user=user --password=password --backup --target-dir=directory_to_store_backedup_files

   
   

2. Backup Particular database

   sudo mariadb-backup --user=user --password=password --databases=database_name --backup --target-dir=directory_to_store_backedup_files

Creating Incremental Local Backup

sudo mariadb-backup --user=user --password=password --backup --target-dir=directory_to_store_backedup_files --incremental-basedir=directory_to_store_incremental_files

Creating Backup using Restic

1. Installation

   sudo apt-get install restic

2. Initiate the Restic repository

   export RESTIC_REPOSITORY="folder_path_to_intitalize_restic_repo"
   export RESTIC_PASSWORD="pwd"
   restic init

3. create the backup

   sudo mariadb-backup --user=user --password=pwd --backup --databases=database_name --stream=xbstream 2>{folder_path_to_intitalize_restic_repo}/mariadb-backup.log | restic backup --stdin --stdin-filename mariadb.xb --tag MariaDB

4. retrieving backup file

   restic restore latest --target directory_to_store_retrived_file 
   mkdir directory_to_store_backedup_files && cd directory_to_store_backedup_files
   mbstream -x <../mariadb.xb
   mariadb-backup --prepare --target-dir=.

Restoring the backed up file

1. Preparing backup

   1. All databases

      sudo mariadb-backup --prepare --target-dir=directory_to_store_backedup_files

   2. Particular Database

      sudo mariadb-backup --prepare --databases=database_name --target-dir=directory_to_store_backedup_files

   3. For incremental backups

      mariadb-backup --prepare --target-dir=directory_to_store_backedup_files

      for dir in directory_to_store_incremental_1_files directory_to_store_incremental_3_files ..... directory_to_store_incremental_n_files; do 
      mariadb-backup --prepare --target-dir=directory_to_store_backedup_files \ --incremental-dir="$dir" 
      done 

2. Restore All databases

   sudo systemctl stop mariadb
   sudo mv /var/lib/mysql/ /var/lib/mysql_old2  # to safely have old sql files
   sudo mkdir /var/lib/mysql
   sudo mariadb-backup --copy-back --target-dir=directory_to_store_backedup_files
   sudo chown -R mysql:mysql /var/lib/mysql
   sudo systemctl start mariadb

3. Restore Particular Database

   sudo systemctl stop mariadb
   sudo cp -R directory_to_store_backedup_files/database_name /var/lib/mysql/
   sudo chown -R mysql:mysql /var/lib/mysql/database_name 
   sudo systemctl start mariadb

to Know More refer: Mariabackup + Restic: A simple and efficient online backup solution for your DBs

watch video: