Mysql获取周一周日

转载

mob64ca1419e0cc 2024-09-28 11:17:44

文章标签 Mysql获取周一周日 sed mysql 备份文件 文章分类 MySQL 数据库

<root@linux0 ~>$ cat xtra.sh 
#! /bin/bash
#back up module, use crond run the shell accordingly
#all back module
#and increment back up module
#all backup at sunday 
#other weekdays increment back up
#when run choose function from all and increment

BACKUP_NAME=root
BACKUP_PW=******
BACKUP_DIR=/data/backup
MYSQL_CNF=/etc/my.cnf
MYSQL_SOCK=/tmp/mysql.sock
CRYCLE=`date +%d`    #保存日志的名字，一个月的日志都会保存，一个月以后会被复盖；
BACKUP_LOG=/data/backup/$CRYCLE.log
ALL_DAY="星期四"   #每周四做全量备份；

allbackup () {   #全量备份，过程输出到日志，恢复还需要用到日志；
echo "innobackupex --defaults-file=$MYSQL_CNF --user=$BACKUP_NAME --password=$BACKUP_PW -S $MYSQL_SOCK $BACKUP_DIR &> $BACKUP_LOG"
innobackupex --defaults-file=$MYSQL_CNF --user=$BACKUP_NAME --password=$BACKUP_PW -S $MYSQL_SOCK $BACKUP_DIR &> $BACKUP_LOG
}

incrementbackup () {  #基于前一天做增量备份；
checkincrement
echo "incrementbackup"
echo "innobackupex --defaults-file=$MYSQL_CNF --user=$BACKUP_NAME --password=$BACKUP_PW -S $MYSQL_SOCK --incremental $BACKUP_DIR --incremental-basedir=$BACKUP_DIR/$LAST_FILE &> $BACKUP_LOG"
innobackupex --defaults-file=$MYSQL_CNF --user=$BACKUP_NAME --password=$BACKUP_PW -S $MYSQL_SOCK --incremental $BACKUP_DIR --incremental-basedir=$BACKUP_DIR/$LAST_FILE &> $BACKUP_LOG
}

checkincrement () {   #利用日志查出前一天备份生成的文件名，用于增量备份；
LAST_DATE=`date -d "-1day" +%d`
    if [ `cat $BACKUP_DIR/$LAST_DATE.log |grep "completed" |wc -l` -eq 2 ]
      then
        a=`cat $BACKUP_DIR/$LAST_DATE.log |grep "Starting the backup operation" |awk '{print $2}'|awk -F ':' '{print $1}'`
        b=`cat $BACKUP_DIR/$LAST_DATE.log |grep "Starting the backup operation" |awk '{print $2}'|awk -F ':' '{print $2}'`
        c=`cat $BACKUP_DIR/$LAST_DATE.log |grep "Starting the backup operation" |awk '{print $2}'|awk -F ':' '{print $3}'`
        LAST_FILE=`date -d "-1day" +%Y-%m-%d_$a-$b-$c`
      else

        echo "last backup may not be completed! or log file was modified " > $BACKUP_LOG #or email out;  
        exit
    fi
}

backupmain () {
datealign   #时间同步；
alive    #检查服务是否在运行，运行中才能备份；
WEEKDAY=`date +%A`
case $WEEKDAY in
  $ALL_DAY)   #全量备份的日子，一周中的其中一天；
	allbackup
	;;
  *)
	incrementbackup
	;;
esac
}

datealign () {   #用于把时间同步，借用其他脚本；
  ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime  
 # yum -y install kde-l10n-Chinese    #机器要运行一次，之后可以注释掉；
  localedef -c -f UTF-8 -i zh_CN zh_CN.UTF-8  
  export LC_ALL=zh_CN.UTF-8 
  echo 'LANG="zh_CN.UTF-8"' > /etc/locale.conf
}  

alive () {
if [ `ps aux |pgrep mysql|wc -l` -lt 2 ]
  then
    echo "mysql may not running"
    exit
fi
}

backupmain

#crond backup 3am everyday
#crontab -e    #配合使用的计划任务，每天03:01分备份；
#1 3 * * * /bin/bash /root/xtra.sh  

#
#
#
#
# 
#
#
#
#
#
#

<root@linux0 ~>$ cat recover.sh    #恢复脚本；
#! /bin/bash
# 1 double check all the backup file whether is OK.
# 2 choose a checkpoint
# 3 find the day between today and the checkpoint
# 4 

