shell如何定时导入mysql数据到oracle

shell如何定时导入mysql数据到oracle并不只看表面做法，关键还要理解相关条件、限制和后续影响。

1、业务场景

A系统负责开单，使用mysql数据库，B系统需要开单数据，使用oracle

2、实现思路

开单数据不可能来一条转移一条，只能定时任务复制一个时间段的数据

考虑业务上的故障可能需要补数，脚本分为两个sh,一个跑定时任务，每5分钟传入时间段给另一个脚本执行复制

先由A系统编写录单信息sql，联合查询出B系统需要的信息，从mysql导出文件，再通过ctl文件用sqlldr导入B系统oracle数据库

3、参考案例

1.mysql的录单信息查询 ,tu_doc_info.sql

SELECT a.Logistics_Id as DOC_ID,b.Order_Id as PO_MARK,a.Created_Dot_Code as SEND_COMP,...from dot_logistics a LEFT JOIN dot_order b ON a.Logistics_Id = b.Logistics_IdLEFT JOIN dot_logistics_package c on a.Logistics_Id = c.Logistics_IdLEFT JOIN ky_ydserver.gs d on a.Created_Dot_Code = d.bmWHERE a.Create_Time BETWEEN '#1' and '#2';

2.mysql导出语法测试,imp5m_wd.sh

#!/bin/sh# ./imp5m_wd.sh "2013-10-21 00:00:00" "2013-10-21 00:04:59"mysqlhost=127.0.0.1mysqluser=rootmysqlpwd=root#替换sql文件中的时间区间sed  -e "s!#1!$1!g" -e "s!#2!$2!g" tu_doc_info.sql >tu_doc_info_2.sql#获取sql语句内容expsql=$(cat tu_doc_info_2.sql)LOC_TS_FILE=output.txt# -N不要表头  -D 选择数据库mysql -N -s -r --default-character-set=utf8 -h${mysqlhost} -P3306 -u${mysqluser} -p${mysqlpwd} -D ydky_dot -e "${expsql}" > $LOC_TS_FILE

3.oracle控制文件 tu_doc_info.ctl

LOAD DATACHARACTERSET UTF8INFILE #1 APPEND INTO TABLE TU_DOC_INFO#2FIELDS TERMINATED BY x'09'TRAILING NULLCOLS(DOC_ID,PO_MARK,SEND_COMP,FIRST_CENT,RECV_COMP,LAST_CENT,OBJ_NAME,PACK_TYP,OBJ_TYP,OBJ_WEI,CASH_WEI,OBJ_VOL,OBJ_QTY,PROD_TYP,SRVC_LEVEL,SEND_TYP,PAY_TYP,DELIV_AMT,PRES_AMT,PRES_EXP,UP_EXP,OTH_EXP,TOT_AMT,CUST_NAME,SEND_PROV,SEND_CITY,SEND_CTRY,CUST_COMP,SEND_ADDR,SEND_MOB_TEL,SIGN_MSG,SEND_TEL,CUST_RECV_NAME,CUST_RECV_COMP,RECV_PROV,RECV_CITY,RECV_CTRY,RECV_ADDR,RECV_MOB_TEL,DELIV_MSG,RECV_TEL,DOC_CMT,ORDER_TIME DATE 'YYYY-MM-DD HH24:MI:SS',ENTRY_MAN,ALT_1,ALT_2,ALT_3,ALT_4,ALT_5,ALT_6,ALT_7,DOC_SRC,PO_TYP,LOAN_AMT,OBJ_SN)

4.oracle 导入测试 imp5m_wd.sh

#!/bin/sh# ./imp5m_wd.sh ./output.txt 2LOC_USER=rootLOC_PASS=root#修改ctl的导入文件和分表的后缀sed  -e "s!#1!'$1'!g" -e "s!#2!$2!g" tu_doc_info.ctl > tu_doc_info_2.ctlsqlldr userid=$LOC_USER/[email protected]/orcl control=tu_doc_info_2.ctl

5.整合导出和导入

传入查询时间，执行成功返回0

