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

jQuery realizes the full function of shopping cart

This article shares the specific code of jQuery t...

Binary Search Tree Algorithm Tutorial for JavaScript Beginners

Table of contents What is a Binary Search Tree (B...

Simple example of HTML checkbox and radio style beautification

Simple example of HTML checkbox and radio style b...

jQuery manipulates cookies

Copy code The code is as follows: jQuery.cookie =...

What you need to know about filters in Vue

Table of contents Preface What is a filter How to...

How to choose the right index in MySQL

Let’s take a look at a chestnut first EXPLAIN sel...

Code for implementing simple arrow icon using div+CSS in HTML

In web design, we often use arrows as decoration ...

Nginx installation error solution

1. Unzip nginx-1.8.1.tar.gz 2. Unzip fastdfs-ngin...

CSS code to achieve 10 modern layouts

Preface I watched web.dev's 2020 three-day li...

Example of MySQL auto-increment ID exhaustion

Display Definition ID When the auto-increment ID ...

javascript countdown prompt box

This article example shares the specific code of ...

WeChat applet implements a simple calculator

A simple calculator written in WeChat applet for ...

Detailed summary of web form submission methods

Let's first look at several ways to submit a ...

Tutorial on installing Android Studio on Ubuntu 19 and below

Based on past experience, taking notes after comp...

Sample code for modifying the input prompt text style in html

On many websites, we have seen the input box disp...