今天完成了一个数据库备份的脚本,主要功能如下,希望大家给一些改进的建议
#!/bin/bash # email: lianjie.ning@qunar.com # last change time: 2011-08-03 set -e set -u TIME=`date +%Y%m%d%H%M%S` TIME_7=`date -d '7 days ago' +%Y%m%d%H%M%S` TIME_YM=`date +%Y%m` DIR_MYSQL='/usr/local/mysql' DIR_BACKUP="/tmp/backup" DIR_DATA="$DIR_MYSQL/data" DIR_PASSWD="$DIR_MYSQL/etc" FILE_PASSWD="$DIR_PASSWD/passwordfile" BINLOG_NAME='mysql-bin' CMD_MYSQLBINLOG="$DIR_MYSQL/bin/mysqlbinlog" CMD_MYSQLDUMP="$DIR_MYSQL/bin/mysqldump" CMD_MYSQL="$DIR_MYSQL/bin/mysql" LIST_EXCLUDE_DB='(test|information_schema|performance_schema)' if [ ! -d $DIR_BACKUP/$TIME_YM ]; then mkdir -p $DIR_BACKUP/$TIME_YM fi cd $DIR_BACKUP/$TIME_YM function result_status() { if [ $ -eq 0 ]; then echo "[`date +%Y%m%d%H%M%S`] SUCCESS! "|tee -a log.$TIME_YM else echo "[`date +%Y%m%d%H%M%S`] ERROR! "|mail -s "backup error $HOSTNAME" ning_lianjie@163.com|tee -a log.$TIME_YM fi } function usage_error() { echo "Usage: $0 RUN ERROR" echo " -a: backup all database -e: backup each database -d: backup single/multi database -t: backup single/multi table of single database -b: backup binlog -r: recover all database(!require password!) -o: recover single database/talbe,you should be designation database name(!require password!) -p: create connect mysql password -s: configuration rsyncd If you want ceate a rsyncd, you should enter '$0 -s cet' If you want restart rsyncd, you should enter '$0 -s rst' -c: sync to backup center " exit 0 } function read_pwd() { read USER PASSWD < $FILE_PASSWD } function backup() { read_pwd LOGBIN_STATUS=`$CMD_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW VARIABLES LIKE 'log_bin'" | gawk '{print $2}'` if [ $LOGBIN_STATUS = "ON" ]; then MASTER='--master-data=2' else MASTER=' ' fi } function backup_all() { backup $CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R -A --add-drop-database $MASTER |gzip >$HOSTNAME.all.$TIME.sql.gz } function backup_each() { backup for db in $($CMD_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW DATABASES"|egrep -v $LIST_EXCLUDE_DB) do $CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R $MASTER $db --databases |gzip >$HOSTNAME.$db.$TIME.sql.gz done # delete 7 days ago for db in $($CMD_MYSQL -u$USER -p$PASSWD -N -s -e "SHOW DATABASES"|egrep -v $LIST_EXCLUDE_DB) do if [ ! -f $HOSTNAME.$db.$TIME_7.sql.gz ]; then echo else rm $HOSTNAME.$db.$TIME_7.sql.gz -f fi done } function backup_db() { shift backup $CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R $MASTER --databases $@| gzip>$HOSTNAME.$OPTARG.$TIME.sql.gz } function backup_dt() { shift if [ $# -ge 2 ]; then backup $CMD_MYSQLDUMP -u$USER -p$PASSWD -x -R $MASTER $@| gzip>$HOSTNAME.$OPTARG.$TIME.sql.gz else usage_error fi } function backup_binlog() { if [ -s $DIR_BACKUP/mysql-bin.queue ]; then read POS < $DIR_BACKUP/mysql-bin.queue cd $DIR_DATA tar -jcvf $DIR_BACKUP/$TIME_YM/$HOSTNAME.$POS.$TIME.bz2 `gawk -F'/' '{print $2}' $BINLOG_NAME.index |sed -n "/$POS/,//p"` cd - if [ -f $DIR_BACKUP/$TIME_YM/$HOSTNAME.$POS.$TIME_7.bz2 ]; then rm $DIR_BACKUP/$TIME_YM/$HOSTNAME.$POS.$TIME_7.bz2 -f fi fi # write last pos gawk -F'/' '{print $2}' $DIR_DATA/$BINLOG_NAME.index | tail -n 1 >$DIR_BACKUP/mysql-bin.queue } function recover_all() { read_pwd shift $CMD_MYSQL -u$USER -p -e "source $@" } function recover_dt() { read_pwd shift if [ $# -eq 2 ]; then $CMD_MYSQL -u$USER -p -D $1 -e "source $2" else usage_error fi } function passwd_create() { if [ ! -e "$DIR_PASSWD" ]; then mkdir -p $DIR_PASSWD fi echo -n "Please enter MySQL(user=root)'s password:" read -s MYSQL_FASSWD cat >$FILE_PASSWD <<+ root $MYSQL_FASSWD + chmod 600 $FILE_PASSWD } function rsyncd() { shift if [ $# -eq 0 ]; then usage_error else DIR_RSYNCD='/usr/local/rsync' FILE_RSYNCD_PASSWORD="$DIR_RSYNCD/rsyncd.password" case "$1" in 'cet') if [ ! -d $DIR_RSYNCD ]; then mkdir -p $DIR_RSYNCD fi if [ ! -e "$DIR_RSYNCD/rsyncd.conf" ]; then touch $DIR_RSYNCD/rsyncd.conf fi mv $DIR_RSYNCD/rsyncd.conf $DIR_RSYNCD/rsyncd.conf.$TIME.bak cat >$DIR_RSYNCD/rsyncd.conf <<+ uid = root gid = root use chroot = no max connections = 5 lock file = $DIR_RSYNCD/rsyncd.lock log file = $DIR_RSYNCD/rsyncd.log pid file = $DIR_RSYNCD/rsyncd.pid hosts allow = 192.168.250.251 hosts deny = * ignore errors read only = yes list = no auth users = backupdbuser secrets file = $DIR_RSYNCD/rsyncd.password [BINLOG] path = $DIR_DATA include = $BINLOG_NAME.* exclude = * [DUMPDB] path = $DIR_BACKUP + cat >$FILE_RSYNCD_PASSWORD <<+ username:password + chmod 600 $FILE_RSYNCD_PASSWORD exit 0 ;; 'rst') if [ -s "$DIR_RSYNCD/rsyncd.pid" ]; then rsyncd_pid=`cat "$DIR_RSYNCD/rsyncd.pid"` if (kill -0 $rsyncd_pid 2>/dev/null); then echo "Shutting down rsyncd" kill $rsyncd_pid else echo "rsyncd #$rsyncd_pid is not running!" rm "$DIR_RSYNCD/rsyncd.pid" fi fi sleep 2 rsync --daemon --config=$DIR_RSYNCD/rsyncd.conf --port=873 echo "rsync --daemon --config=$DIR_RSYNCD/rsyncd.conf --port=873" echo "netstat -tunlp | grep rsync" netstat -tunlp | grep rsync ;; *) usage_error ;; esac fi } #main if [ $# -eq 0 ]; then usage_error else while getopts :aed:t:r:o:bpsc varname do case $varname in a) backup_all ;; e) backup_each ;; d) backup_db $@ ;; t) backup_dt $@ ;; b) backup_binlog ;; r) recover_all $@ ;; o) recover_dt $@ ;; p) passwd_create ;; s) rsyncd $@ ;; c) # rsync -czrptgoD --password-file=/tmp/.passwd $HOSTNAME.*.$TIME.sql.gz backupdbuser@192.168.250.251::DUMPDB/$HOSTNAME/$TIME_YM rsync -czrpt --password-file=/tmp/.passwd $DIR_BACKUP/$TIME_YM backupdbuser@192.168.250.251::DUMPDB/$HOSTNAME result_status ;; :) echo "$varname: 缺少参数" usage_error ;; ) echo "$varname: 非法选项" usage_error ;; esac done fi
原文链接：http://blog.chinaunix.net/space.php uid=16844903&do=blog&id=2010853