# go获取阿里云rds慢日志 ## 初始化项目目录结构 ![](http://xieys.club/images/posts/image-20210702183002425.png) - conf：用于存储配置文件 - pkg：第三方包 - templates: 用于存储html模板 - models 模型文件 ## 创建配置文件 在conf目录下创建conf.ini配置文件 ``` [rds] RegionId = cn-shanghai AccessKeyId = xxxx AccessKeySecret = xxxx DBInstanceID = rm-uf6vn151nl1056ie5,rr-uf66d5yofh1x53p5c,rm-uf6cyi1ds2rp4ujry,rr-uf6a28b0jqz3s7b53,rm-uf6qye4g6x8tbv961,rr-uf6m0hk10zm4alj30,rm-uf6u2lq543e8iogj3,rr-uf6754hbh1f13o8n5,rm-uf6zx1o759ehie878,rr-uf67z2v92b13i6z5l [email] EmailForm = service@lingcb.com EmailTo = guangqiang@lingcb.com,guangqiang@lingcb.com,liaohailong@lingcb.com,amy@lingcb.com,ben@lingcb.com EmailAddr = smtp.exmail.qq.com:25 EmailPubTo = stevengao@lingcb.com,yanggang@lingcb.com EmailUserName = service@lingcb.com EmailPassword = xxxx EmailHost = smtp.exmail.qq.com ``` 在pkg目录下创建settings目录，在settings目录下创建setting.go文件 ``` package settings import ( "github.com/go-ini/ini" "log" ) type Rds struct { RegionId string AccessKeyId string AccessKeySecret string DBInstanceID []string } type Email struct { EmailForm string EmailTo []string EmailPubTo []string EmailAddr string EmailUserName string EmailPassword string EmailHost string } var ( RdsSetting = &Rds{} EmailSetting = &Email{} ) func Setup()(err error) { Cfg,err := ini.Load("conf/conf.ini") if err != nil{ log.Fatalf("Fail to parse 'conf/conf.ini:%v'",err) } if err = Cfg.Section("rds").MapTo(RdsSetting);err != nil{ return err } if err = Cfg.Section("email").MapTo(EmailSetting);err != nil{ return err } return nil } ``` ## 创建发送邮件的方法 在pkg目录下创建utils目录，在utils目录下新建sendMail.go文件 ``` package utils import ( "bytes" "getslow/models" "getslow/pkg/settings" "gopkg.in/gomail.v2" "html/template" ) var s gomail.SendCloser func ConnSmtp()(err error) { d := gomail.NewDialer(settings.EmailSetting.EmailHost,settings.EmailSetting.EmailPort,settings.EmailSetting.EmailUserName,settings.EmailSetting.EmailPassword) s , err = d.Dial() return } func SendMail(index int,dateStr,dbName string,data models.TemplateBody)error{ //创建邮件消息体 m := gomail.NewMessage() // 发件人 m.SetHeader("From", settings.EmailSetting.EmailForm) var sendTo = []string{ settings.EmailSetting.EmailTo[index/2], } sendTo = append(sendTo,settings.EmailSetting.EmailPubTo...) // 收件人(可以有多个) m.SetHeader("To", sendTo...) // 邮件主题 m.SetHeader( "Subject",dateStr + " RDS实例：" + dbName + " 慢查询统计") t,_ := template.ParseFiles("templates/email.html") var body bytes.Buffer t.Execute(&body,data) // html形式的消息 m.SetBody("text/html", body.String()) // 以路径将文件作为附件添加到邮件中 m.Attach(data.Path) // 发送邮件 return gomail.Send(s,m) } ``` 去重函数，由于阿里云慢日志统计是按天统计，而我们一次性是获取7天的慢日志统计，所以每天都有可能重复的语句 在utils目录下创建removal.go文件 ``` package utils import ( "getslow/models" "sort" ) //去重函数，由于阿里云慢日志统计是按天统计，每天都有可能重复的语句 func Removal(sl models.SlowList)(total int,slowList models.SlowList){ var result = map[string]models.SlowModel{} for _,v := range sl{ value,ok := result[v.DBName+v.SQLText] if ok{ result[v.DBName+v.SQLText] = models.SlowModel{ value.DBName, value.SQLText, value.MySQLTotalExecutionCounts + v.MySQLTotalExecutionCounts, value.MySQLTotalExecutionTimes + v.MySQLTotalExecutionTimes, (value.MaxExecutionTime + v.MaxExecutionTime)/2, (value.AvgExecutionTime + v.AvgExecutionTime)/2, value.TotalLockTimes + v.TotalLockTimes, (value.MaxLockTime + v.TotalLockTimes)/2, value.ParseTotalRowCounts + v.ParseTotalRowCounts, (value.ParseMaxRowCount + v.ParseMaxRowCount)/2, value.ReturnTotalRowCounts + v.ReturnTotalRowCounts, (value.ReturnMaxRowCount + v.ReturnMaxRowCount)/2, } }else { result[v.DBName+v.SQLText] = v } } for _,slow := range result{ slowList = append(slowList,slow) } total = len(slowList) sort.Slice(slowList, func(i, j int) bool { //按总执行次数排序 return slowList[i].MySQLTotalExecutionCounts > slowList[j].MySQLTotalExecutionCounts }) return total,slowList } ``` 在utils目录下创建request.go文件 ``` package utils import ( "fmt" "github.com/aliyun/alibaba-cloud-sdk-go/sdk/requests" "github.com/aliyun/alibaba-cloud-sdk-go/services/rds" ) type SlowLogs struct { DetailStartTime string DetailEndTime string Client *rds.Client Request *rds.DescribeSlowLogsRequest } func (s SlowLogs)GetTotal()int { response , err := s.Client.DescribeSlowLogs(s.Request) if err != nil{ fmt.Println(err.Error()) return 0 } return response.TotalRecordCount } func (s SlowLogs)GetSlowData(pageNum int)(response *rds.DescribeSlowLogsResponse,err error){ s.Request.PageNumber = requests.NewInteger(pageNum) response , err = s.Client.DescribeSlowLogs(s.Request) return response,err } type SlowDetailRecords struct { DetailStartTime string DetailEndTime string Client *rds.Client Request *rds.DescribeSlowLogRecordsRequest } func (s SlowDetailRecords)GetTotal()int { response , err := s.Client.DescribeSlowLogRecords(s.Request) if err != nil{ fmt.Println(err.Error()) return 0 } return response.TotalRecordCount } func (s SlowDetailRecords)GetSlowDetailData(pageNum int)(response *rds.DescribeSlowLogRecordsResponse,err error){ s.Request.PageNumber = requests.NewInteger(pageNum) response , err = s.Client.DescribeSlowLogRecords(s.Request) return response,err } ``` 在utils目录下创建Parse.go文件 ``` package utils import ( "getslow/models" "github.com/aliyun/alibaba-cloud-sdk-go/services/rds" ) func SlowParse(data []rds.SQLSlowLog)(slowList []models.SlowModel){ for _,v := range data{ slowList = append(slowList,models.SlowModel{ DBName: v.DBName, SQLText: v.SQLText, MySQLTotalExecutionCounts: v.MySQLTotalExecutionCounts, MySQLTotalExecutionTimes: v.MySQLTotalExecutionTimes, MaxExecutionTime: v.MaxExecutionTime, AvgExecutionTime: v.MySQLTotalExecutionTimes / v.MySQLTotalExecutionCounts, TotalLockTimes: v.TotalLockTimes, MaxLockTime: v.MaxLockTime, ParseTotalRowCounts: v.ParseTotalRowCounts, ParseMaxRowCount: v.ParseMaxRowCount, ReturnTotalRowCounts: v.ReturnTotalRowCounts, ReturnMaxRowCount: v.ReturnMaxRowCount, }) } return slowList } func SlowDetailParse(data []rds.SQLSlowRecord)(slowDetailList []models.SlowDetailModel){ for _,v := range data{ slowDetailList = append(slowDetailList,models.SlowDetailModel{ DBName: v.DBName, SQLText: v.SQLText, QueryTimes: v.QueryTimes, LockTimes: v.LockTimes, ReturnRowCounts: v.ReturnRowCounts, ParseRowCounts: v.ParseRowCounts, }) } return slowDetailList } ``` ## 下载依赖包 ``` go get -u github.com/aliyun/alibaba-cloud-sdk-go/sdk go get -u github.com/360EntSecGroup-Skylar/excelize/v2 go get -u github.com/go-ini/ini go get -u gopkg.in/gomail.v2 ``` ## 添加数据模型 在models目录下添加slow.go文件 ``` package models import ( "fmt" "github.com/360EntSecGroup-Skylar/excelize/v2" ) //慢日志统计 type SlowModel struct { DBName string SQLText string MySQLTotalExecutionCounts int64 MySQLTotalExecutionTimes int64 MaxExecutionTime int64 AvgExecutionTime int64 TotalLockTimes int64 MaxLockTime int64 ParseTotalRowCounts int64 ParseMaxRowCount int64 ReturnTotalRowCounts int64 ReturnMaxRowCount int64 } type SlowList []SlowModel type TemplateBody struct { TotalRecordCount int TRData SlowList Path string } //慢日志详细 type SlowDetailModel struct { DBName string SQLText string QueryTimes int64 LockTimes int64 ReturnRowCounts int64 ParseRowCounts int64 } type SlowDetailList []SlowDetailModel type SlowDetail struct { Instance string SlowDetailList } func (s SlowDetail)Save() { f := excelize.NewFile() //更改sheet1名字 f.SetSheetName("Sheet1",s.Instance) f.SetSheetRow(s.Instance,"A1",&[]interface{}{"数据库名","SQL语句","执行时长(秒)","锁定时长","解析行数","返回行数"}) for i,v := range s.SlowDetailList{ axis := fmt.Sprintf("A%d",i+2) f.SetSheetRow(s.Instance,axis,&[]interface{}{ v.DBName, v.SQLText, v.QueryTimes, v.LockTimes, v.ParseRowCounts, v.ReturnRowCounts, }) } f.SetActiveSheet(1) xlsx_name := fmt.Sprintf("%s.xlsx",s.Instance) // 根据指定路径保存文件 if err := f.SaveAs(xlsx_name); err != nil { fmt.Println(err) } } ``` ## 添加html模板 在templates目录下添加email.html文件 ```
{{range .TRData}} {{end}}
总记录：{{.TotalRecordCount}}
数据库 SQL语句 MySQL总执行次数 MySQL总执行时间 最大执行时长 平均执行时间 锁定总时长/秒 最大锁定时长/秒 解析SQL总行数 解析SQL最大行数 返回SQL总行数 返回SQL最大行数
{{.DBName}} {{.SQLText}} {{.MySQLTotalExecutionCounts}} {{.MySQLTotalExecutionTimes}} {{.MaxExecutionTime}} {{.AvgExecutionTime}} {{.TotalLockTimes}} {{.MaxLockTime}} {{.ParseTotalRowCounts}} {{.ParseMaxRowCount}} {{.ReturnTotalRowCounts}} {{.ReturnMaxRowCount}}
``` ## 创建主程序 ``` package main import ( "fmt" "getslow/models" "getslow/pkg/settings" "getslow/pkg/utils" "github.com/aliyun/alibaba-cloud-sdk-go/sdk/requests" "github.com/aliyun/alibaba-cloud-sdk-go/services/rds" "net/http" "os" "time" ) var ( PageSize = 100 MaxPage int Message = []string{"订单主库","订单从库","营销主库","营销从库","erp主库","erp从库","java主库","java从库","crm主库","crm从库"} ) func main() { if err := settings.Setup();err != nil{ fmt.Println(err) return } //获取慢日志明细时间区间 DetailStartTime := time.Now().Add(-(1*24*time.Hour)).Format("2006-01-02T00:00Z") DetailEndTime := time.Now().Format("2006-01-02T00:00Z") //获取慢日志统计时间区间 //由于取7天的数据，如果数据量多有时候会导致取不到数据，所以这里就只取4天的数据 SlowStartTime := time.Now().Add(-(3 * 24 * time.Hour)).Format("2006-01-02Z") SlowEndTime := time.Now().Add(-(1 * 24 * time.Hour)).Format("2006-01-02Z") fmt.Println(SlowStartTime,SlowEndTime) // // 创建client 实例 client,err := rds.NewClientWithAccessKey( settings.RdsSetting.RegionId, settings.RdsSetting.AccessKeyId, settings.RdsSetting.AccessKeySecret, ) if err != nil{ fmt.Println(err) return } var slowLogList models.SlowList var slowDetailList models.SlowDetailList //创建慢日志统计请求并设置参数 SlowRequest := rds.CreateDescribeSlowLogsRequest() SlowRequest.Scheme = "https" SlowRequest.StartTime = SlowStartTime //按照阿里云的api文档 这里的时间结尾都会加上 Z SlowRequest.EndTime = SlowEndTime SlowRequest.PageSize = requests.NewInteger(PageSize) slowLogs := utils.SlowLogs{ DetailStartTime: SlowStartTime, DetailEndTime: SlowEndTime, Client: client, Request: SlowRequest, } //创建慢日志详细请求并设置参数 RecordRequest := rds.CreateDescribeSlowLogRecordsRequest() RecordRequest.Scheme = "https" RecordRequest.StartTime = DetailStartTime //按照阿里云的api文档 这里的时间结尾都会加上 Z RecordRequest.EndTime = DetailEndTime //每页条数，范围:30~100 RecordRequest.PageSize = requests.NewInteger(PageSize) slowDetail := utils.SlowDetailRecords{ DetailStartTime: DetailEndTime, DetailEndTime: DetailEndTime, Client: client, Request: RecordRequest, } if err := utils.ConnSmtp();err != nil{ panic(err) } for index,value := range settings.RdsSetting.DBInstanceID { slowLogs.Request.PageNumber = requests.NewInteger(1) slowLogs.Request.DBInstanceId = value if total := slowLogs.GetTotal();total > 0{ if total % PageSize >0{ MaxPage = total / PageSize + 1 } }else { fmt.Printf("实例：%s 没有查到慢日志\n",value) continue } for sl:=1;sl<=MaxPage;sl++{ response ,err := slowLogs.GetSlowData(sl) if err != nil{ fmt.Println(err.Error()) continue } if response.GetHttpStatus() != http.StatusOK { fmt.Println(response.GetHttpStatus()) continue } if response.TotalRecordCount == 0 { fmt.Printf("实例: %s 没有慢日志查询\n",value) continue } slowLogList = append(slowLogList,utils.SlowParse(response.Items.SQLSlowLog)...) } total,slows := utils.Removal(slowLogList) if total > 0 { // 页码，范围：大于0并且不超过int的最大值 slowDetail.Request.PageNumber = requests.NewInteger(1) slowDetail.Request.DBInstanceId = value if total := slowDetail.GetTotal();total > 0{ if total % PageSize >0{ MaxPage = total / PageSize + 1 } } for di:=1;di<=MaxPage;di++{ response ,err := slowDetail.GetSlowDetailData(di) if err != nil{ fmt.Println(err.Error()) continue } if response.GetHttpStatus() != http.StatusOK { fmt.Println(response.GetHttpStatus()) continue } if response.TotalRecordCount == 0 { fmt.Printf("实例: %s 没有慢日志查询\n",value) continue } slowDetailList = append(slowDetailList,utils.SlowDetailParse(response.Items.SQLSlowRecord)...) } slowDetail := models.SlowDetail{ Instance: value, SlowDetailList: slowDetailList, } slowDetail.Save() body := models.TemplateBody{ TotalRecordCount: total, TRData: slows, Path: fmt.Sprintf("./%s.xlsx",value), } err = utils.SendMail(index,time.Now().Format("2006-01-02"),Message[index],body) if err != nil{ fmt.Println("email---",err.Error()) } os.Remove(fmt.Sprintf("./%s.xlsx",value)) time.Sleep(2*time.Second) } } } ```