Promethues+Grafana监控host+postgresql+mysql+oracle实践（最全最详细版）

skylines 2023-12-10

334

概述

这次使用Prometheus做监控，开始只想着用它来监控host主机资源和MySQL数据库的，后面就是越加越多，加上了监控PostgreSQL数据库和Oracle数据库。由于Promethues+Grafana都是开源的，所以用到的安装包或者配置文件就比较容易得到。Promethues+Grafana可监控多种程序软件，包括网络、操作系统、数据库、中间件、k8s、docker和业务等等，功能十分强大，针对监控不同的软件对象，将采用不同的exporter进行数据采集，以及在Grafana展示监控信息的时候，使用不同看板的json文件进行展示。所用到的材料文件信息见下面列表，以及提供材料文件的下载地址，方便大家下载使用。

材料信息列表

材料下载地址

链接：https://pan.baidu.com/s/1E0V-hv96gXpRh8-YPnswYA

提取码：3lxr

Grafana更多看板json文件下载地址

链接：https://grafana.com/grafana/dashboards/

以下是详细的安装与配置过程。

##监控服务端安装go

--解压安装包
[root@susource local]# tar -zxvf go1.8.3.linux-amd64.tar.gz
go/
go/AUTHORS
go/CONTRIBUTING.md
go/CONTRIBUTORS
go/LICENSE
go/PATENTS
go/README.md
go/VERSION
go/api/
go/api/README
go/api/except.txt
go/api/go1.1.txt
go/api/go1.2.txt
go/api/go1.3.txt
go/api/go1.4.txt
... ...


--编辑go的环境变量
vi etc/profile
export PATH=$PATH:/usr/local/go/bin


source etc/profile


--查看go的版本
[root@susource go]# go version
go version go1.8.3 linux/amd64






到此，go已经安装成功

##监控服务端安装prometheus

--解压安装包
# tar -zxvf prometheus-2.32.0-beta.0.linux-amd64.tar.gz
prometheus-2.32.0-beta.0.linux-amd64/
prometheus-2.32.0-beta.0.linux-amd64/consoles/
prometheus-2.32.0-beta.0.linux-amd64/consoles/index.html.example
prometheus-2.32.0-beta.0.linux-amd64/consoles/node-cpu.html
prometheus-2.32.0-beta.0.linux-amd64/consoles/node-disk.html
prometheus-2.32.0-beta.0.linux-amd64/consoles/node-overview.html
prometheus-2.32.0-beta.0.linux-amd64/consoles/node.html
prometheus-2.32.0-beta.0.linux-amd64/consoles/prometheus-overview.html
prometheus-2.32.0-beta.0.linux-amd64/consoles/prometheus.html
prometheus-2.32.0-beta.0.linux-amd64/console_libraries/
prometheus-2.32.0-beta.0.linux-amd64/console_libraries/menu.lib
prometheus-2.32.0-beta.0.linux-amd64/console_libraries/prom.lib
prometheus-2.32.0-beta.0.linux-amd64/prometheus.yml
prometheus-2.32.0-beta.0.linux-amd64/LICENSE
prometheus-2.32.0-beta.0.linux-amd64/NOTICE
prometheus-2.32.0-beta.0.linux-amd64/prometheus
prometheus-2.32.0-beta.0.linux-amd64/promtool
[root@susource local]# 


--查看安装的版本信息
[root@susource local]# cd prometheus/
[root@susource prometheus]# 
[root@susource prometheus]# ls
console_libraries  consoles  LICENSE  NOTICE  prometheus  prometheus.yml  promtool
[root@susource prometheus]# ls -lrt
total 191352
-rwxr-xr-x 1 3434 3434 101986796 Nov 16  2021 prometheus
-rwxr-xr-x 1 3434 3434  93929760 Nov 16  2021 promtool
-rw-r--r-- 1 3434 3434       934 Nov 16  2021 prometheus.yml
-rw-r--r-- 1 3434 3434      3646 Nov 16  2021 NOTICE
-rw-r--r-- 1 3434 3434     11357 Nov 16  2021 LICENSE
drwxr-xr-x 2 3434 3434       173 Nov 16  2021 consoles
drwxr-xr-x 2 3434 3434        38 Nov 16  2021 console_libraries
[root@susource prometheus]# 
[root@susource prometheus]# 
[root@susource prometheus]# ./prometheus --version
prometheus, version 2.32.0-beta.0 (branch: HEAD, revision: c32725ba7873dbaa39c223410043430ffa5a26c0)
  build user:       root@da630543d231
  build date:       20211116-11:23:14
  go version:       go1.17.3
  platform:         linux/amd64
