mysql数据库备份(shell)&&邮件报告状态脚本(python)

1、vim mysql_bak.sh

 

#!/bin/bash

#define connection parameters
DB_USER="root"
DB_PASSWORD="123456"
DB_HOST="localhost"
BACKUP_DATABASES="test D1 D2"

#define the directory for store backup file.
BACKUP_DIR="/backup"
BACKUP_LOG="/scripts/mysqlbak/mysqlbak.log"
BACKUP_EMAIL="/scripts/mysqlbak/mail.py"

#mysql,mysqldump and other unilities's path
FIND="/usr/bin/find"
MYSQL="/usr/local/mysql/bin/mysql"
MYSQLDUMP="/usr/local/mysql/bin/mysqldump"
MKDIR="/bin/mkdir"
RM="/bin/rm"
MV="/bin/mv"
TAR="/bin/tar"
XARGS="/usr/bin/xargs"
PYTHON="/usr/local/bin/python"
DATE="/bin/date"
#define the date format for backup file(dd-mm-yyyy)
TIME="$($DATE +%d-%m-%y)"
DATETIME="$($DATE +"%Y-%m-%d %T")"

# mkdir backuo direcotry if not exits
test ! -d $BACKUP_DIR && $MKDIR $BACKUP_DIR

#check the directory for store backup is writeable
test ! -w $BACKUP_DIR && echo "ERROR: $BACKUP_DIR is un-writeable." && exit 0

#begin to backup
echo "##########$DATETIME begin to backup mysql datebases##############" >$BACKUP_LOG
echo -e '\n' >> $BACKUP_LOG
cd $BACKUP_DIR
for DB in $BACKUP_DATABASES

do
    $MYSQLDUMP -u$DB_USER -h$DB_HOST -p$DB_PASSWORD $DB > "$BACKUP_DIR/$TIME.$DB.sql"  2>> $BACKUP_LOG

    #check the backup process is successfull
    if [[ $? == 0 ]];then
        $TAR czf $TIME.$DB.tar.gz $TIME.$DB.sql && $RM -rf $TIME.$DB.sql
        DATETIME="$($DATE +"%Y-%m-%d %T")"
        echo "$DATETIME BACKUP $DB is successfull" >>$BACKUP_LOG
        BAK_SUCCESS_DB="$BAK_SUCCESS_DB$DB,"
      else
 
        DATETIME="$($DATE +"%Y-%m-%d %T")"
        echo "$DATETIME BACKUP $DB  fail" >> $BACKUP_LOG
        BAK_FAIL_DB="$BAK_FAIL_DB$DB,"
    fi

done

DATETIME="$($DATE +"%Y-%m-%d %T")"
echo "$DATETIME BACKUP process is end" >>$BACKUP_LOG

#delete  old files
#echo -e '\n' >> $BACKUP_LOG
DATETIME="$($DATE +"%Y-%m-%d %T")"
echo "$DATETIME begin to delete 30 old back files " >> $BACKUP_LOG
for i in `$FIND $BACK_DIR -type f  -mtime +30`
do
   $RM -rf $i && echo "has rm $i sucessfully" >>$BACKUP_LOG
   RM_BAK_DB="$RM_BAK_DB$i,"
done

echo -e '\n' >> $BACKUP_LOG
DATETIME="$($DATE +"%Y-%m-%d %T")"
echo "$DATETIME backup $BAK_SUCCESS_DB is sucessfull" >> $BACKUP_LOG
echo "$DATETIME backup $BAK_FAIL_DB is failed" >> $BACKUP_LOG
echo "$DATETIME $RM_BAK_DB is deleted" >> $BACKUP_LOG
echo "$DATETIME backup mysql databases and delete 30 old backup files  sucessfully " >> $BACKUP_LOG

# begin to send email to [e-mail]
$PYTHON $BACKUP_EMAIL

2、cat mail.py

#/usr/bin/env python

# import smtplib library and email modules we'll need

import smtplib
import email
from email.MIMEText import MIMEText
#from email.mime.text import MIMEText
#define the smtp server address and it's port
mail_server= 'smtp.xxx.com'
mail_server_port = 25

#define from and to address
from_addr= '[e-mail]'
to_addr=   '[e-mail],[e-mail],[e-mail]'

#format the from and to address
from_header='From: %s\r\n' % from_addr
to_header= 'To: %s\r\n' % to_addr
subject_header= 'Subject: Mysql databases backup log'

#get the email content from txt file
textfile='/data/vmstat'
fp = open(textfile,'rb')
msg= MIMEText(fp.read());
fp.close()
#msg='tset teste test'
email_message = '%s%s%s\r\n\r\n%s' % (from_header,to_header,subject_header,msg)

#begin to  send email
s = smtplib.SMTP(mail_server,mail_server_port)
s.set_debuglevel(1)
s.starttls()
s.login("[e-mail]","mima")
s.sendmail(from_addr,to_addr.split(','),email_message)
s.quit()