使用Linux Crontab 定时任务执行 MySQL load data 脚本向数据库中导入数据

目录

建库建表

准备入库日志文件

编写load data 脚本

编辑定时任务crontab

脚本授权

验证

真实场景

场景

测试准备

创建脚本

编辑定时任务crontab

脚本授权

验证

需要注意的问题

数据重复入库

空数据入库

问题复现及解决

---

上一篇我们介绍了Linux Crontab的相关的知识点，包括crontab表达式语法及含义、查看定时任务执行日志、定时任务管理（启动、停止等），感兴趣的可以看一下 

Linux Crontab 定时任务使用示例，这一篇记录一下我实际遇到的场景：使用定时任务入库日志数据。

如果我的博客对你有帮助，欢迎进行评论✏️✏️、点赞👍👍、收藏⭐️⭐️，满足一下我的虚荣心💖🙏🙏🙏 。

建库建表

首先，准备下测试表，我们登录下数据库：

mysql -u root -p

输入我的数据库密码登录成功如下：

 然后，建一个测试库test，如下：

create database test;

最后，切换到刚创建的数据库test，建一个测试表t_user，只有两个字段，如下：

use test;

CREATE TABLE t_user (
  id int NOT NULL,
  username varchar(36) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果不放心，可以使用 show databases;查看数据库是否创建成功，使用 show tables;查看表是否创建成功。

准备入库日志文件

待读取的test.log内容如下：

1sdzyyjh
2sdzyaaa

编写load data 脚本

在/home/cron 目录下新建脚本读取test.log文件将数据入库：

vim /home/cron/test.sh

内容如下：

#!/bin/sh
tbl_name=t_user
load_data_log=/home/cron/t_user_insertdb.log
final_load_name=/home/cron/test.log
if [ $? -eq 0 ]; then
    echo "success"
else
    echo "fail"
fi
echo ${final_load_name}
load_sql="load data infile '${final_load_name}' 
ignore 
into table ${tbl_name} 
character set utf8 
fields terminated by 'sdzy' 
lines terminated by '\n'
(id,username)
"
mysql 2>>${load_data_log} -uroot -p123456 --local-infile=1  << EOF
use test;
$load_sql;
exit
EOF
echo "$?"
exit

编辑定时任务crontab

vim  /etc/crontab

内容如下：

* * * * * root run-parts /home/cron

脚本授权

chmod 755 test.sh

systemctl start crond.service

验证

启动定时器后我们查询下数据库表中是否有数据：

SELECT * FROM t_user;

如上，表里已经有数据了，说明入库成功，需要注意的是，对于入库后的文件要及时删除或者移动到别的目录，要不会重复入相同数据，如下：

真实场景

场景

我们的业务数据不是即时入库的，因为数据量比较大，数据在最终确定前还要频繁修改，不适合即时入库，所以我们的业务数据都是在最终确定后记录log日志文件到特定目录下，如/home/sdzy/db目录中，然后通过定时任务执行入库脚本将/home/sdzy/db目录中的各种业务数据log文件load data到MySQL数据库相应的数据表中，为防止数据重复导入我把导入后的文件移动到了/home/sdzy/db/final目录中。

偷个懒，就不新建目录了，以下模拟一下这个场景，还是在上面的/home/cron 目录新建了四个文件： student.log，student.20220929095345.log，student.20220929095341.log，teacher.20220929095341.log 。新增两个脚本分别读取/home/cron目录下所有的以student开头并包含日期的log文件和以teacher开头并包含日期的log文件分别将数据插入到t_student和t_teacher表中。像student.log这种没有日期后缀的，是logback还没触发日志回滚，暂时先不入库。

测试准备

准备入库log：

student.log中有两条数据，如下：

student.20220929095341.log中有两条数据，如下：

student.20220929095345.log中有两条数据，如下：

teacher.20220929095341.log中有两条数据，如下：

准备数据表：

再建两个表t_student 和 t_teacher，建表语句如下：

CREATE TABLE t_student (
  id int NOT NULL,
  username varchar(36) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE t_teacher (
  id int NOT NULL,
  username varchar(36) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建脚本

student.sh内容如下：

#!/bin/sh
tbl_name=t_student
LogNameDATE=`date '+%Y%m%d'`
load_data_log=/home/cron/t_studentr_insertdb$LogNameDATE.log
final_file_path=/home/sdzy/db/final/
src_data_path=/home/sdzy/db/
files=$(ls $src_data_path)
for filename in ${src_data_path}/*
do
  temp_file=`basename $filename`
  is_match=$(echo $temp_file | grep "student\.[0-9]\{4\}")
  if [[ "$is_match" != "" ]]
  then
      src_load_name=${src_data_path}${temp_file}
      final_load_name=${final_file_path}${temp_file}
	  mv $src_load_name $final_load_name
      load_sql="load data infile '${final_load_name}' into table ${tbl_name} character set utf8 fields terminated by 'sdzy,' lines terminated by '\n' (id,username)"
	  
mysql 2>>${load_data_log} -uroot -p123456 --local-infile=1  << EOF
use test;
$load_sql;
exit
EOF	  
if [ $? -eq 0 ]; then
    echo " sql success" >> $load_data_log
else
    echo "sql fail" >> $load_data_log
    continue
fi
	  
done
exit

teacher.sh内容如下：

#!/bin/sh
tbl_name=t_teacher
LogNameDATE=`date '+%Y%m%d'`
load_data_log=/home/cron/t_teacher_insertdb$LogNameDATE.log
final_file_path=/home/sdzy/db/final/
src_data_path=/home/sdzy/db/
files=$(ls $src_data_path)
for filename in ${src_data_path}/*
do
  temp_file=`basename $filename`
  is_match=$(echo $temp_file | grep "teacher\.[0-9]\{4\}")
  if [[ "$is_match" != "" ]]
  then
      src_load_name=${src_data_path}${temp_file}
      final_load_name=${final_file_path}${temp_file}
	  mv $src_load_name $final_load_name
      load_sql="load data infile '${final_load_name}' into table ${tbl_name} character set utf8 fields terminated by 'sdzy,' lines terminated by '\n' (id,username)"
	  
mysql 2>>${load_data_log} -uroot -p123456 --local-infile=1  << EOF
use test;
$load_sql;
exit
EOF	  
if [ $? -eq 0 ]; then
    echo " sql success" >> $load_data_log
else
    echo "sql fail" >> $load_data_log
    continue
fi
	  
done
exit

编辑定时任务crontab

vim  /etc/crontab

内容如下：

* * * * * root /home/cron/student.sh
* * * * * root /home/cron/teacher.sh

脚本授权

chmod 755 student.sh
chmod 755 teacher.sh

systemctl start crond.service

验证

启动定时器后我们查询下数据库表中是否有数据：

看下t_student表，如下：

看下t_teacher表，如下：

如上，两个表中都有数据，说明导入成功，如果没有数据请看下日志文件。

需要注意的问题

数据重复入库

上面虽然将log文件中数据成功入库，但是并没有把文件删除或移动到别的目录，下次遍历/home/cron/db目录时数据会重复入库并，所以实际使用的时候注意将load完成之后的文件删掉或移动到别的目录存档。 

空数据入库

由于logback文件轮转不是由时钟驱动的，而是取决于日志记录事件的到达。

例如，在 2002 年 3 月 8 日，假设 fileNamePattern 设置为yyyy-MM-dd (每日翻转)，则文件轮转发生在午夜后第一个事件到达时，比如在00:23:47秒来了一个记录日志的实践，那么实际上文件将在在 2002 年 3 月 9 日 00:23:47轮转，而非2002 年 3 月 9 日 00:00:00。

而实际使用过程中，我们的业务数据不管有没有后续日志记录事件都是要入库，为了在没有后续日志记录事件到达的情况下也能将数据及时入库，我们自己做了事件触发，比如，每天凌晨一点向日志文件追加一条空日志触发上一天文件的轮转，形如：

receiveLogLogger.info("");

但是这样这条空数据也是会入库的，下面会进行演示，并解决这个问题。

问题复现及解决

停止定时任务，清空t_teacher表数据：

systemctl stop crond.service

truncate table t_teacher;

teacher.20220929095341.log中数据修改，如下：

启动定时任务，一分钟后再次查询t_teacher表，如下：

如上，发现空行也入库了。

我这里出了个小问题，因为我建表的时候id没有自增，也没有赋值，插入的时候报了如下错误：

ERROR 1366 (HY000) at line 3: Incorrect integer value: '' for column 'id' at row 2
sql fail

需要修改下 load data语句  在insert table前加一个 ignore 就可以啦，具体自行百度MySQL load data语法。

解决空行入库问题也很简单，只要有一个唯一索引就可以了，但是这样也有个小问题，就是有且只有一条空数据会入库，暂时没找到更好解决办法，我们实际的场景并没有很好的字段可以做唯一键，所以我们也没使用这种方式，而是用了一个很笨的方式，定时删除空数据。

给t_teacher表的username加唯一索引：

alter table t_teacher add unique(username);

清空表后，等下次定时任务执行后，结果如下：

如上，没有重复入数据，空数据也只入了一条。