MySQL自动化备份脚本

今天完成了一个数据库备份的脚本,主要功能如下,希望大家给一些改进的建议

-a: backup all database #全库备份

-e: backup each database#分库备份

-d: backup single/multi database#备份指定的一个库或者多个库

-t: backup single/multi table of singledatabase#备份一个库下面的一个表或者多个表

-b: backup binlog#备份binlog日志,备份过程,每次记录最后的二进制文件号,将之前范围内的binlog打包(bz2格式),放在$DIR_BACKUP目录下

-r: recover all database(!requirepassword!)#恢复全库,为保安全,恢复时需要输入密码

-o: recover single database/talbe,youshould be designation database name(!require password!)#恢复单库或单表

-p: create connect mysql password#创建连接mysql的密码文件,存放位置$DIR_MySQL/etc目录下,权限是600

-s: configuration rsyncd#创建rsyncd服务(需要时,可修改参数创建,因backup机上有rsyncd服务,故不需要在每台DB server上创建rsyncd服务)

    If you want ceate arsyncd, you should enter 'bakrec_mysql.sh -s cet'

    If you want restartrsyncd, you should enter 'bakrec_mysql.sh -s rst'

-c: sync to backup center#同步到backup1

1. #!/bin/bash
3. # email: [email protected]
4. # last change time: 2011-08-03
6. set -e
7. set -u
9. TIME=`date +%Y%m%d%H%M%S`
10. TIME_7=`date -d '7 days ago' +%Y%m%d%H%M%S`
11. TIME_YM=`date +%Y%m`
12. DIR_MYSQL='/usr/local/mysql'
13. DIR_BACKUP="/tmp/backup"
14. DIR_DATA="$DIR_MYSQL/data"
15. DIR_PASSWD="$DIR_MYSQL/etc"
16. FILE_PASSWD="$DIR_PASSWD/passwordfile"
17. BINLOG_NAME='mysql-bin'
18. CMD_MYSQLBINLOG="$DIR_MYSQL/bin/mysqlbinlog"
19. CMD_MYSQLDUMP="$DIR_MYSQL/bin/mysqldump"
20. CMD_MYSQL="$DIR_MYSQL/bin/mysql"
21. LIST_EXCLUDE_DB='(test|information_schema|performance_schema)'
23. if [ ! -d $DIR_BACKUP/$TIME_YM ]; then
24. mkdir -p $DIR_BACKUP/$TIME_YM
25. fi
26. cd $DIR_BACKUP/$TIME_YM
28. function result_status()
29. {
30. if [ $? -eq 0 ]; then
31. echo "[`date +%Y%m%d%H%M%S`] SUCCESS! "|tee -a log.$TIME_YM
32. else
33. echo "[`date +%Y%m%d%H%M%S`] ERROR! "|mail -s "backup error $HOSTNAME" [email protected]|tee -a log.$TIME_YM
34. fi
35. }
37. function usage_error()
38. {
39. echo "Usage: $0 RUN ERROR"
40. echo "
41. -a: backup all database
42. -e: backup each database
43. -d: backup single/multi database
44. -t: backup single/multi table of single database
45. -b: backup binlog
46. -r: recover all database(!require password!)
47. -o: recover single database/talbe,you should be designation database name(!require password!)
48. -p: create connect mysql password
49. -s: configuration rsyncd
50. If you want ceate a rsyncd, you should enter '$0 -s cet'
51. If you want restart rsyncd, you should enter '$0 -s rst'
52. -c: sync to backup center
53. "
54. exit 0
55. }
57. function read_pwd()
58. {
59. read USER PASSWD < $FILE_PASSWD
60. }
62. function backup()
63. {
64. read_pwd
65. LOGBIN_STATUS=`$CMD_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW VARIABLES LIKE 'log_bin'" | gawk '{print $2}'`
66. if [ $LOGBIN_STATUS = "ON" ]; then
67. MASTER='--master-data=2'
68. else
69. MASTER=' '
70. fi
71. }
73. function backup_all()
74. {
75. backup
76. $CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R -A --add-drop-database $MASTER |gzip >$HOSTNAME.all.$TIME.sql.gz
77. }
79. function backup_each()
80. {
81. backup
82. for db in $($CMD_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW DATABASES"|egrep -v $LIST_EXCLUDE_DB)
83. do
84. $CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R $MASTER $db --databases |gzip >$HOSTNAME.$db.$TIME.sql.gz
85. done
86. # delete 7 days ago
87. for db in $($CMD_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW DATABASES"|egrep -v $LIST_EXCLUDE_DB)
88. do
89. if [ ! -f $HOSTNAME.$db.$TIME_7.sql.gz ]; then
90. echo
91. else
92. rm $HOSTNAME.$db.$TIME_7.sql.gz -f
93. fi
94. done
95. }
97. function backup_db()
98. {
99. shift
100. backup
101. $CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R $MASTER --databases $@| gzip>$HOSTNAME.$OPTARG.$TIME.sql.gz
102. }
104. function backup_dt()
105. {
106. shift
107. if [ $# -ge 2 ]; then
108. backup
109. $CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R $MASTER $@| gzip>$HOSTNAME.$OPTARG.$TIME.sql.gz
110. else
111. usage_error
112. fi
113. }
115. function backup_binlog()
116. {
117. if [ -s $DIR_BACKUP/mysql-bin.queue ]; then
118. read POS < $DIR_BACKUP/mysql-bin.queue
119. cd $DIR_DATA
120. tar -jcvf $DIR_BACKUP/$TIME_YM/$HOSTNAME.$POS.$TIME.bz2 `gawk -F'/' '{print $2}' $BINLOG_NAME.index |sed -n "/$POS/,//p"`
121. cd -
122. if [ -f $DIR_BACKUP/$TIME_YM/$HOSTNAME.$POS.$TIME_7.bz2 ]; then
123. rm $DIR_BACKUP/$TIME_YM/$HOSTNAME.$POS.$TIME_7.bz2 -f
124. fi
125. fi
126. # write last pos
127. gawk -F'/' '{print $2}' $DIR_DATA/$BINLOG_NAME.index | tail -n 1 >$DIR_BACKUP/mysql-bin.queue
128. }
130. function recover_all()
131. {
132. read_pwd
133. shift
134. $CMD_MYSQL -u$USER -p -e "source $@"
135. }
137. function recover_dt()
138. {
139. read_pwd
140. shift
141. if [ $# -eq 2 ]; then
142. $CMD_MYSQL -u$USER -p -D $1 -e "source $2"
143. else
144. usage_error
145. fi
146. }
148. function passwd_create()
149. {
150. if [ ! -e "$DIR_PASSWD" ]; then
151. mkdir -p $DIR_PASSWD
152. fi
153. echo -n "Please enter MySQL(user=root)'s password:"
154. read -s MYSQL_FASSWD
155. cat >$FILE_PASSWD <<+
156. root $MYSQL_FASSWD
157. +
158. chmod 600 $FILE_PASSWD
159. }
161. function rsyncd()
162. {
163. shift
164. if [ $# -eq 0 ]; then
165. usage_error
166. else
167. DIR_RSYNCD='/usr/local/rsync'
168. FILE_RSYNCD_PASSWORD="$DIR_RSYNCD/rsyncd.password"
169. case "$1" in
170. 'cet')
171. if [ ! -d $DIR_RSYNCD ]; then
172. mkdir -p $DIR_RSYNCD
173. fi
174. if [ ! -e "$DIR_RSYNCD/rsyncd.conf" ]; then
175. touch $DIR_RSYNCD/rsyncd.conf
176. fi
177. mv $DIR_RSYNCD/rsyncd.conf $DIR_RSYNCD/rsyncd.conf.$TIME.bak
178. cat >$DIR_RSYNCD/rsyncd.conf <<+
179. uid = root
180. gid = root
181. use chroot = no
182. max connections = 5
183. lock file = $DIR_RSYNCD/rsyncd.lock
184. log file = $DIR_RSYNCD/rsyncd.log
185. pid file = $DIR_RSYNCD/rsyncd.pid
186. hosts allow = 192.168.250.251
187. hosts deny = *
188. ignore errors
189. read only = yes
190. list = no
191. auth users = backupdbuser
192. secrets file = $DIR_RSYNCD/rsyncd.password
193. [BINLOG]
194. path = $DIR_DATA
195. include = $BINLOG_NAME.*
196. exclude = *
198. [DUMPDB]
199. path = $DIR_BACKUP
200. +
201. cat >$FILE_RSYNCD_PASSWORD <<+
202. username:password
203. +
204. chmod 600 $FILE_RSYNCD_PASSWORD
205. exit 0
206. ;;
207. 'rst')
208. if [ -s "$DIR_RSYNCD/rsyncd.pid" ]; then
209. rsyncd_pid=`cat "$DIR_RSYNCD/rsyncd.pid"`
210. if (kill -0 $rsyncd_pid 2>/dev/null); then
211. echo "Shutting down rsyncd"
212. kill $rsyncd_pid
213. else
214. echo "rsyncd #$rsyncd_pid is not running!"
215. rm "$DIR_RSYNCD/rsyncd.pid"
216. fi
217. fi
218. sleep 2
219. rsync --daemon --config=$DIR_RSYNCD/rsyncd.conf --port=873
220. echo "rsync --daemon --config=$DIR_RSYNCD/rsyncd.conf --port=873"
221. echo "netstat -tunlp | grep rsync"
222. netstat -tunlp | grep rsync
223. ;;
224. *)
225. usage_error
226. ;;
227. esac
228. fi
229. }
231. #main
232. if [ $# -eq 0 ]; then
233. usage_error
234. else
235. while getopts :aed:t:r:o:bpsc varname
236. do
237. case $varname in
238. a)
239. backup_all
240. ;;
241. e)
242. backup_each
243. ;;
244. d)
245. backup_db $@
246. ;;
247. t)
248. backup_dt $@
249. ;;
250. b)
251. backup_binlog
252. ;;
253. r)
254. recover_all $@
255. ;;
256. o)
257. recover_dt $@
258. ;;
259. p)
260. passwd_create
261. ;;
262. s)
263. rsyncd $@
264. ;;
265. c)
266. # rsync -czrptgoD --password-file=/tmp/.passwd $HOSTNAME.*.$TIME.sql.gz [email protected]::DUMPDB/$HOSTNAME/$TIME_YM
267. rsync -czrpt --password-file=/tmp/.passwd $DIR_BACKUP/$TIME_YM [email protected]::DUMPDB/$HOSTNAME
268. result_status
269. ;;
270. :)
271. echo "$varname: 缺少参数"
272. usage_error
273. ;;
274. \?)
275. echo "$varname: 非法选项"
276. usage_error
277. ;;
278. esac
279. done
280. fi