[root@susource prometheus]# 


--给prometheus配置启停服务


vim prometheus.service
 
# 添加如下内容
[Unit]
Description=https://prometheus.io
  
[Service]
Restart=on-failure
ExecStart=/usr/local/prometheus/prometheus --config.file=/usr/local/prometheus/prometheus.yml
 
[Install]                      
WantedBy=multi-user.target

##监控服务端安装grafana

--使用rpm方式安装grafana
[root@susource prometheus]# ls
grafana-5.2.3-1.x86_64.rpm
[root@susource prometheus]# ls -lrt
total 52792
-rw-r--r-- 1 root root 54057234 Dec  6 19:09 grafana-5.2.3-1.x86_64.rpm
[root@susource prometheus]# yum -y install grafana-5.2.3-1.x86_64.rpm
Loaded plugins: fastestmirror, langpacks
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
Examining grafana-5.2.3-1.x86_64.rpm: grafana-5.2.3-1.x86_64
Marking grafana-5.2.3-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package grafana.x86_64 0:5.2.3-1 will be installed
--> Finished Dependency Resolution
c7-media                                                                                                                                                                                                             | 3.6 kB  00:00:00     


Dependencies Resolved


============================================================================================================================================================================================================================================
 Package                                              Arch                                                Version                                                Repository                                                            Size
============================================================================================================================================================================================================================================
Installing:
 grafana                                              x86_64                                              5.2.3-1                                                grafana-5.2.3-1.x86_64                                              158 M


Transaction Summary
============================================================================================================================================================================================================================================
Install  1 Package


Total size: 158 M
Installed size: 158 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : grafana-5.2.3-1.x86_64                                                                                                                                                                                                   1/1 
### NOT starting on installation, please execute the following statements to configure grafana to start automatically using systemd
 sudo bin/systemctl daemon-reload
 sudo bin/systemctl enable grafana-server.service
### You can start grafana-server by executing
 sudo bin/systemctl start grafana-server.service
POSTTRANS: Running script
  Verifying  : grafana-5.2.3-1.x86_64                                                                                                                                                                                                   1/1 


Installed:
  grafana.x86_64 0:5.2.3-1                                                                                                                                                                                                                  


Complete!
[root@susource prometheus]# 


--把grafana加入系统服务，并将服务启动


[root@susource prometheus]# sbin/chkconfig --add grafana-server
[root@susource prometheus]# service grafana-server start
Starting grafana-server (via systemctl):  [  OK  ]
[root@susource prometheus]# 
[root@susource prometheus]# ps -ef |grep grafana
grafana    9955      1  1 20:03 ?        00:00:00 usr/sbin/grafana-server --config=/etc/grafana/grafana.ini --pidfile=/var/run/grafana/grafana-server.pid cfg:default.paths.logs=/var/log/grafana cfg:default.paths.data=/var/lib/grafana cfg:default.paths.plugins=/var/lib/grafana/plugins cfg:default.paths.provisioning=/etc/grafana/provisioning
root       9989   7808  0 20:04 pts/0    00:00:00 grep --color=auto grafana
[root@susource prometheus]#

##安装grafana服务的piechart插件

--直接把piechart插件的zip包放到/var/lib/grafana/目录解压


[root@susource prometheus]# cd var/lib/grafana/
[root@susource grafana]# ls
grafana.db  plugins
[root@susource grafana]# cd plugins/
[root@susource plugins]# 
[root@susource plugins]# ls
[root@susource plugins]# rz
rz waiting to receive.
 zmodem trl+C ȡ


  100%     637 KB  637 KB/s 00:00:01       0 Errors.zip...


