由于工作需求,导入mysql到多台服务器,因此写了一个shell脚本:
#!/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 " $1 execute [ ${GREEN} SUCCESS ${RESET} ] !" >> ${LOG_FILE} else echo -e " $2 execute [ ${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
转载于:https://blog.51cto.com/bronte/1887881