#!/bin/sh# ./imp5m_wd.sh "2013-10-21 00:00:00" "2013-10-21 00:04:59"source /home/oracle/.bash_profile# 1-1 paraTSK_PRX=$1TSK_BEG=$2# 1-2 genTSK_CUR_DAT=$(date +%s -d "$TSK_BEG")TSK_CUR=${TSK_BEG//[- :]/}TSK_DAY=${TSK_CUR:0:8}TSK_MIN=${TSK_CUR:8:4}TSK_MON=${TSK_DAY:4:2}TSK_FLG=`printf %02d $((10#$TSK_MON % 3 + 1))`TSK_PRT=${TSK_DAY:6:2}# 1-3 cfg dbmysqlhost=10.19.205.73mysqluser=ggsmysqlpwd=**************LOC_USER=ggsLOC_PASS=**********# 1-4 cfg pathLOC_BASE=/u02/dab/shelltestLOC_TS_ORD=${LOC_BASE}/wdorderLOC_TS_IMP=${LOC_TS_ORD}/${TSK_DAY}LOC_DATA_PATH=${LOC_TS_IMP}/${TSK_DAY}_${TSK_MIN}LOC_TS_FILE=${LOC_TS_IMP}/${TSK_DAY}_${TSK_MIN}.txt# 1-5 CHECKif [ ! -d "$LOC_TS_IMP" ]; then    mkdir "$LOC_TS_IMP"ficd ${LOC_BASE}# update expsql and expfromMysqlsed  -e "s!#1!$TSK_PRX!g" -e "s!#2!$TSK_BEG!g" ${LOC_BASE}/tu_doc_info.sql > ${LOC_DATA_PATH}.sqlexpsql=$(cat ${LOC_DATA_PATH}.sql)mysql -N -s -r --default-character-set=utf8 -h${mysqlhost} -P3306 -u${mysqluser} -p${mysqlpwd} -D ydky_dot -e "${expsql}" > $LOC_TS_FILEif [ "$?" != "0" ]; then    exit 128fiif [ ! -e "$LOC_TS_FILE" ]; thenecho "not exists $LOC_TS_FILE"     exit 127fi# 1-5 gen script and importsed  -e "s!#1!'$LOC_TS_FILE'!g" -e "s!#2!$TSK_FLG!g" ${LOC_BASE}/tu_doc_info.ctl > ${LOC_DATA_PATH}.ctlecho "sqlldr userid=$LOC_USER/[email protected]/orcl control=${LOC_DATA_PATH}.ctl"sqlldr userid=$LOC_USER/[email protected]/orcl control=${LOC_DATA_PATH}.ctlif [ "$?" != "0" ]; then    exit 128fiexit 0

6,编写定时任务的imp5m_wd_call.sh，每5分钟执行一次

用一个updata.txt标识执行时间，防止脚本维护期间数据遗漏

#!/bin/sh# configRANGE=9DELAY=2LOC_WORK=/u02/dab/shelltestLOC_DATA_DTA=${LOC_WORK}/wdorder/cd ${LOC_WORK}while [ 1 ]do        # 1 get time from file        timepre_t=`head -1 imp5m_wdorder_call_update.txt`        timepre_s=$(date +%s -d "$timepre_t")        timepre=$(date +%Y%m%d%H%M%S -d "$timepre_t")        # 2 get cur work time (need change range->300 min->3)        timecur_s=$(($timepre_s + $RANGE))        timecur_t=$(date '+%Y-%m-%d %H:%M:%S' -d "1970-01-01 UTC $timecur_s seconds");        timecur=$(date +%Y%m%d%H%M%S -d "$timecur_t")        # 3 get next time        timenxt_s=$(($timepre_s + $RANGE + 1))        timenxt_t=$(date '+%Y-%m-%d %H:%M:%S' -d "1970-01-01 UTC $timenxt_s seconds");        # 4 get now sys time        timenow_t=$(date '+%Y-%m-%d %H:%M:%S' -d '-1 min')        timenow_s=$(date +%s -d "$timenow_t")        # 5 if have task        if [ $timecur_s -lt $timenow_s ]; then                # 5-1 hour                hourpre=`expr substr $timepre 9 2`                # 5-2 do by day                echo "!!!begin<$(date)> [${timepre_t}, ${timecur_t}]"                ./imp5m_wd.sh "${timepre_t}" "${timecur_t}"                if [ "$?" == "0" ]; then                    echo "$timenxt_t" > imp5m_wdorder_call_update.txt                    echo "$timenxt_t" > ${LOC_DATA_DTA}/imp5m_wdorder_call_update.txt                    sleep 1                    echo "!!!done <$(date)>done"                else                    echo "   error for wait"                    sleep $DELAY                fi        else                # 4-8 delay                echo "!!!sleep<$(date)>"                sleep $DELAY        fidone

3、踩坑过程

1. mysql导出可以是任何格式文件，导出csv会遇到格式错误，一条记录没有任何分隔符的占在一个单元格；
2. 导出xls格式没问题，sqlldr导入不支持
3. txt导出分隔符是tab,不是空格，如果ctl文件用空格分隔，时间格式的数据会把日期和时间当两个字段
4. 导出如果带表头，导入oracle需要添加参数skip=1，跳过首行

4、总结