Shulou(Shulou.com)05/31 Report--

This article will give you a detailed explanation on how to build a MySQL monitoring platform. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Prometheus+grafana for this point in time, I believe many colleagues should have started to play, while there may still be some people may not know the existence of prometheus+grafana, there may be some people although know its existence, but do not bother to start, if so, then the following content you have to cheer up, because you may be stunned by the cool visual effects of grafana.

Let's get to know it together.

First of all, let's briefly introduce what the lovebirds, prometheus+grafana, are:

Prometheus is an open source monitoring and alarm system and time series database (TSDB) developed by SoundCloud. Prometheus is a monitoring collection and data storage framework (monitoring server). Specific data collection depends on the specific exporter (monitoring client). For example, after collecting MySQL data, you need to use mysql_exporter,prometheus to call mysql_expoter to collect the monitoring indicators of mysql. The data collected by mysql_exporter is stored in the disk data file of the server where prometheus is located. Its components are basically written in golang, which is very friendly to compilation and deployment. And there is no special dependence, basically all work independently. The following is the prometheus architecture diagram (image source: https://prometheus.io/docs/introduction/overview/)

Grafana is not only a good-looking monitoring drawing program, but also a visual panel (Dashboard). In addition to good looks, grafana also supports multiple data sources (Graphite, zabbix, InfluxDB, Prometheus and OpenTSDB as data sources) and flexible and rich dashboard configuration options (for example, the same collection items of multiple instances can be configured in one display box), which makes it easier to use compared with other open source monitoring systems. The cost of learning is lower. Visually, it looks much more eye-pleasing than any open source monitoring system in the past. Let's take a look at two monitoring images:

People who believe that they are aesthetically normal will not say that the above two pictures are ugly, so the question is, how can we play? The following is a brief introduction to how to build it quickly!

Here to facilitate the demonstration process, we have prepared the following two test servers

Prometheus+grafana server host: 10.10.30.165

MySQL client host: 10.10.20.14

1. Install prometheus

1.1. Download the installation package

For prometheus, suppose we need to monitor MySQL, then we need to download at least 3 components, as follows:

Prometheus package

Node_exporter: an acquisition package that monitors hardware performance indicators such as disk, memory, CPU, etc.

Mysql_exporter: collection package for monitoring various performance indicators of mysql

Download link (there is always only one latest version of the page): https://prometheus.io/download/

Download prometheus

Download node_exporter

Download mysqld_exporter

PS: if you also need to configure monitoring alarms, you need to download the alertmanager package

1.2. Extract the package

Decompress prometheus

[root@localhost ~] # mkdir / data [root@localhost ~] # tar xvf prometheus-2.1.0.linux-amd64.tar.gz-C / data/

Decompress exporter: since the prometheus host itself needs to be monitored, at least the node_exporter package needs to be decompressed.

[root@localhost ~] # tar xf node_exporter-0.15.2.linux-amd64.tar-C / root/# if you need to monitor mysql, continue to extract the mysql_ exporter [root @ localhost ~] # tar xf mysqld_exporter-0.10.0.linux-amd64.tar-C / root/1.3. Start prometheus

Enter the working directory of prometheus

[root@localhost ~] # cd / data/ [root@localhost data] # mv prometheus-2.1.0.linux-amd64/ prometheus [root@localhost ~] # cd / data/prometheus

Configure prometheus.yml Profil

[root@localhost data] # cat prometheus.yml # my global configglobal: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). # A scrape configuration containing exactly one endpoint to scrape:# Here it's Prometheus itself.scrape_configs:- file_sd_configs:- files:- host.ymljob_name: Hostmetrics_path: / metricsrelabel_configs:- source_labels: [_ address__] regex: (. *) target_label: instancereplacement: $1-source_labels: [_ address__] regex: (. *) target_ Label: _ _ address__replacement: $1file_sd_configs:files:- mysql.ymljob_name 9100-file_sd_configs:files:- mysql.ymljob_name: MySQLmetrics_path: / metricsrelabel_configs:- source_labels: [_ _ address__] regex: (. *) target_label: instancereplacement: $1-source_labels: [_ _ address__] regex: (. *) target_label: _ _ address__replacement: $1virtual 9104-job_name: prometheusstatic_configs:- targets:- localhost:9090

Start the prometheus process. 30 days means that prometheus only retains data within 30 days.

[root@localhost prometheus] # / data/prometheus/prometheus-- storage.tsdb.retention=30d &

If it is a 7.x system, you can configure the service startup script as follows

# modify the WorkingDirectory parameter to the working directory of your prometheus [root@localhost prometheus] # cat / usr/lib/systemd/system/ prometheus.service [Unit] Description=Prometheus instanceWants=network-online.targetAfter=network-online.targetAfter=postgresql.service mariadb.service mysql.service[ Service] User=rootGroup=rootType=simpleRestart=on-failureWorkingDirectory=/data/prometheus/RuntimeDirectory=prometheusRuntimeDirectoryMode=0750ExecStart=/data/prometheus/prometheus-storage.tsdb.retention=30d-config.file=/data/prometheus/prometheus.ymlLimitNOFILE=10000TimeoutStopSec= 20 [install] WantedBy=multi-user.target

The default web access port for PS:prometheus is 9090, and you can access http://10.10.30.165:9090 using the following address

2. Install grafana

As mentioned earlier, grafana is a drawing display framework. Grafana displays according to grafana-dashboards, and grafana-dashboards is similar to grafana's drawing configuration file. According to the definition in grafana-dashboards, you need to determine what metrics need to be displayed on the page and how to display them. You need to download and install these two components respectively.

2.1. Download the installation package

For grafana, you need to download a package, a grafana-dashboards package

Download link

Grafana package: https://grafana.com/grafana/download

Grafana-dashboards package: https://github.com/percona/grafana-dashboards/releases

2.2. Extract the package

Decompress grafana

[root@localhost] # tar xf grafana-4.6.3.linux-x64.tar.gz-C / data/prometheus/ [root@localhost ~] # cd / data/prometheus [root@localhost prometheus] # mv grafana-4.6.3/ grafana2.3. Start grafana

Go to the grafana working directory and start

[root@localhost] # cd / data/prometheus/grafana [root@localhost] #. / bin/grafana-server

If it is a 7.x system, you can configure the service startup script as follows

[root@localhost service] # cat / usr/lib/systemd/system/grafana- server.service[Unit] Description=Grafana instanceDocumentation= http://docs.grafana.orgWants=network-online.targetAfter=network-online.targetAfter=postgresql.service mariadb.service mysql.service[Service] User=rootGroup=rootType=simpleRestart=on-failureWorkingDirectory=/data/prometheus/grafanaRuntimeDirectory=grafanaRuntimeDirectoryMode=0750ExecStart=/data/prometheus/grafana/bin/grafana-serverLimitNOFILE=10000TimeoutStopSec=20 [Install] WantedBy=multi-user.target

Open the grafana page (default account and password: admin/admin, default port 3000, accessed through address: http://10.10.30.165:3000) and configure the data source.

Specify the prometheus address. Here we install grafana on the same machine and directly use the address configuration of 127.0.0.1, as shown in the following figure.

2.4. Import grafana-dashboards in grafana

Extract the grafana-dashboards package, which provides a large number of grafana dashboards files in json format. Choose according to your needs. We need to monitor the host and MySQL here, so select some json files as follows.

[root@localhost ~] # tar xvf grafana-dashboards-1.6.1.tar.gz [root@localhost ~] # cd grafana-dashboards-1.6.1 [root@localhost grafana-dashboards-1.6.1] # updatedb [root@localhost grafana-dashboards-1.6.1] # locate json | grep dashboards/./root/grafana-dashboards-1.6.1/dashboards/CPU_Utilization_Details_Cores.json/root/grafana-dashboards-1. 6.1/dashboards/Disk_Performance.json/root/grafana-dashboards-1.6.1/dashboards/Disk_Space.json./root/grafana-dashboards-1.6.1/dashboards/MySQL_InnoDB_Metrics.json/root/grafana-dashboards-1.6.1/dashboards/MySQL_InnoDB_Metrics_Advanced.json./root/grafana-dashboards-1.6.1/dashboards/MySQL_Overview. Json/root/grafana-dashboards-1.6.1/dashboards/MySQL_Performance_Schema.json./root/grafana-dashboards-1.6.1/dashboards/MySQL_Replication.json/root/grafana-dashboards-1.6.1/dashboards/MySQL_Table_Statistics.json./root/grafana-dashboards-1.6.1/dashboards/Summary_Dashboard.json/root/grafana-dashboards-1.6. 1/dashboards/System_Overview.json.

In the grafana page, import the required json file

Select the json file you want to import in the pop-up window

Then, if you have added hosts to your grafana, you can see the corresponding json dashboard monitoring data

At this point, the infrastructure of prometheus+grafana (server side) has been built, and now you can add monitoring nodes to them (client side)

3. Monitor node deployment 3.1. Add host monitoring

Take the addition of a prometheus host (10.10.30.165) as an example to illustrate

Decompress the exporter package

[root@localhost ~] # tar xf node_exporter-0.15.2.linux-amd64.tar [root@localhost ~] # mv node_exporter-0.15.2.linux-amd64 node_exporter

Start the node_exporter program

[root@localhost ~] # cd node_ exporter [root @ localhost node_exporter] # nohup. / node_exporter &

Configure the prometheus host monitoring configuration list file. Since the configuration file host.yml of the monitoring host has been defined in the main configuration file prometheus.yml, you only need to enter the host IP information to take effect dynamically.

[root@localhost node_exporter] # cat / data/prometheus/host.yml- labels:service: testtargets:- 10.10.30.165

Then, you can see the host you configured on the grafana page

PS: if lables has been configured in this file and does not need to be tagged with a separate service tag, the IP of the newly added instance can be placed directly under the same targets, as follows:

[root@localhost mysqld_exporter] # cat / data/prometheus/host.yml- labels:service: testtargets:- 10.10.30.165-10.10.20.143.2. Add MySQL Monitoring

Add MySQL monitoring host. Take 10.10.20.14 as an example to illustrate.

Decompress the exporter package

[root@localhost ~] # tar xf mysqld_exporter-0.10.0.linux-amd64.tar [root@localhost ~] # mv mysqld_exporter-0.10.0.linux-amd64 mysqld_exporter

Configure the environment variables of host IP, database port, database account and password required for monitoring database (Note: this account needs to be created separately, and at least PROCESS, REPLICATION CLIENT and SELECT permissions are required for all tables in all databases)

[root@luoxiaobo-01 ~] # export DATA_SOURCE_NAME='admin:password@ (10.10.20.14 echo 3306) /'[root@luoxiaobo-01 ~] # echo "export DATA_SOURCE_NAME='admin:password@ (10.10.20.14 root@luoxiaobo-01 3306) /'" > / etc/profile

Start exporter

# since a large number of mysql collection items are turned off by default in the latest version, you need to explicitly use the corresponding option to enable it (as of writing time, the latest development version can make the exporter take effect through the configuration item on the Prometheus side Instead of using a large number of startup options in exporter to open) [root@localhost] # cd mysqld_ exporter [root @ localhost mysqld_exporter] # nohup. / mysqld_exporter-- collect.info_schema.processlist-- collect.info_schema.innodb_tablespaces-- collect.info_schema.innodb_metrics-- collect.perf_schema.tableiowaits-- collect.perf_schema.indexiowaits-- collect.perf_schema.tablelocks-- collect.engine_innodb_status-- collect.perf_schema. File_events-collect.info_schema.processlist-collect.binlog_size-collect.info_schema.clientstats-collect.perf_schema.eventswaits &

Configure the prometheus MySQL monitoring configuration list file. Since the configuration file mysql.yml of the monitoring host has been defined in the main configuration file prometheus.yml, you only need to enter the host IP information to take effect dynamically.

[root@localhost mysqld_exporter] # cat / data/prometheus/host.yml- labels:service: testtargets:- 10.10.30.165-10.10.20.14

Then, you can see the MySQL instance you configured on the grafana page

PS: if lables has been configured in this file and does not need to be tagged with a separate service tag, the IP of the newly added instance can be placed directly under the same targets, as follows:

[root@localhost mysqld_exporter] # cat / data/prometheus/mysql.yml- labels:service: testtargets:- 10.10.30.165-10.10.20.143.3. Grafana page dashboard switch

Switch monitoring templates as needed

Then, you can see the data you want.

This is the end of the article on "how to build a MySQL monitoring platform". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.