在项目中有个需求,需要重ftp下载数据,然后更新到mysql数据库中,由于文件较大,也不希望在数据库服务器上部署java应用,所以写了一段shell脚本,定时重ftp下载数据,然后跟新到mysql数据库中,定时任务这里没写。
#!/bin/bash
MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'"
#echo "MYSQL_CSV_FORMAT=$MYSQL_CSV_FORMAT"
HOST=ftp地址
PORT=端口号
USERNAME=用户名
PASSWORD=密码
DST_FOLDER=/data/mysql/import/
DST_FILENAME=/2013123111303.csv
i=1
csv=30.csv
date=$(date +%Y%m%d%H)
#filename=$(($date-$i))$csv
array=($(($date-$i))$csv $(($date-2))$csv $(($date-3))$csv $(($date-4))$csv)
for name in ${array[@]}
do
echo "----------------------------------"
echo ${name}
/usr/kerberos/bin/ftp -u -n $HOST $PORT<<EOF
user $USERNAME $PASSWORD
binary
lcd $DST_FOLDER
get ${name}
quit
#bye
EOF
cd $DST_FOLDER
deletefile=$(($(date +%Y%m%d)-$i))*
echo "delete files ="$deletefile
rm -rf $deletefile
isfile=$(ls|grep ${name})
if [ "${name}" = "$isfile" ]; then
echo "download success"
echo "data import start"
mysql<<MYSQLEOF
use vwdas_newcp
truncate table tab_currentweather_grid;
load data infile '$DST_FOLDER$name' into table tab_currentweather_grid $MYSQL_CSV_FORMAT;
select count(1) total from tab_currentweather_grid;
quit
MYSQLEOF
echo "data import end"
break 1;
else
echo "download fail!"
fi
done
echo "----------------------------------"