sql_exporter mysql_【最佳实践】prometheus 监控 sql server （使用sql_exporter）

超、凢脫俗 2022-10-25 11:26 4637阅读 0赞

【0】核心参考

【简述】

MSSQL的监控，官网没有提供采集器，所以只能用其他程序来监控，本文就用了 sql-exporter。

它可以理解成一个远程连接数据库的工具，可以用它来连接sql server/mysql 等等数据库，并以SQL查询方式采集SQL查询结果。

SQL\_Exporter 是中心化的，可以把对不同实例的agent链接，都放在一台linux服务器上，以便管理、修改。你想想看，如果你更新了采集指标，只需要在该台中心化服务器重启sql\_exporter agent采集客户端即可生效。

不像其他 mysql/linux/windows采集器，他们是部署在实际被采集的服务器上的，如果你想要更新采集器(虽然它做的很好了，不需要更新。除此之外就是无法加入自定义的采集指标或者采集项)，那么需要到那么多台被采集的客户端机器上去覆盖更新，很痛苦的。

【1】安装配置 sql\_exporter

【1.1】下载解压 sql\_exporter

\#wget https://github.com/free/sql\_exporter/releases/download/0.5/sql\_exporter-0.5.linux-amd64.tar.gz

mkdir /soft

cd/softwget https://github.com/free/sql\_exporter/releases/download/0.5/sql\_exporter-0.5.linux-amd64.tar.gz

tar -zxf sql\_exporter-0.5.linux-amd64.tar.gzln -s sql\_exporter-0.5.linux-amd64 sql\_exporter

cd sql\_exporter

【1.2】修改配置文件

\# Global defaults.

global:

\# Subtracted from Prometheus'scrape\_timeout to give us some headroom and prevent Prometheus from timing out first.

scrape\_timeout\_offset: 500ms

\# Minimum interval between collector runs: by default (0s) collectors are executed on every scrape.

min\_interval: 0s

\# Maximum number of open connections to any one target. Metric queries will run concurrently on multiple connections,

\# as will concurrent scrapes.

max\_connections:10\# Maximum number of idle connections to any one target. Unless you use verylongcollection intervals, this should

\# always be the same as max\_connections.

max\_idle\_connections:5\# The target to monitor and the collectors to execute on it.

target:

\# Data source name always has a URI schema that matches the driver name. In some cases (e.g. MySQL)

\# the schema gets dropped or replaced to match the driver expected DSN format.

\# data\_source\_name: 'sqlserver://sql\_exporter:a123456!@192.168.191.81:1433/?encrypt=disable'

data\_source\_name:'sqlserver://sa:a123456!@192.168.191.81:1433'\# Collectors (referenced by name) to execute on the target.

collectors: \[mssql\_standard\]

\# Collector files specifies a list of globs. One collector definition is read from each matchingfile.

collector\_files:- "\*.collector.yml"

解析：

(1)global

收集器中允许最慢的SQL执行超时时间，注意该超时时间应小于prometheus中的 scrape\_time

scrape\_timeout\_offset: 500ms \#用于从 prometheus的 scrape\_timeout 中减去一个偏移时间，防止 prometheus 先超时，如果设置了 scrape\_timeout,scrape

min\_interval: 0s \#收集器每隔0运行一次(默认情况下)收集器每隔0运行一次。

max\_connections:10 \#到任何一个目标的最大打开连接数。采集器信息查询将在多个连接上并发运行，max\_idle\_connections:5 \#到任何一个目标的最大空闲连接数。除非使用很长的收集间隔，否则应该

(2)target

\#数据源

data\_source\_name: 'sqlserver://sa:a123456!@192.168.191.81:1433'

(3)collector

\#引用收集器文件

collector\_files:

\- "\*.collector.yml"

【1.3】自带的sql server监控采集器

![b40ce2a4ec2f49df9c16ba90e41f86cb.png][]

这里我们配置文件中 已经引用了 配置文件相同目录下的 "\*.collector.yml"，所以该文件也被包含进来了。

【2】整合 prometheus + sql\_exporter

那么这个东西其实是一个采集器啊，但是为什么不能放到windows上呢.......好吧，不管了好像没什么很好的windows采集器，自己也不会做，先用着吧

【2.1】修改prometheus.yml配置文件

![5aa7f69bd59af8a39b874d73c550018b.png][]

