mysql 邮件服务器数据库备份脚本

作者：邓卫华 http://dengweihua1.blog.51cto.com

时间：2010.7.14

1、制定备份策略

 

2、完成备份脚本

#/bin/sh

#  filename: mysqlbackup.sh
#  |------------------------------------------------------|
#  | Create by D.W.H 2010.7.13                                   |
#  | Backup extmail  to mysql.extmail.tar.gz                   |

#  | http://dengweihua1.blog.51cto.com                        |
#  |------------------------------------------------------|

#   crontab
#   30 6 * * * /root/script/mysqlbackup.sh extmail -7
#   10 7 * * 0 /root/script/mysqlbackup.sh dspam -14
#   10 6 1 */3 * /root/script/mysqlbackup.sh --all-database -93

myuser='root'     #MYSQL数据库的用户名，可以使用普通有权限用帐号
mypasswd=‘yourpassword’         #MYSQL的密码
mydatabase=$1                               # 数据库名
dayover=$2                                     #备份文件保留长
nsuser='username for remote mache'          #远程共享服务器的用户名和密码
nspasswd=’password for remote mache’
command=/usr/bin/mysqldump 
#把第一个参数以“-”分割并取第三段。--all-database 结果为all.

f1=`echo $1 |cut -d "-" -f3`                   

datef=`date +%F`            #以YYYY-MM-DD显示日期。

 

#日期减去相应的天数后的日期，采用date -d "-7 days"
dateover=`date -d "$2 days" +%Y-%m-%d`
filename="mysql.$f1.$datef.tar.gz"                        #备份输入压缩后的文件名
overfilename="mysql.$f1.$dateover.tar.gz"          #形成超期的文件名
#为了保证数据能正常输出压缩，采用先本地备份成功后移动到远程的方式。

tempfolder=/tmp
tempfilename="$tempfolder/mysql.$f1.temp.sql" 
TAR=tar
remoteshare=//windows share server ip/mail$
dest_dir=/mnt/ip0.16
log=/var/log/backup.log
recipient=[e-mail]

#SENDM=`mail -s "Mail Server Backup Log..." $recipient < $log`

#判断是否有存在2个参数。

if [ $# -ne 2 ]; then
        echo "|-------------------------------------------------------------------|"
        echo "|usage:mysqlbackup.sh &lt;--all-database | dbname> <-days>.       |"
        echo "|       --all-database: all database to backup.                                |"
        echo "|       dbname: database name to backup.                                    |"
        echo "|       -days: delete file for over date.                                           |"
        echo "|-------------------------------------------------------------------|"
        echo " usage error." &gt; $log
    mail -s "MySQL backup faild for Mail Server.." $recipient
# $SENDM
        exit 1
fi

 

判断数据库是否存在。

mysqldbfolder="/var/lib/mysql"
        if [ $1 != "--all-database" ]; then
                if [ ! -d "$mysqldbfolder/$1" ]; then
                        echo "Database no exit!" &gt;$log
                          mail -s "MYSQL Backup faild,database no exit!" $recipient
                            #$SENDM
                        exit 1
                fi
        fi

#
#  If the function of the second parameter is equal to 0,then write “sucess” to the log file.

#

writelog()
{
        if [ $2 -eq 0 ]; then
         echo $1 " ...... backup Sucess." &gt;&gt; $log
        else
         echo $1 " ..... backup Faild." &gt;&gt; $log
     mail -s "$1 Backup faild on Mail Server." $recipient
#         $SENDM
        exit 1
        fi;
}
# MySQL DUMP TO temp File /tmp/tempfilename
# tar tempfile to mysql.$f1.date.tar.gz
echo "Start Backuping...." &gt; $log
echo "$datef "`date +%T` &gt;&gt; $log
echo "------------------------" &gt;&gt; $log
$command -u$myuser -p$mypasswd $mydatabase &gt; $tempfilename
writelog "mysql $1 ...." $?

cd tempfolder
$TAR czf $tempfolder/$filename $tempfilename
writelog "Tar...." $?

#

#判断/mnt/0.16这个目录是否已经被mount了。

count=`mount |grep "$dest_dir" |wc -l`
if [ $count -eq 0 ]; then
        mount -o username=$nsuser,password=$nspasswd $remoteshare $dest_dir
fi
writelog "Mount...." $?

#
# Move Mysql Backup File to Network that is $remoteshare or /mnt/ip0.16
#

mv  $tempfolder/$filename $dest_dir/$filename
writelog "move $filename to $dest_dir " $?

#  Delete over  days ago of $2
# 删除过期的文件。
        if [ -f "$dest_dir/$overfilename" ]; then
                rm -rf $dest_dir/$overfilename
                writelog "$overfilename was deleted...." $?
        fi;

umount $dest_dir
writelog "umount $dest_dir" $?

#把每天步的运行结果邮件出去。

mail -s "MySQL backup Complete for Mail Server.." $recipient&lt;$log
#$SENDM

3、运行crontab -e做日程，或把前的的crontab例处的保存为文件放在cron.d中的day或weeks文件夹中。