导出远程mysql数据库中的表_shell脚本实现导出远程mysql数据库表数据至本地

bin/main.sh脚本内容

#!/bin/bash

#作用：用于同步远程mysql数据库表数据至本地

#作者：丁艺博

source /etc/profile

source ~/.bash_profile

export LANG=en_US.UTF-8

export RUN_HOME=$(cd "$(dirname "$0")"; echo "${PWD%/*}")

source $RUN_HOME/bin/utils.sh

source $RUN_HOME/cfg/db.cfg

JOB_MARK=pension

#临时目录及目标目录定义

TEMP_PATH=/data/mysqldata/mysqldata/database_${JOB_MARK}_temp

TARGET_PATH=/data/oradata/external_table/${JOB_MARK}_service/data

#参数处理

if [[ "$1" == "-help" || "$1" == "-h" || $# -gt 1 ]]; then

echo "usage: $0 """-D cfg=cfg_file_path_x -D starttime=YYYY-MM-DD -D endtime=YYYY-MM-DD""

exit

fi

if [[ $# -ne 1 ]]; then

_loadConf "${RUN_HOME}/cfg/sync_service.cfg"

else

parameter=$1

for i in `echo $parameter|sed "s/-D//g"`

do

key=`echo $i|cut -d"=" -f1`

value=`echo $i|cut -d"=" -f2`

if [ $key == "cfg" ]; then cfg=$value; fi

if [ $key == "starttime" ]; then starttime=$value; fi

if [ $key == "endtime" ]; then endtime=$value; fi

done

echo $cfg

if [ "$cfg" == "" ]; then

_loadConf "${RUN_HOME}/cfg/sync_service.cfg"

elif [ -f "$cfg" ]; then

_loadConf "$cfg"

else

echo "cfg file is not exist!"

exit

fi

_isValidDate $starttime

if [ $? -ne 0 ]; then echo "date format error!"; exit; fi

_isValidDate $endtime

if [ $? -ne 0 ]; then echo "date format error!"; exit; fi

_initDate "$starttime" "$endtime"

fi

if [ ! -d ${TEMP_PATH} ]; then

mkdir -p ${TEMP_PATH}

fi

#同步任务执行主体，循环遍历参数文件执行，如有需要可变更为多进程执行

for cfg in ${CFG_[@]}

do

dburl=`echo ${cfg}|cut -d":" -f4`

database=`echo ${cfg}|cut -d":" -f1`

table=`echo ${cfg}|cut -d":" -f2`

sqlfile=`echo ${cfg}|cut -d":" -f3`

source ${RUN_HOME}/$sqlfile

if [[ "$dburl" == "default" ]]; then

echo ${sql_}

${DB_URL_} -D${database} -N -e "${sql_}" >${TEMP_PATH}/${database}.${table}.dat

if [ $? -eq 0 ]; then

echo "TEMP_PATH: ${TEMP_PATH}/${database}.${table}.dat"

echo "TARGET_PATH: ${TARGET_PATH}/${database}.${table}.dat"

if [ -f ${TARGET_PATH}/${database}.${table}.dat ]; then rm -f ${TARGET_PATH}/${database}.${table}.dat; fi

mv ${TEMP_PATH}/${database}.${table}.dat ${TARGET_PATH}/${database}.${table}.dat

else

echo "${table} sync fail"

fi

fi

done

bin/utils.sh脚本内容

#工具脚本

function _formatDate()

{

_ymdhms=$1

_ymd=`echo ${_ymdhms:0:10}`

_year=`echo ${_ymdhms:0:4}`

_month=`echo ${_ymdhms:5:2}`

_day=`echo ${_ymdhms:8:2}`

_hour=`echo ${_ymdhms:11:2}`

_minute=`echo ${_ymdhms:14:2}`

_second=`echo ${_ymdhms:17:2}`

}

function _loadConf()

{

i_=0

for cv_ in `cat ${1}`;do

if [ "${cv_:0:1}" == "#" -o -z $cv_ ];then continue; fi

CFG_[$i_]=${cv_}

echo "配置文件内容${i_}:${CFG_[${i_}]}"

i_=`expr ${i_} + 1`

done

}

isDate_=0

function _isValidDate(){

if [[ "$1" == "" ]]; then

return 0

fi

date -d "$1" "+%F"|grep -q "$1" 2>/dev/null

if [ $? = 0 ]; then

((isDate_++))

return 0

else

return 1

fi

}

#初始化时间变量

RUN_TIME=`date -d yesterday +"%Y-%m-%d %H:%M:%S"`

START_TIME="`date -d yesterday +%Y-%m-%d` 00:00:00"

END_TIME="`date -d yesterday +%Y-%m-%d` 23:59:59"

START_TIMESTAMP=`date -d "${START_TIME}" +%s`

END_TIMESTAMP=`date -d "${END_TIME}" +%s`

#根据输入日期初始化时间变量

function _initDate()

{

if [[ "$1" != "" ]]; then

START_TIME="$1 00:00:00"

START_TIMESTAMP=`date -d "${START_TIME}" +%s`

fi

if [[ "$2" != "" ]]; then

END_TIME="$2 23:59:59"

END_TIMESTAMP=`date -d "${END_TIME}" +%s`

fi

}

cfg/db.cfg配置文件

DB_URL_="/usr/bin/mysql -h10.1.31.12 -udyb -pdyb123"

cfg/sync_service.cfg配置文件

#database_name:table_name:sql_statement_file:[db_connect]

testdb:test:sql/test.sql:default

sql/test.sql文件

sql_="select concat_ws(x"01",REPLACE(IFNULL(ORG_ID,""),"

",""),REPLACE(IFNULL(jsjg_unified_org_no,""),"

","")) from test where create_time between "${START_TIME}" and "${END_TIME}";"