前言

Grafana是一个开源的，拥有丰富dashboard和图表编辑的指标分析平台，支持多种数据源。下面介绍一些基于mysql数据源的面板配置方法。

端口流量图

数据来源（zabbix监控采集）

表结构

MariaDB [zreport]> desc history_uint;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO   | MUL | NULL    |       |
| clock  | int(11)             | NO   |     | 0       |       |
| value  | bigint(20) unsigned | NO   |     | 0       |       |
| ns     | int(11)             | NO   |     | 0       |       |

rows in set (0.001 sec)

数据样本

MariaDB [zreport]> select * from history_uint order by clock desc limit 10;
+--------+------------+-----------+-----------+
| itemid | clock      | value     | ns        |
+--------+------------+-----------+-----------+
|  36336 | 1639155600 | 122559104 | 354559595 |
|  36505 | 1639155600 |  18404320 | 462215215 |
|  36506 | 1639155600 |  20577192 | 932727023 |
|  36513 | 1639155600 |  15658232 | 932727023 |
|  36337 | 1639155600 | 116371000 | 778553999 |
|  36553 | 1639155600 | 597137480 | 932727023 |
|  36385 | 1639155600 | 174508568 | 827251666 |
|  36552 | 1639155600 | 628670928 | 462215215 |
|  36504 | 1639155600 |  15002368 | 462215215 |
|  36384 | 1639155600 |        88 | 223615600 |

rows in set (1.025 sec)

95计费

#in端口ID元组   IN_ITEMS
SELECT 'IN_ITEMS' AS __text,  '(36384, 36385)' as __value;

#95计费去掉的点数
SELECT 'DROPCOUNTS' AS __text, ROUND((${__to}+1000-${__from})/6000000) AS __value;

#原始数据表名TABLE_R
SELECT 'TABLE_R' AS __text, 'history_uint' AS __value;

#95计费In端口速率(原始数据) INRATE
SELECT 'INRATE' AS __text, (SUM(value)/1000/1000/1000) as __value
FROM 
(SELECT  
date_format(from_unixtime(clock), '%Y-%m-%d %H:%i') as time, 
value  
FROM  ${TABLE_R}  
WHERE itemid IN  ${IN_ITEMS}
AND $__unixEpochFilter(clock)
ORDER BY time) 
AS temp 
GROUP BY time
ORDER by __value 
DESC LIMIT ${DROPCOUNTS},1

#流量表
SELECT 
  unix_timestamp(time) as time_sec, 
  (SUM(value)/1000/1000/1000) as value,
  'In端口流量汇总(优化数据)' as metric  
FROM 
(SELECT  date_format(from_unixtime(clock), '%Y-%m-%d %H:%i') as time , value 
FROM   ${TABLE_O}
WHERE itemid IN ${IN_ITEMS}
AND   $__unixEpochFilter(clock)
ORDER BY  time) 
AS temp 
GROUP BY time;

订单统计分析面板

数据源(mysql数据库)

order_record表

MySQL [yaoguai]> select order_status,price,create_time,format_id,order_type,user_id  from order_record order by id desc limit 10;
+--------------+-------+---------------------+-----------+------------+---------+
| order_status | price | create_time         | format_id | order_type | user_id |
+--------------+-------+---------------------+-----------+------------+---------+
|            5 |   290 | 2022-03-15 19:21:44 |        42 |          0 | 1087835 |
|           10 |   290 | 2022-03-15 19:21:39 |        42 |          0 | 1087836 |
|            5 |   290 | 2022-03-15 19:21:03 |        42 |          0 | 1087833 |
|            5 |   290 | 2022-03-15 19:20:54 |         4 |          0 | 1087834 |
|            5 |   290 | 2022-03-15 19:19:57 |        42 |          0 | 1087824 |
|            5 |   290 | 2022-03-15 19:19:14 |        42 |          0 | 1087830 |
|            5 |   290 | 2022-03-15 19:18:52 |        42 |          0 | 1087831 |
|           10 |   290 | 2022-03-15 19:16:11 |        42 |          0 | 1087810 |
|            5 |   290 | 2022-03-15 19:15:50 |        42 |          0 | 1087829 |
|            5 |   290 | 2022-03-15 19:14:55 |        42 |          0 | 1087828 |

rows in set (0.00 sec)

photo_format

MySQL [yaoguai]> select format_id,format_name from photo_format limit 10;
+-----------+----------------------+
| format_id | format_name          |
+-----------+----------------------+
|         1 | 一寸                 |
|         2 | 小一寸               |
|         3 | 大一寸               |
|         4 | 二寸                 |
|         5 | 小二寸               |
|         6 | 大二寸               |
|         7 | 美国签证             |
|         8 | 日本签证             |
|         9 | 越南签证             |
|        10 | 身份证(无回执)       |

rows in set (0.00 sec)

user表

