|
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
|
#!/bin/bash
#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'
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_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
$?`
echo
"binlog_index:$binlog_index"
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
|
本文转自 wangpengtai 51CTO博客，原文链接：http://blog.51cto.com/wangpengtai/1942258，如需转载请自行联系原作者
