最近公司要实现一个自动统计mysql数据发邮件的功能
采用了shell+sendemail+corntab
下面附代码:
#!/bin/bash
#设置mysql的连接
HOSTNAME="127.0.0.1"
PORT="3306"
USERNAME="xxxx"
PASSWORD="xxxx"
DBNAME="abm"
TABLENAME="abm_audience"
DATE=`date "+%Y-%m-%d"`
select_sql="SELECT
abm_au.tenant_id,
SUM(CASE
WHEN abm_au.status = 'DISTRIBUTED'
AND DATE_FORMAT(abm_au.update_time,'%Y-%m-%d') >= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 15 DAY),'%Y-%m-%d')
AND DATE_FORMAT(abm_au.update_time, '%Y-%m-%d') < DATE_FORMAT(NOW(),'%Y-%m-%d')
THEN 1
ELSE 0
END) AS distributedCount,
SUM(CASE
WHEN abm_au.status = 'READY'
AND DATE_FORMAT(abm_au.created_time,'%Y-%m-%d') >= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 15 DAY),'%Y-%m-%d')
AND DATE_FORMAT(abm_au.created_time,'%Y-%m-%d') < DATE_FORMAT(NOW(),'%Y-%m-%d')
THEN 1
ELSE 0
END) AS builtCount
FROM abm_audience abm_au LEFT JOIN sso.tenant te ON te.tenant_id = abm_au.tenant_id
WHERE te.tenant_sysname IS NOT NULL AND te.platform_type='MVPD'
GROUP BY abm_au.tenant_id"
#执行mysql
/usr/local/mysql/bin/mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} abm -e "${select_sql}" > $DATE.txt
#设置sendemail
email_reciver=xxxxx@qq.com
email_sender=xxxxx@qq.com
email_username=xxxxx@qq.com
email_password=xxxxx
email_smtphost=smtp.qq.com
email_title="title"
email_content="content"
/usr/local/bin/sendEmail -f ${email_sender} -t ${email_reciver} -s ${email_smtphost} -u ${email_title} -xu ${email_username} -a $DATE.txt -xp ${email_password} -m ${email_content} -o message-charset=utf-8
注意:
1.sendemail,corntrab,mysql安装及环境变量的配置,这里我采用的全路径
2.发送邮箱需要开启pop3/smtp功能
3.不同邮箱可能对垃圾邮件的处理方式不同,这里通过sendmail发的邮件有时候gmail会当成垃圾邮件,而163则不会