Using Prometheus and Grafana to monitor the performance of Mysql servers, prometheusgrafana

Using Prometheus and Grafana to monitor the performance of Mysql servers, prometheusgrafana

Overview

Prometheus is an open-source service monitoring system that collects data from remote machines over HTTP and stores the data in a local time series database. It provides a simple webpage interface, a powerful Query Language, and an HTTP interface. Prometheus collects monitoring data by using the exporter installed on a remote machine. The following two exporter are used:

1. Node_exporter-used for Machine System Data
2. Mysqld_exporter-used for Mysql server data
   

Grafana is an open-source data visualization platform with rich functions. It is usually used for time series data visualization. It has built-in support for the following data sources:

You can also use plug-ins to expand supported data sources.

Architecture Diagram

The following figure shows the architecture of this deployment.

Install and run Prometheus

1. Install Prometheus on Monitor

Install to/opt/prometheus

$ 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

2. Edit the configuration file prometheus. yml under the installation directory.

vim /opt/prometheus/prometheus.yml

# My global configglobal: scrape_interval: 15 s # Set the scrape interval to every 15 seconds. default is every 1 minute. evaluation_interval: 15 s # Evaluate rules every 15 seconds. the default is every 1 minute. # scrape_timeout is set to the global default (10 s ). # 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 ults to '/metrics' # scheme defaults to 'http '. static_configs:-targets: ['localhost: 8080'] # The following is the added part-job_name: linux static_configs:-targets: ['123. 30.251.214: 9100 '] labels: instance: db1-job_name: mysql static_configs:-targets: ['2017. 30.251.214: 9104 '] labels: instance: db1

172.30.251.214 is the IP address of MySQL, and the port is the listening port of the corresponding exporter.

3. Start Prometheus

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

Prometheus has a built-in web interface, which can be accessed through http: // monitor_host: 9090:

On the Status-> Targets page, we can see the two Targets we configured, whose State is DOWN.

4. Install and run exporter on the monitored machine.

Download exporters and decompress it:

$ Mkdir/opt/prometheus/prometheus_exporters $ cd/opt/prometheus/prometheus_exporters $ wget javaswget javastar zxvf node_exporter-0.14.0-rc.1.linux-amd64.tar.gz $ tar zxvf mysqld_exporter-0.9.0.linux-amd64.tar.gz # Move the respective executable files to/opt/prometheus /prometheus_exporters $ ll/opt/prometheus/prometheus_exporterstotal 24464-rwxr-xr-x 1 root 12182376 Feb 23 mysqld_exporter-rwxr-xr-x 1 root 12862209 Feb 23 node_exporter

Run node_exporter

$ 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"

Mysqld_exporter needs to connect to MySQL, so MySQL permissions are required. We create users for them and grant them the required permissions:

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

Create the. my. cnf file and run mysqld_exporter:

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

Return to the Prometheus web interface Status-> Targets, and you can see that the Status of the two Targets has changed to UP:

Install and run Grafana

Installation:

The official installation documents of Grafana are clear and will not be detailed here.

Configuration:

Edit the configuration file/etc/grafana. ini and modify the values of the two parameters in the dashboards. json section:

[dashboards.json]enabled = truepath = /var/lib/grafana/dashboards

Import Prometheus Dashboard:

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

Start:

$ /etc/init.d/grafana-server start

Access the Grafana Web interface through http: // monitor_host: 3000 (default account/password is admin/admin)

After logging on, add the Data source on the Data Sources page:

Then you can select a different dashboard (upper left corner) and a time period (upper right corner) to present the chart:

System Overview:

MySQL Overview:

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.