sql server(mssqlserver)启动失败_Prometheus监控实战之sql_exporter使用(第六篇)

概述：sql_exporter导出器主要用来配置连接到到MySQL(MariaDB)、PostgreSQL等数据库，允许用户编写SQL来获取业务相关指标，例如，领导想知道当天的支付成功订单数、支付失败订单数、退订数、营业额等等，而这些数据均可以从数据库中获取到。那就让我们使用sql_exporter来实践一下。

操作步骤：

1、下载sql_exporter并解压

https://github.com/free/sql_exporter/releases

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

# tar xvf sql_exporter-0.5.linux-amd64.tar.gz -C /usr/local

# cd /usr/local && ln -s sql_exporter-0.5.linux-amd64 sql_exporter

2、修改主配置文件

配置文件名字可以自定义，即默认的sql_exporter.yml可以重命名为config.yml

# cd /usr/local/sql_exporter

# mv sql_exporter.yml config.yml

# cat config.yml

# Global defaults.

global:

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

scrape_timeout: 30s

# 用于从prometheus的scrape_timeout中减去一个偏移时间，防止prometheus先超时，如果设置了scrape_timeout，scrape_timeout_offset基本可以忽略。

scrape_timeout_offset: 500ms

# 各收集器是否同时执行.

min_interval: 0s

# 允许对数据库最大连接数

max_connections: 10

# 允许对数据库保持空闲时连接数

max_idle_connections: 5

# 目标数据库连接配置.

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://prom_user:prom_password@dbserver1.example.com:1433'

data_source_name: 'mysql://monitor:monitorpass@tcp(192.168.59.250:3306)/test'

# Collectors (referenced by name) to execute on the target.

# 多个收集器需要用逗号隔开，如[order01,order02]

collectors: [order]

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

collector_files:

- "order.collector.yml"

3、编写具体的收集器配置文件

# cat order.collector.yml

collector_name: order

metrics:

- metric_name: order_success_day

type: gauge

help: '每日支付成功订单数'

values: [order_success_day]

query: |

select count(order_id) as 'order_success_day' from t_orders where status=1 and createtime BETWEEN concat(curdate(),' 00:00:00') AND concat(curdate(),' 23:59:59')

- metric_name: order_fail_day

type: gauge

help: '每日支付失败订单数'

values: [order_fail_day]

query: |

select count(order_id) as 'order_fail_day' from t_orders where status=2 and createtime BETWEEN concat(curdate(),' 00:00:00') AND concat(curdate(),' 23:59:59')

- metric_name: order_wait_day

type: gauge

help: '每日待支付订单数'

values: [order_wait_day]

query: |

select count(order_id) as 'order_wait_day' from t_orders where status=3 and createtime BETWEEN concat(curdate(),' 00:00:00') AND concat(curdate(),' 23:59:59')

- metric_name: order_cancel_day

type: gauge

help: '每日退订订单数'

values: [order_cancel_day]

query: |

select count(order_id) as 'order_cancel_day' from t_orders where status=4 and createtime BETWEEN concat(curdate(),' 00:00:00') AND concat(curdate(),' 23:59:59')

- metric_name: order_unknown_day

type: gauge

help: '每日退订订单数'

values: [order_unknown_day]

query: |

select count(order_id) as 'order_unknown_day' from t_orders where status=5 and createtime BETWEEN concat(curdate(),' 00:00:00') AND concat(curdate(),' 23:59:59')

4、配置启动脚本

# cat > /lib/systemd/system/sql_exporter.service <

[Unit]

Description=sql_exporter

[Service]

ExecStart=/usr/local/sql_exporter/sql_exporter -config.file /usr/local/sql_exporter/config.yml -web.listen-address 0.0.0.0:9399

Restart=on-failure

[Install]

WantedBy=multi-user.target

EOF

备注：sql_exporter的默认端口是9399

5、启动sql_exporter

# systemctl enable sql_exporter

# systemctl restart sql_exporter

可通过 http://IP:9399/metrics查看

5、将sql_exporter作为目标添加到prometheus

# cat prometheus.yml

......

......

- targets: ['monitor01:9399']

labels:

app: sql_exporter

nodename: monitor01

role: sql_exporter

# ./promtool check config prometheus.yml

# curl -X POST http://192.168.59.250:9090/-/reload

6、在Grafana中展示数据