分享程序员开发的那些事...
更新时间：2022-08-17 07:51:45
一、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 backupFULL_BAKDAY='Sunday'
TODAY=`date +%A`
DATE=`date +%Y%m%d`
############################time variables for binlog#############################liftcycle for saving binlogDELETE_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.000009function 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 runingBACKDIR_EXSIT $BACKUP_LOGOLDER_BACKDIR_EXSIT $BACKUP_LOGMAIN >> $BACKUP_LOG |
三、测试方法：
使用了一个测试脚本，修改日期，达到一个月的演示效果。
|
1
2
3
4
5
6
|
#!/bin/bashfor 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 空格
#加个空格，不然有些机器不能执行脚本 |