TODAY=`date +%d`     #今天是几号，用于查出应该用哪些备份文件；
WEEKDAY=`date +%A`   #今天是周几；可看跟全量备份之间有哪些备份文件；
BACKUP_DIR=/data/backup
R_FILE=dirname   #保存备份文件的名字；
MYSQL_CNF=/etc/my.cnf
SQL_USER=mysql   #mysql的用户，数据文件的属主；
FIRST="innobackupex --apply-log --redo-only $BACKUP_DIR"   #恢复命令，增量使用；
SECOND="--incremental-dir=$BACKUP_DIR"   #增量使用； 
THIRD="innobackupex --apply-log $BACKUP_DIR"  #最后步骤，必需；
FOUR="innobackupex --copy-back $BACKUP_DIR"   #最后步骤，必需；

findfile () {
case $WEEKDAY in   #如果周五就有两个备份文件可用，周四的全量和周五早些时候的备份；如此类推；
  "星期五")
        f=1
        ;;
  "星期六")
        f=2
        ;;
  "星期日")
        f=3
        ;;
  "星期一")
        f=4
        ;;
  "星期二")
        f=5
        ;;
  "星期三")
        f=6
        ;;
  "星期四")
        f=0
        ;;
esac

>$BACKUP_DIR/$R_FILE #clean the file;   #清空文件里的内容；

for i in `seq 0 $f`      
  do
    L_DATE=`date -d "-"$i"day" +%d`    #日期就是备份日志的名字；
    echo -e "\033[33m checking $BACKUP_DIR/$L_DATE.log \033[0m"
    if cat $BACKUP_DIR/$L_DATE.log | grep "completed OK!" >/dev/null   #获取日志名字，检查备份文件是否有提示完成；
      then
        echo -e "\033[33m OK. information as below: \033[0m"     
        tail -2 $BACKUP_DIR/$L_DATE.log
        dirname    #成功的日志，查出备份时间，生成文件名；
      else
        echo -e "\033[33m log file situation!! \033[0m"
        exit
    fi
  done
echo -e "\033[33m backup checkpoint listing: \033[0m"
cat $BACKUP_DIR/$R_FILE
}

dirname () {   #生成文件名；
a=`cat $BACKUP_DIR/$L_DATE.log |grep "Starting the backup operation" |awk '{print $2}'|awk -F ':' '{print $1}'`
b=`cat $BACKUP_DIR/$L_DATE.log |grep "Starting the backup operation" |awk '{print $2}'|awk -F ':' '{print $2}'`
c=`cat $BACKUP_DIR/$L_DATE.log |grep "Starting the backup operation" |awk '{print $2}'|awk -F ':' '{print $3}'`
d=`date -d "-"$i"day" +%Y-%m-%d_$a-$b-$c`
echo "$d" >> $BACKUP_DIR/$R_FILE
}

choose () {   #选择恢复的节点；
echo -e "\033[33m please see below checkpoint until the last all backup: \033[0m"
cat $BACKUP_DIR/$R_FILE
while :
  do
    read -p "please select the recover checkpoint:" x    
    if echo $x |egrep [0-9]{4}-[0-9]{2}-[0-9]{2}_[0-9]{2}-[0-9]{2}-[0-9]{2}   #文件名格式一定要对；
        then
          echo -e "\033[33m recover checkpoint is $x \033[0m"
          while :
            do
                read -p "please input yes to begin the recover (no to exit):" n
                    case $n in
                           "yes")    #再次确认恢复；
                                sleep 2
                                break
                                ;;
                           "no")
                                exit
                                ;;      
                           *)   
                                continue
                                ;;      
                    esac
            done
          break
        else
          continue
    fi
  done
}