[root@susource plugins]# ls -lrt
total 640
-rw-r--r-- 1 root root 652481 Dec  6 19:08 grafana-piechart-panel.zip
[root@susource plugins]# unzip grafana-piechart-panel.zip 
Archive:  grafana-piechart-panel.zip
5f249d5bc95050865f32e5cb1f6f265f3198adbc
   creating: grafana-piechart-panel-5f249d5/
  inflating: grafana-piechart-panel-5f249d5/.gitignore  
  inflating: grafana-piechart-panel-5f249d5/Gruntfile.js  
  inflating: grafana-piechart-panel-5f249d5/LICENSE  
  inflating: grafana-piechart-panel-5f249d5/README.md  
   creating: grafana-piechart-panel-5f249d5/dist/
  inflating: grafana-piechart-panel-5f249d5/dist/README.md  
   creating: grafana-piechart-panel-5f249d5/dist/css/
  inflating: grafana-piechart-panel-5f249d5/dist/css/piechart.dark.css  
  inflating: grafana-piechart-panel-5f249d5/dist/css/piechart.light.css  
  inflating: grafana-piechart-panel-5f249d5/dist/editor.html  
   creating: grafana-piechart-panel-5f249d5/dist/img/
  inflating: grafana-piechart-panel-5f249d5/dist/img/piechart-donut.png  
  inflating: grafana-piechart-panel-5f249d5/dist/img/piechart-legend-on-graph.png  
  inflating: grafana-piechart-panel-5f249d5/dist/img/piechart-legend-rhs.png  
  inflating: grafana-piechart-panel-5f249d5/dist/img/piechart-legend-under.png  
  inflating: grafana-piechart-panel-5f249d5/dist/img/piechart-options.png  
 extracting: grafana-piechart-panel-5f249d5/dist/img/piechart_logo_large.png  
 extracting: grafana-piechart-panel-5f249d5/dist/img/piechart_logo_small.png  
  inflating: grafana-piechart-panel-5f249d5/dist/legend.js  
  inflating: grafana-piechart-panel-5f249d5/dist/legend.js.map  
   creating: grafana-piechart-panel-5f249d5/dist/lib/
  inflating: grafana-piechart-panel-5f249d5/dist/lib/perfect-scrollbar.min.js  
  inflating: grafana-piechart-panel-5f249d5/dist/module.html  
  inflating: grafana-piechart-panel-5f249d5/dist/module.js  
  inflating: grafana-piechart-panel-5f249d5/dist/module.js.map  
  inflating: grafana-piechart-panel-5f249d5/dist/piechart_ctrl.js  
  inflating: grafana-piechart-panel-5f249d5/dist/piechart_ctrl.js.map  
  inflating: grafana-piechart-panel-5f249d5/dist/plugin.json  
  inflating: grafana-piechart-panel-5f249d5/dist/rendering.js  
  inflating: grafana-piechart-panel-5f249d5/dist/rendering.js.map  
   creating: grafana-piechart-panel-5f249d5/dist/src/
   creating: grafana-piechart-panel-5f249d5/dist/src/img/
  inflating: grafana-piechart-panel-5f249d5/dist/src/img/piechart-donut.png  
  inflating: grafana-piechart-panel-5f249d5/dist/src/img/piechart-legend-on-graph.png  
  inflating: grafana-piechart-panel-5f249d5/dist/src/img/piechart-legend-rhs.png  
  inflating: grafana-piechart-panel-5f249d5/dist/src/img/piechart-legend-under.png  
  inflating: grafana-piechart-panel-5f249d5/dist/src/img/piechart-options.png  
 extracting: grafana-piechart-panel-5f249d5/dist/src/img/piechart_logo_large.png  
 extracting: grafana-piechart-panel-5f249d5/dist/src/img/piechart_logo_small.png  
  inflating: grafana-piechart-panel-5f249d5/package.json  
   creating: grafana-piechart-panel-5f249d5/src/
   creating: grafana-piechart-panel-5f249d5/src/css/
  inflating: grafana-piechart-panel-5f249d5/src/css/piechart.dark.css  
  inflating: grafana-piechart-panel-5f249d5/src/css/piechart.light.css  
  inflating: grafana-piechart-panel-5f249d5/src/editor.html  
   creating: grafana-piechart-panel-5f249d5/src/img/
  inflating: grafana-piechart-panel-5f249d5/src/img/piechart-donut.png  
  inflating: grafana-piechart-panel-5f249d5/src/img/piechart-legend-on-graph.png  
  inflating: grafana-piechart-panel-5f249d5/src/img/piechart-legend-rhs.png  
  inflating: grafana-piechart-panel-5f249d5/src/img/piechart-legend-under.png  
  inflating: grafana-piechart-panel-5f249d5/src/img/piechart-options.png  
 extracting: grafana-piechart-panel-5f249d5/src/img/piechart_logo_large.png  
 extracting: grafana-piechart-panel-5f249d5/src/img/piechart_logo_small.png  
  inflating: grafana-piechart-panel-5f249d5/src/legend.js  
   creating: grafana-piechart-panel-5f249d5/src/lib/
  inflating: grafana-piechart-panel-5f249d5/src/lib/perfect-scrollbar.min.js  
  inflating: grafana-piechart-panel-5f249d5/src/module.html  
  inflating: grafana-piechart-panel-5f249d5/src/module.js  
  inflating: grafana-piechart-panel-5f249d5/src/piechart_ctrl.js  
  inflating: grafana-piechart-panel-5f249d5/src/plugin.json  
  inflating: grafana-piechart-panel-5f249d5/src/rendering.js  