【2.2】启动 sql\_exporter

(1)封装成系统服务

\[Unit\]

Description=sql\_exporter

\[Service\]

Type=simple

ExecStart=/soft/sql\_exporter/sql\_exporter -config.file /soft/sql\_exporter/sql\_exporter.yml

Restart=on-failure

\[Install\]

WantedBy=multi-user.target

(2)启动、查看

systemctl daemon-reload

systemctl start sql\_exporter

systemctl status sql\_exporter-l

启动成功，并且 默认端口是 9399

![38dd3674d016f4320df0950bc3333d51.png][]

【2.3】核验

如下图，这就成功了啊

![a92b03332a9df677ee301e2db612edab.png][]

【3】结合 grafana 显示

【3.1】导入MSSQL模板

![a1720f0d4e75d4b255bb7da21a64c076.png][]

![0ffdcb285622237c50f58660bba5f127.png][]

导入这个 9336 模板

![687b9788e1f63c10a568256c1f7c3c15.png][]

【3.2】查看仪表盘

最后结果：好像还行，但很多图表no data ，这个模板和采集器不是很配，有空可以自己配置修改一下

![cc91182082aebc4998812c91c6f9041d.png][]

【4】自定义MSSQL

【4.0】MSSQL权限与监控账户

DECLARE @sql VARCHAR(max)

