linux_monitor/monitor.sql

/*
 Navicat Premium Data Transfer

 Source Server         : 192.168.226.133_3306
 Source Server Type    : MySQL
 Source Server Version : 100317
 Source Host           : 192.168.226.133:3306
 Source Schema         : monitor

 Target Server Type    : MySQL
 Target Server Version : 100317
 File Encoding         : 65001

 Date: 05/11/2020 10:25:27
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for cpu_percent
-- ----------------------------
DROP TABLE IF EXISTS `cpu_percent`;
CREATE TABLE `cpu_percent`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `time` timestamp(0) NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP(0),
  `percent` double(255, 0) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for disk
-- ----------------------------
DROP TABLE IF EXISTS `disk`;
CREATE TABLE `disk`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `time` timestamp(0) NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP(0),
  `path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `fs_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `total` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `free` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `used` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `used_percent` double UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for info
-- ----------------------------
DROP TABLE IF EXISTS `info`;
CREATE TABLE `info`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `cpu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `cpu_cores` int(11) UNSIGNED NULL DEFAULT NULL,
  `mhz` double(255, 0) UNSIGNED NULL DEFAULT NULL,
  `cache_size` int(11) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for memory
-- ----------------------------
DROP TABLE IF EXISTS `memory`;
CREATE TABLE `memory`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `time` timestamp(0) NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP(0),
  `total` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `free` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `cached` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `used_percent` double UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for netio
-- ----------------------------
DROP TABLE IF EXISTS `netio`;
CREATE TABLE `netio`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `time` timestamp(0) NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP(0),
  `bytes_recv` bigint(255) UNSIGNED NULL DEFAULT NULL,
  `bytes_sent` bigint(255) UNSIGNED NULL DEFAULT NULL,
  `packets_recv` bigint(255) UNSIGNED NULL DEFAULT NULL,
  `packets_sent` bigint(255) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`, `time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Procedure structure for insert_cpu_percent
-- ----------------------------
DROP PROCEDURE IF EXISTS `insert_cpu_percent`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `insert_cpu_percent`(i varchar(255), t int, p DOUBLE)
BEGIN
	#Routine body goes here...
	INSERT INTO `cpu_percent`(`id`, `time`, `percent`) VALUES (i,FROM_UNIXTIME(t),p);
	DELETE FROM `cpu_percent` WHERE UNIX_TIMESTAMP(time) < (t-1800); # 删除半小时之前的数据
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for insert_memory
-- ----------------------------
DROP PROCEDURE IF EXISTS `insert_memory`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `insert_memory`(i varchar(255), t int,total bigint,free bigint, cached bigint, percent DOUBLE)
BEGIN
	#Routine body goes here...
	INSERT INTO `memory`(`id`, `time`, `total`, `free`, `cached`, `used_percent`) VALUES (i,FROM_UNIXTIME(t),total,free,cached,percent);
	DELETE FROM `memory` WHERE UNIX_TIMESTAMP(time) < (t-1800); # 删除半小时之前的数据
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for register_client
-- ----------------------------
DROP PROCEDURE IF EXISTS `register_client`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `register_client`(i varchar(255),cn varchar(255), cc varchar(255), mhz double, cs int)
BEGIN
	DECLARE client_count int;
	select count(*) INTO client_count from info WHERE id = i;
	IF client_count = 0 THEN
		# not exist
		insert into info VALUE (i, cn, cc, mhz, cs);
	else
		# exist
		update info set cpu_name = cn, cpu_cores = cc, mhz = mhz, cache_size = cs WHERE id = i;
	end if;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for select_cpu_percent_with_time
-- ----------------------------
DROP PROCEDURE IF EXISTS `select_cpu_percent_with_time`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `select_cpu_percent_with_time`(i varchar(255),t int,li INT)
BEGIN
	-- i  id
	-- t  time
	-- li limit
	select unix_timestamp(time),percent from cpu_percent where id = i AND UNIX_TIMESTAMP(time)>t ORDER BY time DESC LIMIT li;
	select unix_timestamp(max(time)) from cpu_percent where id = i AND UNIX_TIMESTAMP(time)>t;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for select_memory
-- ----------------------------
DROP PROCEDURE IF EXISTS `select_memory`;
delimiter ;;
CREATE DEFINER=`root`@`%` PROCEDURE `select_memory`(i varchar(255),t int,li INT)
BEGIN
	#Routine body goes here...
	select unix_timestamp(time),total,free,cached,used_percent from memory where id = i AND UNIX_TIMESTAMP(time)>t ORDER BY time DESC LIMIT li;
	select unix_timestamp(max(time)) from memory where id = i AND UNIX_TIMESTAMP(time)>t;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;