Detailed explanation of performance monitoring of MySQL server using Prometheus and Grafana

Detailed explanation of performance monitoring of MySQL server using Prometheus and Grafana

Overview

Prometheus is an open source service monitoring system that collects data from remote machines through the HTTP protocol and stores it in a local time series database. It provides a simple web interface, a powerful query language, HTTP interface, and more. Prometheus collects monitoring data through exporters installed on remote machines. The following two exporters are used here:

  1. node_exporter – for machine system data
  2. mysqld_exporter – for MySQL server data

Grafana is an open source, feature-rich data visualization platform that is often used for visualizing time series data. It has built-in support for the following data sources:

The supported data sources can be expanded through plug-ins.

Architecture diagram

The following is the architecture diagram 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 in the installation directory

vim /opt/prometheus/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).
 # 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']
 # The following is the added part - 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

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

3. Start Prometheus

[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

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

On the Status->Targets page, you can see the two Targets we configured, and their Status is DOWN.

4. Next, you need to install and run exporter on the monitored machine

Download exporters and unzip:

$ 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
# Move the respective executables to /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

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 it needs MySQL permissions. We create a user for it and grant 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=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

Go back to the Prometheus web interface Status->Targets, and you can see that the status of the two Targets has become UP:

Install and run Grafana

Install:

The official documentation for Grafana installation is quite clear, so I won’t go into details here.

Configuration:

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

[dashboards.json]
enabled = true
path = /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 up:

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

Access the Grafana web interface via http://monitor_host:3000 (the default username/password is admin/admin)

After logging in, add a data source through the Data Sources page:

You can then render the chart by selecting different dashboards (upper left) and time periods (upper right):

System Overview:

MySQL Overview:

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • Detailed tutorial on grafana installation and usage
  • Use Grafana to display monitoring charts of Docker containers and set email alert rules (illustration)
  • Detailed explanation of the tutorial on monitoring Springboot applications using Prometheus+Grafana
  • Use Grafana+Prometheus to monitor MySQL service performance
  • Detailed explanation of the process of building Prometheus+Grafana based on docker
  • Summary of influx+grafana custom python data collection and some pitfalls
  • Detailed steps for SpringBoot+Prometheus+Grafana to implement application monitoring and alarm
  • How to install grafana and add influxdb monitoring under Linux
  • Analyze the method of prometheus+grafana monitoring nginx
  • Prometheus monitors MySQL using grafana display
  • How to monitor Docker using Grafana on Ubuntu
  • Detailed tutorial on building a JMeter+Grafana+Influxdb monitoring platform with Docker
  • Tutorial on building a JMeter+Grafana+influxdb visual performance monitoring platform in docker environment
  • Deploy grafana+prometheus configuration using docker
  • ELK and Grafana jointly create visual monitoring to analyze nginx logs
  • It doesn’t matter if you forget your Grafana password. 2 ways to reset your Grafana admin password

<<:  Introduction and use of Javascript generator

>>:  How to solve the problem of ping being successful but the port being unavailable in Linux

Recommend

Detailed explanation of common usage of MySQL query conditions

This article uses examples to illustrate the comm...

Native JavaScript to implement random roll call table

This article example shares the specific code of ...

MYSQL updatexml() function error injection analysis

First, understand the updatexml() function UPDATE...

Vue-router example code for dynamically generating navigation menus based on backend permissions

Table of contents Vue.js 1. Register global guard...

File backup solution between servers, how to automatically back up server files to another server?

Many organizations have the need to back up file ...

Brief introduction and usage of Table and div

Web front end 1 Student ID Name gender age 01 Zha...

How to allow remote access to open ports in Linux

1. Modify the firewall configuration file # vi /e...

How to use docker to deploy spring boot and connect to skywalking

Table of contents 1. Overview 1. Introduction to ...

Detailed analysis of the parameter file my.cnf of MySQL in Ubuntu

Preface Based on my understanding of MySQL, I thi...

9 Practical CSS Properties Web Front-end Developers Must Know

1. Rounded Corners Today's web designs are con...

Several reasons for not compressing HTML

The reason is simple: In HTML documents, multiple ...

How to build a drag and drop plugin using vue custom directives

We all know the drag-and-drop feature of HTML5, w...

A summary of detailed insights on how to import CSS

The development history of CSS will not be introd...

Things to note when writing self-closing XHTML tags

The img tag in XHTML is so-called self-closing, w...

Example of implementing grouping and deduplication in MySQL table join query

Table of contents Business Logic Data table struc...