mysql导入表shell脚本
由于工作需求，导入mysql到多台服务器，因此写了一个shell脚本：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
#!/bin/bash
######################################
# create date_time:2016-12-31 15:44:36
# Notice: 1. 先将要执行更新的sql文件放在一个目录下面，然后将目录赋值给DATA_PATH变量。
# 2. 数据库的一些变量修改，数据库名仅仅填写:ddd2_word即可
# 4. 再serlist.txt中写入需要导入的主机名和id
#serlist.txt格式如下，不要填写id和host，仅仅填写你需要你的id和主机名就可以。
#id host
#1 188.188.1.10
######################################
RED=\\e[1m\\e[31m
GREEN=\\e[1m\\e[32m
YELLOW=\\e[1m\\e[33m
RESET=\\e[0m
# Stored in a table where you want to import
DATA_PATH='data'
# Mysql some of the variables
MYSQL_DBNAME='db_world'
MYSQL_USER='root'
MYSQL_PASS='root'
MYSQL_PATH='/usr/local/mysql/bin'
# Time format function
DATATIME=`date "+%Y-%m-%d %H:%M:%S"`
# Log output path
FILE_NAME=`echo $0 |cut -d'.' -f1`
LOG_FILE="./logs/${FILE_NAME}.log"
LOGDIR=`dirname ${LOG_FILE}`
[ ! -d ${LOGDIR} ] && mkdir -p ${LOGDIR}
seconds=0
nanoseconds=0
function countime {
seconds=0
nanoseconds=0
startimeS1=$(echo $1 | cut -d '.' -f 1)
startimeS2=$(echo $1 | cut -d '.' -f 2)
endtimeS1=$(echo $2 | cut -d '.' -f 1)
endtimeS2=$(echo $2 | cut -d '.' -f 2)
#把当前时间转化为Linux时间
starttimeS1Linux=`date -d"$startimeS1" +%s`
endtimeS1Linux=`date -d"$endtimeS1" +%s`
seconds=`expr$endtimeS1Linux - $starttimeS1Linux`
nanoseconds=`expr $endtimeS2 - $startimeS2`
}
function error {
if [ $? -eq 0 ];then
echo -e " $1execute [ ${GREEN} SUCCESS ${RESET} ] !" >> ${LOG_FILE}
else
echo -e " $2execute [ ${RED} FAIL${RESET} ] ^~^" >> ${LOG_FILE}
exit 1
fi
}
function importable {
startime=`date +"%Y-%m-%d %H:%M:%S.%N"`
while read id host
do
if [ ! -z $id ] &&[ ! -z $host ];then
startTime=`date +"%Y-%m-%d %H:%M:%S.%N"`
echo "${DATATIME} --- From serlist.txt read $host, began to import ${MYSQL_DBNAME} tables!" >> ${LOG_FILE}
for tab in `ls ${DATA_PATH}`
do
echo "${DATATIME} --- Began to $host $id import $tab ...." >> ${LOG_FILE}
${MYSQL_PATH}/mysql -u"${MYSQL_USER}" -p"${MYSQL_PASS}" -h"$host" ${MYSQL_DBNAME}_vn${id} < ${DATA_PATH}/$tab
error "${DATATIME} --- Import $tab " "${DATATIME} --- Import $tab"
done
endTime=`date +"%Y-%m-%d %H:%M:%S.%N"`
countime "$startTime" "$endTime"
echo -e "${GREEN} Import to $host RunTime: ${RESET}${RESET} ${seconds}.${nanoseconds}s">> ${LOG_FILE}
fi
done < serlist.txt
endtime=`date +"%Y-%m-%d %H:%M:%S.%N"`
countime "$startime" "$endtime"
echo -e "${GREEN} import table finished ! ${RESET}" >> ${LOG_FILE}
echo -e "${YELLOW} The total runtime import table: ${RESET} ${seconds}.${nanoseconds}s" >> ${LOG_FILE}
}
if [ ! -d ${DATA_PATH} ];then
echo "SQL file directory is not specified !"
exit 1
fi
if [ ! -f serlist.txt ];then
echo "The serlist.txt file does not exist !"
exit 1
fi
importable
页:
[1]