分享一個使用 xtrabackup 備份 MySQL 資料庫的腳本

說明同 分享一個 MySQL Shell 備份MySQL資料庫的腳本

#!/bin/bash

# =========================================================================
# (C) Copyright 2003-2033 hoplite
# =========================================================================
# Script Purpose : backup mysql database by xtrabackup
# -------------------------------------------------------------------------
# instructions
# 1.setup login-path
#   create a login-ptah: mysql_config_editor set --login-path=bak -h localhost -P 3306 -uroot -p
#   list login-path: mysql_config_editor print --all
#   login test: mysql --login-path=bak
# 2.setup cron job. note: escape character \ for % must be specified in cron job
#   example: full backup on Sunday,incremental backup on Mon/Wed/Fri,differential backup on Tues/Thur/Sat
#   0 0 * * 0     /u01/backup/mysql_xtrabackup.sh bak /u01/backup/xtrabackup/0 full
#   0 0 * * 1,3,5 /u01/backup/mysql_xtrabackup.sh bak /u01/backup/xtrabackup/`date +"\%w"` incremental /u01/backup/xtrabackup/`date -d yesterday +"\%w"`
#   0 0 * * 2,4,6 /u01/backup/mysql_xtrabackup.sh bak /u01/backup/xtrabackup/`date +"\%w"` incremental /u01/backup/xtrabackup/0

# Version Date      Description
# -------------------------------------------------------------------------
# V1.0              Initial version
# V1.1 2023-02-14   fix bug
# =========================================================================

[ -f ~/.bash_profile ] && . ~/.bash_profile
set -e -u -o pipefail

show_usage ()
{
  echo "usage: `basename $0` login-path target-basedir backuplevel"
  echo "eg.    `basename $0` bak /u01/backup/xtrabackup full|incremental|differential"
}

if [ $# -lt 3 ] || [ $# -gt 3 ]; then
   show_usage
   exit 0
else
  start_time=`date +%F" "%T`

  p_loginpath=$1
  p_targetbasedir=$2
  p_backuplevel=$3

  v_id=`date +%Y%m%d%H%M%S`
  v_hash=`echo -n ${p_loginpath}|md5sum|cut -f1 -d" "`
  [ -d ${p_targetbasedir} ] || mkdir -p ${p_targetbasedir}

  stusfile=${p_targetbasedir}/.status.${v_hash}
  log_file=${p_targetbasedir}/backup.log
  lastbackupfile=${p_targetbasedir}/.lastbackup.${v_hash}
  lastfullbackupfile=${p_targetbasedir}/.lastfullbackup.${v_hash}
  xtra_log=xtrabackup.log

  #check last backup result
  if [ -f ${stusfile} ]; then
    if [ `cat ${stusfile}` = "running" ]; then
      loginfo="`date +%F" "%T` ...ERROR:last backup fail, check it,then drop status file and rerun." ; echo ${loginfo} ; echo ${loginfo} >> ${log_file}
      exit 1
    fi
  else
    echo "running" > ${stusfile}
  fi 

  #check last backup and last full backup
  v_backuplevel=""
  v_switch2full="N"  #switch to full backup if does not find last bakcup directory or last backup record
  if [ "${p_backuplevel}" = "full" ]; then
    v_switch2full="Y"
  elif [ "${p_backuplevel}" = "incremental" ]; then
    if [ -f ${lastbackupfile} ]; then
      v_lastbackupdir=`cat ${lastbackupfile} `
      if [ -d ${v_lastbackupdir} ]; then
        v_backuplevel="incr"
      else
        v_switch2full="Y"
      fi
    else
      v_switch2full="Y"
    fi
  elif [ "${p_backuplevel}" = "differential" ]; then
    if [ -f ${lastfullbackupfile} ]; then
      v_lastfullbackupdir=`cat ${lastfullbackupfile} `
      if [ -d ${v_lastfullbackupdir} ]; then
        v_backuplevel="diff"
      else
        v_switch2full="Y"
      fi
    else
      v_switch2full="Y"
    fi
  else
   show_usage
   exit 1
  fi
  
  if [ "${v_switch2full}" = "Y" ]; then
    v_backuplevel="full"
  fi
  v_targetdir=${p_targetbasedir}/${v_backuplevel}.${v_id}
  [ -d ${v_targetdir} ] || mkdir -p ${v_targetdir}
  loginfo="`date +%F" "%T` ...start backup to ${v_targetdir}" ; echo ${loginfo} ; echo ${loginfo} >> ${log_file}

  if [ "${v_backuplevel}" = "full" ]; then
    xtrabackup --no-defaults --login-path=${p_loginpath} --backup --target-dir=${v_targetdir} > ${v_targetdir}/${xtra_log} 2>&1
    echo ${v_targetdir} > ${lastfullbackupfile}
  elif [ "${v_backuplevel}" = "incr" ]; then
    xtrabackup --no-defaults --login-path=${p_loginpath} --backup --target-dir=${v_targetdir} --incremental-basedir=${v_lastbackupdir} > ${v_targetdir}/${xtra_log} 2>&1
  elif [ "${v_backuplevel}" = "diff" ]; then
    xtrabackup --no-defaults --login-path=${p_loginpath} --backup --target-dir=${v_targetdir} --incremental-basedir=${v_lastfullbackupdir} > ${v_targetdir}/${xtra_log} 2>&1
  fi
  echo ${v_targetdir} > ${lastbackupfile}
  echo "completed" > ${stusfile}
  loginfo="`date +%F" "%T` ...end backup to ${v_targetdir}" ; echo ${loginfo} ; echo ${loginfo} >> ${log_file}
  
  tmpfile=/tmp/.tmpfile.`basename $0`.${v_hash}.${v_id}
  find ${p_targetbasedir} -mtime +15 \( -name "full.20????????????" -o -name "incr.20????????????" -o -name "diff.20????????????" \) -type d > ${tmpfile}
  #eg. /u01/backup/xtrabakcup/full.20230214183657
  while read foldername
  do
    rm -rf "${foldername:-unset}"
    echo "deleted backup folder : ${foldername}"
  done < ${tmpfile}

  end_time=`date +%F" "%T`
  t_start_time=`date -d "${start_time}" +%s`
  t_end_time=`date -d "${end_time}" +%s`
  t_diff_secs=$((${t_end_time} - ${t_start_time}))
  t_hours=$((${t_diff_secs}/3600))
  t_mins=$(($((${t_diff_secs}-${t_hours}*3600))/60))
  t_secs=$((${t_diff_secs}%60))

  echo
  echo ==================================================
  echo "File Name:`basename $0`    Start Time:${start_time}    End Time:${end_time}    Total Cost:${t_hours}:${t_mins}:${t_secs}"
  echo ==================================================
  echo
fi