本文共 8833 字，大约阅读时间需要 29 分钟。
一、MySQL的日常备份方案：
全备+增量备份：
1、周日凌晨三点进行全备；
2、周一到周日增量备份。
不是往常的周日全备份，周一到周六增量备份，这样如果周日数据库在完全备份前出问题，恢复完成后，会少周日一天的数据量，所以七天增量备份，周日全备可以更好的保全数据。
这是备份周期演示表：
| 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 |
二、备份脚本：
模块化定制，可以随意移动，调节备份策略！
变量栏的帐号密码，文件路径根据自己实际环境可以进行修改，自由度比较高，模块函数全变量，适用度较高，但是可能还有不完善的地方，欢迎提出，谢谢！
| 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 | 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` do $DB_BIN /mysqlbinlog -u$DB_USER -p$DB_PASSWORD -P$DB_PORT --start-datetime= "$START_BACKUPBINLOG_TIMEPOINT" $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 |
三、测试方法：
使用了一个测试脚本，修改日期，达到一个月的演示效果。
| 1 2 3 4 5 6 | #!/bin/bash for day in {1..30} do date -s "2017-06-$day 12:00:00" /bin/bash /root/bakup/mysql_backup .sh done |
四、脚本使用方法：
| 1 2 3 4 | crontab -e 0 3 * * * /bin/bash /root/bakup/mysql_bakup .sh > /dev/null 2>&1 空格 #加个空格，不然有些机器不能执行脚本 |