爱站程序员基地为什么写这个功能？
之前项目中，生产环境经常因为慢查询导致数据库（阿里云RDS）CPU告警，导致服务不可用，为了让开发人员注意到sql中的慢查询，笔者使用golang开发了如下慢查询告警功能<https://github.com/cxdhefei/rds4go>：
代码结构如下：
[code]rds4go--config.ini--email.html--main.go--package-mac.sh--package-windows.bat--slowlog.sh
main.go
[code]package mainimport (\"bytes\"\"fmt\"\"github.com/aliyun/alibaba-cloud-sdk-go/sdk/requests\"\"github.com/aliyun/alibaba-cloud-sdk-go/services/rds\"\"github.com/go-ini/ini\"\"github.com/jordan-wright/email\"\"html/template\"\"net/http\"\"net/smtp\"\"strings\"\"time\")var (regionId stringaccessKeyId stringaccessKeySecret stringDBInstanceId stringDBName stringemailFrom stringemailTo stringemailAddr stringemailUsername stringemailPassword stringemailHost string)type TemplateBody struct {DBName stringTotalRecordCount intTRData []rds.SQLSlowLog}func init() {cfg, err := ini.Load(\"config.ini\")if err != nil {fmt.Print(err.Error())return}//rds配置rds, err := cfg.GetSection(\"rds\")if err != nil {fmt.Print(err.Error())return}regionId = rds.Key(\"regionId\").String()accessKeyId = rds.Key(\"accessKeyId\").String()accessKeySecret = rds.Key(\"accessKeySecret\").String()DBInstanceId = rds.Key(\"DBInstanceId\").String()DBName = rds.Key(\"DBName\").String()//email配置email, err := cfg.GetSection(\"email\")if err != nil {fmt.Print(err.Error())return}emailFrom = email.Key(\"emailFrom\").String()emailTo = email.Key(\"emailTo\").String()emailAddr = email.Key(\"emailAddr\").String()emailUsername = email.Key(\"emailUsername\").String()emailPassword = email.Key(\"emailPassword\").String()emailHost = email.Key(\"emailHost\").String()}func main() {day, _ := time.ParseDuration(\"-24h\")now := time.Now().Add(day).Format(\"2006-01-02\")// 创建client实例client, err := rds.NewClientWithAccessKey(regionId, // 您的地域IDaccessKeyId, // 您的AccessKey IDaccessKeySecret) // 您的AccessKey Secretif err != nil {fmt.Print(err.Error())return}// 创建慢日志统计请求并设置参数request := rds.CreateDescribeSlowLogsRequest()request.Scheme = \"https\"request.StartTime = now + \"Z\"request.EndTime = request.StartTimerequest.DBName = DBName//页码，范围：大于0且不超过Integer的最大值request.PageNumber = requests.NewInteger(1)//每天条数，范围：30~100request.PageSize = requests.NewInteger(100)//每个实例的慢查询统计，分别发送邮件DBInstanceIds := strings.Split(DBInstanceId, \",\")for _, v := range DBInstanceIds {request.DBInstanceId = vresponse, err := client.DescribeSlowLogs(request)if err != nil {fmt.Print(err.Error())return}if response.GetHttpStatus() != http.StatusOK {fmt.Print(response.GetHttpStatus())return}if response.TotalRecordCount == 0 {fmt.Printf(\"实例：%s，数据库：%s，没有慢查询记录/n\", v, DBName)continue}logs := response.Items.SQLSlowLogfor i := 0; i < len(logs); i++ {logs[i].AvgExecutionTime = logs[i].MySQLTotalExecutionTimes / logs[i].MySQLTotalExecutionCounts}fmt.Printf(\"response is %#v\\n\", response)body := TemplateBody{DBName: DBName, TotalRecordCount:response.TotalRecordCount, TRData: logs}err = sendMail(now, v, body)if err != nil {fmt.Print(err.Error())}}}//发送邮件func sendMail(dateStr, dbId string, data TemplateBody) error {e := email.NewEmail()e.From = emailFrome.To = strings.Split(emailTo,\",\")//e.Bcc = []string{\"[email protected]\"}//e.Cc = []string{\"[email protected]\"}e.Subject = dateStr + \" RDS实例：\" + dbId + \" 慢查询统计\"t := template.Must(template.ParseFiles(\"email.html\"))body := new(bytes.Buffer)//作为变量传递给html模板t.Execute(body, data)e.HTML = body.Bytes()return e.Send(emailAddr, smtp.PlainAuth(\"\", emailUsername, emailPassword, emailHost))}
email.html
[code]<!DOCTYPE html><html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\" /><title></title></head><body><!-- 最外层table--><table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" height=\"100%\" width=\"100%\" style=\"\"><tr><td align=\"center\" valign=\"top\"><!-- 定宽table--><table border=\"1\" cellpadding=\"0\" cellspacing=\"0\" width=\"\"><caption style=\"padding: 20px; font-weight: 600; font-size: 26px;\">数据库：{{.DBName}}，总记录：{{.TotalRecordCount}}</caption><thead><tr><th align=\"center\" width=\"50%\" style=\"padding:5px;\">SQL语句</th><th align=\"center\" style=\"padding:5px;\">最大执行时长</th><th align=\"center\" style=\"padding:5px;\">平均执行时间</th><th align=\"center\" style=\"padding:5px;\">MySQL总执行次数</th><th align=\"center\" style=\"padding:5px;\">MySQL总执行时间</th><th align=\"center\" style=\"padding:5px;\">解析SQL总行数</th><th align=\"center\" style=\"padding:5px;\">解析SQL最大行数</th><th align=\"center\" style=\"padding:5px;\">返回SQL总行数</th><th align=\"center\" style=\"padding:5px;\">返回SQL最大行数</th><th align=\"center\" style=\"padding:5px;\">最大锁定时长/秒</th><th align=\"center\" style=\"padding:5px;\">锁定总时长/秒</th><th align=\"center\" width=\"6%\" style=\"padding:5px;\">数据生成日期</th></tr></thead><tbody>{{range .TRData}}<tr><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.SQLText}}</td><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.MaxExecutionTime}}</td><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.AvgExecutionTime}}</td><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.MySQLTotalExecutionCounts}}</td><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.MySQLTotalExecutionTimes}}</td><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.ParseTotalRowCounts}}</td><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.ParseMaxRowCount}}</td><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.ReturnTotalRowCounts}}</td><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.ReturnMaxRowCount}}</td><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.MaxLockTime}}</td><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.TotalLockTimes}}</td><td align=\"center\" style=\"padding:5px;word-wrap:break-word; word-break:break-all;\">{{.CreateTime}}</td></tr>{{end}}</tbody></table></td></tr></table></body></html>
config.ini
[code][rds]regionId=cn-hangzhouaccessKeyId=xxxxaccessKeySecret=xxxxDBInstanceId=xxxx,xxxx,xxxxDBName=xxxx[email][email protected],[email protected]=smtp.qq.com:[email protected]=xxxxemailHost=smtp.qq.com
使用说明：
- install SDK :
[code]go get -u github.com/aliyun/alibaba-cloud-sdk-go/sdk
- mac下执行package-mac.sh，将编译生成linux下可执行文件
- windows下执行package-windows.sh，将编译生成linux下可执行文件
- 将可执行文件rds4go、配置文件config.ini(修改相关配置)、邮件模板文件email.html上传至服务器某文件夹下
- chmod a+x rds4go
- 定时任务crontab（比如每天早晨8点发送前一天的慢查询日志)：
[code]0 8 * * * sh /home/netcafe/script/rds/slowlog.sh
