您好,登录后才能下订单哦!
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
一、MySQL的日常备份方案:
全备+增量备份:
1、周日凌晨三点进行全备;
2、周一到周日增量备份。
不是往常的周日全备份,周一到周六增量备份,这样如果周日数据库在完全备份前出问题,恢复完成后,会少周日一天的数据量,所以七天增量备份,周日全备可以更好的保全数据。
这是备份周期演示表:
Sun 3:00------Mon 3:00-----------------Tue 3:00----------Wed 3:00----------Thu 3:00----------Fri 3:00----------Sat 3:00----------Sun 3:00 (flush)Sun full---(flush)Sun->Mon binlog---(flush)Mon->Tue---(flush)Tue->Wed---(flush)Wed->Thu---(flush)Thu->Fri---(flush)Fri->Sat---(flush)Sun full---(flush)Sun->Mon binlog---(flush)Mon->Tue---(flush)Tue->Wed---(flush)Wed->Thu---(flush)Thu->Fri---(flush)Fri->Sat---(flush)Sun full
二、备份脚本:
模块化定制,可以随意移动,调节备份策略!
变量栏的帐号密码,文件路径根据自己实际环境可以进行修改,自由度比较高,模块函数全变量,适用度较高,但是可能还有不完善的地方,欢迎提出,谢谢!
vim /root/mysql_bakup.sh #!/bin/bash #Date:2017/5/2 #Author:wangpengtai #Blog:http://wangpengtai.blog.51cto.com #At Sunday, we will backup the completed databases and the incresed binary log during Saturday to Sunday. #In other weekdays, we only backup the increaing binary log at that day! ################################ #the globle variables for MySQL# ################################ DB_USER='root' DB_PASSWORD='123456' DB_PORT='3306' BACKUPDIR='/tmp/mysqlbakup' BACKUPDIR_OLDER='/tmp/mysqlbakup_older' DB_PID='/data/mysql/log/mysqld.pid' DB_SOCK='/data/mysql/log/mysql.sock' LOG_DIR='/data/mysql/log' BACKUP_LOG='/tmp/mysqlbakup/backup.log' DB_BIN='/usr/local/mysql/bin' #time variables for completed backup FULL_BAKDAY='Sunday' TODAY=`date +%A` DATE=`date +%Y%m%d` ########################### #time variables for binlog# ########################### #liftcycle for saving binlog DELETE_OLDLOG_TIME=$(date "-d 14 day ago" +%Y%m%d%H%M%S) #The start time point to backup binlog, the usage of mysqlbinlog is --start-datetime, --stop-datetime, time format is %Y%m%d%H%M%S, eg:20170502171054, time zones is [start-datetime, stop-datetime) #The date to start backup binlog is yesterday at this very moment! START_BACKUPBINLOG_TIMEPOINT=$(date "-d 1 day ago" +"%Y-%m-%d %H:%M:%S") #BINLOG_LIST=`cat /data/mysql/log/mysql-bin.index` #注意在my.cnf中配置binlog文件位置时需要使用绝对路径,一定想成好习惯,不要给别人挖坑!! #####################举例######################## #[mysqld] #log_bin = /var/lib/mysql/mysql-bin #####################举例######################## BINLOG_INDEX='/data/mysql/log/mysql-bin.index' ############################################## #Judge the mysql process is running or not. # #mysql stop return 1, mysql running return 0.# ############################################## function DB_RUN(){ if test -a $DB_PID && test -a $DB_SOCK;then return 0 else return 1 fi } ################################################################################################### #Judge the bacup directory is exsit not. # #If the mysqlbakup directory was exsited, there willed return 0. # # If there is no a mysqlbakup directory, the fuction will create the directory and return value 1.# ################################################################################################### function BACKDIR_EXSIT(){ if test -d $BACKUPDIR;then # echo "$BACKUPDIR was exist." return 0 else echo "$BACKUPDIR is not exist, now create it." mkdir -pv $BACKUPDIR return 1 fi } ################################################################################################### #Judge the binlog is configed or not. # #If the mysqlbakup directory was exsited, there willed return 0. # # If there is no a mysqlbakup directory, the fuction will create the directory and return value 1.# ################################################################################################### function BINLOG_EXSIT(){ if test -f $BINLOG_INDEX;then # echo "$BACKUPDIR was exist." return 0 fi } ################################################### #The full backup for all Databases # #This function is use to backup the all databases.# ################################################### function FULL_BAKUP(){ echo "At `date +%D\ %T`: Starting full backup the MySQL DB ... " # rm -fr $BACKUPDIR/db_fullbak_$DATE.sql #for test !! $DB_BIN/mysqldump --lock-all-tables --flush-logs --master-data=2 -u$DB_USER -p$DB_PASSWORD -P$DB_PORT -A |gzip > $BACKUPDIR/db_fullbak_$DATE.sql.gz FULL_HEALTH=`echo $?` if [[ $FULL_HEALTH == 0 ]];then echo "At `date +%D\ %T`: MySQL DB incresed backup successfully" else echo "MySQL DB full backup failed!" fi } #python # >>> with open('/data/mysql/log/mysql-bin.index','r') as obj: # ... for i in obj: # ... print os.path.basename(i) # ... # mysql-bin.000006 # mysql-bin.000007 # mysql-bin.000008 # mysql-bin.000009 function INCREASE_BAKUP(){ echo "At `date +%D\ %T`: Starting increased backup the MySQL DB ... " $DB_BIN/mysqladmin -u$DB_USER -p$DB_PASSWORD -P$DB_PORT flush-logs $DB_BIN/mysql -u$DB_USER -p$DB_PASSWORD -P$DB_PORT -e "purge master logs before ${DELETE_OLDLOG_TIME}" for i in `cat $BINLOG_INDEX | awk -F'/' '{print $NF}'` do $DB_BIN/mysqlbinlog -u$DB_USER -p$DB_PASSWORD -P$DB_PORT --start-datetime="$START_BACKUPBINLOG_TIMEPOINT" $LOG_DIR/$i |gzip >> $BACKUPDIR/db_daily_$DATE.sql.gz done # $DB_BIN/mysqlbinlog -u$DB_USER -p$DB_PASSWORD -P$DB_PORT --start-datetime="$START_BACKUPBINLOG_TIME" $LOG_DIR/mysql-bin.[0-9]* |gzip >> $BACKUPDIR/db_daily_$DATE.sql.gz INCREASE_HEALTH=`echo $?` if [[ $INCREASE_HEALTH == 0 ]];then echo "At `date +%D\ %T`: MySQL DB incresed backup successfully" else echo "MySQL DB incresed backup failed!" fi } function OLDER_BACKDIR_EXSIT(){ if test -d $BACKUPDIR_OLDER;then # echo "$BACKUPDIR_OLDER was exist." return 0 else echo "$BACKUPDIR_OLDER is not exist, now create it." mkdir -pv $BACKUPDIR_OLDER # return 1 fi } function BAKUP_CLEANER(){ #move the backuped file that created time out of 7 days to the BACKUPDIR_OLDER directory returnkey=`find $BACKUPDIR -name "*.sql.gz" -mtime +7 -exec ls -lh {} \;` returnkey_old=`find $BACKUPDIR_OLDER -name "*.sql.gz" -mtime +14 -exec ls -lh {} \;` if [[ $returnkey != '' ]];then echo "----------------------" echo "Moving the older backuped file out of 7 days to $BACKUPDIR_OLDER." echo "The moved file list is:" find $BACKUPDIR -name "*.sql.gz" -mtime +7 -exec mv {} $BACKUPDIR_OLDER \; echo "-----------------------" elif [[ $returnkey_old != '' ]];then #delete the backuped file that created time out of 14 days from BACKUPDIR_OLDER directory. echo "Delete the older backuped file out of 14 days from $BACKUPDIR_OLDER." echo "The deleted files list is:" find $BACKUPDIR_OLDER -name "*.sql.gz" -mtime +14 -exec rm -fr {} \; fi } #################################### #--------------main----------------# #################################### function MAIN(){ DB_RUN #Judge the process is run or not, if not run, the script will not bakup db Run_process=`echo $?` echo $? if [[ $Run_process == 0 ]];then BINLOG_EXSIT binlog_index=`echo $?` if [[ $binlog_index == 0 ]];then echo "**********START**********" echo $(date +"%y-%m-%d %H:%M:%S %A") echo "~~~~~~~~~~~~~~~~~~~~~~~" if [[ $TODAY == $FULL_BAKDAY ]];then echo "Start completed bakup ..." INCREASE_BAKUP FULL_BAKUP #full backup to all DB BAKUP_CLEANER else echo "Start increaing bakup ..." INCREASE_BAKUP fi echo "~~~~~~~~~~~~~~~~~~~~~~~" echo $(date +"%y-%m-%d %H:%M:%S %A") echo "**********END**********" else echo "**********START**********" echo $(date +"%y-%m-%d %H:%M:%S %A") echo "~~~~~~~~~~~~~~~~~~~~~~~" echo "Sorry, MySQL binlog was not configed, please config the my.cnf firstly!" echo "~~~~~~~~~~~~~~~~~~~~~~~" echo $(date +"%y-%m-%d %H:%M:%S %A") echo "**********END**********" fi else echo "**********START**********" echo $(date +"%y-%m-%d %H:%M:%S %A") echo "~~~~~~~~~~~~~~~~~~~~~~~" echo "Sorry, MySQL was not running, the db could not be backuped!" echo "~~~~~~~~~~~~~~~~~~~~~~~" echo $(date +"%y-%m-%d %H:%M:%S %A") echo "**********END**********" fi } #starting runing BACKDIR_EXSIT $BACKUP_LOG OLDER_BACKDIR_EXSIT $BACKUP_LOG MAIN >> $BACKUP_LOG
三、测试方法:
使用了一个测试脚本,修改日期,达到一个月的演示效果。
#!/bin/bash for day in {1..30} do date -s "2017-06-$day 12:00:00" /bin/bash /root/bakup/mysql_backup.sh done
四、脚本使用方法:
crontab -e 0 3 * * * /bin/bash /root/bakup/mysql_bakup.sh > /dev/null 2>&1 空格 #加个空格,不然有些机器不能执行脚本
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。