数据库指定库表中的字段进行备份,并以表格的形式定时发送邮件到指定邮箱 ​

备份数据进行定时邮件发送

mkdir -p /opt/module/shell/mkdir -p /opt/module/shell/sql/revenue.sqlmkdir -p /opt/module/shell/csv/revenue#MailTool.jar 放在指定目录下 便于发送邮件使用 可自动生成表格 vim /opt/module/shell/daily_text.sh# !/bin/bash#数据库指定库.表中的字段进行备份,并以表格的形式发送邮件到指定邮箱#定义变量dat=`date -d '1 days ago' +%Y-%m`basepath=/opt/module/shellsql_revenue=${basepath}/sql/revenue.sqlcsv_revenue=${basepath}/csv/revenue/${dat}.csvfilename=`date +%Y-%m`# html_revenue=${basepath}/html/revenue/${dat}.html#current=`date -d last-day "+%Y-%m-%d"`#timeStamp=`date -d "$current" +%s`# 将current转换为时间戳，精确到毫秒#currentTimeStamp=$((timeStamp*1000))#current2=`date "+%Y-%m-%d"`#time2Stamp=`date -d "$current2" +%s`# 将current转换为时间戳，精确到毫秒#current2TimeStamp=$((time2Stamp*1000))#具体的sql语句 备份某个库里的某个表中的指定字段sql_revenue_total="select date,tb_card_no,goodsId,outItemSKu,money from itoyo_2018.o_jujibao_record Order by date"#数据库的登陆ip，端口，用户，密码mysql=/usr/bin/mysqlhost=数据库ipport=3306username=数据库指定用户名password=密码#将备份的内容导出到定义好的变量文件中$mysql -h$host -P$port -u$username  -p$password  -e "$sql_revenue_total" > $csv_revenuetotal=`$mysql -h$host -P$port -u$username -p$password -e "$sql_revenue_total" | tail -n+2`echo -e "$total" >> $csv_revenuesed -i 's/\t/","/g' $csv_revenuesed -i 's/^/"/g'    $csv_revenuesed -i 's/$/"/g'    $csv_revenuesed -i '2,3d' $csv_revenue#过滤出备份文件中当月的信息到指定文件cat $csv_revenue | grep -E "(date|$filename)" |sed '$d' > $csv_revenue.bak#编辑邮件内容及格式confile=${basepath}/conf.propertiesrm -f $confile    #这里我用的是腾讯企业邮箱，exmail.qq.com  邮箱端口为465echo  "mailhost=smtp.exmail.qq.com"                                       > $confileecho  "mailport=465"                                                      >> $confileecho  "username=邮箱地址"                              >> $confileecho  "password=邮箱密码"                                                 >> $confileecho  "from=发件人邮箱"                                  >> $confileecho  "to=收件人邮箱" >> $confileecho  "#cc="                                                              >> $confileecho  "#bcc="                                                             >> $confileecho  "subject=标题 $dat"                                           >> $confileecho  "content=
本月数据
#table_revenue#
 " >> $confileecho  "table_revenue=$csv_revenue.bak"                                    >> $confileecho  "file=$csv_revenue.bak"                                             >> $confile#发送邮件  并-mail指定邮件内容  要执行计划任务 必须写绝对路径/data/tools/jdk1.7.0_67/bin/java -jar /opt/module/shell/MailTool.jar -mail $confile

定时计划任务

#该daily_text.sh脚本必须给执行权限crontab -e00 18 30 * * . /opt/module/shell/daily_text.sh