#!/bin/sh

# Create a backup user
# GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost' identified by 'YourPassword';
# FLUSH PRIVILEGES;
#
# Usage:
# MYSQL_PASSWORD=YourPassword bash run-mariabackup.sh

MYSQL_USER=backup
MYSQL_PASSWORD=123456
MYSQL_HOST=localhost
MYSQL_PORT=3306
BACKCMD=mariabackup # Galera Cluster uses mariabackup instead of xtrabackup.
BACKDIR=/data/mysql_backup    ##Directory to store backup files  存放备份文件的目录
FULLBACKUPCYCLE=604800 # Create a new full backup every X seconds  （一周备份一次完整备份）
KEEP=3 # Number of additional backups cycles a backup should kept for.
LOCKDIR=/tmp/mariabackup.lock

ReleaseLockAndExitWithCode () {
  if rmdir $LOCKDIR
  then
    echo "Lock directory removed"
  else
    echo "Could not remove lock dir" >&2
  fi
  exit $1
}

GetLockOrDie () {
  if mkdir $LOCKDIR
  then
    echo "Lock directory created"
  else
    echo "Could not create lock directory" $LOCKDIR
    echo "Is another backup running?"
    exit 1
  fi
}

USEROPTIONS="--user=${MYSQL_USER} --password=${MYSQL_PASSWORD} --host=${MYSQL_HOST} --port=${MYSQL_PORT}"
ARGS=""
BASEBACKDIR=$BACKDIR/base
INCRBACKDIR=$BACKDIR/incr
START=`date +%s`

echo "----------------------------"
echo
echo "run-mariabackup.sh: MySQL backup script"
echo "started: `date`"
echo

if test ! -d $BASEBACKDIR
then
  mkdir -p $BASEBACKDIR
fi

# Check base dir exists and is writable
if test ! -d $BASEBACKDIR -o ! -w $BASEBACKDIR
then
  error
  echo $BASEBACKDIR 'does not exist or is not writable'; echo
  exit 1
fi

if test ! -d $INCRBACKDIR
then
  mkdir -p $INCRBACKDIR
fi

# check incr dir exists and is writable
if test ! -d $INCRBACKDIR -o ! -w $INCRBACKDIR
then
  error
  echo $INCRBACKDIR 'does not exist or is not writable'; echo
  exit 1
fi

if [ -z "`mysqladmin $USEROPTIONS status | grep 'Uptime'`" ]
then
  echo "HALTED: MySQL does not appear to be running."; echo
  exit 1
fi

if ! `echo 'exit' | /usr/bin/mysql -s $USEROPTIONS`
then
  echo "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)"; echo
  exit 1
fi

GetLockOrDie

echo "Check completed OK"

# Find latest backup directory
LATEST=`find $BASEBACKDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`

AGE=`stat -c %Y $BASEBACKDIR/$LATEST`

if [ "$LATEST" -a `expr $AGE + $FULLBACKUPCYCLE + 5` -ge $START ]
then
  echo 'New incremental backup'
  # Create an incremental backup

  # Check incr sub dir exists
  # try to create if not
  if test ! -d $INCRBACKDIR/$LATEST
  then
    mkdir -p $INCRBACKDIR/$LATEST
  fi

  # Check incr sub dir exists and is writable
  if test ! -d $INCRBACKDIR/$LATEST -o ! -w $INCRBACKDIR/$LATEST
  then
    echo $INCRBACKDIR/$LATEST 'does not exist or is not writable'
    ReleaseLockAndExitWithCode 1
  fi

  LATESTINCR=`find $INCRBACKDIR/$LATEST -mindepth 1  -maxdepth 1 -type d | sort -nr | head -1`
  if [ ! $LATESTINCR ]
  then
    # This is the first incremental backup
    INCRBASEDIR=$BASEBACKDIR/$LATEST
  else
    # This is a 2+ incremental backup
    INCRBASEDIR=$LATESTINCR
  fi

  TARGETDIR=$INCRBACKDIR/$LATEST/`date +%F_%H-%M-%S`
  mkdir -p $TARGETDIR

  # Create incremental Backup
  $BACKCMD --backup $USEROPTIONS $ARGS --extra-lsndir=$TARGETDIR --incremental-basedir=$INCRBASEDIR --stream=xbstream | gzip > $TARGETDIR/backup.stream.gz
