MySQL database is automatically backed up and email notifications are sent

A prerequisite

system Environment : centos6.1

script role : Back up the specified database on the specified server and transfer to another backup host via SCP, and then send a backup whether the message was successful to the specified mailbox

idea : First in the backup script to export the backup report as a Db_backup.log file, and then the mail script will send this log content as the message content to the designated user, also implemented the mail notification

Note : If you need to back up to a remote host, you need to do SSH key authentication first, SCP to use

Two backup scripts

Backup Script Usage : If you need to back up a database, add a backup command after the script, like so

backup_db 10.90.5.50 dbname MySQL 123456 innodb
Function name the database name to be backed up by the IP connection database user name password database engine type

#!/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# backup time 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_ backupmysqldump= '/usr/local/mysql/bin/mysqldump ' #删除上一次备份报告rm  -f  $HOMEDIR/db_backup.log############## ################################################## #function #################################################### ######################## #backup  functionbackup_db  () {if [ $# -ne 5 ]; thenecho  "Usage: backupdb ip dbname user password dbtype" echo  "please  try again "exit 1fiip=$1dbname=$2user=$3password=$4dbtype=$5echo "------------------------ --start backup  $IP   $DBname db-------------------------------------" >>  $HOMEDIR/db_backup.logecho " Start  date:  ' date +%y%m%d_%h%m%s '  >>  $HOMEDIR/db_backup.logif [  $DBTYPE  =  "InnoDB"  ];then$mysqldump -u$user -p$password -h$ip --single-transaction  --flush-logs --routines --events --master-data=2  "$DBNAME"  >  $HOMEDIR/$ dbname/${dbname}_$date.sqlif [ $? -ne 0 ];thenecho  "$IP   $DBNAME  $ Dbtype db  backup is not success ,please check it out! "  >>  $HOMEDIR/db_backup.logelseecho  "$IP   $DBNAME   $DBTYPE  db   Backup is success! "  >>  $HOMEDIR/db_backup.logfielse  $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                 fifiecho  "end date:  ' date +%y%m%d_%h%m%s '"  >> $ homedir/db_backup.loggzip  $HOMEDIR/$DBNAME/${dbname}_$date.sqlsize= ' du -sh  $HOMEDIR/$DBNAME/${ dbname}_$date.sql.gz | awk  ' {print $1} ' echo  ' the file size is: $ SIZE " >>  $HOMEDIR/db_backup.logecho "--------------------------end backup  $IP   $DBNAME  db-------------------------------------" >>  $HOMEDIR/db_backup.log#delete  the backup files which were create two month agorm -f  $HOMEDIR/$DBNAME/${dbname}_$date2.sql.gz} #scp  function : scp the backup file  to another server as different place backupscp_function  () {         if [ $# -ne 1 ];then                 echo  "Usage: scp_function dbname"                  echo  "Please try  again "                 exit 1        fidbname=$1scp -p52814  -i / home/lj/.ssh/id_rsa  $HOMEDIR/$DBNAME/${dbname}_$date.sql.gz [email protected]:/data/dbbackup/if  [ $? -ne 0 ];thenecho  "$DBNAME  db backup data transfer  is not success ,please check it out! "  >>  $HOMEDIR/db_backup.logelseecho  "$DBNAME  db backup data transfer  is success! "  >>  $HOMEDIR/db_backup.logfi}################################################################# ######## #function ################################################################## #使用perl命令执行邮件脚本 to send a backup report where the sendmail.pl is sent under the mail script perl /home/ scripts/sendmail.pl

Three Send mail scripts

The plugin needs to be installed in the script, please confirm that Net::smtp_auth is installed on your system; Text::iconv; Mime::base64;data::D umper

#!/usr/bin/perl#author: xxx#name:sendamil.pl#purpose: send file comment email to  people#date: 2014-08-26# Please confirm that the following plug-in is installed Use strict;use net::smtp_auth;use text::iconv;use  mime::base64;use data::D umper, #邮件服务器地址my   $mailhost      =  ' Mail.qq.com '; #邮件从哪个邮箱发出 (from) my  $mailfrom      =  ' [email protected] '; Who needs to send the message to my  @mail_account  =  (' [email protected] '); #邮件发送方邮箱my   $mailuser       =  ' [email protected] '; #发送方邮箱密码my   $mailpasswd    =  ' Jiankong '; #db_backup. Log contents are sent as message content, db_backup.log the backup report exported for the backup script my  $filename =  '/data/dbdata_backup /db_backup.log '; #邮件主题my   $subject =  ' dazhima_db_backup '; open (MYFILE, "$filename")  | |  die  "$!"; my  $text;while  (<MYFILE>) {   $text  .= $_. " <br> ";} 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 ',  '  )  .  "?= < $mailfrom > \n"  );         $smtp->datasend (  "To:=?utf-8? B? "  . encode_base64 (  ' Jiankong ',  '  )  .  "?= < $mailto > \n"  );         $smtp->datasend (  "Subject:=?utf-8? B? "  . encode_base64 (  $subject,  '  )  .  "? =\n\n"  );          $smtp->datasend ();         $smtp Datasend ( encode_base64 (  $text,  " )  . "  \n " );          $smtp->dataend ();    }     $smtp Quit;}

Four use

Add a crontab record

0 2 * * */var/script/db_backup.sh

Perform a backup script at 2 o ' Day

This article is from the "Fightering-cluter" blog, make sure to keep this source http://3974020.blog.51cto.com/3964020/1545114

MySQL database is automatically backed up and email notifications are sent