數(shù)據(jù)庫指定庫表中的字段進(jìn)行備份,并以表格的形式定時(shí)發(fā)送郵件到指定郵箱 ?

備份數(shù)據(jù)進(jìn)行定時(shí)郵件發(fā)送

mkdir -p /opt/module/shell/
mkdir -p /opt/module/shell/sql/revenue.sql
mkdir -p /opt/module/shell/csv/revenue
#MailTool.jar 放在指定目錄下 便于發(fā)送郵件使用 可自動(dòng)生成表格 
vim /opt/module/shell/daily_text.sh
# !/bin/bash
#數(shù)據(jù)庫指定庫.表中的字段進(jìn)行備份,并以表格的形式發(fā)送郵件到指定郵箱
#定義變量
dat=`date -d '1 days ago' +%Y-%m`
basepath=/opt/module/shell
sql_revenue=${basepath}/sql/revenue.sql
csv_revenue=${basepath}/csv/revenue/${dat}.csv
filename=`date +%Y-%m`
# html_revenue=${basepath}/html/revenue/${dat}.html
#current=`date -d last-day "+%Y-%m-%d"`
#timeStamp=`date -d "$current" +%s`
# 將current轉(zhuǎn)換為時(shí)間戳，精確到毫秒
#currentTimeStamp=$((timeStamp*1000))
#current2=`date "+%Y-%m-%d"`
#time2Stamp=`date -d "$current2" +%s`
# 將current轉(zhuǎn)換為時(shí)間戳，精確到毫秒
#current2TimeStamp=$((time2Stamp*1000))

#具體的sql語句 備份某個(gè)庫里的某個(gè)表中的指定字段
sql_revenue_total="select date,tb_card_no,goodsId,outItemSKu,money from itoyo_2018.o_jujibao_record Order by date"

#數(shù)據(jù)庫的登陸ip，端口，用戶，密碼
mysql=/usr/bin/mysql
host=數(shù)據(jù)庫ip
port=3306
username=數(shù)據(jù)庫指定用戶名
password=密碼

#將備份的內(nèi)容導(dǎo)出到定義好的變量文件中
$mysql -h$host -P$port -u$username  -p$password  -e "$sql_revenue_total" > $csv_revenue
total=`$mysql -h$host -P$port -u$username -p$password -e "$sql_revenue_total" | tail -n+2`
echo -e "$total" >> $csv_revenue
sed -i 's/\t/","/g' $csv_revenue
sed -i 's/^/"/g'    $csv_revenue
sed -i 's/$/"/g'    $csv_revenue
sed -i '2,3d' $csv_revenue

#過濾出備份文件中當(dāng)月的信息到指定文件
cat $csv_revenue | grep -E "(date|$filename)" |sed '$d' > $csv_revenue.bak
#編輯郵件內(nèi)容及格式
confile=${basepath}/conf.properties
rm -f $confile
    #這里我用的是騰訊企業(yè)郵箱，exmail.qq.com  郵箱端口為465
echo  "mailhost=smtp.exmail.qq.com"                                       > $confile
echo  "mailport=465"                                                      >> $confile
echo  "username=郵箱地址"                              >> $confile
echo  "password=郵箱密碼"                                                 >> $confile
echo  "from=發(fā)件人郵箱"                                  >> $confile
echo  "to=收件人郵箱" >> $confile
echo  "#cc="                                                              >> $confile
echo  "#bcc="                                                             >> $confile
echo  "subject=標(biāo)題 $dat"                                           >> $confile
echo  "content=<p>本月數(shù)據(jù)<br><table>#table_revenue#</table><br></p> " >> $confile
echo  "table_revenue=$csv_revenue.bak"                                    >> $confile
echo  "file=$csv_revenue.bak"                                             >> $confile
#發(fā)送郵件  并-mail指定郵件內(nèi)容  要執(zhí)行計(jì)劃任務(wù) 必須寫絕對(duì)路徑
/data/tools/jdk1.7.0_67/bin/java -jar /opt/module/shell/MailTool.jar -mail $confile

定時(shí)計(jì)劃任務(wù)

#該daily_text.sh腳本必須給執(zhí)行權(quán)限
crontab -e
00 18 30 * * . /opt/module/shell/daily_text.sh