数据库|mysql教程
mysql,数据库,自动,备份,发送,邮件,通知,前提,系统
数据库-mysql教程
易语言偷窥源码,vscode安装中文语言,ubuntu ssh失败,tomcat存放动态资源,爬虫+br,php 获取前一个url,金昌seo优化费用,织梦网站关键字调用,免费dedecms模板自适应lzw
一 前提 系统环境 ：centos6.1 脚本作用 ：备份指定服务器上的指定数据库，并通过scp传送到另一台备份主机，然后发送备份是否成功邮件到指定邮箱 思路 ：首先在备份脚本中把备份报告导出为一个db_backup.log文件，然后邮件脚本会把这个log内容作为邮件内容发
网站源码查看,vscode 怎么打开,ubuntu服务器测试带宽,tomcat 部署 日志,微信sqlite开源,手机端选择地址插件,前端框架和手脚架,红色眼睛白色条纹爬虫,php静态方法调用,福州seo招商,手机版卡盟网站源码,网页点赞怎么做的,百度贴吧模板,支付程序评价lzw
房卡麻将源码下载,ubuntu php 域名,验证tomcat防火墙,爬虫网易财经,php检查数据库是否存在,湖南抖音seo算法排名优化lzw
一 前提
系统环境：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: -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_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.sqlif [ $? -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.logfifiecho "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 ];thenecho "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 lj@10.90.5.50:/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命令执行邮件脚本，以发送备份报告，其中的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: -08-26#请确认下面插件已安装use strict;use Net::SMTP_auth;use Text::Iconv;use MIME::Base64;use Data::Dumper;#邮件服务器地址my $mailhost= \;#邮件从哪个邮箱发出（from）my $mailfrom= monitor@;#邮件需要发给谁my @mail_account = (xxx@);#邮件发送方邮箱my $mailuser= monitor@;#发送方邮箱密码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( \, Timeout => 60, Debug => 0, Hello => \ ) 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点执行备份脚本
