Grafana+Mysql+Go实现监控

准备阶段

安装Grafana

# 添加grafana源
vim /etc/yum.repos.d/grafana.repo
[grafana]
name=grafana
baseurl=https://packages.grafana.com/enterprise/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt

# 安装grafana
yum install grafana-enterprise.x86_64 

# 启动grafana
systemctl daemon-reload 
systemctl start grafana-server
systemctl enable grafana-server
systemctl status grafana-server

# 登录
admin/admin

安装mariadb

略。。。

Go模拟写入数据

code

package main

import (
	"database/sql"
	"fmt"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

type user struct {
	id   int
	age  int
	name string
}

//连接数据库、验证数据库
func initDB() (err error) {
	dsn := "root:root@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		fmt.Printf("open failed err:%v\n", err)
		return err
	}

	err = db.Ping()
	if err != nil {
		fmt.Printf("ping failed err:%v\n", err)
		return err
	}
	return nil

}

//插入数据
func insertRowDemo(i int) {
	sqlStr := "insert into grafana(time,value) values(?,?)"
	ret, err := db.Exec(sqlStr, time.Now().Format("2006-01-02 03:04:05.000"), i)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId()
	if err != nil {
		fmt.Printf("get lastinsert ID failed,err:%v\n", err)
		return
	}
	fmt.Printf("insert success,the id is %d.\n", theID)
	// wg.Done()
}

// 更新数据
func updateRowDemo() {
	sqlStr := "update grafana set time= ? where id = ?"
	t1 := time.Now()
	for i := 1; i < 745; i++ {
		ret, err := db.Exec(sqlStr, t1.Format("2006-01-02-15:04"), i)
		if err != nil {
			fmt.Printf("update failed,err:%v\n", err)
			return
		}
		n, err := ret.RowsAffected()
		if err != nil {
			fmt.Printf("get RowsAffected failed,err:%v\n", err)
			return
		}
		fmt.Printf("update success,affected rows:%d\n", n)
		t1 = t1.Add(time.Minute)
	}
}

func main() {
	//连接数据库
	err := initDB()
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
	//模拟插入数据
	for i := 1; i < 745; i++ {
		// wg.Add(1)
		insertRowDemo(i)
		time.Sleep(10 * time.Microsecond)
	}
	//更新插入数据希望格式
	updateRowDemo()
}

mariadb

MariaDB [sql_test]> select * from grafana;
+-----+------------------+-------+
| id  | time             | value |
+-----+------------------+-------+
|   1 | 2021-09-05-15:04 |   256 |
|   2 | 2021-09-05-15:05 |   257 |
|   3 | 2021-09-05-15:06 |   258 |
|   4 | 2021-09-05-15:07 |   259 |
|   5 | 2021-09-05-15:08 |   260 |
|   6 | 2021-09-05-15:09 |   261 |
|   7 | 2021-09-05-15:10 |   262 |
|   8 | 2021-09-05-15:11 |   263 |
|   9 | 2021-09-05-15:12 |   264 |
|  10 | 2021-09-05-15:13 |   265 |
|  11 | 2021-09-05-15:14 |   266 |
|  12 | 2021-09-05-15:15 |   267 |
|  13 | 2021-09-05-15:16 |   268 |
...

配置阶段（grafana）

添加mysql数据源

添加Dashboard

# 此处根据对应数据库的tables的实际情况，即想获取的字段进行修改
SELECT
  UNIX_TIMESTAMP(time) as time_sec,
  value as connections,
  id as id
FROM grafana
WHERE $__timeFilter(time)
ORDER BY time ASC