使用shell将采集到的数据实现批量入到MySQL数据库中

最新推荐文章于 2024-07-19 15:56:03 发布

原创最新推荐文章于 2024-07-19 15:56:03 发布 · 832 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL #shell

运维同时被 2 个专栏收录

9 篇文章

订阅专栏

shell

4 篇文章

订阅专栏

本文介绍了一种通过Shell脚本实现的呼叫数据实时统计方法，并将其存入MySQL数据库的过程。该方案适用于需要频繁监控呼叫状态和服务质量的场景。

业务需求

平台需要收集呼叫相关信息，涉及以下caps呼叫总数，队列情况，处理时延，程序链接数，收集之后的数据格式如下
2019-03-26 15:31:01,10.41.47.27,16,0,0,29
2019-03-26 15:31:02,10.41.47.27,12,0,2,29
2019-03-26 15:31:03,10.41.47.27,9,0,0,29
2019-03-26 15:31:04,10.41.47.27,19,0,0,29
2019-03-26 15:31:05,10.41.47.27,17,0,0,29
2019-03-26 15:31:06,10.41.47.27,15,0,0,29
2019-03-26 15:31:07,10.41.47.27,14,0,0,29
每秒统计一次，现需要将这些采集数据入库到MySQL中

数据库格式

CREATE TABLE `mon_rm_secdata` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NULL,
  `server_ip` varchar(255) DEFAULT NULL,
  `caps_num` int(10) DEFAULT NULL,
  `dea_time` int(10) DEFAULT NULL,
  `que_num` int(10) DEFAULT NULL,
  `cms_num` int(10) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

shell实现方式

#!/bin/bash
#
db_server='x.x.x.x'
db_port='3306'
db_user='user'
db_pass='passwd'
db_name="openser"
table_name="mon_rm_secdata"
import_file="./mysqltest.sh "

cat $import_file |while read line
  do
     data_time=$(echo $line|cut -d "," -f1)
     server_ip=$(echo $line|cut -d "," -f2)
     caps_num=$(echo $line|cut -d "," -f3)
     dea_num=$(echo $line|cut -d "," -f4)
     que_num=$(echo $line|cut -d "," -f5)
     cms_num=$(echo $line|cut -d "," -f6)
     echo "$data_time,$server_ip,$caps_num,$dea_num,$que_num,$cms_num"
     sleep 0.001
     mysql  -h$db_server -u$db_user -p$db_pass -P$db_port -e "insert into $db_name.$table_name(date_time,server_ip,caps_num,dea_time,que_num,cms_num) values('$data_time','$server_ip','$caps_num','$dea_num','$que_num','$cms_num')";
  done