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 [email protected]
$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= '[email protected]'
to_addr= '[email protected],[email protected],[email protected]'
#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("[email protected]","mima")
s.sendmail(from_addr,to_addr.split(','),email_message)
s.quit()