mysql備份指令碼，一天執行一次全量備份，三次增量備份

標籤：.sql   sleep   roo   mysq   增量   產生   dump   mysql   資料   

線上一個小業務的mysql備份

全量備份

#!/bin/bash#crete by hexm at 2016-10-02#scripte name : full_backup.sh#descriptioni :  mysql full backup. oneday at a time.#time infodate=`date +%Y%m%d`#time_now1=`date +%Y%m%d-%H:%M:%S`#dir infoBACKUP_DIR="/app/dbbackup/full_backup/"today_dir="/app/dbbackup/full_backup/${date}"LOG=/app/dbbackup/logs/dumpfull_${date}.txtif [ ! -d "${BACKUP_DIR}" ]; then  mkdir -p "${BACKUP_DIR}"fiif [ ! -d "${today_dir}" ]; then  mkdir -p "${today_dir}"fiecho `date +%Y%m%d%H%M%S` > ${today_dir}/time.txt# 迴圈databases數組time_now1=`date +%Y%m%d-%H:%M:%S`echo "${time_now1} start to full backup" >> ${LOG}for db in `/usr/local/mysql/bin/mysql -uroot -pwoyoudabaitu -e ‘show databases‘|grep -Ev "mysql|test|performance_schema|information_schema|Database"`do    # 備份資料庫產生SQL檔案    #/bin/nice -n 19 /usr/local/mysql/bin/mysqldump -uroot -pwoyoudabaitu --database $db > ${today_dir}${db}-$(date +%Y%m%d).sql    /bin/nice -n 19 //usr/local/mysql/bin/mysqldump -uroot -pwoyoudabaitu --database --flush-privileges --master-data --single-transaction --opt $db > ${today_dir}/${db}-${date}.sqldonetime_now2=`date +%Y%m%d-%H:%M:%S`echo "${time_now2} full backup successfull" >> $LOG#get positionsleep 2if [ -f "${today_dir}/db_179_act-${date}.sql" ]; then  sed -n ‘22p‘ ${today_dir}/db_179_act-${date}.sql |awk -F"[! ]" ‘{print $4,$10}‘ > $today_dir/position  echo "${time_now2} get position ok!" >> $LOGelse  time_now3=`date +%Y%m%d-%H:%M:%S`  echo "${time_now3} get position faild" >> $LOG  #$SEND_MSGfi#remove 1 days ago backup.find $BACKUP_DIR  -mtime +7 -delete#echo "remove 1 days ago full backup!" >> $LOG

增量備份

#!/bin/bash#crete by hexm at 2016-10-03#scripte name : increment_backup.sh#descriptioni :  mysql increment backup. 6 hours at a time.#time infodate=`date +%Y%m%d`time_hour=`date +%Y%m%d%H`#time_now1=`date +%Y%m%d-%H:%M:%S`#dir infoBACKUP_DIR=/app/dbbackup/increment_backup/LOG=/app/dbbackup/logs/dumpincr_${date}.txt#TIME_DIR=/app/dbbackup/full_backup/${time_hour}if [ ! -d "${BACKUP_DIR}" ]; then  mkdir -p "${BACKUP_DIR}"fitime_now1=`date +%Y%m%d-%H:%M:%S`echo "${time_now1} start to increment backup" >> ${LOG}cd /app/mysql.bintar cf ${time_hour}_mysql_bin.tar.gz *waitmv /app/mysql.bin/*.tar.gz ${BACKUP_DIR}cdls ${BACKUP_DIR}/${time_hour}_mysql_bin.tar.gz &> /dev/nullif [ $? -eq 0 ];then  time_now2=`date +%Y%m%d-%H:%M:%S`  echo "${time_now2} increment backup successfull" >> $LOGelse  echo "${time_now2} increment backup faild" >> $LOG  #SEND_MSGfi#remove 1 days ago backup.#find $BACKUP_DIR  -mtime +7 -deletefind $BACKUP_DIR -type f -name "*.tar.gz" -mtime +7 -delete#echo "remove 1 days ago increment backup!" >> $LOG

定時任務

#mysqldump backup mysql 00 02 * * * /bin/bash /app/bin/full_backup.sh & &>/dev/null00 08 * * * /bin/bash /app/bin/increment_backup.sh & &>/dev/null00 14 * * * /bin/bash /app/bin/increment_backup.sh & &>/dev/null00 20 * * * /bin/bash /app/bin/increment_backup.sh & &>/dev/null

 

mysql備份指令碼，一天執行一次全量備份，三次增量備份