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

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