Sysadmin

Mariabackup bash scripts

Install mariabackup

Install the MariaDB-backup package for MariaDB 10 and above. If you have a lower version, upgrade from 5.x to 10.x first.

# yum install MariaDB-backup

Create a test database

Let's create a test database to test backup and restoring.

# mysql -u root -p
create database test_restore;
show databases;
use test_restore;
create table test (a int);
show tables;
insert into test set a = 2;
select * from test;

Create a user for backups

Create a separate user to backup databases

CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'some_password';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';
flush privileges;
exit

Write the scripts

I created two shell scripts: one that performs full backups of the database and another that performs incremental backups. The scripts will:

# cd /usr/local/sbin
# vim backup_databases_full.sh

This is the content of my script performing the full backup:

#!/bin/bash

find /var/mariadb/backup/* -type d -mmin +$((60*14)) -exec rm -rf {} \; # delete backup directories older than 14 hours

DAY_DIR=/var/mariadb/backup/`date +%Y-%m`/
TARGET_DIR=${DAY_DIR}`date +%d_%Hh_full`/

if [[ -e $TARGET_DIR ]]
then
    printf "[`date --iso-8601=ns`] Directory ${TARGET_DIR} already exists\n" >> ${TARGET_DIR}my_error.log
else
    mkdir -p $TARGET_DIR

    SECONDS=0

    mariabackup --backup \
        --target-dir=${TARGET_DIR} \
        --user=mariabackup --password=some_password >> ${TARGET_DIR}my_output.log 2>>${TARGET_DIR}my_output.log
    
    printf "completed in ${SECONDS} seconds\n" >> ${TARGET_DIR}my_output.log

    printf $TARGET_DIR > ${DAY_DIR}last_completed_backup
fi
# vim backup_databases_inc.sh

And this is the content of my script performing incremental backups (based on a full backup or on a previous incremental backup):

#!/bin/bash

find /var/mariadb/backup/* -type d -mmin +$((60*14)) -exec rm -rf {} \; # delete backup directories older than 14 hours

DAY_DIR=/var/mariadb/backup/`date +%Y-%m`/
TARGET_DIR=${DAY_DIR}`date +%d_%Hh%Mm_inc`/

if [[ -e $TARGET_DIR ]]
then
    printf "[`date --iso-8601=ns`] Directory ${TARGET_DIR} already exists\n" >> ${TARGET_DIR}my_error.log
else
    mkdir -p $DAY_DIR

    if [[ -e ${DAY_DIR}last_completed_backup ]]
    then
        BASE_DIR=$(head -n 1 ${DAY_DIR}last_completed_backup)
        
        if [[ -z ${BASE_DIR} ]]
        then
            printf "[`date --iso-8601=ns --utc`] Base dir is an empty string\n" >> ${DAY_DIR}my_error.log
        else
            mkdir -p $TARGET_DIR

            SECONDS=0

            mariabackup --backup \
                --target-dir=${TARGET_DIR} \
                --incremental-basedir=${BASE_DIR} \
                --user=mariabackup --password=some_password >> ${TARGET_DIR}my_output.log 2>>${TARGET_DIR}my_output.log

            printf "completed in ${SECONDS} seconds\n" >> ${TARGET_DIR}my_output.log

            printf $TARGET_DIR > ${DAY_DIR}last_completed_backup
        fi
    else
        printf "[`date --iso-8601=ns --utc`] No base dir for incremental backup\n" >> ${DAY_DIR}my_error.log
    fi
fi

Add permission to execute the file:

# chmod +x backup_databases_full.sh
# chmod +x backup_databases_inc.sh

Add cron jobs

Add cron jobs that execute the above scripts. I backup a replicated database on a second VPS so I do not worry about backing up while the application is in use. However, if you have a single VPS, you might want to perform full backups at a time where it does not interfere with database usage. See https://crontab.guru for helping setting up your cron task. You can perform incremental backups after a full backup at shorter periods, as they consume less resources.

# crontab -e
0 0,12 * * * bash /usr/local/sbin/backup_databases_full.sh
30 * * * * bash /usr/local/sbin/backup_databases_inc.sh

Run the scripts (change the test data after performing the full backup and before the incremental backup in order to make sure that the incremental backups work):

bash /usr/local/sbin/backup_databases_full.sh
bash /usr/local/sbin/backup_databases_inc.sh

Restore the database

And now let's delete the database data and restore the backup:

# systemctl stop mariadb.service
# rm -rf /var/lib/mysql/* # you should always empty the datadir directory before restoring
# mariabackup --prepare --target-dir=/var/mariadb/backup/2019-03/29_07h_full/
# mariabackup --prepare --target-dir=/var/mariadb/backup/2019-03/29_07h_full/ --incremental-dir=/var/mariadb/backup/2019-03/29_07h30m_inc/
# mariabackup --copy-back --target-dir=/var/mariadb/backup/2019-03/29_07h/
# chown -R mysql:mysql /var/lib/mysql/
# systemctl start mariadb.service

You can now try to connect to the test_restore database and query the recovered data.

Comments

Comments including links will not be approved.