Grafana+Prometheus系统监控MySQL

Grafana+Prometheus 不单单是可以监控MySQL，还可以监控服务器，MongoDB等等

首先看架构图

NO.	组件	作用
1	mysql_exporter	MySQL监控数据采集组件
2	node_exporter	服务器机器监控数据采集组件
3	Prometheus	将mysql_exporter和node_exporter收集过来的数监控数据进行汇总和存储
4	Grafana	将Prometheus采集的数据图形化展示
5	Grafana-Dashboards	Grafana需要用到该项目中的JSON文件

各组件下载地址：
点击下载​🔗​：Prometheus 和 node_exporter
点击下载​🔗​：mysqld_exporter
点击下载​🔗​：Grafana
点击下载​🔗​：Grafana-dashboards

2、安装 && 配置各个组件

(1)、部署&&启动 node_exporter

tar xf node_exporter-0.15.2.linux-amd64.tar
mv node_exporter-0.15.2.linux-amd64 node_exporter
cd node_exporter
nohup ./node_exporter &

(2)、部署&&启动 mysqld_exporter

tar xf mysqld_exporter-0.10.0.linux-amd64.tar
mv mysqld_exporter-0.10.0.linux-mv amd64 mysqld_exporter
export DATA_SOURCE_NAME='MySQL用户名:MySQL密码@(MySQL服务器IP:MySQL端口)/'

echo "export DATA_SOURCE_NAME='MySQL用户名:MySQL密码@(MySQL服务器IP:MySQL端口)/'" >> /etc/profile
cd mysqld_exporter
nohup ./mysqld_exporter --collect.info_schema.processlist --collect.info_schema.innodb_tablespaces --collect.info_schema.innodb_metrics --collect.perf_schema.tableiowaits --collect.perf_schema.indexiowaits --collect.perf_schema.tablelocks --collect.engine_innodb_status --collect.perf_schema.file_events  --collect.binlog_size --collect.info_schema.clientstats --collect.perf_schema.eventswaits &

(3) 部署&&启动 prometheus

mkdir /data
tar xvf prometheus-2.1.0.linux-amd64.tar -C /data
cd /data
mv prometheus-2.1.0.linux-amd64 prometheus
cd prometheus
vim prometheus.yml

修改配置文件 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.
    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.
  - job_name : 'prometheus'
    static_configs:
      - targets: ['localhost:9090']
  - job_name: 'MySQL'
    static_configs:
      - targets: ['localhost:9104']
  - job_name: 'metrics'
    static_configs:
      - targets: ['localhost:9100']

启动 prometheus

/data/prometheus/prometheus --storage.tsdb.retention=30d &

这里的30d表示监控数据保留30天

浏览器访问效果如下：

(4) 部署&&启动 Grafana

tar xf grafana-4.6.3.linux-x64.tar.gz -C /data/prometheus
cd /data/prometheus
mv grafana-4.6.3 grafana
cd /data/prometheus/grafana
./bin/grafana-server &

此时可以通过浏览器访问3000端口

tar xvf grafana-dashboards-1.6.1.tar.gz
cd grafana-dashboards-1.6.1/dashboards

grafana-dashboards-1.6.1/dashboards 目录下有很多JSON文件

-rw-rw-r--. 1 root root  19674 Jan 24  2018 Advanced_Data_Exploration.json
-rw-rw-r--. 1 root root  76737 Jan 24  2018 Amazon_RDS___Aurora_MySQL__Metrics.json
-rw-rw-r--. 1 root root  54685 Jan 24  2018 CPU_Utilization_Details_Cores.json
-rw-rw-r--. 1 root root  29537 Jan 24  2018 Cross_Server_Graphs.json
-rw-rw-r--. 1 root root  38710 Jan 24  2018 Disk_Performance.json
-rw-rw-r--. 1 root root  21171 Jan 24  2018 Disk_Space.json
-rw-rw-r--. 1 root root  70279 Jan 24  2018 MongoDB_Cluster_Summary.json
-rw-rw-r--. 1 root root  54214 Jan 24  2018 MongoDB_InMemory.json
-rw-rw-r--. 1 root root  71683 Jan 24  2018 MongoDB_MMAPv1.json
-rw-rw-r--. 1 root root  51277 Jan 24  2018 MongoDB_Overview.json
-rw-rw-r--. 1 root root  72417 Jan 24  2018 MongoDB_ReplSet.json
-rw-rw-r--. 1 root root 118089 Jan 24  2018 MongoDB_RocksDB.json
-rw-rw-r--. 1 root root  75611 Jan 24  2018 MongoDB_WiredTiger.json
-rw-rw-r--. 1 root root 103381 Jan 24  2018 MySQL_InnoDB_Metrics_Advanced.json
-rw-rw-r--. 1 root root  85258 Jan 24  2018 MySQL_InnoDB_Metrics.json
-rw-rw-r--. 1 root root  37331 Jan 24  2018 MySQL_MyISAM_Aria_Metrics.json
-rw-rw-r--. 1 root root  93817 Jan 24  2018 MySQL_MyRocks_Metrics.json
-rw-rw-r--. 1 root root 176359 Jan 24  2018 MySQL_Overview.json
-rw-rw-r--. 1 root root  27872 Jan 24  2018 MySQL_Performance_Schema.json
-rw-rw-r--. 1 root root  34803 Jan 24  2018 MySQL_Query_Response_Time.json
-rw-rw-r--. 1 root root  42843 Jan 24  2018 MySQL_Replication.json
-rw-rw-r--. 1 root root  26985 Jan 24  2018 MySQL_Table_Statistics.json
-rw-rw-r--. 1 root root  98619 Jan 24  2018 MySQL_TokuDB_Metrics.json
-rw-rw-r--. 1 root root  27114 Jan 24  2018 MySQL_User_Statistics.json
-rw-rw-r--. 1 root root  50691 Jan 24  2018 Prometheus_Exporters_Overview.json
-rw-rw-r--. 1 root root 110080 Jan 24  2018 Prometheus_Exporter_Status.json
-rw-rw-r--. 1 root root 180715 Jan 24  2018 Prometheus.json
-rw-rw-r--. 1 root root  36308 Jan 24  2018 ProxySQL_Overview.json
-rw-rw-r--. 1 root root  42398 Jan 24  2018 PXC_Galera_Cluster_Overview.json
-rw-rw-r--. 1 root root  71221 Jan 24  2018 PXC_Galera_Graphs.json
-rw-rw-r--. 1 root root  40792 Jan 24  2018 Summary_Dashboard.json
-rw-rw-r--. 1 root root  98041 Jan 24  2018 System_Overview.json
-rw-rw-r--. 1 root root  34173 Jan 24  2018 Trends_Dashboard.json

但这里还需要上传JSON

查看上传结果

点击列表中的连接，可以查看各个指标