MySQL数据异构同步至Sybase

同步三张表，每次全量同步。利用select into outfile 导出，利用bcp导入。
利用expect 进行远程拷贝和删除。此方法也可复用至其他数据库。
sync_pnrinfo_f_mysql_t_dw.sh 主操作脚本
exec_on_remote.expect expect远程执行命令脚本
copy_remotefile_to_local.expect expectscp远程执行命令脚本

sync_pnrinfo_f_mysql_t_dw.sh
#!/bin/bash
#sync from GParner to dw_trandata
#table mapping as following:
**# t_pnr_base_authorization > t_sync_d_pnr_base_auth

source ~/.bash_profile

if [ ! -f /usr/bin/expect ]; then
echo “can not find file: /usr/bin/expect”
exit -1
fi

echo “************************************************************************************************”
echo “BEGIN date '+%Y-%m-%d %H:%M:%S' Sync GPartner Tables From MySQL To Sybase”
echo “************************************************************************************************”

mysqlHost=“10.156.236.222”
mysqllogin=“mysql -uexportData -pvcittt -h${mysqlHost} -c UserCenter”
mysqldir="/var/lib/mysql-files"
mysqlHostUser=“root”
mysqlHostPassword=“vcittt”

dwdir="/home/sybase/scripts/data/"
dwlogin="-Udw_trandata -PGome_DwUserWater -Syfuatsybdb01"

currentday=date '+%Y%m%d%H%M%S'
mysql_sybase_table_names="/tmp/mysql_sybase_table_names.tmp"

$mysqllogin<<EOFselectIFNULL(aut{h}_{i}d{,}^{\prime \prime })asaut{h}_{i}d,IFNULL(en{t}_{c}ode{,}^{\prime \prime })asen{t}_{c}ode,IFNULL(pr{o}_{i}d{,}^{\prime \prime })aspr{o}_{i}d,IFNULL(pk{g}_{i}d{,}^{\prime \prime })aspk{g}_{i}d,IFNULL(tran{s}_{m}e{r}_{n}o{,}^{\prime \prime })astran{s}_{m}e{r}_{n}o,IFNULL(aut{h}_{s}tatus{,}^{\prime \prime })asaut{h}_{s}tatus,IFNULL(star{t}_{a}ut{h}_{d}ate{,}^{\prime \prime })asstar{t}_{a}ut{h}_{d}ate,IFNULL(en{d}_{a}ut{h}_{d}ate{,}^{\prime \prime })asen{d}_{a}ut{h}_{d}ate,IFNULL(disable{,}^{\prime \prime })asdisable,IFNULL(status{,}^{\prime \prime })asstatus,IFNULL(remark{,}^{\prime \prime })asremark,IFNULL(creat{e}_{t}ime{,}^{\prime \prime })ascreat{e}_{t}ime,IFNULL(creator{,}^{\prime \prime })ascreator,IFNULL(modifie{d}_{t}ime{,}^{\prime \prime })asmodifie{d}_{t}ime,IFNULL(modifier{,}^{\prime \prime })asmodifier,IFNULL(i{s}_{d}elete{,}^{\prime \prime })asi{s}_{d}elete,IFNULL(version{,}^{\prime \prime })asversion,IFNULL(expansio{n}_{m}ode{,}^{\prime \prime })asexpansio{n}_{m}ode,IFNULL(pla{t}_{s}e{r}_{c}ode{,}^{\prime \prime })aspla{t}_{s}e{r}_{c}ode,IFNULL(aut{h}_{c}ode{,}^{\prime \prime })asaut{h}_{c}ode,IFNULL(rol{e}_{c}ode{,}^{\prime \prime })asrol{e}_{c}odefrom{t}_{p}n{r}_{b}as{e}_{a}uthorizationintooutfil{e}^{\prime }${mysqldir}/t_pnr_base_authorization_${currentday}.txt’ CHARACTER SET gb2312 fields terminated by ‘|@@|’ lines terminated by ‘@||@\n’ ;
EOF

if [ $?-ne0];thenecho"\ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast \ast "echo"failedtoget{t}_{p}n$