mysql 备份脚本

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196

#!/bin/bash INNOBACKUPEXFULL="/usr/bin/innobackupex" MYSQL_CMD=' --socket=/ssd/mysql/3346/tmp/mysql.sock --user=root --password=mysqlpassword --port=3346' MYSQL_UP=' --user=root --password=mysqlpassword --port=3346 '  #mysqladmin的用户名和密码 TMPLOG="/data/tmp/innobackupex.$$.log" MY_CNF="/usr/local/mysql/mysql3346.cnf" #mysql的配置文件 TMP_DIR=/data/backup_tmp MYSQL=/usr/local/mysql/bin/mysql MYSQL_ADMIN=/usr/local/mysql/bin/mysqladmin BACKUP_DIR=/data/backup                                             # 备份的主目录 FULLBACKUP_DIR=$BACKUP_DIR/full                             # 全库备份的目录 INCRBACKUP_DIR=$BACKUP_DIR/incre                            # 增量备份的目录 FULLBACKUP_INTERVAL=86400                                   # 全库备份的间隔周期，时间：秒 KEEP_FULLBACKUP=1                                           # 至少保留几个全库备份 logfiledate=/data/backup/backup.`date +%Y%m%d%H%M`.txt                  #开始时间 STARTED_TIME=`date +%s`   ############################################################################# # 显示错误并退出 ############################################################################# error() {     echo "$1" 1>&2     exit 1 } # 检查执行环境 if [ ! -x $INNOBACKUPEXFULL ]; then   error "$INNOBACKUPEXFULL未安装或未链接到/usr/bin." fi   if [ ! -d $BACKUP_DIR ]; then   error "备份目标文件夹:$BACKUP_DIR不存在." fi   mysql_status=`netstat -nl | awk 'NR>2{if ($4 ~ /.*:3346/) {print "Yes";exit 0}}'` if [ "$mysql_status" != "Yes" ];then     error "MySQL 没有启动运行." fi   if ! `echo 'exit' | $MYSQL -s $MYSQL_CMD` ; then  error "提供的数据库用户名或密码不正确!" fi   # 备份的头部信息 echo "----------------------------" echo echo "$0: MySQL备份脚本" echo "开始于: `date +%F' '%T' '%w`" echo   #新建全备和差异备份的目录 mkdir -p $FULLBACKUP_DIR mkdir -p $INCRBACKUP_DIR   #查找最新的完全备份 LATEST_FULL_BACKUP=`find $FULLBACKUP_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`   # 查找最近修改的最新备份时间 #########m by sk 将文件目录改成时间"2017-10-01_02-02-02" #先转成TEMPSTR "2017-10-01 02:02:02" #LATEST_FULL_BACKUP_CREATED_TIME=`stat -c %Y $FULLBACKUP_DIR/$LATEST_FULL_BACKUP` TEMPSTR=`echo "$LATEST_FULL_BACKUP" | sed "s/_/ /"|sed "s/-/:/3g"` LATEST_FULL_BACKUP_CREATED_TIME=`date +%s -d "$TEMPSTR"` ######### #如果全备有效进行增量备份否则执行完全备份 if [ "$LATEST_FULL_BACKUP" -a `expr $LATEST_FULL_BACKUP_CREATED_TIME + $FULLBACKUP_INTERVAL - 300` -ge $STARTED_TIME ] ; then     # 如果最新的全备未过期则以最新的全备文件名命名在增量备份目录下新建目录     echo -e "完全备份$LATEST_FULL_BACKUP未过期,将根据$LATEST_FULL_BACKUP名字作为增量备份基础目录名"     echo "                     "     NEW_INCRDIR=$INCRBACKUP_DIR/$LATEST_FULL_BACKUP     mkdir -p $NEW_INCRDIR       # 查找最新的增量备份是否存在.指定一个备份的路径作为增量备份的基础     LATEST_INCR_BACKUP=`find $NEW_INCRDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n"  | sort -nr | head -1`         if [ ! $LATEST_INCR_BACKUP ] ; then             INCRBASEDIR=$FULLBACKUP_DIR/$LATEST_FULL_BACKUP             echo -e "增量备份将以$INCRBASEDIR作为备份基础目录"             echo "                     "         else             INCRBASEDIR=$INCRBACKUP_DIR/${LATEST_FULL_BACKUP}/${LATEST_INCR_BACKUP}             echo -e "增量备份将以$INCRBASEDIR作为备份基础目录"             echo "                     "         fi       echo "使用$INCRBASEDIR作为基础本次增量备份的基础目录."     $INNOBACKUPEXFULL --defaults-file=$MY_CNF --encrypt=AES256 --encrypt-key=jiamistring --encrypt-threads=5  --use-memory=4G $MYSQL_CMD --incremental --parallel=4 --no-lock --safe-slave-backup --slave-info --tmpdir=$TMP_DIR $NEW_INCRDIR --incremental-basedir $INCRBASEDIR > $TMPLOG 2>&1       #保留一份备份的详细日志       cat $TMPLOG>$logfiledate         if [ -z "`tail -1 $TMPLOG | grep 'innobackupex: completed OK!'`" ] ; then      echo "$INNOBACKUPEX命令执行失败:"; echo      echo -e "---------- $INNOBACKUPEX_PATH错误 ----------"      cat $TMPLOG      rm -f $TMPLOG      exit 1     fi       THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPLOG`     ##add by sk     echo "skmsize:`du -sm $THISBACKUP | awk '{print $1}'`">>$logfiledate     ##     rm -f $TMPLOG         echo -n "数据库成功备份到:$THISBACKUP"     xbcrypt -d --encrypt-key=jiamistring --encrypt-algo=AES256 -i $THISBACKUP/xtrabackup_checkpoints.xbcrypt -o $THISBACKUP/xtrabackup_checkpoints     echo       # 提示应该保留的备份文件起点     LATEST_FULL_BACKUP=`find $FULLBACKUP_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`     NEW_INCRDIR=$INCRBACKUP_DIR/$LATEST_FULL_BACKUP     LATEST_INCR_BACKUP=`find $NEW_INCRDIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n"  | sort -nr | head -1`     RES_FULL_BACKUP=${FULLBACKUP_DIR}/${LATEST_FULL_BACKUP}     RES_INCRE_BACKUP=`dirname ${INCRBACKUP_DIR}/${LATEST_FULL_BACKUP}/${LATEST_INCR_BACKUP}`       echo     echo -e '\e[31m NOTE:---------------------------------------------------------------------------------.\e[m' #红色     echo -e "必须保留$KEEP_FULLBACKUP份全备即全备${RES_FULL_BACKUP}和${RES_INCRE_BACKUP}目录中所有增量备份."     echo -e '\e[31m NOTE:---------------------------------------------------------------------------------.\e[m' #红色     echo else     echo  "*********************************"     echo -e "正在执行全新的完全备份...请稍等..."     echo  "*********************************"     $INNOBACKUPEXFULL --defaults-file=$MY_CNF --encrypt=AES256 --encrypt-key=jiamistring --encrypt-threads=5  --use-memory=4G --no-lock --safe-slave-backup  --parallel=4 --tmpdir=$TMP_DIR --slave-info $MYSQL_CMD $FULLBACKUP_DIR > $TMPLOG 2>&1     #保留一份备份的详细日志     cat $TMPLOG>$logfiledate     if [ -z "`tail -1 $TMPLOG | grep 'innobackupex: completed OK!'`" ] ; then      echo "$INNOBACKUPEX命令执行失败:"; echo      echo -e "---------- $INNOBACKUPEX_PATH错误 ----------"      cat $TMPLOG      rm -f $TMPLOG      exit 1     fi              THISBACKUP=`awk -- "/Backup created in directory/ { split( \\\$0, p, \"'\" ) ; print p[2] }" $TMPLOG`     ##add by sk         echo "skmsize:`du -sm $THISBACKUP | awk '{print $1}'`">>$logfiledate         ##     rm -f $TMPLOG       echo -n "数据库成功备份到:$THISBACKUP"     xbcrypt -d --encrypt-key=jiamistring --encrypt-algo=AES256 -i $THISBACKUP/xtrabackup_checkpoints.xbcrypt -o $THISBACKUP/xtrabackup_checkpoints     echo       # 提示应该保留的备份文件起点       LATEST_FULL_BACKUP=`find $FULLBACKUP_DIR -mindepth 1 -maxdepth 1 -type d -printf "%P\n" | sort -nr | head -1`       RES_FULL_BACKUP=${FULLBACKUP_DIR}/${LATEST_FULL_BACKUP}       echo     echo -e '\e[31m NOTE:---------------------------------------------------------------------------------.\e[m' #红色     echo -e "无增量备份,必须保留$KEEP_FULLBACKUP份全备即全备${RES_FULL_BACKUP}."     echo -e '\e[31m NOTE:---------------------------------------------------------------------------------.\e[m' #红色     echo   fi   #删除过期的全备 echo -e "find expire backup file...........waiting........." echo -e "寻找过期的全备文件并删除">>$logfiledate #for efile in $(/usr/bin/find $FULLBACKUP_DIR/ -mtime +7) ##do ##  if [ -d ${efile} ]; then #   rm -rf "${efile}" #   echo -e "删除过期全备文件:${efile}" >>$logfiledate #   elif [ -f ${efile} ]; then #   rm -rf "${efile}" #   echo -e "删除过期全备文件:${efile}" >>$logfiledate #   fi; #   #done cd $FULLBACKUP_DIR /usr/bin/find   -maxdepth 1 -type d -mtime +5|xargs rm -fr if [ $? -eq "0" ];then    echo    echo -e "未找到可以删除的过期全备文件" fi echo echo "完成于: `date +%F' '%T' '%w`"   cd $INCRBACKUP_DIR /usr/bin/find   -maxdepth 1 -type d -mtime +5|xargs rm -fr if [ $? -eq "0" ];then    echo    echo -e "未找到可以删除的过期增量备份文件" fi echo echo "完成于: `date +%F' '%T' '%w`" exit 0