else
  echo 'New full backup'

  TARGETDIR=$BASEBACKDIR/`date +%F_%H-%M-%S`
  mkdir -p $TARGETDIR

  # Create a new full backup
  $BACKCMD --backup $USEROPTIONS $ARGS --extra-lsndir=$TARGETDIR --stream=xbstream | gzip > $TARGETDIR/backup.stream.gz
fi

MINS=$(($FULLBACKUPCYCLE * ($KEEP + 1 ) / 60))
echo "Cleaning up old backups (older than $MINS minutes) and temporary files"

# Delete old backups
for DEL in `find $BASEBACKDIR -mindepth 1 -maxdepth 1 -type d -mmin +$MINS -printf "%P\n"`
do
  echo "deleting $DEL"
  rm -rf $BASEBACKDIR/$DEL
  rm -rf $INCRBACKDIR/$DEL
done

SPENT=$((`date +%s` - $START))
echo
echo "took $SPENT seconds"
echo "completed: `date`"
ReleaseLockAndExitWithCode 0

├── base
│   └── 2020-05-15_17-07-52
│       ├── backup.stream.gz
│       ├── xtrabackup_checkpoints
│       └── xtrabackup_info
└── incr
    └── 2020-05-15_17-07-52
        ├── 2020-05-15_17-13-15
        │   ├── backup.stream.gz
        │   ├── xtrabackup_checkpoints
        │   └── xtrabackup_info
        ├── 2020-05-15_17-14-36
        │   ├── backup.stream.gz
        │   ├── xtrabackup_checkpoints
        │   └── xtrabackup_info
        ├── 2020-05-15_17-16-23
        │   ├── backup.stream.gz
        │   ├── xtrabackup_checkpoints
        │   └── xtrabackup_info
        └── 2020-05-15_17-18-17
            ├── backup.stream.gz
            ├── xtrabackup_checkpoints
            └── xtrabackup_info

1. ####  copy backup  files  拷贝Or备份
2. cd /data/mysql_backup/
3. copy  /data/mysql_backup/  /data/mysql_backup-default/  or tar zcf  mysql_backup.tar.gz /data/mysql_backup/

1. # decompress   解压已备份的数据库备份文件
2. cd /data/mysql_backup/
3. for i in $(find . -name backup.stream.gz | grep '2020-05-15_17-07-52' | xargs dirname); \
4. do \
5. mkdir -p $i/backup; \
6. zcat $i/backup.stream.gz | mbstream -x -C $i/backup/;
7. done

1. # prepare     恢复数据 多次增量备份需要恢复到其中摸个版本 例如本次恢复到2020-05-15_17-16-23这个增量版本 需依次恢复之前的版本 首先恢复全量的然后依次增量直至 2020-05-15_17-16-23
2. mariabackup --prepare --target-dir /data/mysql_backup/base/2020-05-15_17-07-52/backup/ --user backup --password "123456" --apply-log-only
3. mariabackup --prepare --target-dir base/2020-05-15_17-07-52/backup/ --user backup --password "123456" --apply-log-only --incremental-dir incr/2020-05-15_17-07-52/2020-05-15_17-13-15/backup/
4. mariabackup --prepare --target-dir base/2020-05-15_17-07-52/backup/ --user backup --password "123456" --apply-log-only --incremental-dir incr/2020-05-15_17-07-52/2020-05-15_17-14-36/backup/
5. mariabackup --prepare --target-dir base/2020-05-15_17-07-52/backup/ --user backup --password "123456" --apply-log-only --incremental-dir incr/2020-05-15_17-07-52/2020-05-15_17-16-23/backup/

1. # stop mairadb  停止数据库
2. service mariadb stop

1. # empty datadir   清空数据库  或者 给当前数据库重命名
2. mv /var/lib/mysql/ /var/lib/mysql_bak/

1. # copy-back   拷贝我们恢复的数据库到已清空的  /var/lib/mysql/
2. mariabackup --copy-back --target-dir base/2020-05-15_17-07-52/backup/ --user backup --password "123456" --datadir /var/lib/mysql/

1. # fix privileges   重置权限
2. chown -R mysql:mysql /var/lib/mysql/

1. # start mariadb    启动数据库
2. service mariadb start