使用golang进行慢查询告警，邮件通知相关人员

最新推荐文章于 2022-10-18 10:59:32 发布

coding4all

最新推荐文章于 2022-10-18 10:59:32 发布

阅读量1k

点赞数 1

分类专栏： golang 运维文章标签： golang mysql

本文链接：https://blog.csdn.net/q1298252589/article/details/106129419

版权

运维同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

golang

2 篇文章 0 订阅

订阅专栏

为什么写这个功能？

之前项目中，生产环境经常因为慢查询导致数据库（阿里云RDS）CPU告警，导致服务不可用，为了让开发人员注意到sql中的慢查询，笔者使用golang开发了如下慢查询告警功能<https://github.com/cxdhefei/rds4go>：

代码结构如下：

rds4go
  --config.ini
  --email.html
  --main.go
  --package-mac.sh
  --package-windows.bat
  --slowlog.sh

main.go

package main

import (
	"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 string
	accessKeyId string
	accessKeySecret string
	DBInstanceId string
	DBName string

	emailFrom string
	emailTo string
	emailAddr string
	emailUsername string
	emailPassword string
	emailHost string
)

type TemplateBody struct {
	DBName  string
	TotalRecordCount  int
	TRData  []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,             // 您的地域ID
		accessKeyId,         // 您的AccessKey ID
		accessKeySecret)        // 您的AccessKey Secret
	if err != nil {
		fmt.Print(err.Error())
		return
	}
	// 创建慢日志统计请求并设置参数
	request := rds.CreateDescribeSlowLogsRequest()
	request.Scheme = "https"
	request.StartTime = now + "Z"
	request.EndTime = request.StartTime
	request.DBName = DBName
	//页码，范围：大于0且不超过Integer的最大值
	request.PageNumber = requests.NewInteger(1)
	//每天条数，范围：30~100
	request.PageSize = requests.NewInteger(100)

	//每个实例的慢查询统计，分别发送邮件
	DBInstanceIds := strings.Split(DBInstanceId, ",")
	for _, v := range DBInstanceIds {
		request.DBInstanceId = v
		response, 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.SQLSlowLog
		for 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 = emailFrom
	e.To = strings.Split(emailTo,",")
	//e.Bcc = []string{"test_bcc@example.com"}
	//e.Cc = []string{"test_cc@example.com"}
	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

<!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

[rds]
regionId=cn-hangzhou
accessKeyId=xxxx
accessKeySecret=xxxx
DBInstanceId=xxxx,xxxx,xxxx
DBName=xxxx
[email]
emailFrom=xxxx
emailTo=xxx@gmail.com,xxx@163.com
emailAddr=smtp.qq.com:587
emailUsername=xxx@qq.com
emailPassword=xxxx
emailHost=smtp.qq.com

使用说明：

install SDK :

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点发送前一天的慢查询日志)：
```
0 8 * * * sh /home/netcafe/script/rds/slowlog.sh
```