mysql导出shell脚本_mysql导入导出csv的shell脚本小例子

导出：

#!/bin/bash

host="172.16.8.191"

port="3306"

user="root"

password="123456"

dbname="db_indicator_drawline"

dbset="--default-character-set=utf-8 -A"

#cmd="show databases"

#databases=$(mysql -h${host} -P${port} -u${user} -p${password} -e "${cmd}")

#for database in ${databases}

#do

#echo ${database}

cmd="show tables"

tables=$(mysql -h${host} -P${port} -u${user} -p${password} ${dbname} -e "${cmd}")

for table in ${tables}

do

if ((13 == ${#table}))

then

echo ${table}

for fid in 10104

do

cmd="SELECT qxmode, 8 as fid,period,datetime,data FROM ${table} where fid = ${fid} and datetime <= 20180901000000"

echo ${cmd}

mysql -h${host} -P${port} -u${user} -p${password} ${dbname} -N -e "${cmd}" > ./tmp/${fid}/${fid}-${table}.csv

done

fi

done

#done

导入：

#!/bin/bash

#Error encountered execution terminated

#set -e

#do something...

#set +e

host="172.16.8.191"

port="3306"

user="root"

password="123456"

dbname="test_db_indicator_drawline"

dbset="--default-character-set=utf-8 -A"

path=./tmp/

dirlist=$(ls -l ${path} | grep '^d' | awk -F' ' '{print $NF}')

for dir in ${dirlist}

do

echo "dir:${path}${dir}" >> dir.log

files=$(ls ${path}${dir})

for file in ${files}

do

echo ${file}

fid=${file%-*}

tablecsv=${file#*-}

table=${tablecsv%.*}

echo ${fid} ${table}

cmd="load data infile '${path}${dir}/${file}' replace into table ${table} (qxmode,fid,period,datetime,data)"

echo ${cmd}

mysql -h${host} -P${port} -u${user} -p${password} ${dbname} -e "${cmd}"

done

done

set 命令