mysql数据库自动备份,并发送邮件通知
一 前提 系统环境 :centos6.1 脚本作用 :备份指定服务器上的指定数据库,并通过scp传送到另一台备份主机,然后发送备份是否成功邮件到指定邮箱 思路 :首先在备份脚本中把备份报告导出为一个db_backup.log文件,然后邮件脚本会把这个log内容作为邮件内容发
一 前提
系统环境:centos6.1
脚本作用:备份指定服务器上的指定数据库,并通过scp传送到另一台备份主机,然后发送备份是否成功邮件到指定邮箱
思路:首先在备份脚本中把备份报告导出为一个db_backup.log文件,然后邮件脚本会把这个log内容作为邮件内容发送给指定用户,也就实现了邮件通知
说明:如果需要备份到远程主机,需要首先做好ssh密钥认证,scp要使用到
二 备份脚本
备份脚本用法:如果需要备份某个数据库,在脚本后面添加一条备份命令,像下面这样
backup_db 10.90.5.50 dbname mysql 123456 innodb
函数名 mysql所在IP 所要备份的数据库名 连接数据库用户名 密码 数据库引擎类型
#!/bin/bash #name:backup_db.sh #Purpose: backup mysql database #Author: xxx #Date: 2014-08-26 #useage:backup_db IP databasename user password dbtype #example:backup_db 10.90.5.50 dbname mysql 123456 innodb #备份时间 DATE=`date -d"today" +%Y%m%d_%H%M%S` #前一个时间 DATE2=`date -d"1 month ago" +%Y%m%d_%H%M%S` #备份目录 HOMEDIR=/data/dbdata_backup MYSQLDUMP='/usr/local/mysql/bin/mysqldump' #删除上一次备份报告 rm -f $HOMEDIR/db_backup.log #################################################################function############################################################################ #backup function backup_db () { if [ $# -ne 5 ];then echo "Usage: backupdb IP dbname user password dbtype" echo "Please try again" exit 1 fi IP=$1 DBNAME=$2 USER=$3 PASSWORD=$4 DBTYPE=$5 echo "--------------------------start backup $IP $DBNAME db-------------------------------------" >> $HOMEDIR/db_backup.log echo "start date: `date +%Y%m%d_%H%M%S`" >> $HOMEDIR/db_backup.log if [ $DBTYPE = "innodb" ];then $MYSQLDUMP -u$USER -p$PASSWORD -h$IP --single-transaction --flush-logs --routines --events --master-data=2 "$DBNAME" > $HOMEDIR/$DBNAME/${DBNAME}_$DATE.sql if [ $? -ne 0 ];then echo "$IP $DBNAME $DBTYPE db backup is not success ,please check it out!" >> $HOMEDIR/db_backup.log else echo "$IP $DBNAME $DBTYPE db backup is success!" >> $HOMEDIR/db_backup.log fi else $MYSQLDUMP -u $USER -p$PASSWORD -h$IP -l --flush-logs --routines --events --master-data=2 "$DBNAME" > $HOMEDIR/$DBNAME/${DBNAME}_$DATE.sql if [ $? -ne 0 ];then echo "$IP $DBNAME $DBTYPE db backup is not success ,please check it out!" >> $HOMEDIR/db_backup.log else echo "$IP $DBNAME $DBTYPE db backup is success!" >> $HOMEDIR/db_backup.log fi fi echo "End date: `date +%Y%m%d_%H%M%S`" >> $HOMEDIR/db_backup.log gzip $HOMEDIR/$DBNAME/${DBNAME}_$DATE.sql SIZE=`du -sh $HOMEDIR/$DBNAME/${DBNAME}_$DATE.sql.gz | awk '{print $1}'` echo "the file size is: $SIZE" >> $HOMEDIR/db_backup.log echo "--------------------------end backup $IP $DBNAME db-------------------------------------" >> $HOMEDIR/db_backup.log #delete the backup files which were create two month ago rm -f $HOMEDIR/$DBNAME/${DBNAME}_$DATE2.sql.gz } #scp function : scp the backup file to another server as different place backup scp_function () { if [ $# -ne 1 ];then echo "Usage: scp_function dbname" echo "Please try again" exit 1 fi DBNAME=$1 scp -P52814 -i /home/lj/.ssh/id_rsa $HOMEDIR/$DBNAME/${DBNAME}_$DATE.sql.gz lj@10.90.5.50:/data/dbbackup/ if [ $? -ne 0 ];then echo "$DBNAME db backup data transfer is not success ,please check it out!" >> $HOMEDIR/db_backup.log else echo "$DBNAME db backup data transfer is success!" >> $HOMEDIR/db_backup.log fi } ##########################################################################function################################################################## #使用perl命令执行邮件脚本,以发送备份报告,其中的sendmail.pl为下面发邮件脚本 perl /home/scripts/sendmail.pl
三 发送邮件脚本
脚本中需要安装插件,请确认你的系统中已安装Net::SMTP_auth;Text::Iconv;MIME::Base64;Data::Dumper
#!/usr/bin/perl #Author: xxx #name:sendamil.pl #Purpose: Send file comment email to people #Date: 2014-08-26 #请确认下面插件已安装 use strict; use Net::SMTP_auth; use Text::Iconv; use MIME::Base64; use Data::Dumper; #邮件服务器地址 my $mailhost = 'mail.qq.com'; #邮件从哪个邮箱发出(from) my $mailfrom = 'monitor@qq.com'; #邮件需要发给谁 my @mail_account = ('xxx@qq.com'); #邮件发送方邮箱 my $mailuser = 'monitor@qq.com'; #发送方邮箱密码 my $mailpasswd = 'jiankong'; #db_backup.log中的内容会作为邮件内容发送,db_backup.log为备份脚本所导出的此次备份报告 my $filename = '/data/dbdata_backup/db_backup.log'; #邮件主题 my $subject = 'dazhima_db_backup'; open(MYFILE,"$filename") || die "$!"; my $text; while (){ $text .= $_."
"; } close(MYFILE); &SendMail($text,$subject); sub SendMail() { my $text = $_[0]; my $subject = $_[1]; my $smtp = Net::SMTP_auth->new( 'mail.medees.com', Timeout => 60, Debug => 0, Hello => 'medees.com' ) or die "Error.\n"; $smtp->auth( 'LOGIN', $mailuser, $mailpasswd ); foreach my $mailto (@mail_account) { $smtp->mail($mailfrom); $smtp->to($mailto); $smtp->data(); $smtp->datasend("Content-Type:text/html;charset=utf-8\n"); $smtp->datasend("Content-Transfer-Encoding:base64\n"); $smtp->datasend( "From:=?utf-8?B?" . encode_base64( 'JianKong', '' ) . "?= \n" ); $smtp->datasend( "To:=?utf-8?B?" . encode_base64( 'JianKong', '' ) . "?= \n" ); $smtp->datasend( "Subject:=?utf-8?B?" . encode_base64( $subject, '' ) . "?=\n\n" ); $smtp->datasend(); $smtp->datasend( encode_base64( $text, '' ) . " \n" ); $smtp->dataend(); } $smtp->quit; }
四 使用
添加一条crontab记录
0 2 * * * /var/script/db_backup.sh
在每天的2点执行备份脚本
上一篇: vue跳转新页面后回到顶部
下一篇: java中一个类实例化的加载