[root@susource plugins]# 


--启动grafana服务
[root@susource plugins]# service grafana-server restart
Restarting grafana-server (via systemctl):  [  OK  ]

##被监控端安装node_exporter（本次在192.168.163.2/176两台安装）

--解压node_exporter安装包
[root@susource local]# tar -zxvf node_exporter-0.18.1.linux-amd64.tar.gz 
node_exporter-0.18.1.linux-amd64/
node_exporter-0.18.1.linux-amd64/node_exporter
node_exporter-0.18.1.linux-amd64/NOTICE
node_exporter-0.18.1.linux-amd64/LICENSE
[root@susource local]# 


--启动node_exporter
[root@susource local]# cd node_exporter-0.18.1.linux-amd64/
[root@susource node_exporter-0.18.1.linux-amd64]#  
[root@susource node_exporter-0.18.1.linux-amd64]# ls
LICENSE  node_exporter  NOTICE
[root@susource node_exporter-0.18.1.linux-amd64]# ./node_exporter &
[1] 8458
[root@susource node_exporter-0.18.1.linux-amd64]# INFO[0000] Starting node_exporter (version=0.18.1, branch=HEAD, revision=3db77732e925c08f675d7404a8c46466b2ece83e)  source="node_exporter.go:156"
INFO[0000] Build context (go=go1.12.5, user=root@b50852a1acba, date=20190604-16:41:18)  source="node_exporter.go:157"
INFO[0000] Enabled collectors:                           source="node_exporter.go:97"
INFO[0000]  - arp                                        source="node_exporter.go:104"
INFO[0000]  - bcache                                     source="node_exporter.go:104"
INFO[0000]  - bonding                                    source="node_exporter.go:104"
INFO[0000]  - conntrack                                  source="node_exporter.go:104"
INFO[0000]  - cpu                                        source="node_exporter.go:104"
INFO[0000]  - cpufreq                                    source="node_exporter.go:104"
INFO[0000]  - diskstats                                  source="node_exporter.go:104"
INFO[0000]  - edac                                       source="node_exporter.go:104"
INFO[0000]  - entropy                                    source="node_exporter.go:104"
INFO[0000]  - filefd                                     source="node_exporter.go:104"
INFO[0000]  - filesystem                                 source="node_exporter.go:104"
INFO[0000]  - hwmon                                      source="node_exporter.go:104"
INFO[0000]  - infiniband                                 source="node_exporter.go:104"
INFO[0000]  - ipvs                                       source="node_exporter.go:104"
INFO[0000]  - loadavg                                    source="node_exporter.go:104"
INFO[0000]  - mdadm                                      source="node_exporter.go:104"
INFO[0000]  - meminfo                                    source="node_exporter.go:104"
INFO[0000]  - netclass                                   source="node_exporter.go:104"
INFO[0000]  - netdev                                     source="node_exporter.go:104"
INFO[0000]  - netstat                                    source="node_exporter.go:104"
INFO[0000]  - nfs                                        source="node_exporter.go:104"
INFO[0000]  - nfsd                                       source="node_exporter.go:104"
INFO[0000]  - pressure                                   source="node_exporter.go:104"
INFO[0000]  - sockstat                                   source="node_exporter.go:104"
INFO[0000]  - stat                                       source="node_exporter.go:104"
INFO[0000]  - textfile                                   source="node_exporter.go:104"
INFO[0000]  - time                                       source="node_exporter.go:104"
INFO[0000]  - timex                                      source="node_exporter.go:104"
INFO[0000]  - uname                                      source="node_exporter.go:104"
INFO[0000]  - vmstat                                     source="node_exporter.go:104"
INFO[0000]  - xfs                                        source="node_exporter.go:104"
INFO[0000]  - zfs                                        source="node_exporter.go:104"
INFO[0000] Listening on :9100                            source="node_exporter.go:170"




