shell脚本处理文件

项目简介

具体代码没有实施，可能路径啥的需要调整！整体框架是行的通的！
目的：添加员工名单
用shell脚本从服务器拿数据到本地，将数据存到mysql，进行处理后存到本地，然后再发送到服务器.
tip(sh代码直接用window写入txt文件（换行格式为/r/n），然后再将txt名字改成name.sh时，上传到linux。执行sh脚本时会报错！建议直接在linux里编辑sh脚本（换行格式为LF）)

代码文件

创建数据库

CREATE TABLE `testdatas` (
  `sno` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sex` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inputdate` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `export_status` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `testdata` (
  `sno` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sex` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inputdate` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `export_status` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

main.sh

. ./log_hp.conf#下文有文件内容
. ./dir_hp.conf#里面可以写一些常量 比如path=/root/data/input HOST=yourhost
INPUT1=testdata
OUTPUT1=testdata2
INPUTSPNAME=datafrom
OUTPUTSPNAME=datato
path1=/export/nfs/IF/receive/HP/
path2=/root/if/hp/
HOST=w3v2_rizap_hulft
scp ${HOST}:${path1}/${INPUT1}* ${path2}
ssh ${HOST} "sudo mv ${path1}/${INPUT1}* ${path1}/LOG/"
if [-d ${path2}/${INPUT1}* ]; then 
#cd path
sh ${INPUTSPNAME}.sh
log_info "${INPUTSPNAME} start"
sh ${OUTPUTSPNAME}.sh
log_info "${OUTPUTSPNAME} start"
#发送到服务器
scp ${path2}/${OUTPUT1}* ${HOST}:${path1}/
else
log_info "file not found ..."
fi

log_hp.conf文件

function log_info() {
  echo $(date '+%Y-%m-%d %H:%M:%S') [INFO] $* >> hp_batch.log
}

datafrom.sh

#!/bin/sh
OUTPUT1=testdata2
path2=/root/if/hp/
SPNAME=File_to_Mysql
TABLENAME=testdata
# "消除 SJIS→UTF8変換
sed -e s/$'"'//g path${FILENAME}*${EXP} | iconv -f cp932 -t utf8 |  tr -d '\r' > ${path2}/testdataUTF8
mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASS} -D${DB_NAME} --default-character-set=utf8 -e"truncate table ${TABLENAME}"
mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASS} -D${DB_NAME} --default-character-set=utf8 -e"LOAD DATA LOCAL INFILE '${path2}/testdataUTF8' INTO TABLE ${TABLENAME} FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'"
mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASS} -D${DB_NAME} --default-character-set=utf8 -e"call ${SPNAME}"

datato.sh

#!/bin/sh
OUTPUT=testdataOUT
OUTPUT1=testdata
path2=/root/if/hp/
SPNAME=Mysql_to_File
# 调用存储过程
mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASS} -D${DB_NAME} --default-character-set=utf8 -e"call ${SPNAME}" > ${path2}/${OUTPUT1}*

# 用t替换逗号，删除第一行、UTF8→SJIS转换
sed -e s/$'\t'/,/g ${path2}/${OUTPUT}* | sed -e '1d' | iconv -f utf8 -t cp932 > ${path2}/${OUTPUT1}*
mysql -h${DB_HOST} -u${DB_USER} -p${DB_PASS} -D${DB_NAME} --default-character-set=utf8 -e"update ${TABLENAME} set HP_export_status = 1 where HP_export_status = 0;"

procedure.sql

-- File_to_mysql
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `File_to_mysql`()
begin

update
	testdata
set
	inputdate = date_format(timestampadd(hour, 9, now()), '%Y%m%d%H%i%s'),
    export_status = 0
where
	export_status is null
;
-- testdatas是一个员工总表，目的是删除总表员工编号与新加员工编号相同的编号
delete from
	testdatas
where
	sno in (select sno from testdata)
;
-- 插入新员工
insert into
	testdatas
select * from testdata
;
-- Index重构 方便快速查询
drop index material_index on testdatas;
create index material_index on testdatas (sno);
end ;;
DELIMITER ;

-- Mysql_to_File
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `Mysql_to_File`()
begin
-- 查询新员工（状态为0的）
select * from
    testdatas
where
    export_status = 0
;
end ;;
DELIMITER ;

log_hp.conf日志输出

function log_info() {
  echo $(date '+%Y-%m-%d %H:%M:%S') [INFO] $* >> jm_batch.log
}

function slack() {
  local msg="$*"
  local slackapi=https://hooks.slack.com/services/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
  local username=jeansmate@rizap-jm
  icon=jeansmate1
  msg2=`echo $msg | sed -e 's/"/\\\\"/g'`
#  /usr/bin/curl -q -X POST --data-urlencode "payload={\"username\":\"${username}\",\"text\":\"<!channel> ${msg2}\",\"icon_emoji\":\":${icon}:\"}" ${slackapi}
}