用脚本实现表的远程准实时同步

23erewr

要同步某个数据库下的少数几个表，轻量级的方案有oracle的高级复制（物化视图方式）和mysql的主从复制，但均需要主库到从库有较好的网络环境，跨机房的话，最好走专线。如果主库到从库的网络环境不好，甚至主库无法直接ping通从库，需要从跳板机跳转呢，此时只有用最原始的方式，手工编写脚本来实现。场景有两种：

一、主表的数据是纯增量型（如cdr话单之类的表，主表只有insert操作），以mysql为例
需求：每天凌晨00：30将昨天的数据从A库同步至C库

网络环境：主库香港库A(172.17.5.14)无法直接访问北京从库C(10.10.10.64)，需要从跳板机深圳B服务器(192.168.40.47)跳转。
这种场景相对简单，以mysql为例子，先在B库上导出数据，脚本片段：
dt=`date -d '-24 hours' +%Y%m%d`
mt=`date -d '-24 hours' +%Y%m`
/usr/bin/mysqldump -uroot -pxxxxxx -h 172.17.5.14 globallink_g g_cdr_gprs_$mt --where="substr(cdr_time_stamp,1,8)='$dt'" --no-create-info --single-transaction >$sqlfile1 2>>$errlogfile
......

if scp -P 2222 $bz_file root\@192.168.40.47:/hkcdr/ >/dev/null 2>>$errlogfile;then
  mv $bz_file /opt/get_hkcdr/bak/
  echo `get_time` "success to copy $bz_file to remote! " >>$logfile
else
  echo `get_time` "fail to copy $bz_file to remote, please read $errlogfile to get detail" >>$logfile   
  exit 1
fi

跳板机B上做端口转发，将本机收到的2222端口转发至10.10.10.64
#ssh -CfNgL 192.168.40.47:2222:10.10.10.64:22 root@192.168.40.47

此定时任务00:15执行，然后在北京库上部署入库定时任务，00:30执行：
#!/bin/bash
# Writed by Weikle in Dec., 2015, import cdr files to beijing bss db in /hkcdr
#
db_name=("globallink_g" "globallink_k" "globallink_ey" "globallink_y")
logfile="/hkcdr/import_hkcdr.log"
errlogfile="/hkcdr/import_hkcdr_error.log"

get_time() {
echo "[`date '+%Y-%m-%d %H:%M:%S'`]:"
}

imp_cdr() {
/usr/bin/mysql -uroot -pxxxxxx $1 <$2
}

for i in ${db_name

}
do
sqlfile="/hkcdr/${i}_cdr_`date -d '-24 hours' +%Y%m%d`.sql"
bzip2 -d $sqlfile.bz2
if imp_cdr $i $sqlfile >/dev/null 2>$errlogfile.$i;then
  mv $sqlfile "$sqlfile".processed
  echo `get_time` "import $sqlfile success!" >>$logfile
else
  cat $errlogfile.$i >>$logfile
  echo `get_time` "fail to import $sqlfile!" >>$logfile
  mv $sqlfile "$sqlfile".err
  exit 1
fi
done

注意：如果入库失败，须将sql文件增加err后缀作为标识。由于主库是增量数据，所以入库的时候，可以不处理以前的err文件，不会引起数据混乱。后续单独处理即可。

二、主表的数据有各种DML（包括insert/update/delete），以oracle为例

这种情况就复杂一些，需要在主表建立触发器，将主表上的DML语句单独存到一个表里去。
触发器片段如下：
create or replace trigger simm_trigger  after insert or update or delete  on uprg_simm_h_f_master for each row
declare
  v_log    varchar2(500):= '';
  errcode  number;
  errmsg   varchar2(100);
  --output_file utl_file.file_type;
begin
--output_file := utl_file.fopen ('RWDIR','simmr_sync.sql', 'A');
if inserting then
   v_log :='insert into uprg_simm_h_f_master values('
   ||''''||:new.imsi_h||''''||','
   ||''''||:new.msisdn_h||''''||','
   ||'null'||','
   ||''''||:new.msisdn_f||''''||','
   ||:new.status||','
   ||:new.sendout||','
   ||''''||:new.instance_name||''''||','
   ||'to_date('||''''||to_char(:new.last_modified_dat,'YYYY-MM-DD HH24-MI-SS')||''''||','||''''||'YYYY-MM-DD HH24-MI-SS'||''''||')'||','
   ||:new.networkid||','
   ||''''||:new.mcc||''''||','
   ||''''||:new.mnc||''''||','
   ||''''||:new.instance_gt||''''||','
   ||''''||:new.product_name||''''||')'||';' ;
......
end if;
  --utl_file.put_line (output_file, v_log);
  --UTL_FILE.FCLOSE(output_file);
  --create table simmr_sync(sql varchar2(500),sync_time date);
  insert into simmr_sync values(v_log,sysdate);
exception
  when others then
  errcode:=sqlcode;
  errmsg:=sqlerrm(sqlcode);--sqlerrm(100) means'ORA-1403:no data found',error code is +100
  insert into simmr_sync_err values(errcode,errmsg,sysdate);
end;

此时，将主表的DML存入了simmr_sync表，如下：


同样，这里每个小时同步一次，比如每个小时的10分将其scp至从库，然后从库每个小时的15分入库，
入库脚本片段如下：
ls -rt /u01/simmr_sync/*.sql|while read aa;do
/u01/app/oracle/product/11.2.0/bin/sqlplus -s 'rwuser/xxxxxx@RWDB'  <<EOF >$db_result
set FEEDBACK off
set TERM  on
set TRIM  on
set TRIMS on
set linesize 500
set pagesize 0
set heading off
@$aa
EOF
  if grep "ORA-" $db_result >/dev/null 2>&1;then
    echo -e "fail to import $aa:sqlplus or sql execution error at `date '+%Y-%m-%d %H:%M:%S'`\n" >>$logfile
    cat $db_result >>$logfile
    mv $aa "$aa".err
    exit 1
  else
    mv $aa "$aa".bak
    echo -e "success to import $aa at `date '+%Y-%m-%d %H:%M:%S'`\n" >>$logfile
    find /u01/simmr_sync -name "*.bak" -mtime 1 -exec rm {} \;
  fi
  done
else
  echo -e "no file to import at `date '+%Y-%m-%d %H:%M:%S'`\n" >>$logfile
fi

注意：这里在读取/u01/simmr_sync/*.sql的时候，必须用ls -rt的方式按时间顺序入库，不能乱，因为数据在主库上可能随时间被反复更新，如果漏掉了前面的sql，单独执行后面的sql会引起数据混乱。