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 description of mysql replace into usage

The replace statement is generally similar to ins...

How to query or obtain images in a private registry

Docker queries or obtains images in a private reg...

Docker: Modifying the DOCKER_OPTS parameter in /etc/default/docker does not take effect

By default, /etc/default/docker configuration wil...

How to change the root password of Mysql5.7.10 on MAC

First, start MySQL in skip-grant-tables mode: mys...

CSS Sticky Footer Several Implementations

What is "Sticky Footer" The so-called &...

How to use commands in Mysql to achieve hierarchical search help detailed explanation

Preface This article mainly introduces the releva...

Sample code for using CSS to write a textured gradient background image

The page length in the project is about 2000px or...

Nginx defines domain name access method

I'm building Nginx recently, but I can't ...

Dockerfile text file usage example analysis

Dockerfile is a text file used to build an image....

The magic of tbody tag speeds up the display of table content

You must have saved other people’s web pages and l...

Solution to Docker disk space cleaning

Some time ago, I encountered the problem that the...

Ubuntu Basic Tutorial: apt-get Command

Preface The apt-get command is a package manageme...

About MySQL innodb_autoinc_lock_mode

The innodb_autoinc_lock_mode parameter controls t...

MySQL concurrency control principle knowledge points

Mysql is a mainstream open source relational data...

A brief analysis of Linux resolv.conf

1. Introduction resolv.conf is the configuration ...