Prometheus monitors MySQL using grafana display

Prometheus monitors MySQL through exporter and displays it with grafana charts

Overview:

Prometheus is an open source monitoring and alarm system developed by SoundCloud and comes with a time series database based on the Go language. Prometheus obtains the metrics on the specified target (target) in a periodic pull manner according to the configured task (job).

The Prometheus ecosystem includes multiple components:

• Prometheus Server: Completes data collection, service discovery, and data storage according to configuration.
• Push Gateway: A plug-in provided to deal with some push scenarios. The monitoring data is first pushed to the Push Gateway, and then collected and pulled by the Prometheus Server. Used for jobs that are short-lived and may disappear before Prometheus pulls them (if the data on the Push Gateway does not change during the Prometheus Server collection interval, the Prometheus Server will collect the same data twice, with only different timestamps)
• Exporters (probes): is a general term for a type of data collection components of Prometheus. It is responsible for collecting data from the target and converting it into a format supported by Prometheus. Different from traditional data collection components, it does not send data to the central server, but waits for the central server to actively capture it.
• Alertmanager: Prometheus server is mainly responsible for analyzing data according to PromQL-based alarm rules. If the rules defined by PromQL are met, an alarm will be generated and sent to Alertmanager. Alertmanager processes the alarm information according to the configuration and sends it. Common receiving methods include: email, webhook, etc. Alertmanager has three ways to process alarm information: grouping, suppression, and silencing.

Next, let's start the demonstration

1. Test machine

prometheus-server 192.168.56.140
MySQL host01 192.168.56.103
MySQL host02 192.168.56.104

2. Configure mysql host01

MySQL version used:

8.0.25 MySQL Community Server

3. Create an exporter account

mysqld_exporter obtains data by querying mysql's status table and status commands. Therefore, you need to create the corresponding account in MySQL first

create user 'exporter'@'%' identified by 'Xiaopang*803';
GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'exporter'@'%';
GRANT SELECT ON performance_schema.* TO 'exporter'@'%';
flush privileges;

4. Download and install mysqld_exporter

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.13.0/mysqld_exporter-0.13.0.linux-amd64.tar.gz
tar xvzf mysqld_exporter-0.13.0.linux-amd64.tar.gz -C /usr/local/.
cd /usr/local && ln -s mysqld_exporter-0.13.0.linux-amd64/mysqld_exporter

4.1 Edit file and enter password

Edit the following file and enter the exporter user name and password (the same as the account and password created in MySQL above)

[root@host01 mysqld_exporter]# vi .my.cnf
[client]
user=exporter
password=Xiaopang*803

4.2 Add startup service file

[root@host01 ~]# vi /etc/systemd/system/mysqld_exporter.service

[Unit]
Description=mysqld_exporter
After=network.target

[Service]
Type=simple
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysqld_exporter/.my.cnf
Restart=on-failure

[Install]
WantedBy=multi-user.target

4.3 Starting mysqld_exporter

service mysqld_exporter start

4.4 Test Verification

mysqld_exporter uses port 9104 by default. We can enter the following address in the browser. Check whether there is data output.

Enter http://192.168.56.103:9104/metrics

The output information is similar to the following:

# HELP go_gc_duration_seconds A summary of the pause duration of garbage collection cycles.
# TYPE go_gc_duration_seconds summary
go_gc_duration_seconds{quantile="0"} 2.5395e-05
go_gc_duration_seconds{quantile="0.25"} 3.5372e-05
go_gc_duration_seconds{quantile="0.5"} 3.9393e-05
go_gc_duration_seconds{quantile="0.75"} 5.5068e-05
go_gc_duration_seconds{quantile="1"} 0.062537624
go_gc_duration_seconds_sum 0.453204071
go_gc_duration_seconds_count 2131
# HELP go_goroutines Number of goroutines that currently exist.
# TYPE go_goroutines gauge

5. Download and install node_exporter

If you only install mysqld_exporter, you cannot monitor OS-related data, so you need to install node_exporter for OS monitoring.

wget https://github.com/prometheus/node_exporter/releases/download/v1.2.2/node_exporter-1.2.2.linux-amd64.tar.gz

tar xvzf node_exporter-1.2.2.linux-amd64.tar.gz -C /usr/local/.
cd /usr/local && ln -s node_exporter-1.2.2.linux-amd64/ node_exporter

5.1 Add startup service file

[root@host01 ~]# vi /etc/systemd/system/node_exporter.service
[Unit]
Description=node_export
Documentation=https://github.com/prometheus/node_exporter
After=network.target

[Service]
Type=simple
User=root
Group=root
ExecStart=/usr/local/node_exporter/node_exporter
Restart=on-failure
[Install]
WantedBy=multi-user.target

5.2 Start node_exporter

service node_exporter start

5.3 Test Verification

By default, node_exporter uses port 9100. We can enter the following address in the browser. Check whether there is data output.

Enter http://192.168.56.103:9100/metrics

The output is similar to the following:

# HELP go_gc_duration_seconds A summary of the pause duration of garbage collection cycles.
# TYPE go_gc_duration_seconds summary
go_gc_duration_seconds{quantile="0"} 2.5934e-05
go_gc_duration_seconds{quantile="0.25"} 4.0072e-05
go_gc_duration_seconds{quantile="0.5"} 4.7616e-05
go_gc_duration_seconds{quantile="0.75"} 6.726e-05
go_gc_duration_seconds{quantile="1"} 0.228887598
go_gc_duration_seconds_sum 0.550266258
go_gc_duration_seconds_count 793
# HELP go_goroutines Number of goroutines that currently exist.
# TYPE go_goroutines gauge

6. Install prometheus+grafana

Use version:

prometheus 2.28

grafana 6.7.6

6.1 Installation

Download the package

wget https://github.com/prometheus/prometheus/releases/download/v2.28.1/prometheus-2.28.1.linux-amd64.tar.gz

6.2 Unzip and add soft links

tar xvzf prometheus-2.28.1.linux-amd64.tar.gz -C /usr/local/.
cd /usr/local/
ln -s prometheus-2.28.1.linux-amd64/prometheus

6.3 Add startup service

[root@prometheus-server prometheus]# vi /etc/systemd/system/prometheus.service
[Unit]
Description=Prometheus Monitoring System
Documentation=Prometheus Monitoring System

[Service]
Type=simple
User=root
Group=root
ExecStart=/usr/local/prometheus/prometheus \
--config.file=/usr/local/prometheus/prometheus.yml \
--storage.tsdb.path="data/" \
--storage.tsdb.retention.time=15d \
--web.max-connections=512 \
--web.listen-address=:9090

6.4 Add MySQL monitoring

vi /usr/local/prometheus/prometheus.yml

scrape_configs:
# The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.

  - job_name: 'mysql'
    static_configs:
      - targets: ['192.168.56.103:9104']
        labels:
          instance: mysql_instance1
  - job_name: 'linux'
    static_configs:
      - targets: ['192.168.56.103:9100']
        labels:
          instance: mysql_instance1

6.5 Start prometheus

service prometheus start

6.7 View prometheus

Prometheus default monitoring port

http://192.168.56.140:9090/

Click status->target. If everything is normal, you can see that the state of mysql/linux is UP as follows

7. Download and install grafana

wget https://dl.grafana.com/oss/release/grafana-6.7.6-1.x86_64.rpm

rpm -ivh grafana-6.7.6-1.x86_64.rpm

7.1 Accessing Grafana

The display function of prometheus is very weak. In order to better display graphics, we need grafana

Enter http://192.168.56.140:3000/

Configure the data source as the HTTP link of proemtheus (note that it is HTTP, not HTTPS)

7.2 Import MySQL monitoring template

The display of grafana data is achieved through templates. There are many shared templates on the grafana website that you can explore on your own.

I downloaded this template from the following link.

https://grafana.com/api/dashboards/9623/revisions/4/download

Version mismatch issue

Due to version mismatch, some items are not displayed normally after completion. The version it uses is grafana5.0, and mine is 6.x.

But this little problem didn't bother me, I modified it myself. It can be displayed normally. The following is the modified JSON file.

josn_jb51.rar

Modification process

Many times, many things are not completely ready for use. We need to make some modifications according to our needs.

It took me about half an hour to figure out how to modify it. Then it took about another two hours to modify and complete the corresponding projects.

There are two main types of problems encountered during the modification process:

1) The names of Grafana 5.x and 6.x components have changed.

"Buffer Pool Size of Total RAM" cannot be displayed properly because the component names of 6.0 and 5.0 are different.

Replace pmm-singlestat-panel -> singlestat to get it done

2) The name of the attribute extracted by the exporter has changed

I am using node_exporter-1.2.2, and the properties extracted about OS do not match the defined property names in the JSON file.

The method is to search for the new attribute name directly in "http://192.168.56.103:9100/metrics" and replace the old attribute name in the JSON file.

For example:

Replace node_memory_MemTotal->node_memory_MemTotal_bytes
Replace node_memory_MemTotal->node_memory_MemTotal_bytes

Import

Because I made some modifications, you can directly enter the JSON content when importing.

Click Load, and then select prometheus as the data source.

7.3 Start the sysbench stress testing tool

The purpose of enabling the sysbench tool is to generate charts with data changes through stress testing (otherwise, if there is no traffic, the data will not move).

Here, I am testing remotely (running sysbench on another machine host02). The purpose is to generate network traffic data.

[root@host02 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --time=9180 --mysql-host=host01 --mysql-port=3306 --mysql-user=dbusr --mysql-password=Xiaopang*803 --mysql-db=db1 --table-size=50000 --tables=15 --threads=15 --report-interval=10 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 15
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 15 tps: 112.68 qps: 2268.92 (r/w/o: 1589.76/452.30/226.85) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 15 tps: 113.91 qps: 2282.81 (r/w/o: 1598.47/456.52/227.81) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 15 tps: 109.80 qps: 2192.95 (r/w/o: 1536.66/436.69/219.59) lat (ms,95%): 240.02 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 15 tps: 112.70 qps: 2265.36 (r/w/o: 1583.17/456.79/225.40) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 15 tps: 101.00 qps: 2013.42 (r/w/o: 1413.32/398.10/202.00) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00

7.4 Check grafana and see the result after completion

Only some of the diagrams are posted here.

This is the end of this article about Prometheus MySQL monitoring using grafana. For more relevant MySQL grafana content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!