MySQL XtraBackup备份脚本

kiloyp

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 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246

#backup.sh #!/bin/sh #on xtrabackup 2.1.8 # 第一次执行它的时候它会检查是否有完全备份,否则先创建一个全库备份 # 当你再次运行它的时候，它会根据脚本中的设定来基于之前的全备或增量备份进行增量备份 ################## #author:rrhelei@126.com# ################## INNOBACKUPEX_PATH=innobackupex  #INNOBACKUPEX的命令 INNOBACKUPEXFULL=/usr/local/xtrabackup/bin/$INNOBACKUPEX_PATH  #INNOBACKUPEX的命令路径 #mysql目标服务器以及用户名和密码 MYSQL_CMD="--host=192.168.1.250--user=root --password=MANAGER --port=3306" MYSQL_UP="--user=root --password='MANAGER' --port=3306 "  #mysqladmin的用户名和密码 TMPLOG="/tmp/innobackupex.$$.log" MY_CNF=/etc/my.cnf#mysql的配置文件 MYSQL=/usr/local/mysql/bin/mysql MYSQL_ADMIN=/usr/local/mysql/bin/mysqladmin BACKUP_DIR=/databackup# 备份的主目录 FULLBACKUP_DIR=$BACKUP_DIR/full# 全库备份的目录 INCRBACKUP_DIR=$BACKUP_DIR/incre# 增量备份的目录 FULLBACKUP_INTERVAL=86400# 全库备份的间隔周期，时间：秒 KEEP_FULLBACKUP=1 # 至少保留几个全库备份 logfiledate=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 ~ /.*:3306/) {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` # 查找最近修改的最新备份时间 LATEST_FULL_BACKUP_CREATED_TIME=`stat-c %Y $FULLBACKUP_DIR/$LATEST_FULL_BACKUP` #如果全备有效进行增量备份否则执行完全备份 if ["$LATEST_FULL_BACKUP" -a `expr $LATEST_FULL_BACKUP_CREATED_TIME +$FULLBACKUP_INTERVAL + 5` -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 --use-memory=4G $MYSQL_CMD --incremental $NEW_INCRDIR--incremental-basedir $INCRBASEDIR > $TMPLOG 2>&1 #保留一份备份的详细日志 cat$TMPLOG>/backup/$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` rm-f $TMPLOG echo -n "数据库成功备份到:$THISBACKUP" 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[31mNOTE:---------------------------------------------------------------------------------.\e[m'#红色 echo -e "必须保留$KEEP_FULLBACKUP份全备即全备${RES_FULL_BACKUP}和${RES_INCRE_BACKUP}目录中所有增量备份." echo -e '\e[31mNOTE:---------------------------------------------------------------------------------.\e[m'#红色 echo else echo  "*********************************" echo -e "正在执行全新的完全备份...请稍等..." echo  "*********************************" $INNOBACKUPEXFULL--defaults-file=$MY_CNF --use-memory=4G  $MYSQL_CMD$FULLBACKUP_DIR > $TMPLOG 2>&1 #保留一份备份的详细日志 cat$TMPLOG>/backup/$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` rm-f $TMPLOG echo -n "数据库成功备份到:$THISBACKUP" 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[31mNOTE:---------------------------------------------------------------------------------.\e[m'#红色 echo -e "无增量备份,必须保留$KEEP_FULLBACKUP份全备即全备${RES_FULL_BACKUP}." echo -e '\e[31mNOTE:---------------------------------------------------------------------------------.\e[m'#红色 echo fi #删除过期的全备 echo -e "findexpire backup file...........waiting........." echo -e "寻找过期的全备文件并删除">>/backup/$logfiledate for efile in$(/usr/bin/find $FULLBACKUP_DIR/ -mtime +6) do if[ -d ${efile} ]; then rm-rf "${efile}" echo -e "删除过期全备文件:${efile}" >>/backup/$logfiledate elif[ -f ${efile} ]; then rm-rf "${efile}" echo -e "删除过期全备文件:${efile}" >>/backup/$logfiledate fi; done if [ $? -eq"0" ];then    echo    echo -e "未找到可以删除的过期全备文件" fi echo echo "完成于: `date +%F' '%T' '%w`" exit 0