recover () {
#stop the service
#rm the data dir
if [ $(($LINE-$CLINE)) -eq 0 ]    #选择到全量备份点；
  then
    must   #全量恢复；
  else
    	echo -e "\033[33m **1** $FIRST/`sed -n "$LINE"p $BACKUP_DIR/$R_FILE`/ \033[0m"
        $FIRST/`sed -n "$LINE"p $BACKUP_DIR/$R_FILE`/   #增量恢复第一步；
    	echo $?
    	sleep 8
        
    	for i in `seq $(($LINE-1)) -1 $(($CLINE+1))`     #只有一个增量文件，不会实施，两个以上实施；
  	  do
    		echo -e "\033[33m **x** $FIRST/`sed -n "$LINE"p $BACKUP_DIR/$R_FILE`/ $SECOND/`sed -n "$i"p $BACKUP_DIR/$R_FILE`/ \033[0m"
		$FIRST/`sed -n "$LINE"p $BACKUP_DIR/$R_FILE`/ $SECOND/`sed -n "$i"p $BACKUP_DIR/$R_FILE`/   #增量加到全量文件里；
    		echo $?
    		sleep 8
  	  done

	echo -e "\033[33m **3** $THIRD/`sed -n "$LINE"p $BACKUP_DIR/$R_FILE`/ $SECOND/`sed -n "$CLINE"p $BACKUP_DIR/$R_FILE`/ \033[0m"
	$THIRD/`sed -n "$LINE"p $BACKUP_DIR/$R_FILE`/ $SECOND/`sed -n "$CLINE"p $BACKUP_DIR/$R_FILE`/   #增量的第3步，如果只有一个增量文件，直接实施这一步；
	echo $?
	sleep 8
	must    #最后实施最后两步；
fi  
}

must () {   #全量恢复的步骤，增量恢复的最后两步；
echo -e "\033[33m **4** $THIRD/`sed -n "$LINE"p $BACKUP_DIR/$R_FILE`/ \033[0m"
$THIRD/`sed -n "$LINE"p $BACKUP_DIR/$R_FILE`/
echo $?
sleep 8

echo -e "\033[33m **5** $FOUR/`sed -n "$LINE"p $BACKUP_DIR/$R_FILE`/ \033[0m"
$FOUR/`sed -n "$LINE"p $BACKUP_DIR/$R_FILE`/
if [ $? -eq 0 ]
  then
    chown -R $SQL_USER:$SQL_USER /data/mysql
    service mysqld start
  else
    exit
fi
}

backupdata () {   #备份数据；
RECOVER_DIR=/recoverdir_`date +%m%d_%H%M`   #新建目录；
LINE=`wc -l $BACKUP_DIR/$R_FILE|awk '{print $1}'`     #总的可用于恢复的文件数；
CLINE=`cat $BACKUP_DIR/$R_FILE|grep $x -n |awk -F ':' '{print $1}'`   #选择的恢复节点，在文件名文件的排序；

echo -e "\033[33m backupdata \033[0m"
DATADIR=`cat $MYSQL_CNF |grep datadir |awk -F '=' '{print $2}'`   #利用配置文件，找出数据目录；
echo -e "\033[33m rm -rf $DATADIR.bak \033[0m"
rm -rf $DATADIR.bak   #删除之前的保存；
echo -e "\033[33m /usr/bin/cp -r $DATADIR $DATADIR.bak \033[0m"
/usr/bin/cp -r $DATADIR $DATADIR.bak   #备份；
if [ $? -eq 1 ]
  then
    echo "copy mysql datadir error"
    exit
fi
echo -e "\033[33m rm -rf $DATADIR/* \033[0m"
rm -rf $DATADIR/*   #删除数据，不删除恢复错误；

echo -e "\033[33m *back recover_file*:mkdir $BACKUP_DIR$RECOVER_DIR \033[0m"
mkdir $BACKUP_DIR$RECOVER_DIR    #新建备份文件的备份目录；
for i in `seq $LINE -1 $CLINE`    #把会用到的备份文件保存一份；
  do
    echo -e "\033[33m /usr/bin/cp -r $BACKUP_DIR/`sed -n "$i"p $BACKUP_DIR/$R_FILE` $BACKUP_DIR$RECOVER_DIR/ \033[0m"
    /usr/bin/cp -r $BACKUP_DIR/`sed -n "$i"p $BACKUP_DIR/$R_FILE` $BACKUP_DIR$RECOVER_DIR/
    if [ $? -eq 1 ]
      then
        echo "cope xtra backup file error"
        exit
      else
        sleep 2
    fi
  done
echo -e "\033[33m recover_file backup as $BACKUP_DIR$RECOVER_DIR \033[0m"
ls $BACKUP_DIR$RECOVER_DIR
}

findfile
choose
pkill mysql
backupdata
recover

#
#
#
#
#
#
#
#
#
#
#
#