Xtrabackup定时备份数据库脚本
来源:互联网 发布:windows ssh使用 编辑:程序博客网 时间:2024/05/22 10:31
定时备份MySQL数据库
脚本内容:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127
#!/bin/bash## 备份计划任务#### 每天凌晨1:30一次全量备份## 每天间隔1小时一次增量备份## 30 1 * * * backup.sh full## 00 * * * * backup.sh inc#### 恢复数据步骤:## (1)、查看备份日志,找到全量备份和增量备份的关系(注意增量备份的顺序)#### cat ${BACKUP_BASE_DIR}/${INC_BASE_LIST}## (2)、全量备份## innobackupex --defaults-file=/etc/my.cnf --apply-log ${BACKUP_BASE_DIR}/full_dir#### (3)、第一个增量## innobackupex --defaults-file=/etc/my.cnf --apply-log ${BACKUP_BASE_DIR}/full_dir \## --incremental-dir=${BACKUP_BASE_DIR}/one_inc_dir#### (4)、第二个增量## innobackupex --defaults-file=/etc/my.cnf --apply-log ${BACKUP_BASE_DIR}/full_dir \## --incremental-dir=${BACKUP_BASE_DIR}/two_inc_dir#### (5)、恢复数据## innobackupex --defaults-file=/etc/my.cnf --copy-back ${BACKUP_BASE_DIR}/full_dirPATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbinBACKUP_BASE_DIR="/backup/xtrabackup"INC_BASE_LIST="${BACKUP_BASE_DIR}/inc_list.txt"XTRABACKUP_PATH="/usr/local/xtrabackup/bin/innobackupex"MYSQL_CNF="/etc/my.cnf"MYSQL_HOSTNAME=127.0.0.1MYSQL_USERNAME=rootMYSQL_PASSWORD=w7tQ5NNWWRkLOCK_FILE=/tmp/innobackupex.lockTHREAD=3mkdir -p ${BACKUP_BASE_DIR}CURRENT_BACKUP_PATH="${BACKUP_BASE_DIR}/$(date +%F_%H-%M)"[[ -d ${CURRENT_BACKUP_PATH} ]] && CURRENT_BACKUP_PATH="${BACKUP_BASE_DIR}/$(date +%F_%H-%M-%S)"print_help(){echo "--------------------------------------------------------------"echo "Usage: $0 full | inc | help "echo "--------------------------------------------------------------"exit 1}[[ $# -lt 1 || "$1" == "help" ]] && print_help[[ -f "$LOCK_FILE" ]] && echo -e "Usage: rm -f $LOCK_FILE\nUsage: chattr -i $LOCK_FILE && rm -f $LOCK_FILE" && exit 1FullBackup(){touch $LOCK_FILEchattr +i $LOCK_FILElocal rc=0${XTRABACKUP_PATH} \--defaults-file=${MYSQL_CNF} \--user=${MYSQL_USERNAME} \--password=${MYSQL_PASSWORD} \--host=${MYSQL_HOSTNAME} \--parallel=${THREAD} \--no-timestamp ${CURRENT_BACKUP_PATH} > ${CURRENT_BACKUP_PATH}_full.log 2>&1grep ".*\ completed\ OK\!" ${CURRENT_BACKUP_PATH}_full.log > /dev/null 2>&1if [ $? -ne 0 ];thenrc=1[[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && rm -rf ${CURRENT_BACKUP_PATH}elseecho "NULL|${CURRENT_BACKUP_PATH}|full" >> ${INC_BASE_LIST}[[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && chattr +i ${CURRENT_BACKUP_PATH} || rc=1fichattr -i ${LOCK_FILE}rm -f $LOCK_FILEchattr +a ${INC_BASE_LIST}return $rc}IncBackup(){touch $LOCK_FILEchattr +i $LOCK_FILElocal rc=0PREV_BACKUP_DIR=$(sed '/^$/d' ${INC_BASE_LIST} | tail -1 | awk -F '|' '{print $2}')${XTRABACKUP_PATH} \--defaults-file=${MYSQL_CNF} \--user=${MYSQL_USERNAME} \--password=${MYSQL_PASSWORD} \--host=${MYSQL_HOSTNAME} \--no-timestamp --incremental ${CURRENT_BACKUP_PATH} \--incremental-basedir=${PREV_BACKUP_DIR} > ${CURRENT_BACKUP_PATH}_inc.log 2>&1grep ".*\ completed\ OK\!" ${CURRENT_BACKUP_PATH}_inc.log > /dev/null 2>&1if [ $? -ne 0 ];thenrc=1[[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && rm -rf ${CURRENT_BACKUP_PATH}elseecho "${PREV_BACKUP_DIR}|${CURRENT_BACKUP_PATH}|inc" >> ${INC_BASE_LIST}[[ -d ${CURRENT_BACKUP_PATH} && $(pwd) != "/" ]] && chattr +i ${CURRENT_BACKUP_PATH} || rc=1fichattr -i ${LOCK_FILE}rm -f $LOCK_FILEchattr +a ${INC_BASE_LIST}return $rc}## 全量备份if [ "$1" == "full" ];thenFullBackupfi## 增量备份if [ "$1" == "inc" ];then## 判断上一次备份是否存在,无则进行全量备份if [[ ! -f ${INC_BASE_LIST} || $(sed '/^$/d' ${INC_BASE_LIST} | wc -l) -eq 0 ]];thenFullBackupelseIncBackupfifi## 删除14天前的备份if [[ -d ${BACKUP_BASE_DIR} && $(pwd) != "/" ]];thenfind ${BACKUP_BASE_DIR} -name "$(date -d '14 days ago' +'%F')_*" | xargs chattr -ifind ${BACKUP_BASE_DIR} -name "$(date -d '14 days ago' +'%F')_*" | xargs rm -rffi
加入计划任务
123
crontab -e30 1 * * * /home/sh/backup.sh full00 */6 * * * /home/sh/backup.sh inc
阅读全文
0 0
- Xtrabackup定时备份数据库脚本
- 数据库定时备份脚本
- xtrabackup自动备份脚本
- xtrabackup自动备份脚本
- xtrabackup备份脚本
- MySQL XtraBackup备份脚本
- xtrabackup自动备份脚本
- Oracle数据库定时备份脚本
- Oracle数据库定时备份脚本
- mysql 定时备份数据库脚本
- Xtrabackup数据库备份工具
- xtrabackup备份mysql数据库
- xtrabackup<三、增量备份脚本>
- 定时备份Sybase数据库的脚本问题
- Oracle数据库定时备份脚本[转]
- MySQL数据库定时自动备份脚本
- MySQL数据库定时自动备份脚本
- Linux定时备份数据库与脚本加密
- 两款简易计算器纯js版附源码下载
- Codeforces Round #434 div2 A, B , C,D 题 题解
- HDU
- 处理注册时的生日日期格式问题
- 阿里云数据库推出SQL Server 2016 单机基础版本
- Xtrabackup定时备份数据库脚本
- spring boot项目通过外置tomcat运行
- 科三考试---专为denny而写
- Pig Latin 常用指令
- Robot Framework如何对Android的控件定位
- 不惧面试:HTTP协议(1)
- Sublime Text 3 快捷键总结
- 杭电6216 (打表+二分)之 A Cubic number and A Cubic Number
- 关于tomcat启动完成后,出现Exception http-apr-8080-2