Use Grafana+Prometheus to monitor MySQL service performance

Prometheus (also called Prometheus) official website: https://prometheus.io/docs/introduction/overview/

Grafana official website: https://grafana.com/enterprise

feature

The main features of Prometheus are:

• A multidimensional data model with time series data identified by metric names and key/value pairs
• A flexible query language to exploit this dimension
• No reliance on distributed storage; individual server nodes are autonomous
• Time series collection happens via a pull model over HTTP
• Push time series support via intermediate gateway
• Discover targets through service discovery or static configuration
• Multiple graph and dashboard support modes

Components

The Prometheus ecosystem consists of multiple components, many of which are optional:

• The main Prometheus server, used to store time series data
• Client library for instrumenting application code
• Push gateway for supporting short-lived jobs
• Special purpose exporters for services like HAProxy, StatsD, Graphite, etc.
• An alertmanager handles alerts
• Various support tools

Most Prometheus components are written in Go, so they are easy to build and deploy as static binaries.

Today, this article focuses on introducing the method of using Grafana+Prometheus to monitor MySQL performance.

#cmd /usr/local

Today I will talk about how to monitor the performance of the MySQL database in the server

1. Database Operation

1.mysql start

#service mysqld start #Start the database #service mysqld stop #Close the database #service mysqld restart #Restart the database

2. Enter mysql

mysql -u root -p

3. Add a new monitoring mysql account

#use mysql
#GRANT ALL PRIVILEGES ON *.* TO 'account'@'localhost' identified by 'password';<br>#flush privileges; #Refresh

2. Prometheus Construction

Here we will provide you with a clean compressed package of the Prometheus database, just unzip it.

Put the compressed package on the server

#cmd /usr/local

Unzip

#tar -zxvf prometheus-xxxxx

Now modify the configuration file of Prometheus. There is a prometheus.yml file in its installation directory. Add

- job_name: 'linux' static_configs: - targets: ['localhost:9100'] - job_name: 'mysql' static_configs: - targets: ['localhost:9104']

Here everyone should pay attention to one thing, you must strictly follow the format of Prometheus, do not add spaces and indents at will, otherwise errors will occur

The purpose of adding this step is to open two ports for Prometheus for Grafana monitoring

Set file permissions: #chmod 777 prometheus.yml Start from the configuration file

#./prometheus --config.file=prometheus.yml &

Setting Linux system ports

firewall-cmd --list-ports ##List open ports firewall-cmd --add-port=9090/tcp --permanent ##Permanently add port 9090 firewall-cmd --add-port=9100/tcp --permanent ##Permanently add port 9100 firewall-cmd --add-port=9104/tcp --permanent ##Permanently add port 9104 firewall-cmd --list-ports ##List open ports systemctl stop firewalld ##Turn off the firewall systemctl start firewalld ##Turn on the firewall systemctl status firewalld ##Check the firewall status systemctl restart firewalld ##Restart the firewall systemctl disable firewalld ##Turn off the firewall at startup, that is, permanently turn it off

Windows access: http://192.168.xx.xx:9090 statue-targets Check that the port status is up, which means success

3. mysqld_exporter setup

Here we will also provide a compressed package for you

1. Upload mysqld_exporter to local 2. Unzip tar -zxvf mysqld_exporter-xxxxxx 3. Create a .my.cnf configuration file under /usr/local/mysql_exporter and write the created username and password

[client] user=xxxx password=xxxx

4. Start the service

Start in /usr/local/mysql_exporter ./mysqld_exporter --config.my-cnf=my.cnf

5. Install lsof

#yum install lsof

6. Check the usage of port 9104

lsof -i:9104

7.

Close the process#kill -9 process number

8. Restart Prometheus

4. Node_exporter monitors Linux

Here is a compressed package for you

1. Upload to /usr/local/ 2. Unzip tar -zxvf node_exporter-xxxxx 3. In the /usr/local/node_exporter directory, start ./node_exporter & 4. Access http://server ip:9100/metrics through a browser to see the monitoring data

At this time, we can see that all the task items are in the UP state, so our monitoring platform is built. As for how Gafana connects to the Prometheus database, we will not explain it. If you need it, please read the previous articles.

Attached Linux effect diagram

Finally, I attach the compressed package required for this article and the monitoring template of Grafana based on Prometheus database.

Link: https://pan.baidu.com/s/124sJ0cv3zghK02n4W-rSZw Extraction code: w987

Link: https://pan.baidu.com/s/1l3pvorWAv3_VfwhL6G9PHg Extraction code: m7nw

Summarize

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