同步三张表,每次全量同步。利用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"
m y s q l l o g i n < < E O F s e l e c t I F N U L L ( a u t h i d , ′ ′ ) a s a u t h i d , I F N U L L ( e n t c o d e , ′ ′ ) a s e n t c o d e , I F N U L L ( p r o i d , ′ ′ ) a s p r o i d , I F N U L L ( p k g i d , ′ ′ ) a s p k g i d , I F N U L L ( t r a n s m e r n o , ′ ′ ) a s t r a n s m e r n o , I F N U L L ( a u t h s t a t u s , ′ ′ ) a s a u t h s t a t u s , I F N U L L ( s t a r t a u t h d a t e , ′ ′ ) a s s t a r t a u t h d a t e , I F N U L L ( e n d a u t h d a t e , ′ ′ ) a s e n d a u t h d a t e , I F N U L L ( d i s a b l e , ′ ′ ) a s d i s a b l e , I F N U L L ( s t a t u s , ′ ′ ) a s s t a t u s , I F N U L L ( r e m a r k , ′ ′ ) a s r e m a r k , I F N U L L ( c r e a t e t i m e , ′ ′ ) a s c r e a t e t i m e , I F N U L L ( c r e a t o r , ′ ′ ) a s c r e a t o r , I F N U L L ( m o d i f i e d t i m e , ′ ′ ) a s m o d i f i e d t i m e , I F N U L L ( m o d i f i e r , ′ ′ ) a s m o d i f i e r , I F N U L L ( i s d e l e t e , ′ ′ ) a s i s d e l e t e , I F N U L L ( v e r s i o n , ′ ′ ) a s v e r s i o n , I F N U L L ( e x p a n s i o n m o d e , ′ ′ ) a s e x p a n s i o n m o d e , I F N U L L ( p l a t s e r c o d e , ′ ′ ) a s p l a t s e r c o d e , I F N U L L ( a u t h c o d e , ′ ′ ) a s a u t h c o d e , I F N U L L ( r o l e c o d e , ′ ′ ) a s r o l e c o d e f r o m t p n r b a s e a u t h o r i z a t i o n i n t o o u t f i l e ′ mysqllogin << EOF select IFNULL(auth_id ,'') as auth_id , IFNULL(ent_code ,'') as ent_code , IFNULL(pro_id ,'') as pro_id , IFNULL(pkg_id ,'') as pkg_id , IFNULL(trans_mer_no ,'') as trans_mer_no , IFNULL(auth_status ,'') as auth_status , IFNULL(start_auth_date,'') as start_auth_date , IFNULL(end_auth_date ,'') as end_auth_date , IFNULL(disable ,'') as disable , IFNULL(status ,'') as status , IFNULL(remark ,'') as remark , IFNULL(create_time ,'') as create_time , IFNULL(creator ,'') as creator , IFNULL(modified_time ,'') as modified_time , IFNULL(modifier ,'') as modifier , IFNULL(is_delete ,'') as is_delete , IFNULL(version ,'') as version , IFNULL(expansion_mode,'') as expansion_mode , IFNULL(plat_ser_code ,'') as plat_ser_code , IFNULL(auth_code ,'') as auth_code , IFNULL(role_code ,'') as role_code from t_pnr_base_authorization into outfile ' mysqllogin<<EOFselectIFNULL(authid,′′)asauthid,IFNULL(entcode,′′)asentcode,IFNULL(proid,′′)asproid,IFNULL(pkgid,′′)aspkgid,IFNULL(transmerno,′′)astransmerno,IFNULL(authstatus,′′)asauthstatus,IFNULL(startauthdate,′′)asstartauthdate,IFNULL(endauthdate,′′)asendauthdate,IFNULL(disable,′′)asdisable,IFNULL(status,′′)asstatus,IFNULL(remark,′′)asremark,IFNULL(createtime,′′)ascreatetime,IFNULL(creator,′′)ascreator,IFNULL(modifiedtime,′′)asmodifiedtime,IFNULL(modifier,′′)asmodifier,IFNULL(isdelete,′′)asisdelete,IFNULL(version,′′)asversion,IFNULL(expansionmode,′′)asexpansionmode,IFNULL(platsercode,′′)asplatsercode,IFNULL(authcode,′′)asauthcode,IFNULL(rolecode,′′)asrolecodefromtpnrbaseauthorizationintooutfile′{mysqldir}/t_pnr_base_authorization_${currentday}.txt’ CHARACTER SET gb2312 fields terminated by ‘|@@|’ lines terminated by ‘@||@\n’ ;
EOF
if [ ? − n e 0 ] ; t h e n e c h o " ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ ∗ " e c h o " f a i l e d t o g e t t p n r b a s e a u t h o r i z a t i o n d a t a f r o m M Y S Q L : ? -ne 0 ]; then echo "************************************************************" echo "failed to get t_pnr_base_authorization data from MYSQL: ?−ne0];thenecho"∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗∗"echo"failedtogettpn