[root@susource node_exporter-0.18.1.linux-amd64]#


--配置node_exporter的启停服务
cd etc/systemd/system
vi node_exporter.service
[Unit]
Description=Prometheus node_exporter
After=network.target
[Service]
Type=simple
ExecStart=/usr/local/node_exporter-0.18.1.linux-amd64
[Install]
WantedBy=multi-user.target

##在prometheus的配置文件prometheus.yml添加监控节点连接信息

--编辑prometheus.yml配置文件
cd usr/local/prometheus
vi prometheus.yml
# my global config
global:
  scrape_interval: 15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
  evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.
  # scrape_timeout is set to the global default (10s).


# Alertmanager configuration
alerting:
  alertmanagers:
    - static_configs:
        - targets:
          # - alertmanager:9093


# Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
rule_files:
  # - "first_rules.yml"
  # - "second_rules.yml"


# A scrape configuration containing exactly one endpoint to scrape:
# Here it's Prometheus itself.
scrape_configs:
  # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  - job_name: "prometheus"


    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.


    static_configs:
      - targets: ["localhost:9090"]


  - job_name: "176_mysql8_host"
    static_configs:
      - targets: ["192.168.163.176:9100"]




  - job_name: "2_oracle11.2.0.4"
    static_configs:
      - targets: ["192.168.163.2:9100"]


  - job_name: "176_mysql8_database"
    static_configs:
      - targets: ["192.168.163.176:9104"]


  - job_name: "2_oracle_mysource"
    static_configs:
      - targets: ["192.168.163.2:9161"]
        labels:
          instance: '192.168.163.2'
          tags: 'oracle_mysource'




  - job_name: "176_postgresdb"
    static_configs:
      - targets: ["192.168.163.176:9187"]
        labels:
          instance: '192.168.163.176'
          tags: 'my_postgresql'
[root@susource prometheus]#

如上的job_name: "176_mysql8_host"与job_name: "2_oracle11.2.0.4"就是监控192.168.163.176/2这两台主机的连接信息。

node_exporter采集到的监控信息，通过固有的网络端口暴露给prometheus，让其捕获并存储，后期作为grafana的数据源，通过grafana展示出来。

##prometheus捕获的targets列表信息与状态

查看对应target的metrics信息

这部分就是采集主机资源的metrics内容。

##使用grafana

浏览器打开http://192.168.163.2:3000 （IP地址为服务器IP）默认密码admin/admin，进入后修改密码.

##连接prometheus到grafana

