将采样文件通过脚本及定时任务按时自动导入数据库

最新推荐文章于 2022-08-19 22:45:58 发布

ljhynl

最新推荐文章于 2022-08-19 22:45:58 发布

阅读量483

点赞数

本文链接：https://blog.csdn.net/Z1998hx0919/article/details/107030122

版权

mysql 同时被 2 个专栏收录

12 篇文章 1 订阅

订阅专栏

Linux

12 篇文章 0 订阅

订阅专栏

实验伪码

自动导入数据到库
假设当前执行时间为2020-06-28 21:44:01
1、做成脚本配置成定时执行，入库脚本一个小时执行一次，将上一个小时的采样记录导入数据库中
2、计算出当前的文件名 $file_name = 2020_06_28_20.txt ,也就是上一个小时的采样文件
3、记录日志，记录当前加载的数据文件名（ 2020_06_28_20.txt）
4、通过load data语句将/root/script/2020_06_28 _20.txt文件导入数据表
5、echo 当前输出时间 load_data.sh （输出的数据文件名）2020_06_28 _20.txt execute load data successful”

shell脚本

#!/bin/bash
. /etc/profile
. ~/.bash_profile
file_one=$(date -d '-1 hour' +%Y_%m_%d_%H)
echo $file_one.txt
mysql -h 127.0.0.1 -P3306 -uroot -pljh cpu_mem_disk -e "LOAD DATA LOCAL INFILE '/root/script/$file_one.txt' INTO TABLE mem FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (mdate,cpu,mem,disk)";
currTime=$(date +"%Y-%m-%d %H:%M:%S")
echo $currTime
echo load_data.sh execute load data  successful!

配置定时任务

0 */1 * * * /root/test/load_data.sh >> /root/test/log_load_data  # log_load_data用来记录每小时运行的输出结果

实验结果

1、执行脚本结果
在这里插入图片描述

2、日志文件每小时记录结果
在这里插入图片描述
3、数据表显示结果

MariaDB [cpu_mem_disk]> select * from mem;
+---------------------+-----+------+------+
| mdate               | cpu | mem  | disk |
+---------------------+-----+------+------+
| 2020-06-29 20:00:01 |  14 | 3383 |   13 |
| 2020-06-29 20:01:01 |  10 | 3383 |   13 |
| 2020-06-29 20:02:01 |   0 | 3383 |   13 |
| 2020-06-29 20:03:01 |   4 | 3383 |   13 |
| 2020-06-29 20:04:01 |   0 | 3383 |   13 |
| 2020-06-29 20:05:01 |   4 | 3382 |   13 |
| 2020-06-29 20:06:01 |   0 | 3383 |   13 |
| 2020-06-29 20:07:01 |   4 | 3382 |   13 |
| 2020-06-29 20:08:01 |   4 | 3382 |   13 |
| 2020-06-29 20:09:01 |   0 | 3383 |   13 |
| 2020-06-29 20:10:01 |   4 | 3382 |   13 |
| 2020-06-29 20:11:01 |   0 | 3382 |   13 |
| 2020-06-29 20:12:01 |   7 | 3381 |   13 |
| 2020-06-29 20:13:01 |   0 | 3380 |   13 |
| 2020-06-29 20:14:01 |   4 | 3381 |   13 |
| 2020-06-29 20:15:01 |   4 | 3381 |   13 |
| 2020-06-29 20:16:01 |   4 | 3380 |   13 |
| 2020-06-29 20:17:01 |   4 | 3380 |   13 |
| 2020-06-29 20:18:01 |   0 | 3380 |   13 |
| 2020-06-29 20:19:01 |   4 | 3381 |   13 |
| 2020-06-29 20:20:01 |  55 | 3381 |   13 |
| 2020-06-29 20:21:01 |   4 | 3378 |   13 |
| 2020-06-29 20:22:01 |   4 | 3378 |   13 |
| 2020-06-29 20:23:01 |   0 | 3378 |   13 |
| 2020-06-29 20:24:01 |   0 | 3377 |   13 |
| 2020-06-29 20:25:01 |   0 | 3377 |   13 |
| 2020-06-29 20:26:01 |   4 | 3378 |   13 |
| 2020-06-29 20:27:01 |   4 | 3378 |   13 |
| 2020-06-29 20:28:01 |   0 | 3377 |   13 |
| 2020-06-29 20:29:01 |   4 | 3378 |   13 |
| 2020-06-29 20:30:01 |   4 | 3377 |   13 |
| 2020-06-29 20:31:01 |   0 | 3378 |   13 |
| 2020-06-29 20:32:02 |   0 | 3377 |   13 |
| 2020-06-29 20:33:01 |   0 | 3378 |   13 |
| 2020-06-29 20:34:01 |   4 | 3377 |   13 |
| 2020-06-29 20:35:01 |   4 | 3377 |   13 |
| 2020-06-29 20:36:01 |   0 | 3378 |   13 |
| 2020-06-29 20:37:01 |   4 | 3378 |   13 |
| 2020-06-29 20:38:01 |   4 | 3377 |   13 |
| 2020-06-29 20:39:01 |   0 | 3377 |   13 |
| 2020-06-29 20:40:01 |   0 | 3378 |   13 |
| 2020-06-29 20:41:01 |   4 | 3378 |   13 |
| 2020-06-29 20:42:01 |   0 | 3378 |   13 |
| 2020-06-29 20:43:02 |   0 | 3377 |   13 |
| 2020-06-29 20:44:01 |   0 | 3378 |   13 |
| 2020-06-29 20:45:01 |   0 | 3377 |   13 |
| 2020-06-29 20:46:01 |   4 | 3378 |   13 |
| 2020-06-29 20:47:01 |   0 | 3377 |   13 |
| 2020-06-29 20:48:01 |   4 | 3377 |   13 |
| 2020-06-29 20:49:01 |   4 | 3378 |   13 |
| 2020-06-29 20:50:01 |   0 | 3378 |   13 |
| 2020-06-29 20:51:01 |   0 | 3378 |   13 |
| 2020-06-29 20:52:01 |   7 | 3378 |   13 |
| 2020-06-29 20:53:01 |   0 | 3378 |   13 |
| 2020-06-29 20:54:01 |   0 | 3378 |   13 |
| 2020-06-29 20:55:01 |   0 | 3377 |   13 |
| 2020-06-29 20:56:01 |   0 | 3377 |   13 |
| 2020-06-29 20:57:01 |   0 | 3378 |   13 |
| 2020-06-29 20:58:01 |   4 | 3377 |   13 |
| 2020-06-29 20:59:01 |   0 | 3377 |   13 |
+---------------------+-----+------+------+
60 rows in set (0.00 sec)