SET @sql=CAST('use master;CREATE LOGIN \[sql\_exporter\] WITH PASSWORD=N''qICJEasdqwDiOSrdT96'', DEFAULT\_DATABASE=\[master\], CHECK\_EXPIRATION=OFF, CHECK\_POLICY=OFF; GRANT VIEW SERVER STATE TO \[sql\_exporter\];

GRANT VIEW ANY DEFINITION TO \[sql\_exporter\];'AS VARCHAR(max))

select @sql=@sql+CAST('use'+name+';CREATE USER \[sql\_exporter\] FOR LOGIN \[sql\_exporter\];

EXEC sp\_addrolemember N''db\_datareader'', N''sql\_exporter'';'+CHAR(10) AS VARCHAR(max))

from master.sys.databases and state=0EXEC(@sql)

防火墙什么的就不用我说了吧？

【4.1】自定义采集器

【4.2】采集器启动

布置在linux 中间节点，也可以直接布置在prometheus节点上

如果密码有特殊字符，在URL上使用报错，则参见附录，用特殊字符替换

比如密码： !@\#$%^qwe123   转移成 %21%40%23%24%25%5Eqwe123

nohup /data/mssql/sql\_exporter -config.data-source-name=sqlserver://sql\_exporter:qICJEasdqwDiOSrdT96@10.112.5.106:1433/?encrypt=disable -config.file=/data/mssql/sql\_exporter.yml -web.listen-address=127.0.0.1:9400 -log\_dir=/data/mssql\_log &

nohup/data/mssql/sql\_exporter -config.data-source-name=sqlserver://sql\_exporter:qICJEasdqwDiOSrdT96@10.112.5.105:1433/?encrypt=disable -config.file=/data/mssql/sql\_exporter.yml -web.listen-address=127.0.0.1:9401 -log\_dir=/data/mssql\_log &

【4.3】prometheus配置

因为是自定义，所以在job name中特地加了 mssql 关键字，以便【4.4】中的变量好获取到所有mssql 的job，以供筛选

\- job\_name: '大连娱网\_mssql'static\_configs:- targets: \['127.0.0.1:9400'\]

labels:

name:'我是第一台机器DB\[10.112.5.106\]'

\- targets: \['127.0.0.1:9401'\]

labels:

name:'我是第二台机器DB\[10.112.5.10\]'

这里的配置，要和【4.2】的相互对应，否则会采集出问题。

这里的name 为什么会写上IP呢，这是因为为了辨识是哪台机器，也是为了【4.4】中的大盘 仪表盘上可以显示出机器IP来，因为我们这个是自定义的，无法像官网提供的一样。其实我们这个有点类似于pushgateway

【4.4】自定义仪表盘

核心变量

![a26f4e1d7ba75d1a22d4e81b478ff42f.png][]

骚气界面预览

![2725dd9870241032ce6aec78b3544e5a.png][]

【4.5】报警规则

groups:-name: MSSQL告警规则

rules:-alert: mssql引擎服务宕机expr: windows\_service\_state\{state="running",exported\_name="mssqlserver"\}!=1

for: 1m

labels:

severity: warning

annotations:

summary:"详细: \{ \{ $labels \}\}"

\-alert: mssql代理服务宕机expr: windows\_service\_state\{exported\_name="sqlserveragent",state="running"\}!=1

for: 1m

labels:

severity: warning

annotations:

summary:"详细: \{ \{ $labels \}\}"

\-alert: mssql引擎服务重启expr: mssql\_db\_uptime < 3600

for: 1m

labels:

severity: warning

annotations:

summary:"详细: \{ \{ $labels \}\}"description:"mssql引擎服务1小时内有过重启，现已重启\{ \{ $value \}\} 秒"

\- alert: mssql数据库不可用/不可访问expr: mssql\_current\_state\_dbState !=0

for: 1m

labels:

severity: warning

annotations:

summary:"详细: \{ \{ $labels \}\}"description:"db:\{ \{ $labels.db \}\}\\n value:\{ \{ $labels.value \}\}=\{ \{ $value \}\}"

\-alert: mssql阻塞expr: sum(mssql\_current\_state\_blocking)>5

for: 1m

labels:

severity: warning

annotations:

summary:"详细: \{ \{ $labels \}\}"description:"mssql请求阻塞数>5，当前：\{ \{ $value \}\}"

\-alert: mssql请求过多expr: sum(mssql\_current\_state\_requests)>100

for: 1m

labels:

severity: warning

annotations:

summary:"详细: \{ \{ $labels \}\}"description:"mssql请求数>100，当前：\{ \{ $value \}\}"

\-alert: mssql死锁产生expr: increase(mssql\_counter\{type\_object="SQLServer:Locks",type\_counter="Number of Deadlocks/sec",type\_instance="\_Total"\}\[5m\])>0

for: 1m

labels:

severity: warning

annotations:

summary:"详细: \{ \{ $labels \}\}"description:"mssql 5分钟内死锁产生次数：\{ \{ $value \}\}"

\-alert: mssql作业执行错误expr: increase(mssql\_job\_state\_today\[5m\])>0

for: 1m

labels:

severity: warning

annotations:

summary:"详细: \{ \{ $labels \}\}"description:"mssql 今天作业运行错误次数：\{ \{ $value \}\}"

\-alert: mssql镜像状态变化expr: increase(mssql\_mirror\_sync\{value="status"\} \[5m\])!=0

for: 1m

labels:

severity: warning

annotations:

summary:"详细: \{ \{ $labels \}\}"description:"db:\{ \{ $labels.db \}\}\\n value:\{ \{ $labels.value \}\}=\{ \{ $value \}\}"

【4.6】报警模板

\{ \{ define "email.html"\}\}

\{ \{- if gt (len .Alerts.Firing) 0 -\}\}\{ \{ range.Alerts \}\}

告警项: \{ \{ .Labels.alertname \}\}  
项目组:\{ \{ .Labels.job \}\}  
实例名:\{ \{ .Labels.name \}\}-\{ \{ .Labels.instance \}\}  
详情: \{ \{ .Annotations.description \}\}  
级别: \{ \{ .Labels.severity \}\}  
开始时间: \{ \{ (.StartsAt.Add 28800e9).Format"2006-01-02 15:04:05" \}\}

\++++++++++++++++++++++++++++++++++++

\+++++++++++++++++++++++++++++++++++++  
\{ \{ end \}\}\{ \{ end-\}\}

\{ \{- if gt (len .Alerts.Resolved) 0 -\}\}\{ \{ range.Alerts \}\}

Resolved  
告警项: \{ \{ .Labels.alertname \}\}  
项目组:\{ \{ .Labels.job \}\}  
实例名:\{ \{ .Labels.name \}\}-\{ \{ .Labels.instance \}\}  
详情: \{ \{ .Annotations.description \}\}  
级别: \{ \{ .Labels.severity \}\}  
开始时间: \{ \{ (.StartsAt.Add 28800e9).Format"2006-01-02 15:04:05" \}\}  
恢复时间: \{ \{ (.EndsAt.Add 28800e9).Format"2006-01-02 15:04:05" \}\}

\++++++++++++++++++++++++++++++++++++

\+++++++++++++++++++++++++++++++++++++  
\{ \{ end \}\}\{ \{ end-\}\}

\{ \{- end \}\}

企业微信

\{ \{ define "wechat.default.message"\}\}

\{ \{- if gt (len .Alerts.Firing) 0 -\}\}\{ \{ range.Alerts \}\}

告警项: \{ \{ .Labels.alertname \}\}

项目组:\{ \{ .Labels.job \}\}

实例名:\{ \{ .Labels.name \}\}-\{ \{ .Labels.instance \}\}

详情: \{ \{ .Annotations.description \}\}

级别: \{ \{ .Labels.severity \}\}

开始时间: \{ \{ (.StartsAt.Add 28800e9).Format"2006-01-02 15:04:05"\}\}------------------------------------

\------------------------------------\{ \{ end \}\}\{ \{ end-\}\}

\{ \{- if gt (len .Alerts.Resolved) 0 -\}\}\{ \{ range.Alerts \}\}

Resolved

告警项: \{ \{ .Labels.alertname \}\}

项目组:\{ \{ .Labels.job \}\}

实例名:\{ \{ .Labels.name \}\}-\{ \{ .Labels.instance \}\}

详情: \{ \{ .Annotations.description \}\}

级别: \{ \{ .Labels.severity \}\}

开始时间: \{ \{ (.StartsAt.Add 28800e9).Format"2006-01-02 15:04:05"\}\}

恢复时间: \{ \{ (.EndsAt.Add 28800e9).Format"2006-01-02 15:04:05"\}\}------------------------------------

\------------------------------------\{ \{ end \}\}\{ \{ end-\}\}

\{ \{- end \}\}

【参考文档】

【附录】

由于在url中特殊符号都有特殊意义或者被认为是不安全的字符，所以在拼接url时应当替换出url中的特殊字符

例如

var x = "2\# 前缘肋"

var rp= x.replace('\#','%23'); // %23是\#的URL编码 要用他来替换原有的\#

w3schools网站上列出了此类编码参考

CharacterFrom Windows-1252From UTF-8

space

%20

%21

%22

%23

%24

%25

%26

%27

(

%28

)

%29

%2A

%2B

%2C

%2D

%2E

%2F

%30

%31

%32

%33

%34

%35

%36

%37

%38

%39

%3A

;

%3B

%3C

%3D

%3E

%3F

%40

%41

%42

%43

%44

%45

%46

%47

%48

%49

%4A

%4B

%4C

%4D

%4E

%4F

%50

%51

%52

%53

%54

%55

%56

%57

%58

%59

%5A

%5B

%5C

%5D

%5E

%5F

%60

%61

%62

%63

%64

%65

%66

%67

%68

%69

%6A

%6B

%6C

%6D

%6E

%6F

%70

%71

%72

%73

%74

%75

%76

%77

%78

%79

%7A

%7B

%7C

%7D

%7E

%7F

%80

%E2%82%AC

%81

‚

%82

%E2%80%9A

%83

%C6%92

„

%84

%E2%80%9E

…

%85

%E2%80%A6

†

%86

%E2%80%A0

‡

%87

%E2%80%A1

%88

%CB%86

‰

%89

%E2%80%B0

%8A

%C5%A0

‹

%8B

%E2%80%B9

%8C

%C5%92

%8D

%C5%8D

%8E

%C5%BD

%8F

%90

%C2%90

‘

%91

%E2%80%98

’

%92

%E2%80%99

“

%93

%E2%80%9C

”

%94

%E2%80%9D

•

%95

%E2%80%A2

–

%96

%E2%80%93

—

%97

%E2%80%94

%98

%CB%9C

™

%99

%E2%84

%9A

%C5%A1

›

%9B

%E2%80

%9C

%C5%93

%9D

%9E

%C5%BE

%9F

%C5%B8

%A0

%C2%A0

%A1

%C2%A1

%A2

%C2%A2

%A3

%C2%A3

%A4

%C2%A4

%A5

%C2%A5

%A6

%C2%A6

%A7

%C2%A7

%A8

%C2%A8

%A9

%C2%A9

%AA

%C2%AA

%AB

%C2%AB

%AC

%C2%AC

%AD

%C2%AD

%AE

%C2%AE

%AF

%C2%AF

%B0

%C2%B0

%B1

%C2%B1

%B2

%C2%B2

%B3

%C2%B3

%B4

%C2%B4

%B5

%C2%B5

%B6

%C2%B6

%B7

%C2%B7

%B8

%C2%B8

%B9

%C2%B9

%BA

%C2%BA

%BB

%C2%BB

%BC

%C2%BC

%BD

%C2%BD

%BE

%C2%BE

%BF

%C2%BF

%C0

%C3%80

%C1

%C3%81

%C2

%C3%82

%C3

%C3%83

%C4

%C3%84

%C5

%C3%85

%C6

%C3%86

%C7

%C3%87

%C8

%C3%88

%C9

%C3%89

%CA

%C3%8A

%CB

%C3%8B

%CC

%C3%8C

%CD

%C3%8D

%CE

%C3%8E

%CF

%C3%8F

%D0

%C3%90

%D1

%C3%91

%D2

%C3%92

%D3

%C3%93

%D4

%C3%94

%D5

%C3%95

%D6

%C3%96

%D7

%C3%97

%D8

%C3%98

%D9

%C3%99

%DA

%C3%9A

%DB

%C3%9B

%DC

%C3%9C

%DD

%C3%9D

%DE

%C3%9E

%DF

%C3%9F

%E0

%C3%A0

%E1

%C3%A1

%E2

%C3%A2

%E3

%C3%A3

%E4

%C3%A4

%E5

%C3%A5

%E6

%C3%A6

%E7

%C3%A7

%E8

%C3%A8

%E9

%C3%A9

%EA

%C3%AA

%EB

%C3%AB

%EC

%C3%AC

%ED

%C3%AD

%EE

%C3%AE

%EF

%C3%AF

%F0

%C3%B0

%F1

%C3%B1

%F2

%C3%B2

%F3

%C3%B3

%F4

%C3%B4

%F5

%C3%B5

%F6

%C3%B6

%F7

%C3%B7

%F8

%C3%B8

%F9

%C3%B9

%FA

%C3%BA

%FB

%C3%BB

%FC

%C3%BC

%FD

%C3%BD

%FE

%C3%BE

%FF

%C3%BF

URL Encoding Reference

The ASCII control characters %00-%1F were originally designed to control hardware devices.

Control characters have nothing to do inside a URL.

ASCII CharacterDescriptionURL-encoding

NUL

null character

%00

SOH

start of header

%01

STX

start of text

%02

ETX

end of text

%03

EOT

end of transmission

%04

ENQ

enquiry

%05

ACK

acknowledge

%06

BEL

bell (ring)

%07

backspace

%08

horizontal tab

%09

line feed

%0A

vertical tab

%0B

form feed

%0C

carriage return

%0D

shift out

%0E

shift in

%0F

DLE

data link escape

%10

DC1

device control 1

%11

DC2

device control 2

%12

DC3

device control 3

%13

DC4

device control 4

%14

NAK

negative acknowledge

%15

SYN

synchronize

%16

ETB

end transmission block

%17

CAN

cancel

%18

end of medium

%19

SUB

substitute

%1A

ESC

escape

%1B

file separator

%1C

group separator

%1D

record separator

%1E

unit separator

[b40ce2a4ec2f49df9c16ba90e41f86cb.png]: /images/20221024/ea7ad4182d40404080b25343ae3914aa.png
[5aa7f69bd59af8a39b874d73c550018b.png]: /images/20221024/d3d34df4e3754fd3889805f45e2b3c07.png
[38dd3674d016f4320df0950bc3333d51.png]: /images/20221024/991738711c334a6ea2cc9c80cce2599b.png
[a92b03332a9df677ee301e2db612edab.png]: /images/20221024/365e8f61dc53403caabf847a5d9f7038.png
[a1720f0d4e75d4b255bb7da21a64c076.png]: /images/20221024/6029e45a4eb44b4eb9ab8cf0e0e2c928.png
[0ffdcb285622237c50f58660bba5f127.png]: /images/20221024/7ff50fc75e0248e58918a850101071b9.png
[687b9788e1f63c10a568256c1f7c3c15.png]: /images/20221024/d53acd71947c4f8dbc402ec3c019babf.png
[cc91182082aebc4998812c91c6f9041d.png]: /images/20221024/513d6908d092441ba33a3341621eaec2.png
[a26f4e1d7ba75d1a22d4e81b478ff42f.png]: /images/20221024/273d002ecff7441186779bdbcb201e19.png
[2725dd9870241032ce6aec78b3544e5a.png]: /images/20221024/42a67dea537e459eb57fe94b01592efd.png