使用shell實現mysql自動全備、增備&記錄備份

標籤：mysql 熱備

資料庫熱備指令碼：

vim backup.sh

#!/bin/sh

time=`date "+%Y%m%d_%H%M%S"`

host=`hostname`

week=`date +%w`

monitor="/home/mysql/monitor/mysql_hotbackup_status.txt" ##zabbix監控檔案

time_start=`date +%s`

n=0 ###要周幾做全備，周日是0，周一到周六依次為1~6

function check_status()

{

    status=`cat ${1}|grep "innobackupex: completed OK"|wc -l`

    if [ $status -eq 1 ]

    then

        echo 0 > ${monitor}

    else

        echo 1 > ${monitor}

    fi

}

if [ $week -eq $n ]

then

    /bin/sh /home/mysql/shell/mysql_xtrabackup.sh full >> /home/mysql/log/xtrabackup_log/${host}_${time}_full.log 2>&1

    check_status /home/mysql/log/xtrabackup_log/${host}_${time}_full.log;

else

    /bin/sh /home/mysql/shell/mysql_xtrabackup.sh incr >> /home/mysql/log/xtrabackup_log/${host}_${time}_incr.log 2>&1

    check_status /home/mysql/log/xtrabackup_log/${host}_${time}_incr.log;

fi

time_end=`date +%s`

times=$((${time_end}-${time_start}))

if [ $week -eq $n ]

then

    echo "it takes ${times} seconds to backup the full backup!!!" >> /home/mysql/log/xtrabackup_log/${host}_${time}_full.log

else

    echo "it takes ${times} seconds to backup the incr backup!!!" >> /home/mysql/log/xtrabackup_log/${host}_${time}_incr.log

fi

###Database Backup策略###

###周日全備，周一~周六增量備份###

vim mysql_xtrabackup.sh 

#!/bin/bash

if [ $# -ne 1 ]

then

    echo "usage: `basename $0` [full|incr]"

    exit 1

fi 

name=`hostname`               # the name for bakcup

conf_file=/etc/my.cnf           # the mysql config file

bakuser="backup"

bakpass="backup"

bakdir=/data/mysql/mysql3306/tmp/   # the directory to backup

baklog=/home/mysql/log

host=localhost

innobackupex=/usr/bin/innobackupex

remotedir="/mysqlbackup/databak/${name}"

time=`date "+%Y%m%d_%H%M%S"`              # don‘t modify

yesterday=`date  +"%Y%m%d" -d  "-1 days"`

chkpointdir=/home/mysql/chkpoint/

full() {

ssh [email protected] "mkdir ${remotedir}/${name}_${time}_full/"

innobackupex --defaults-file=${conf_file} --user=${bakuser} --password=${bakpass} --host=${host} --slave-info --stream=xbstream --compress --extra-lsndir=${chkpointdir} --parallel=16 --throttle=120 ${bakdir}|ssh [email protected] \ "xbstream -x -C ${remotedir}/${name}_${time}_full/"

scp /etc/my.cnf mysql02:${remotedir}/${name}_${time}_full/

}

 

incr() {

ssh [email protected] "mkdir /${remotedir}/${name}_${time}_incr/"

lsn=`cat ${chkpointdir}/xtrabackup_checkpoints|grep to_lsn|awk ‘{print $NF}‘`

innobackupex --defaults-file=${conf_file} --user=${bakuser} --password=${bakpass} --host=${host} --slave-info --stream=xbstream --compress --extra-lsndir=${chkpointdir} --parallel=16 --throttle=120 --incremental --incremental-lsn=${lsn}  ${bakdir}|ssh [email protected] \ "xbstream -x -C ${remotedir}/${name}_${time}_incr/"

scp /etc/my.cnf mysql02:${remotedir}/${name}_${time}_incr/

}

case "$1" in

full)

full

;;

incr)

incr

;;

*)

echo "Usage: $0 full|incr"

esac

需求情境：

備份策略：周日全備，周一到周六增備。

備份路徑：異地備份，壓縮備份。

前提條件：mysql伺服器同備份機mysql02配置了ssh互信。

資料庫記錄備份指令碼：

vim binlog_mysql01.sh

#!/bin/sh

BACKUP_BIN="/usr/bin/mysqlbinlog"

LOCAL_BACKUP_DIR="/mysqlbackup/logbak/mysql01/"

BACKUP_LOG="/home/mysql/log/binlog_log"

REMOTE_HOST="192.168.1.1"

REMOTE_PORT="3306"

REMOTE_USER="backup"

REMOTE_PASS="backup"

FIRST_BINLOG="mysql-bin.000001"

#time to wait before reconnecting after failure

SLEEP_SECONDS=10

##create local_backup_dir if necessary

mkdir -p ${LOCAL_BACKUP_DIR}

cd ${LOCAL_BACKUP_DIR}

## 運行while迴圈，串連斷開後等待指定時間，重新串連

while :

do

  if [ `ls -A "${LOCAL_BACKUP_DIR}" |wc -l` -eq 0 ];then

     LAST_FILE=${FIRST_BINLOG}

  else

     LAST_FILE=`ls -l ${LOCAL_BACKUP_DIR} | grep -v backuplog |tail -n 1 |awk ‘{print $9}‘`

  fi

  ${BACKUP_BIN} --raw --read-from-remote-server --stop-never --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS} ${LAST_FILE} 

  echo "`date +"%Y/%m/%d %H:%M:%S"` mysqlbinlog停止，傳回碼：$?" | tee -a ${BACKUP_LOG}

  echo "${SLEEP_SECONDS}秒後再次串連並繼續備份" | tee -a ${BACKUP_LOG}  

  sleep ${SLEEP_SECONDS}

done

nohup sh binlog_mysql01.sh 方式啟動指令碼，即時備份binlog資料。

使用shell實現mysql自動全備、增備&記錄備份