Mysql学习利用Prometheus与Grafana对Mysql服务器的性能监控详解

MYSQL应用
$ wget https://github.com/prometheus/prometheus/releases/download/v1.5.2/prometheus-1.5.2.linux-amd64.tar.gz
$ tar zxvf prometheus-1.5.2.linux-amd64.tar.gz
$ mv prometheus-1.5.2.linux-amd64 /opt/prometheus

MYSQL应用
vim /opt/prometheus/prometheus.yml

MYSQL应用
# 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).
 # Attach these labels to any time series or alerts when communicating with
 # external systems (federation, remote storage, Alertmanager).
 external_labels:
 monitor: 'codelab-monitor'
# Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
rule_files:
 # - "first.rules"
 # - "second.rules"
# 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: linux
 static_configs:
 - targets: ['172.30.251.214:9100']
 labels:
 instance: db1
 - job_name: mysql
 static_configs:
 - targets: ['172.30.251.214:9104']
 labels:
 instance: db1

MYSQL应用
[loya@centos6 prometheus]$ ./prometheus
INFO[0000] Starting prometheus (version=1.5.2, branch=master, revision=bd1182d29f462c39544f94cc822830e1c64cf55b) source=main.go:75
INFO[0000] Build context (go=go1.7.5, user=root@a8af9200f95d, date=20170210-14:41:22) source=main.go:76
INFO[0000] Loading configuration file prometheus.yml source=main.go:248
INFO[0000] Loading series map and head chunks... source=storage.go:373
INFO[0000] 0 series loaded. source=storage.go:378
INFO[0000] Starting target manager... source=targetmanager.go:61
INFO[0000] Listening on :9090 source=web.go:259

MYSQL应用
$ mkdir /opt/prometheus/prometheus_exporters
$ cd /opt/prometheus/prometheus_exporters
$ wget https://github.com/prometheus/node_exporter/releases/download/v0.14.0-rc.1/node_exporter-0.14.0-rc.1.linux-amd64.tar.gz
$ wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.9.0/mysqld_exporter-0.9.0.linux-amd64.tar.gz
$ tar zxvf node_exporter-0.14.0-rc.1.linux-amd64.tar.gz
$ tar zxvf mysqld_exporter-0.9.0.linux-amd64.tar.gz
# 将各自的可执行文件移动到/opt/prometheus/prometheus_exporters
$ ll /opt/prometheus/prometheus_exporters
total 24464
-rwxr-xr-x 1 root root 12182376 Feb 23 19:01 mysqld_exporter
-rwxr-xr-x 1 root root 12862209 Feb 23 19:01 node_exporter

MYSQL应用
$ cd /opt/prometheus/prometheus_exporters
$ ./node_exporter 
INFO[0000] Starting node_exporter (version=0.14.0-rc.1, branch=master, revision=5a07f4173d97fa0dd307db5bd3c2e6da26a4b16e) source="node_exporter.go:136"
INFO[0000] Build context (go=go1.7.4, user=root@ed143c8f2fcd, date=20170116-16:00:03) source="node_exporter.go:137"
INFO[0000] No directory specified, see --collector.textfile.directory source="textfile.go:57"
INFO[0000] Enabled collectors: source="node_exporter.go:156"
INFO[0000] - entropy source="node_exporter.go:158"
INFO[0000] - loadavg source="node_exporter.go:158"
INFO[0000] - stat source="node_exporter.go:158"
INFO[0000] - diskstats source="node_exporter.go:158"
INFO[0000] - textfile source="node_exporter.go:158"
INFO[0000] - vmstat source="node_exporter.go:158"
INFO[0000] - meminfo source="node_exporter.go:158"
INFO[0000] - filefd source="node_exporter.go:158"
INFO[0000] - filesystem source="node_exporter.go:158"
INFO[0000] - mdadm source="node_exporter.go:158"
INFO[0000] - netdev source="node_exporter.go:158"
INFO[0000] - sockstat source="node_exporter.go:158"
INFO[0000] - time source="node_exporter.go:158"
INFO[0000] - zfs source="node_exporter.go:158"
INFO[0000] - edac source="node_exporter.go:158"
INFO[0000] - hwmon source="node_exporter.go:158"
INFO[0000] - netstat source="node_exporter.go:158"
INFO[0000] - uname source="node_exporter.go:158"
INFO[0000] - conntrack source="node_exporter.go:158"
INFO[0000] Listening on :9100 source="node_exporter.go:176"

MYSQL应用
mysql> GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'prom'@'localhost' identified by 'abc123';
mysql> GRANT SELECT ON performance_schema.* TO 'prom'@'localhost';

MYSQL应用
$ cd /opt/prometheus/prometheus_exporters
$
$ cat << EOF > .my.cnf
[client]
user=prom
password=abc123
EOF
$
$ ./mysqld_exporter -config.my-cnf=".my.cnf"
INFO[0000] Starting mysqld_exporter (version=0.9.0, branch=master, revision=8400af20ccdbf6b5e0faa2c925c56c48cd78d70b) source=mysqld_exporter.go:432
INFO[0000] Build context (go=go1.6.3, user=root@2c131c66ca20, date=20160926-18:28:09) source=mysqld_exporter.go:433
INFO[0000] Listening on :9104 source=mysqld_exporter.go:451

MYSQL应用
[dashboards.json]
enabled = true
path = /var/lib/grafana/dashboards

MYSQL应用
$ git clone https://github.com/percona/grafana-dashboards.git
$ cp -r grafana-dashboards/dashboards /var/lib/grafana

MYSQL应用
$ /etc/init.d/grafana-server start