MySQL备份脚本与rsync创建

脚本功能：

1·指定不同的参数，实现分库、分表、rsync创建

2·打印执行结果，同时记录到日志

脚本内容如下：

1. #!/bin/bash
3. STARTTIME=`/bin/date +%Y%m%d%H%M%S`
4. STARTTIME7=`date -d '7 days ago' +%Y%m%d%H%M%S`
5. YEARMONTH=`date +%Y%m`
6. MKDIR='/bin/mkdir'
7. MYSQLDUMP='/usr/bin/mysqldump'
8. GZIP='/bin/gzip'
9. RM='/bin/rm'
10. MV='/bin/mv'
11. CHMOD='/bin/chmod'
12. TOUCH='/bin/touch'
13. BACKUP_DIR='/server/backup_mysql'
14. BINLOG_DIR='/var/lib/mysql'
15. BINLOG_NAME='mysql-bin'
17. #define function
18. function log_success_msg()
19. {
20. echo " SUCCESS! $@"
21. }
22. function log_failure_msg()
23. {
24. echo " ERROR! $@"
25. }
27. function ResultStat() {
28. if [ $RESULT -eq 0 ]; then
29. log_success_msg " [`/bin/date +%Y%m%d%H%M%S`] $STATS_NAME
30. -------------------------------------------------------------------------------"|tee -a backup_mysql.$YEARMONTH.log
31. else
32. log_failure_msg " [`/bin/date +%Y%m%d%H%M%S`] $STATS_NAME
33. -------------------------------------------------------------------------------"|tee -a backup_mysql.$YEARMONTH.log
34. fi
35. }
37. function UsageError()
38. {
39. echo
40. echo "$0 : Run Error"
41. echo "Usage : Try '$0 -d or -a'
42. -d : designation database or table
43. -a : detach of all databases
44. -rsyncd : configuration rsync server"
45. }
47. function CreateDir()
48. {
49. if [ ! -d $BACKUP_DIR ]; then
50. $MKDIR -p $BACKUP_DIR
51. RESULT=$?
52. STATS_NAME="Check OR Create dir...$BACKUP_DIR"
53. ResultStat
54. sleep 2
55. fi
56. cd $BACKUP_DIR
57. }
59. # create .my.cnf file
60. if [ ! -e '/root/.my.cnf' ]; then
61. $TOUCH /root/.my.cnf
62. $MV /root/.my.cnf /root/.my.cnf.$STARTTIME.bak
63. cat >/root/.my.cnf <<+
64. [client]
65. user = root
66. password = 123456
67. +
68. RESULT=$?
69. STATS_NAME="The /root/.my.cnf is created."
70. ResultStat
71. fi
73. # chose backup type
74. if [ $# -eq 0 ]; then
75. UsageError
76. exit 1
77. elif [ $1 = '-d' ]; then
78. CreateDir
79. if [[ -n $2 && -z $3 ]]; then
80. $MYSQLDUMP -x -R --triggers --master-data=2 $2 |$GZIP >$2.$STARTTIME.sql.gz
81. RESULT=$?
82. STATS_NAME="# $MYSQLDUMP -x -R --triggers --master-data=2 $2 |$GZIP >$2.$STARTTIME.sql.gz"
83. ResultStat
84. elif [[ -n $2 && -n $3 ]]; then
85. $MYSQLDUMP -x -R --triggers --master-data=2 $2 $3 |$GZIP >$2.$3.$STARTTIME.sql.gz
86. RESULT=$?
87. STATS_NAME="# $MYSQLDUMP -x -R --triggers --master-data=2 $2 $3 |$GZIP >$2.$3.$STARTTIME.sql.gz"
88. ResultStat
89. else
90. UsageError
91. exit 1
92. fi
93. elif [ $1 = '-a' ]; then
94. CreateDir
95. for db in $(mysql -e "show databases" | egrep -v "(Database|test|information_schema|performance_schema)")
96. do
97. $MYSQLDUMP -x -R --triggers --master-data=2 $db |$GZIP >$db.$STARTTIME.sql.gz
98. RESULT=$?
99. STATS_NAME="# $MYSQLDUMP -x -R --triggers --master-data=2 $db |$GZIP >$db.$STARTTIME.sql.gz"
100. ResultStat
101. done
102. elif [ $1 = '-rsyncd' ]; then
103. PASSWORD_FILE='/usr/local/rsync/rsyncd.password'
104. RSYNCD_DIR='/usr/local/rsync'
105. if [ ! -d $RSYNCD_DIR ]; then
106. $MKDIR -p $RSYNCD_DIR
107. RESULT=$?
108. STATS_NAME="# $MKDIR -p $RSYNCD_DIR"
109. ResultStat
110. fi
111. if [ ! -e '/etc/xinetd.d/rsync' ]; then
112. $TOUCH /etc/xinetd/d/rsync
113. fi
114. $MV /etc/xinetd.d/rsync /etc/xinetd.d/rsync.$STARTTIME.bak
115. cat >/etc/xinetd.d/rsync <<+
116. service rsync
117. {
118. disable = no
119. socket_type = stream
120. wait = no
121. user = root
122. server = /usr/bin/rsync
123. server_args = --daemon --config=/etc/rsyncd.conf
124. log_on_failure += USERID
125. }
126. +
127. RESULT=$?
128. STATS_NAME="The /etc/xinetd.d/rsync is created."
129. ResultStat
131. if [ ! -e '/etc/rsyncd.conf' ]; then
132. $TOUCH /etc/rsyncd.conf
133. fi
134. $MV /etc/rsyncd.conf /etc/rsyncd.conf.$STARTTIME.bak
135. cat >/etc/rsyncd.conf <<+
136. uid = root
137. gid = root
138. use chroot = no
139. max connections = 5
140. lock file = /usr/local/rsync/rsyncd.lock
141. log file = /usr/local/rsync/rsyncd.log
142. pid file = /usr/local/rsync/rsyncd.pid
143. hosts allow = 192.168.250.250
144. hosts deny = *
145. ignore errors
146. read only = yes
147. list = no
148. auth users = backupdbuser
149. secrets file = /usr/local/rsync/rsyncd.password
151. [BINLOG]
152. path = $BINLOG_DIR
153. include = $BINLOG_NAME.*
154. exclude = *
156. [DUMPDB]
157. path = $BACKUP_DIR
158. +
159. RESULT=$?
160. STATS_NAME="The /etc/rsyncd.conf is created."
161. ResultStat
163. cat >$PASSWORD_FILE <<+
164. backupdbuser:vn55JU;bp
165. +
166. $CHMOD 600 $PASSWORD_FILE
167. RESULT=$?
168. STATS_NAME="The $PASSWORD_FILE is created.
169. # $CHMOD 600 $PASSWORD_FILE
170. please start RsyncServer. Refer: /etc/init.d/xinetd reload | restart"
171. ResultStat
172. exit 0
173. else
174. UsageError
175. exit 1
176. fi
178. #rm 7 days ago
179. for db in $(mysql -e "show databases" | egrep -v "(Database|test|information_schema|performance_schema)")
180. do
181. if [ ! -f $db.$STARTTIME7.sql.gz ]; then
182. STATS_NAME="$db.$STARTTIME7.sql.gz is not exist..."
183. ResultStat
184. else
185. $RM $db.$STARTTIME7.sql.gz -rf
186. RESULT=$?
187. STATS_NAME="# $RM $db.$STARTTIME7.sql.gz -rf..."
188. ResultStat
189. fi
190. done