技术标签: Prometheus和Grafana的监控Linux服务器和 node_exporter Prometheus Grafana
【使用root用户执行以下操作】
目录
4)我们访问Prometheus :http://100.100.100.25:9090
4)监听端口为9100,我们访问 http://100.100.100.25:9100
5)配置Prometheus,将node_exporter加入到监控中
6)在grafana中的配置 (http://100.100.100.25:3000)
2)将下载的mysqld_exporter上传到mysql所在的linux服务器
3)在root目录下建立文件 " .my.cnf " ,输入mysql的访问参数
4)启动mysqld_exporter,指定监听端口为9200
5)我们访问 mysqld_exporter : http://100.100.100.25:9200
6)修改prometheus配置,在prometheus.yml加入mysql的监听地址
8)访问grafana(http://100.100.100.25:3000),将mysql数据源添加
10)将my2.sql导入到MYSQL数据库(100.100.100.25:3306/pig),该监控就是利用my2.sql中的数据进行展示的
tar -zxvf prometheus-2.15.1.linux-amd64.tar.gz
./prometheus
[如果需要挂后台在命令后面加 & 符号]
wget https://dl.grafana.com/oss/release/grafana-6.5.2.linux-amd64.tar.gz
tar -zxvf grafana-6.5.2.linux-amd64.tar.gz
./grafana-server
[准备工作已经完成,下面我们来安装监控Linux服务器的软件和监控mysql数据库的软件]
tar -zxvf node_exporter-0.18.1.linux-amd64.tar.gz
./node_exporter
vim prometheus.yml
./prometheus
添加Prometheus数据源
tar -zxvf mysqld_exporter-0.12.1.linux-amd64.tar.gz
vi /root/.my.cnf
[文件内容如下]
[client]
host = 100.100.100.25
user = root
password = password
./mysqld_exporter --web.listen-address=":9200" &
vim prometheus.yml
[增加的内容]
- job_name: '100.100.100.25_mysql'
static_configs:
- targets: [100.100.100.25:9200]
./prometheus
-- by [email protected]
-- My2 Collector
-- 0.0.1 2013-02-14 First version for MySQL 5.6
-- 0.0.6 2017-04-01 DBCPU as SUM_TIMER_WAIT from events_statements_summary_global_by_event_name
-- 0.0.7 2017-11-01 bug fixed (0 as first value for delta), MariaDB 10.2 support, new custom statistics
-- 0.0.7a 2018-02-18 substr(EVENT_NAME,15) --> substr(EVENT_NAME,15,60)
-- 0.0.8 2018-04-01 MySQL v.8.0 support
-- 0.0.9a 2018-08-15 Delta statistics (useful for Grafana and others), (a) got some useful global_variable
-- 0.0.10 2018-10-31 Replication Lag (also with multi-threaded slaves)
-- Create Database, Tables, Stored Routines and Jobs for My2 dashboard
create database IF NOT EXISTS my2;
use my2;
CREATE TABLE IF NOT EXISTS status (
VARIABLE_NAME varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
VARIABLE_VALUE varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
TIMEST timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS current (
VARIABLE_NAME varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
VARIABLE_VALUE varchar(1024) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB;
ALTER TABLE status
ADD unique KEY idx01 (VARIABLE_NAME,timest);
-- delete from my2.status where VARIABLE_NAME like 'PROCESSES_HOSTS.%';
-- update my2.status set variable_value=0, timest=timest where VARIABLE_NAME like '%-d' and variable_value<0;
ALTER TABLE current
ADD unique KEY idx02 (VARIABLE_NAME);
DROP PROCEDURE IF EXISTS collect_stats;
DELIMITER // ;
CREATE PROCEDURE collect_stats()
BEGIN
DECLARE a datetime;
DECLARE v varchar(10);
set sql_log_bin = 0;
set a=now();
select substr(version(),1,3) into v;
if v='5.7' OR v='8.0' then
insert into my2.status(variable_name,variable_value,timest)
select upper(variable_name),variable_value, a
from performance_schema.global_status
where variable_value REGEXP '^-*[[:digit:]]+(\.[[:digit:]]+)?$'
and variable_name not like 'Performance_schema_%'
and variable_name not like 'SSL_%';
insert into my2.status(variable_name,variable_value,timest)
SELECT 'replication_worker_time', coalesce(max(PROCESSLIST_TIME), 0.1), a
FROM performance_schema.threads
WHERE (NAME = 'thread/sql/slave_worker'
AND (PROCESSLIST_STATE IS NULL
OR PROCESSLIST_STATE != 'Waiting for an event from Coordinator'))
OR NAME = 'thread/sql/slave_sql';
-- *** Comment the following 4 lines with 8.0 ***
else
insert into my2.status(variable_name,variable_value,timest)
select variable_name,variable_value,a
from information_schema.global_status;
end if;
insert into my2.status(variable_name,variable_value,timest)
select concat('PROCESSES.',user),count(*),a
from information_schema.processlist
group by user;
insert into my2.status(variable_name,variable_value,timest)
select concat('PROCESSES_HOSTS.',SUBSTRING_INDEX(host,':',1)),count(*),a
from information_schema.processlist
group by concat('PROCESSES_HOSTS.',SUBSTRING_INDEX(host,':',1));
insert into my2.status(variable_name,variable_value,timest)
select concat('PROCESSES_COMMAND.',command),count(*),a
from information_schema.processlist
group by concat('PROCESSES_COMMAND.',command);
insert into my2.status(variable_name,variable_value,timest)
select substr(concat('PROCESSES_STATE.',state),1,64),count(*),a
from information_schema.processlist
group by substr(concat('PROCESSES_STATE.',state),1,64);
if v='5.6' OR v='5.7' OR v='8.0' OR v='10.' then
insert into my2.status(variable_name,variable_value,timest)
SELECT 'SUM_TIMER_WAIT', sum(sum_timer_wait*1.0), a
FROM performance_schema.events_statements_summary_global_by_event_name;
end if;
-- Delta values
if v='5.7' OR v='8.0' then
insert into my2.status(variable_name,variable_value,timest)
select concat(upper(s.variable_name),'-d'), greatest(s.variable_value-c.variable_value,0), a
from performance_schema.global_status s, my2.current c
where s.variable_name=c.variable_name;
insert into my2.status(variable_name,variable_value,timest)
SELECT concat('COM_',upper(substr(s.EVENT_NAME,15,58)), '-d'), greatest(s.COUNT_STAR-c.variable_value,0), a
FROM performance_schema.events_statements_summary_global_by_event_name s, my2.current c
WHERE s.EVENT_NAME LIKE 'statement/sql/%'
AND s.EVENT_NAME = c.variable_name;
insert into my2.status(variable_name,variable_value,timest)
SELECT 'SUM_TIMER_WAIT-d', sum(sum_timer_wait*1.0)-c.variable_value, a
FROM performance_schema.events_statements_summary_global_by_event_name, my2.current c
WHERE c.variable_name='SUM_TIMER_WAIT';
insert into my2.status(variable_name, variable_value, timest)
select 'replication_connection_status',if(SERVICE_STATE='ON', 1, 0),a
from performance_schema.replication_connection_status;
insert into my2.status(variable_name, variable_value, timest)
select 'replication_applier_status',if(SERVICE_STATE='ON', 1, 0),a
from performance_schema.replication_applier_status;
delete from my2.current;
insert into my2.current(variable_name,variable_value)
select upper(variable_name),variable_value+0
from performance_schema.global_status
where variable_value REGEXP '^-*[[:digit:]]+(\.[[:digit:]]+)?$'
and variable_name not like 'Performance_schema_%'
and variable_name not like 'SSL_%';
insert into my2.current(variable_name,variable_value)
SELECT substr(EVENT_NAME,1,40), COUNT_STAR
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/%';
insert into my2.current(variable_name,variable_value)
SELECT 'SUM_TIMER_WAIT', sum(sum_timer_wait*1.0)
FROM performance_schema.events_statements_summary_global_by_event_name;
insert into my2.current(variable_name,variable_value)
select concat('PROCESSES_COMMAND.',command),count(*)
from information_schema.processlist
group by concat('PROCESSES_COMMAND.',command);
insert into my2.current(variable_name,variable_value)
select upper(variable_name),variable_value
from performance_schema.global_variables
where variable_name in ('max_connections', 'innodb_buffer_pool_size', 'query_cache_size',
'innodb_log_buffer_size', 'key_buffer_size', 'table_open_cache');
else
insert into my2.status(variable_name,variable_value,timest)
select concat(upper(s.variable_name),'-d'), greatest(s.variable_value-c.variable_value,0), a
from information_schema.global_status s, my2.current c
where s.variable_name=c.variable_name;
delete from my2.current;
insert into my2.current(variable_name,variable_value)
select upper(variable_name),variable_value+0
from information_schema.global_status
where variable_value REGEXP '^-*[[:digit:]]+(\.[[:digit:]]+)?$'
and variable_name not like 'Performance_schema_%'
and variable_name not like 'SSL_%';
insert into my2.current(variable_name,variable_value)
select upper(variable_name),variable_value
from information_schema.global_variables
where variable_name in ('max_connections', 'innodb_buffer_pool_size', 'query_cache_size',
'innodb_log_buffer_size', 'key_buffer_size', 'table_open_cache');
end if;
set sql_log_bin = 1;
END //
DELIMITER ; //
-- Collect daily statistics on space usage and delete old statistics (older than 62 days, 1 year for DB size)
DROP PROCEDURE IF EXISTS collect_daily_stats;
DELIMITER // ;
CREATE PROCEDURE collect_daily_stats()
BEGIN
DECLARE a datetime;
set sql_log_bin = 0;
set a=now();
insert into my2.status(variable_name,variable_value,timest)
select concat('SIZEDB.',table_schema), sum(data_length+index_length), a
from information_schema.tables group by table_schema;
insert into my2.status(variable_name,variable_value,timest)
select 'SIZEDB.TOTAL', sum(data_length+index_length), a
from information_schema.tables;
delete from my2.status where timest < date_sub(now(), INTERVAL 62 DAY) and variable_name <>'SIZEDB.TOTAL';
delete from my2.status where timest < date_sub(now(), INTERVAL 365 DAY);
set sql_log_bin = 1;
END //
DELIMITER ; //
-- The event scheduler must also be activated in the my.cnf (event_scheduler=1)
set global event_scheduler=1;
set sql_log_bin = 0;
DROP EVENT IF EXISTS collect_stats;
CREATE EVENT collect_stats
ON SCHEDULE EVERY 10 Minute
DO call collect_stats();
DROP EVENT IF EXISTS collect_daily_stats;
CREATE EVENT collect_daily_stats
ON SCHEDULE EVERY 1 DAY
DO call collect_daily_stats();
set sql_log_bin = 1;
-- Use a specific user (suggested)
-- create user my2@'%' identified by 'P1e@seCh@ngeMe';
-- grant all on my2.* to my2@'%';
【注意:这个仪表盘的数据要过一会才会有】
1)下载mysql_exporter:
2)解压 mysqld_exporter,在目录下创建 ".my.cnf" 文件
[client]
host = localhost
user = root
password = password
-- by [email protected]
-- My2_80 Collector
-- 0.0.1 2013-02-14 First version for MySQL 5.6
-- 0.0.6 2017-04-01 DBCPU as SUM_TIMER_WAIT from events_statements_summary_global_by_event_name
-- 0.0.7 2017-11-01 bug fixed (0 as first value for delta), MariaDB 10.2 support, new custom statistics
-- 0.0.7a 2018-02-18 substr(EVENT_NAME,15) --> substr(EVENT_NAME,15,60)
-- 0.0.8 2018-04-01 MySQL v.8.0 support
-- 0.0.9a 2018-08-15 Delta statistics (useful for Grafana and others), (a) got some useful global_variable
-- 0.0.10 2018-10-31 Replication Lag (also with multi-threaded slaves)
-- 0.0.11 2019-05-05 MySQL v.8 only
-- Create Database, Tables, Stored Routines and Jobs for My2 dashboard
create database IF NOT EXISTS my2;
use my2;
CREATE TABLE IF NOT EXISTS status (
VARIABLE_NAME varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
VARIABLE_VALUE varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
TIMEST timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS current (
VARIABLE_NAME varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
VARIABLE_VALUE varchar(1024) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB;
ALTER TABLE status
ADD unique KEY idx01 (VARIABLE_NAME,timest);
-- delete from my2.status where VARIABLE_NAME like 'PROCESSES_HOSTS.%';
-- update my2.status set variable_value=0, timest=timest where VARIABLE_NAME like '%-d' and variable_value<0;
ALTER TABLE current
ADD unique KEY idx02 (VARIABLE_NAME);
DROP PROCEDURE IF EXISTS collect_stats;
DELIMITER // ;
CREATE PROCEDURE collect_stats()
BEGIN
DECLARE a datetime;
DECLARE v varchar(10);
set sql_log_bin = 0;
set a=now();
select substr(version(),1,3) into v;
insert into my2.status(variable_name,variable_value,timest)
select upper(variable_name),variable_value, a
from performance_schema.global_status
where variable_value REGEXP '^-*[[:digit:]]+(\.[[:digit:]]+)?$'
and variable_name not like 'Performance_schema_%'
and variable_name not like 'SSL_%';
insert into my2.status(variable_name,variable_value,timest)
SELECT 'REPLICATION_MAX_WORKER_TIME', coalesce(max(PROCESSLIST_TIME), 0.1), a
FROM performance_schema.threads
WHERE (NAME = 'thread/sql/slave_worker'
AND (PROCESSLIST_STATE IS NULL
OR PROCESSLIST_STATE != 'Waiting for an event from Coordinator'))
OR NAME = 'thread/sql/slave_sql';
insert into my2.status(variable_name,variable_value,timest)
select concat('PROCESSES.',user),count(*),a
from information_schema.processlist
group by user;
insert into my2.status(variable_name,variable_value,timest)
select concat('PROCESSES_HOSTS.',SUBSTRING_INDEX(host,':',1)),count(*),a
from information_schema.processlist
group by concat('PROCESSES_HOSTS.',SUBSTRING_INDEX(host,':',1));
insert into my2.status(variable_name,variable_value,timest)
select concat('PROCESSES_COMMAND.',command),count(*),a
from information_schema.processlist
group by concat('PROCESSES_COMMAND.',command);
insert into my2.status(variable_name,variable_value,timest)
select substr(concat('PROCESSES_STATE.',state),1,64),count(*),a
from information_schema.processlist
group by substr(concat('PROCESSES_STATE.',state),1,64);
insert into my2.status(variable_name,variable_value,timest)
SELECT 'SUM_TIMER_WAIT', sum(sum_timer_wait*1.0), a
FROM performance_schema.events_statements_summary_global_by_event_name;
-- Delta values
insert into my2.status(variable_name,variable_value,timest)
select concat(upper(s.variable_name),'-d'), greatest(s.variable_value-c.variable_value,0), a
from performance_schema.global_status s, my2.current c
where s.variable_name=c.variable_name;
insert into my2.status(variable_name,variable_value,timest)
SELECT concat('COM_',upper(substr(s.EVENT_NAME,15,58)), '-d'), greatest(s.COUNT_STAR-c.variable_value,0), a
FROM performance_schema.events_statements_summary_global_by_event_name s, my2.current c
WHERE s.EVENT_NAME LIKE 'statement/sql/%'
AND s.EVENT_NAME = c.variable_name;
insert into my2.status(variable_name,variable_value,timest)
SELECT 'SUM_TIMER_WAIT-d', sum(sum_timer_wait*1.0)-c.variable_value, a
FROM performance_schema.events_statements_summary_global_by_event_name, my2.current c
WHERE c.variable_name='SUM_TIMER_WAIT';
insert into my2.status(variable_name, variable_value, timest)
select 'REPLICATION_CONNECTION_STATUS',if(SERVICE_STATE='ON', 1, 0),a
from performance_schema.replication_connection_status;
insert into my2.status(variable_name, variable_value, timest)
select 'REPLICATION_APPLIER_STATUS',if(SERVICE_STATE='ON', 1, 0),a
from performance_schema.replication_applier_status;
delete from my2.current;
insert into my2.current(variable_name,variable_value)
select upper(variable_name),variable_value+0
from performance_schema.global_status
where variable_value REGEXP '^-*[[:digit:]]+(\.[[:digit:]]+)?$'
and variable_name not like 'Performance_schema_%'
and variable_name not like 'SSL_%';
insert into my2.current(variable_name,variable_value)
SELECT substr(EVENT_NAME,1,40), COUNT_STAR
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'statement/sql/%';
insert into my2.current(variable_name,variable_value)
SELECT 'SUM_TIMER_WAIT', sum(sum_timer_wait*1.0)
FROM performance_schema.events_statements_summary_global_by_event_name;
insert into my2.current(variable_name,variable_value)
select concat('PROCESSES_COMMAND.',command),count(*)
from information_schema.processlist
group by concat('PROCESSES_COMMAND.',command);
insert into my2.current(variable_name,variable_value)
select upper(variable_name),variable_value
from performance_schema.global_variables
where variable_name in ('max_connections', 'innodb_buffer_pool_size', 'query_cache_size',
'innodb_log_buffer_size', 'key_buffer_size', 'table_open_cache');
set sql_log_bin = 1;
END //
DELIMITER ; //
-- Collect daily statistics on space usage and delete old statistics (older than 62 days, 1 year for DB size)
DROP PROCEDURE IF EXISTS collect_daily_stats;
DELIMITER // ;
CREATE PROCEDURE collect_daily_stats()
BEGIN
DECLARE a datetime;
set sql_log_bin = 0;
set a=now();
insert into my2.status(variable_name,variable_value,timest)
select concat('SIZEDB.',table_schema), sum(data_length+index_length), a
from information_schema.tables group by table_schema;
insert into my2.status(variable_name,variable_value,timest)
select 'SIZEDB.TOTAL', sum(data_length+index_length), a
from information_schema.tables;
delete from my2.status where timest < date_sub(now(), INTERVAL 62 DAY) and variable_name <>'SIZEDB.TOTAL';
delete from my2.status where timest < date_sub(now(), INTERVAL 365 DAY);
set sql_log_bin = 1;
END //
DELIMITER ; //
-- The event scheduler must also be activated in the my.cnf (event_scheduler=1)
set global event_scheduler=1;
set sql_log_bin = 0;
DROP EVENT IF EXISTS collect_stats;
CREATE EVENT collect_stats
ON SCHEDULE EVERY 10 Minute
DO call collect_stats();
DROP EVENT IF EXISTS collect_daily_stats;
CREATE EVENT collect_daily_stats
ON SCHEDULE EVERY 1 DAY
DO call collect_daily_stats();
set sql_log_bin = 1;
-- Use a specific user (suggested)
-- create user my2@'%' identified by 'P1e@seCh@ngeMe';
-- grant all on my2.* to my2@'%';
3)cmd启动 mysqld_exporter
mysqld_exporter.exe --web.listen-address=":9104" --config.my-cnf=.my.cnf
4)在Prometheus配置中增加对该mysql的监听
vim prometheus.yml
./prometheus
5)在grafana上(http://100.100.100.25:3000/)增加该数据源
6)选择MYSQL监控仪表盘
【访问Prometheus ,http://100.100.100.25:9090 可以看到在配置中添加的监听服务器】
文章浏览阅读2w次,点赞7次,收藏51次。四个步骤1.创建C++ Win32项目动态库dll 2.在Win32项目动态库中添加 外部依赖项 lib头文件和lib库3.导出C接口4.c#调用c++动态库开始你的表演...①创建一个空白的解决方案,在解决方案中添加 Visual C++ , Win32 项目空白解决方案的创建:添加Visual C++ , Win32 项目这......_c#调用lib
文章浏览阅读4.6k次。苹方字体是苹果系统上的黑体,挺好看的。注重颜值的网站都会使用,例如知乎:font-family: -apple-system, BlinkMacSystemFont, Helvetica Neue, PingFang SC, Microsoft YaHei, Source Han Sans SC, Noto Sans CJK SC, W..._ubuntu pingfang
文章浏览阅读159次。表单表单概述表单标签表单域按钮控件demo表单标签表单标签基本语法结构<form action="处理数据程序的url地址“ method=”get|post“ name="表单名称”></form><!--action,当提交表单时,向何处发送表单中的数据,地址可以是相对地址也可以是绝对地址--><!--method将表单中的数据传送给服务器处理,get方式直接显示在url地址中,数据可以被缓存,且长度有限制;而post方式数据隐藏传输,_html表单的处理程序有那些
文章浏览阅读1.2k次。使用说明:开启Google的登陆二步验证(即Google Authenticator服务)后用户登陆时需要输入额外由手机客户端生成的一次性密码。实现Google Authenticator功能需要服务器端和客户端的支持。服务器端负责密钥的生成、验证一次性密码是否正确。客户端记录密钥后生成一次性密码。下载谷歌验证类库文件放到项目合适位置(我这边放在项目Vender下面)https://github.com/PHPGangsta/GoogleAuthenticatorPHP代码示例://引入谷_php otp 验证器
文章浏览阅读4.3k次,点赞5次,收藏11次。matplotlib.plot画图横坐标混乱及间隔处理_matplotlib更改横轴间距
文章浏览阅读2.2k次。①Storage driver 处理各镜像层及容器层的处理细节,实现了多层数据的堆叠,为用户 提供了多层数据合并后的统一视图②所有 Storage driver 都使用可堆叠图像层和写时复制(CoW)策略③docker info 命令可查看当系统上的 storage driver主要用于测试目的,不建议用于生成环境。_docker 保存容器
文章浏览阅读834次,点赞27次,收藏13次。网络拓扑结构是指计算机网络中各组件(如计算机、服务器、打印机、路由器、交换机等设备)及其连接线路在物理布局或逻辑构型上的排列形式。这种布局不仅描述了设备间的实际物理连接方式,也决定了数据在网络中流动的路径和方式。不同的网络拓扑结构影响着网络的性能、可靠性、可扩展性及管理维护的难易程度。_网络拓扑csdn
文章浏览阅读1.8k次,点赞5次,收藏8次。IOS系统Date的坑要创建一个指定时间的new Date对象时,通常的做法是:new Date("2020-09-21 11:11:00")这行代码在 PC 端和安卓端都是正常的,而在 iOS 端则会提示 Invalid Date 无效日期。在IOS年月日中间的横岗许换成斜杠,也就是new Date("2020/09/21 11:11:00")通常为了兼容IOS的这个坑,需要做一些额外的特殊处理,笔者在开发的时候经常会忘了兼容IOS系统。所以就想试着重写Date函数,一劳永逸,避免每次ne_date.prototype 将所有 ios
文章浏览阅读5.3k次。方法一:用PLSQL Developer工具。 1 在PLSQL Developer的sql window里输入select * from test for update; 2 按F8执行 3 打开锁, 再按一下加号. 鼠标点到第一列的列头,使全列成选中状态,然后粘贴,最后commit提交即可。(前提..._excel导入pl/sql
文章浏览阅读83次。Git常用命令速查手册1、初始化仓库git init2、将文件添加到仓库git add 文件名 # 将工作区的某个文件添加到暂存区 git add -u # 添加所有被tracked文件中被修改或删除的文件信息到暂存区,不处理untracked的文件git add -A # 添加所有被tracked文件中被修改或删除的文件信息到暂存区,包括untracked的文件...
文章浏览阅读202次。分享119个ASP.NET源码总有一个是你想要的_千博二手车源码v2023 build 1120
文章浏览阅读1.8k次。版权声明:转载请注明出处 http://blog.csdn.net/irean_lau。目录(?)[+]1、缺省构造函数。2、缺省拷贝构造函数。3、 缺省析构函数。4、缺省赋值运算符。5、缺省取址运算符。6、 缺省取址运算符 const。[cpp] view plain copy_空类默认产生哪些类成员函数