grafana安装配置使用，添加Mysql数据源，连表查询配置展示曲线

1. 下载grafana并安装

1.1 官网下载较慢，在清华镜像站下载

下载链接>> https://mirrors.tuna.tsinghua.edu.cn/grafana/yum/rpm/

[root@vm10-10-128-68 local]# wget https://mirrors.tuna.tsinghua.edu.cn/grafana/yum/rpm/Packages/grafana-7.3.3-1.x86_64.rpm

1.2 yum安装解决依赖关系

[root@vm10-10-128-68 local]# yum -y install grafana-7.3.3-1.x86_64.rpm

2. 开启grafana服务

2.1 开启服务

systemctl start grafana-server

2.2 查看端口验证（grafana默认监听3000端口）

[root@vm10-10-128-68 local]# netstat -naputl | grep 3000
tcp6       0      0 :::3000                 :::*                    LISTEN      460764/grafana-serv

 

3. 浏览器访问并配置

3.1 打开浏览器输入 IP:3000 访问

例如： 192.168.153.183:3000

3.2 访问成功之后输入默认的用户名和密码登录

默认的用户名和密码均为：admin

 

添加数据源

在登陆系统后的首页，就可以进行数据源的添加了。

 

 

 

 

 

 

 

 

 

 

 

 配置连表查询配置曲线

Mysql表关系

表1 monitor01_pinglossdata 其中ip_resource_id字段外键到表monitor01_ipresource，

mysql> select * from monitor01_pinglossdata where ctime BETWEEN "2023-04-10 17:59:00" and"2023-04-10 18:00:00" ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    4786893
Current database: monitor_test

+---------+----------------------------+-------------+----------------+
| id      | ctime                      | float_value | ip_resource_id |
+---------+----------------------------+-------------+----------------+
| 1517049 | 2023-04-10 17:59:12.000000 |           0 |              3 |
| 1517050 | 2023-04-10 17:59:12.000000 |           0 |              9 |
| 1517051 | 2023-04-10 17:59:12.000000 |           0 |              2 |
| 1517052 | 2023-04-10 17:59:12.000000 |           0 |              7 |
| 1517053 | 2023-04-10 17:59:12.000000 |           0 |              8 |
| 1517054 | 2023-04-10 17:59:12.000000 |           0 |              5 |
| 1517055 | 2023-04-10 17:59:12.000000 |           0 |              4 |
| 1517056 | 2023-04-10 17:59:12.000000 |           0 |              6 |
| 1517057 | 2023-04-10 17:59:32.000000 |           0 |              9 |
| 1517058 | 2023-04-10 17:59:32.000000 |           0 |              2 |
| 1517059 | 2023-04-10 17:59:32.000000 |           0 |              7 |
| 1517060 | 2023-04-10 17:59:32.000000 |           0 |              5 |
| 1517061 | 2023-04-10 17:59:32.000000 |           0 |              6 |
| 1517062 | 2023-04-10 17:59:32.000000 |           0 |              4 |
| 1517063 | 2023-04-10 17:59:32.000000 |           0 |              8 |
| 1517064 | 2023-04-10 17:59:32.000000 |           0 |              3 |
| 1517065 | 2023-04-10 17:59:52.000000 |           0 |              3 |
| 1517066 | 2023-04-10 17:59:52.000000 |           0 |              2 |
| 1517067 | 2023-04-10 17:59:52.000000 |           0 |              4 |
| 1517068 | 2023-04-10 17:59:52.000000 |           0 |              5 |
| 1517069 | 2023-04-10 17:59:52.000000 |           0 |              6 |
| 1517070 | 2023-04-10 17:59:52.000000 |           0 |              7 |
| 1517071 | 2023-04-10 17:59:52.000000 |           0 |              8 |
| 1517072 | 2023-04-10 17:59:55.000000 |           2 |              9 |
+---------+----------------------------+-------------+----------------+
24 rows in set (1.20 sec)

mysql> desc monitor01_pinglossdata;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| ctime | datetime(6) | NO | | NULL | |
| float_value | double | NO | | NULL | |
| ip_resource_id | bigint(20) | NO | MUL | NULL | |
+----------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

monitor01_ipresource 表字段

mysql> select * from  monitor01_ipresource;
+----+-------------------------+---------------+------------+------------------+----------+
| id | name                    | customer_name | uid        | genericIPAddress | location |
+----+-------------------------+---------------+------------+------------------+----------+
|  1 | 测试IP                  | 谷歌          | 73398680   | 8.8.8.8          | 美国     |
+----+-------------------------+---------------+------------+------------------+----------+
10 rows in set (0.00 sec)

mysql> desc monitor01_ipresource;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name             | varchar(256) | NO   |     | NULL    |                |
| customer_name    | varchar(256) | NO   |     | NULL    |                |
| uid              | varchar(32)  | NO   |     | NULL    |                |
| genericIPAddress | char(39)     | NO   |     | NULL    |                |
| location         | varchar(256) | NO   |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql>

 

 

 

grafana配置展示曲线 dashboard / Edit Panel

Mysql连表查询语句示例：

mysql> select d.ctime as ctime, d.float_value, i.genericIPAddress  from monitor01_pinglossdata as d left outer join monitor01_ipresource as i on d.ip_resource_id = i.id where d.ctime BETWEEN "2023-04-10 17:59:00" and"2023-04-10 18:00:00" ORDER BY d.ctime;

 

 Grafana SQL语句 

SELECT
 $__timeGroupAlias(d.ctime,$__interval),
  max(d.float_value) as "丢包率", 
  i.genericIPAddress
    
FROM monitor01_pinglossdata AS d
left outer join monitor01_ipresource AS i 
on d.ip_resource_id = i.id
WHERE $__timeFilter(d.ctime)
GROUP BY d.ctime
ORDER BY d.ctime

(里面的 as "丢包率" 没有作用 )