MySQL [yaoguai]> select id,mini_kind from user order by id desc limit 10;
+---------+-----------+
| id      | mini_kind |
+---------+-----------+
| 1087840 |         1 |
| 1087839 |         1 |
| 1087838 |         1 |
| 1087837 |         1 |
| 1087836 |         1 |
| 1087835 |         1 |
| 1087834 |         1 |
| 1087833 |        10 |
| 1087832 |         1 |
| 1087831 |         1 |

rows in set (0.00 sec)

Stat 面板

#金额
SELECT 
  now() as time_sec,
  sum(case a.order_status when '10' then a.num else 0 end) as '成功订单金额(单位:元)',
    sum(case a.order_status when '5' then a.num else 0 end) as '失败订单金额(单位:元)'
from (
    select order_status ,sum(price)/100 as num
    from order_record 
    where $__timeFilter(create_time)
    GROUP BY order_status) a

#个数
SELECT 
  now() as time_sec,
  sum(case a.order_status when '10' then a.num else 0 end) as '成功订单数(单位:个)',
    sum(case a.order_status when '5' then a.num else 0 end) as '失败订单数(单位:个)'
from (
    select order_status , count(order_status) as num
    from order_record 
    where $__timeFilter(create_time)
    GROUP BY order_status) a

Bar gauge 面板

select 
    c.format_name as format_name,
    count(c.format_name) as num 
from order_record b 
    join photo_format c 
    on b.format_id = c.format_id 
where 
  $__timeFilter(b.create_time)
and 
  b.order_status = 10  
GROUP BY c.format_name
order by count(c.format_name) desc
;

Pie chart 面板

SELECT 
  now() as time_sec,
    sum(case a.order_type when '0' then a.num else 0 end) as '未换装电子版',
    sum(case a.order_type when '5' then a.num else 0 end) as '未换装打印版',
    sum(case a.order_type when '10' then a.num else 0 end) as '已换装电子版',
    sum(case a.order_type when '15' then a.num else 0 end) as '已换装打印版'
from (
    select order_type ,sum(price)/100 as num
    from order_record 
    where $__timeFilter(create_time)
    and order_status = 10 
    GROUP BY order_type) a

Time series 面板

SELECT 
    unix_timestamp(time) as time_sec, 
    sum(value) as value,
    '所有订单' as metric  
FROM (
    select date_format(b.create_time, '%Y-%m-%d %H') as time , 1 as value
    from order_record b
    where $__timeFilter(b.create_time)
    and b.order_status = 10 
    GROUP BY b.create_time)
    AS temp 
GROUP BY time;

Table 面板

SELECT 
    (
        CASE 
            WHEN `mini_kind`=0 THEN 'web-官网'
            WHEN `mini_kind`=1 THEN '微信-APP'
            WHEN `mini_kind`=10 THEN '支付宝-APP'
            WHEN `mini_kind`=12 THEN '支付宝-APP2'
            ELSE NULL
        END
      ) '用户类型',
    user_id as '用户ID', 
    count(user_id) as '订单数',
    sum(price)/100 as '总金额(单位:元)'
FROM 
(SELECT 
    c.mini_kind as mini_kind, 
    b.user_id as user_id,  
    b.price as price
  FROM  order_record b 
  join user c 
  on b.user_id = c.id
  WHERE 
    $__timeFilter(b.create_time)
  AND
    b.order_status = 10
  ORDER BY  b.user_id) 
AS a 
GROUP BY user_id
order by sum(price) desc 
limit 20 
;

geek

· iPhone · Safari

沙发

请问mysql里面的数据更新后grafana视图会更新吗

2022-07-25 回复

行云流水作者
· MacOS · Google Chrome

@ geek

会，mysql数据源和其他数据源没什么区别

2022-07-25 回复
1. geek
  · Windows 10 · Google Chrome
  
  @ 行云流水
  
  我测试了一下，好像最快刷新频率是5s一次，输入更低的刷新时间系统默认不能使用，是这样吗
  
  2022-07-25 回复
  1. 行云流水作者
    · MacOS · Google Chrome
    
    @ geek
    
    5s还不够快吗，你要干啥
    
    2022-07-25 回复
    
    geek
    · Windows 10 · Google Chrome
    
    @ 行云流水
    
    想拿他监控股市变化
    
    2022-07-25 回复
    
    行云流水作者
    · MacOS · Google Chrome
    
    @ geek
    
    在Dashboard settings 里面可以自定义刷新频率
    
    2022-07-25 回复

哈

· MacOS · Google Chrome

板凳

添加邮件通知了

2022-07-09 回复

卡卡

地毯

继续测试

2022-07-08 回复

行云流水作者
· MacOS · Google Chrome

@ 卡卡

谢谢你的评论

2022-07-09 回复

第4楼

我就是测试一下

grafana基于mysql数据源的数据可视化展示

前言

端口流量图

数据来源（zabbix监控采集）

订单统计分析面板

数据源(mysql数据库)

Stat 面板

Bar gauge 面板

Pie chart 面板

Time series 面板

Table 面板

利用prometheus+grafana监控redis-server状态

采用Prometheus+Grafana 监控H3C交换机状态

grafana之mysql数据源设置变量

centos8下grafana的安装与使用

评论 (10)