1、点击配置，添加数据源

2、添加prometheus数据源

如图所示的my_prometheus是我已经添加的数据源。

3、填写数据源的配置信息

如上，可以自行命名数据源、选择数据源的类型（有prometheus/postgresql/mysql等多种类型）、填写数据源的URL（prometheus部署的服务器的IP和端口，默认端口为9090）、选择http method为“GET”,然后保存。

##grafana导入看盘的图表node-exporter.json

1、点解看板管理

2、点击导入

3、选择并上传对应的看板json文件

4、给选好的看板命名并选择数据源

入以上，可以自行命名，选择上一步配置好的数据源（databases_overviews_prome）,填写完好，就点击导入就可以，然后就跳转到监控的展示页面，如下图所示。

以上已经完成了prometheus+grafana监控主机操作系统资源的安装与配置。以下就是再讲述一下prometheus+grafana监控MySQL、Oracle和PostgreSQL的过程。同样适用以上prometheus作为服务与数据源，添加这三种数据库的监控配置，只需在被监控的机器上部署exporter,并在grafana前端页面上传并导入对应的看板json文件就可以。

一般exporter部署在被监控的数据库服务器上，如果异机部署exporter，则需要在部署的机器上安装一个数据库客户端。

##部署mysql_exporter监控MySQL数据库

--上传并解压mysql_exporter
[root@mysqltest02 local]# tar -zxvf mysqld_exporter-0.11.0.linux-amd64.tar.gz
mysqld_exporter-0.11.0.linux-amd64/
mysqld_exporter-0.11.0.linux-amd64/NOTICE
mysqld_exporter-0.11.0.linux-amd64/LICENSE
mysqld_exporter-0.11.0.linux-amd64/mysqld_exporter
[root@mysqltest02 local]#


--编辑exporter连接MySQL数据库的参数文件
vi my.cnf
[client]
user=mytest
password=manager123
host=192.168.163.176
port=3306


--启动
/usr/local/mysqld_exporter-0.11.0.linux-amd64/mysqld_exporter --config.file=/usr/local/mysqld_exporter-0.11.0.linux-amd64/my.cnf


--给mysql_exporter配置启停服务
vi /etc/systemd/system/mysqld_exporter.service
[Unit]
Description=Prometheus mysqld_exporter
After=network.target
[Service]
Type=simple
ExecStart=/usr/local/mysqld_exporter-0.11.0.linux-amd64/mysqld_exporter --config.file=/usr/local/mysqld_exporter-0.11.0.linux-amd64/my.cnf 
[Install]
WantedBy=multi-user.target






--在prometheus.yml添加mysql_exporter连接信息
cat >> /usr/local/prometheus/prometheus.yml
 - job_name: "176_mysql8_database"
    static_configs:
      - targets: ["192.168.163.176:9104"]

每修改完 prometheus.yml文件，保存并重启prometheus服务。

systemctl restart prometheus.service

接着就是需要在grafana上添加监控MySQL数据库的看板json文件，添加过上参考以上添加监控主机看板的json文件一样。

##部署oracledb_exporter监控Oracle数据库

--上传并解压oracledb_exporter安装包
[root@susource local]# tar -zxvf oracledb_exporter.0.2.3.linux-amd64.tar.gz 
oracledb_exporter.0.2.3.linux-amd64/
oracledb_exporter.0.2.3.linux-amd64/oracledb_exporter
oracledb_exporter.0.2.3.linux-amd64/default-metrics.toml
#因为使用的是oracledb_exporter.0.2.3需要的是Oracle 18.0版本的客户端，本应该需要安装rpm -ivh oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm
rpm -ivh oracle-instantclient18.5-sqlplus-18.5.0.0.0-3.x86_64.rpm，由于我的Oracle数据库是11.2.0.4版本的，18.0版本的客户端可以连接11.2版本的数据库，可以不需要安装客户端。


--修改目录名
[root@susource local]# mv oracledb_exporter.0.2.3.linux-amd64 oracledb_exporter


