xtrabackup.sh

#!/bin/bash

set -eu

ROTATE=7
BACKUP_DIR=/backup/mysql
XTRABACKUP=/usr/bin/xtrabackup
MYSQL_CNF=/root/.my.cnf
CPU_COUNT=$(grep -c processor /proc/cpuinfo)
MYSQL_ROOT_PASSWORD=$(grep -m1 password $MYSQL_CNF | sed -e 's/password="\(.*\)"/\1/')

if [ ! -d $BACKUP_DIR ]; then
        mkdir -p $BACKUP_DIR
fi

today=$(date "+%Y%m%d")
$XTRABACKUP --no-server-version-check -u root -p$MYSQL_ROOT_PASSWORD --backup --stream=xbstream --parallel=$CPU_COUNT | pigz --best -c >"$BACKUP_DIR/${today}.gz"

while : ;do
        backup_list=($(ls -t $BACKUP_DIR))
        if [ $ROTATE -ge ${#backup_list[@]} ]; then
                break
        fi
        rm -f "$BACKUP_DIR/${backup_list[-1]}"
done

cat << EOS

restore:


1. stop mysql
# systemctl stop mysql

2. remove mysql data directory
# rm -fr /var/lib/mysql/*
   or
# mv /var/lib/mysql /var/lib/mysql.bak
#
# mkdir /var/lib/mysql && chmod 700 /var/lib/mysql

3. decompress backup data
# mkdir $BACKUP_DIR/$today
# (cd $BACKUP_DIR/$today; gzip -d <../"${today}.gz" | xbstream -x)

4. prepare
# $XTRABACKUP --prepare --target-dir=$BACKUP_DIR/$today

5. if "completed OK!" is output on console, execute mariadb-backup restore
# $XTRABACKUP --copy-back --target-dir=$BACKUP_DIR/$today
# chown -R mysql.mysql /var/lib/mysql

6. start mariadb
# systemctl start mysql

7. last
# rm -fr "$BACKUP_DIR/$today"
EOS