--修改oracledb_exporter目录属性
chown -R oracle:oinstall /usr/local/oracledb_exporter
chmod -R 775 usr/local/oracledb_exporter


--如果监听没有开启，需要开启监听
lsnrctl start




--向tnsnames.ora文件添加连接串
cat>>/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
MYSOURCE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = susource)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mysource)
    )
  )


--验证连接串的连通性
$ tnsping mysource


--向oracle用户下的~/.bash_profile文件添加以下环境变量
cat>> ~/.bash_profile
export DATA_SOURCE_NAME=system/oracle@mysource
export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib


--保存并生效
source ~/.bash_profile


--使用oracle用户启动oracledb_exporter
$ /usr/local/oracledb_exporter/oracledb_exporter &
#此时会报错：./oracledb_exporter: error while loading shared libraries: libclntsh.so.18.1: cannot open shared object file: No such file or directory
由于我的数据库版本是11.2.0.4的，$ORACLE_HOME/lib下面没有libclntsh.so.18.1文件，只有libclntsh.so.11.1文件。


--解决以上报错，创建libclntsh.so.18.1文件软连接指向libclntsh.so.11.1文件
[oracle@susource lib]$ ls -lrt libclntsh.so.11.1
-rwxr-xr-x. 1 oracle oinstall 53796056 Oct  9  2018 libclntsh.so.11.1
[oracle@susource lib]$  
[oracle@susource lib]$ ln -s libclntsh.so.11.1 libclntsh.so.18.1
[oracle@susource lib]$ ls -lrt libclntsh.so.18.1


lrwxrwxrwx  1 oracle oinstall        17 Dec  8 20:02 libclntsh.so.18.1 -> libclntsh.so.11.1




--再次使用oracle用户启动oracledb_exporter
$ /usr/local/oracledb_exporter/oracledb_exporter &
[oracle@susource oracledb_exporter]$ ./oracledb_exporter &
[1] 36516
[oracle@susource oracledb_exporter]$ INFO[0000] Starting oracledb_exporter 0.2.3              source=main.go:337
INFO[0000] Listening on :9161                            source=main.go:359


[oracle@susource oracledb_exporter]$ 
#此时，错误已经解决，成功启动oracledb_exporter。


--在prometheus.yml添加oracledb_exporter连接信息
cat >> /usr/local/prometheus/prometheus.yml
  - job_name: "2_oracle_mysource"
    static_configs:
      - targets: ["192.168.163.2:9161"]
        labels:
          instance: '192.168.163.2'
          tags: 'oracle_mysource'

每修改完 prometheus.yml文件，保存并重启prometheus服务。

systemctl restart prometheus.service

接着就是需要在grafana上添加监控Oracle数据库的看板json文件，添加过上参考以上添加监控主机看板的json文件一样。

##部署postgres_exporter监控PostgreSQL数据库

--上传并解压postgres_exporter安装包
[root@mysqltest02 local]# tar -zxvf postgres_exporter-0.11.1.linux-amd64.tar.gz 
postgres_exporter-0.11.1.linux-amd64/
postgres_exporter-0.11.1.linux-amd64/LICENSE
postgres_exporter-0.11.1.linux-amd64/NOTICE
postgres_exporter-0.11.1.linux-amd64/postgres_exporter


--修改目录名
[root@mysqltest02 local]# 
[root@mysqltest02 local]# mv postgres_exporter-0.11.1.linux-amd64 postgres_exporter


--修改postgres_exporter目录属性
chown -R postgres:postgres user/local/postgres_exporter
chmod -R 775 user/local/postgres_exporter


--向postgres用户的~/.bash_profile文件添加环境变量
cat>> ~/.bash_profile
export DATA_SOURCE_NAME=postgresql://postgres_exporter:mypassword@192.168.163.176:5433/postgres?sslmode=disable


--保存并生效
source ~/.bash_profile


--创建监控用户
CREATE user postgres_exporter WITH LOGIN PASSWORD 'mypassword';     


--向postgresql.conf参数文件添加参数
cat >>postgresql.conf
# Add settings for extensions here


shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all   




--编译添加pg_stat_statements插件  
[root@mysqltest02 ~]# cd home/postgres/postgresql-16.0/contrib
[root@mysqltest02 contrib]# make & make install
[1] 4496
make -C ../src/backend generated-headers
make -C ../src/backend generated-headers
make[1]: Entering directory `/home/postgres/postgresql-16.0/src/backend'
make -C catalog distprep generated-header-symlinks
make[1]: Entering directory `/home/postgres/postgresql-16.0/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/postgresql-16.0/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-16.0/src/backend/catalog'
make[2]: Entering directory `/home/postgres/postgresql-16.0/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-16.0/src/backend/catalog'
make -C nodes distprep generated-header-symlinks
make -C nodes distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/postgresql-16.0/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-16.0/src/backend/nodes'
make[2]: Entering directory `/home/postgres/postgresql-16.0/src/backend/nodes'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-16.0/src/backend/nodes'


... ...
make[1]: Leaving directory `/home/postgres/postgresql-16.0/contrib/unaccent'
make -C vacuumlo all
make[1]: Entering directory `/home/postgres/postgresql-16.0/contrib/vacuumlo'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I../../src/interfaces/libpq -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o vacuumlo.o vacuumlo.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2  vacuumlo.o -L../../src/common -lpgcommon -L../../src/port -lpgport -L../../src/interfaces/libpq -lpq -L../../src/port -L../../src/common   -Wl,--as-needed -Wl,-rpath,'/pgsql/postgresql/lib',--enable-new-dtags   -lpgcommon -lpgport -lz -lreadline -lpthread -lrt -ldl -lm  -o vacuumlo
make[1]: Leaving directory `/home/postgres/postgresql-16.0/contrib/vacuumlo'


[1]+  Done                    make


--重启postgresql数据库
/pgsql/postgresql/bin/pg_ctl -D pgbase/pgdata -l logfile restart




--进入postgresql数据库创建扩展
[postgres@mysqltest02 ~]$ psql -p5433
psql (16.0)
Type "help" for help.


postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# SELECT * FROM pg_extension;
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 13674 | plpgsql            |       10 |           11 | f              | 1.0        |           | 
 16518 | pg_stat_statements |       10 |         2200 | t              | 1.10       |           | 
(2 rows)
#扩展已经创建。




--pg 10.0以上版本，执行一下三段SQL


CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$
BEGIN
  IF NOT EXISTS (
          SELECT                       -- SELECT list can stay empty for this
          FROM   pg_catalog.pg_user
          WHERE  usename = 'postgres_exporter') THEN
    CREATE USER postgres_exporter;
  END IF;
END;
$$ language plpgsql;




SELECT __tmp_create_user();
DROP FUNCTION __tmp_create_user();




ALTER USER postgres_exporter WITH PASSWORD 'mypassword';
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;


--创建queries.yaml文件
#文件内容见以上材料包的queries.yaml.txt文件
cd /usr/local/postgres_exporter; cat >>queries.yaml




--启动postgres_exporter
cd /usr/local/postgres_exporter
./postgres_exporter --extend.query-path=queries.yaml &


--在prometheus.yml添加postgres_exporter连接信息
cat >> /usr/local/prometheus/prometheus.yml
   - job_name: "176_postgresdb"
    static_configs:
      - targets: ["192.168.163.176:9187"]
        labels:
          instance: '192.168.163.176'
          tags: 'my_postgresql'

每修改完 prometheus.yml文件，保存并重启prometheus服务。

systemctl restart prometheus.service

接着就是需要在grafana上添加监控PostgreSQL数据库的看板json文件，添加过上参考以上添加监控主机看板的json文件一样。

以上是这次prometheus+grafana部署和配置的全过程。

如果感觉以上监控看板的模板内容不够丰富，还可以自己进行修改各看板对应的json文件内容，或者往各个exporter添加相应的采集信息内容。

（文毕）

文章转载自skylines，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Promethues+Grafana监控host+postgresql+mysql+oracle实践（最全最详细版）

评论