数据比对脚本 - Powered by MinDoc

文件说明
使用方式

文件说明

#source mysql
s_m_db_type="mysql"
s_m_db_user="zcbus"
s_m_db_pwd="123456"
s_m_db_host="172.17.58.146"
s_m_db_port="3306"

#target oracle
t_o_db_type="oracle"
t_o_db_user="zcbus"
t_o_db_pwd="123456"
t_o_db_host="172.17.58.145"
t_o_db_port="1521"
t_o_service_name="oracle12c"

#target mysql
t_m_db_type="mysql"
t_m_db_user="zcbus"
t_m_db_pwd="123456"
t_m_db_host="172.17.58.147"
t_m_db_port="3306"

#target sqlserver
t_s_db_type="sqlserver"
t_s_db_user="test"
t_s_db_pwd="123456"
t_s_db_host="172.17.104.185"
t_s_db_port="1433"
t_s_db_name="test"

#note
info(){
  dt=`date +"%Y%m%d%H%M%S"`
  echo "info :"${dt}" $1"
}
error(){
  dt=`date +"%Y%m%d%H%M%S"`
  echo "error:"${dt}" $1"
  exit
}
warn(){
  dt=`date +"%Y%m%d%H%M%S"`
  echo "warn :"${dt}" $1"
}
wait(){
  sleep 3
}
debug(){
  if [ ${DEBUG} -eq 1 ];then
    dt=`date +"%Y%m%d%H%M%S"`
    echo "debug :"${dt}" $1"
  fi
}

note(){
string=$1
for((x=1;x<=30;x++));do
   echo -n "#"
done
echo -n " ${string} "
length=${#string}
z=`expr 100 - 30 - ${length}`
for((y=1;y<=${z};y++));do
   echo -n "#"
done
echo ""
}

tablelist [源端目标端用户，表名]

mm.info3015|dbo.info3015_1

com.sh

#!/bin/bash
#set -x
curpath=`dirname $0`
. ./env
cd ${curpath}

check_exe(){
   msg=$1
   check_flag=$2
   if [ ${check_flag} -eq 0 ];then
      info "${msg} successfully ..."
   else
      warn "${msg} failed , please check !!!"
      #exit
   fi
}

com()
{
   s_args="$1"
   s_db_type=`echo ${s_args}|awk '{print $1}'`
   s_db_user=`echo ${s_args}|awk '{print $2}'`
   s_db_pwd=`echo ${s_args}|awk '{print $3}'`
   s_db_host=`echo ${s_args}|awk '{print $4}'`
   s_db_port=`echo ${s_args}|awk '{print $5}'`
   t_args="$2"
   t_db_type=`echo ${t_args}|awk '{print $1}'`
   t_db_user=`echo ${t_args}|awk '{print $2}'`
   t_db_pwd=`echo ${t_args}|awk '{print $3}'`
   t_db_host=`echo ${t_args}|awk '{print $4}'`
   t_db_port=`echo ${t_args}|awk '{print $5}'`
   tabinfo="$3"
   ifrepair="$4"
   s_table_owner=`echo ${tabinfo} |awk -F "|" '{print $1}'|awk -F "." '{print $1}'`
   s_table_name=`echo ${tabinfo} |awk -F "|" '{print $1}' |awk -F "." '{print $2}'`
   t_table_owner=`echo ${tabinfo} |awk -F "|" '{print $2}' |awk -F "." '{print $1}'`
   t_table_name=`echo ${tabinfo} |awk -F "|" '{print $2}' |awk -F "." '{print $2}'`
   if [ ${s_table_owner}"X" == "X" ];then
      error "Must Set source owner "
   fi
   if [ ${s_table_name}"X" == "X" ];then
      error "Must Set source table name "
   fi
   if [ ${t_table_owner}"X" == "X" ];then
      error "Must Set target owner "
   fi
   if [ ${t_table_name}"X" == "X" ];then
      error "Must Set target table name"
   fi
   echo "      ******   ${s_db_type} to ${t_db_type} ::: ${s_table_owner}.${s_table_name}---->${t_table_owner}.${t_table_name}   ******"
   c_str="table_compare -source ${s_db_type}:${s_db_user}/'${s_db_pwd}'@${s_db_host}:${s_db_port}"
   if [ ${s_db_type} == "oracle" ];then
      s_service_name=`echo ${s_args}|awk '{print $6}'`
      c_str="${c_str}/${s_service_name} -source_table ${s_table_owner}.${s_table_name}"
   elif [ ${s_db_type} == "mysql" ];then
      c_str="${c_str} -source_table ${s_table_owner}.${s_table_name}"
   elif [ ${s_db_type} == "postgresql" -o ${s_db_type} == "sqlserver" ];then
      s_db_name=`echo ${s_args}|awk '{print $6}'`
      c_str="${c_str} -source_table ${s_db_name}.${s_table_owner}.${s_table_name}"
   else
      error "${s_db_type} (source) is not support,please check!!!"
      exit
   fi
   c_str="${c_str} -target ${t_db_type}:${t_db_user}/'${t_db_pwd}'@${t_db_host}:${t_db_port}"
   if [ ${t_db_type} == "oracle" ];then
      t_service_name=`echo ${t_args}|awk '{print $6}'`
      c_str="${c_str}/${t_service_name} -target_table ${t_table_owner}.${t_table_name}"
   elif [ ${t_db_type} == "mysql" ];then
      c_str="${c_str} -target_table ${t_table_owner}.${t_table_name}"
   elif [ ${t_db_type} == "postgresql" -o ${t_db_type} == "sqlserver" ];then
      t_db_name=`echo ${t_args}|awk '{print $6}'`
      c_str="${c_str} -target_table ${t_db_name}.${t_table_owner}.${t_table_name}"
   else
      error "${t_db_type} (target) is not support,please check!!!"
      exit
   fi
   if [ ${ifrepair} -eq 1 ];then
     c_str=${c_str}" -repair "
   fi

   echo ${c_str}
   ../${c_str}  > tmp 2>&1
   #echo ${c_str}
   cat tmp |grep "begin compare content of table" 
   cat tmp |tail -n 8|grep -Ev "Speed|report file generated|^$" 
   mv report_* ${curpath}/report/
   if [ ${ifrepair} -eq 1 ];then
     mv repair_* ${curpath}/repair/
   fi
   rm -rf src.* tgt.* tmp 
   echo "------------------------------------------------------------------------------------------------------"
}

main()
{
   mkdir -p ${curpath}/report/
   mkdir -p ${curpath}/repair/
   s_connect_strs_mysql=`cat env|grep "^s_m_"|awk -F "=" '{print $2}'|sed 's/\"//g'`
   t_connect_strs_oracle=`cat env|grep "^t_o_"|awk -F "=" '{print $2}'|sed 's/\"//g'`
   t_connect_strs_sqlserver=`cat env|grep "^t_s_"|awk -F "=" '{print $2}'|sed 's/\"//g'`
   t_connect_strs_mysql=`cat env|grep "^t_m_"|awk -F "=" '{print $2}'|sed 's/\"//g'`
   echo "------------------------------------------------------------------------------------------------------"
   while read -r line 
   do
      tabinfo=${line}
      #com "${s_connect_strs_mysql}" "${t_connect_strs_oracle}" "${tabinfo}"
      com "${s_connect_strs_mysql}" "${t_connect_strs_sqlserver}" "${tabinfo}" 0
      #com "${s_connect_strs_mysql}" "${t_connect_strs_mysql}" "${tabinfo}"
   done < tablelist
}

main
[oracle@interface com]$ cat com.sh
#!/bin/bash
#set -x
curpath=`dirname $0`
. ./env
cd ${curpath}

check_exe(){
   msg=$1
   check_flag=$2
   if [ ${check_flag} -eq 0 ];then
      info "${msg} successfully ..."
   else
      warn "${msg} failed , please check !!!"
      #exit
   fi
}

com()
{
   s_args="$1"
   s_db_type=`echo ${s_args}|awk '{print $1}'`
   s_db_user=`echo ${s_args}|awk '{print $2}'`
   s_db_pwd=`echo ${s_args}|awk '{print $3}'`
   s_db_host=`echo ${s_args}|awk '{print $4}'`
   s_db_port=`echo ${s_args}|awk '{print $5}'`
   t_args="$2"
   t_db_type=`echo ${t_args}|awk '{print $1}'`
   t_db_user=`echo ${t_args}|awk '{print $2}'`
   t_db_pwd=`echo ${t_args}|awk '{print $3}'`
   t_db_host=`echo ${t_args}|awk '{print $4}'`
   t_db_port=`echo ${t_args}|awk '{print $5}'`
   tabinfo="$3"
   ifrepair="$4"
   s_table_owner=`echo ${tabinfo} |awk -F "|" '{print $1}'|awk -F "." '{print $1}'`
   s_table_name=`echo ${tabinfo} |awk -F "|" '{print $1}' |awk -F "." '{print $2}'`
   t_table_owner=`echo ${tabinfo} |awk -F "|" '{print $2}' |awk -F "." '{print $1}'`
   t_table_name=`echo ${tabinfo} |awk -F "|" '{print $2}' |awk -F "." '{print $2}'`
   if [ ${s_table_owner}"X" == "X" ];then
      error "Must Set source owner "
   fi
   if [ ${s_table_name}"X" == "X" ];then
      error "Must Set source table name "
   fi
   if [ ${t_table_owner}"X" == "X" ];then
      error "Must Set target owner "
   fi
   if [ ${t_table_name}"X" == "X" ];then
      error "Must Set target table name"
   fi
   echo "      ******   ${s_db_type} to ${t_db_type} ::: ${s_table_owner}.${s_table_name}---->${t_table_owner}.${t_table_name}   ******"
   c_str="table_compare -source ${s_db_type}:${s_db_user}/'${s_db_pwd}'@${s_db_host}:${s_db_port}"
   if [ ${s_db_type} == "oracle" ];then
      s_service_name=`echo ${s_args}|awk '{print $6}'`
      c_str="${c_str}/${s_service_name} -source_table ${s_table_owner}.${s_table_name}"
   elif [ ${s_db_type} == "mysql" ];then
      c_str="${c_str} -source_table ${s_table_owner}.${s_table_name}"
   elif [ ${s_db_type} == "postgresql" -o ${s_db_type} == "sqlserver" ];then
      s_db_name=`echo ${s_args}|awk '{print $6}'`
      c_str="${c_str} -source_table ${s_db_name}.${s_table_owner}.${s_table_name}"
   else
      error "${s_db_type} (source) is not support,please check!!!"
      exit
   fi
   c_str="${c_str} -target ${t_db_type}:${t_db_user}/'${t_db_pwd}'@${t_db_host}:${t_db_port}"
   if [ ${t_db_type} == "oracle" ];then
      t_service_name=`echo ${t_args}|awk '{print $6}'`
      c_str="${c_str}/${t_service_name} -target_table ${t_table_owner}.${t_table_name}"
   elif [ ${t_db_type} == "mysql" ];then
      c_str="${c_str} -target_table ${t_table_owner}.${t_table_name}"
   elif [ ${t_db_type} == "postgresql" -o ${t_db_type} == "sqlserver" ];then
      t_db_name=`echo ${t_args}|awk '{print $6}'`
      c_str="${c_str} -target_table ${t_db_name}.${t_table_owner}.${t_table_name}"
   else
      error "${t_db_type} (target) is not support,please check!!!"
      exit
   fi
   if [ ${ifrepair} -eq 1 ];then
     c_str=${c_str}" -repair "
   fi

   echo ${c_str}
   ../${c_str}  > tmp 2>&1
   #echo ${c_str}
   cat tmp |grep "begin compare content of table" 
   cat tmp |tail -n 8|grep -Ev "Speed|report file generated|^$" 
   mv report_* ${curpath}/report/
   if [ ${ifrepair} -eq 1 ];then
     mv repair_* ${curpath}/repair/
   fi
   rm -rf src.* tgt.* tmp 
   echo "------------------------------------------------------------------------------------------------------"
}

main()
{
   mkdir -p ${curpath}/report/
   mkdir -p ${curpath}/repair/
   s_connect_strs_mysql=`cat env|grep "^s_m_"|awk -F "=" '{print $2}'|sed 's/\"//g'`
   t_connect_strs_oracle=`cat env|grep "^t_o_"|awk -F "=" '{print $2}'|sed 's/\"//g'`
   t_connect_strs_sqlserver=`cat env|grep "^t_s_"|awk -F "=" '{print $2}'|sed 's/\"//g'`
   t_connect_strs_mysql=`cat env|grep "^t_m_"|awk -F "=" '{print $2}'|sed 's/\"//g'`
   echo "------------------------------------------------------------------------------------------------------"
   while read -r line 
   do
      tabinfo=${line}
      #com "${s_connect_strs_mysql}" "${t_connect_strs_oracle}" "${tabinfo}" 1
      com "${s_connect_strs_mysql}" "${t_connect_strs_sqlserver}" "${tabinfo}" 0
      #com "${s_connect_strs_mysql}" "${t_connect_strs_mysql}" "${tabinfo}" 0
   done < tablelist
}
main

使用方式

编辑com.sh脚本，选择需要制定源和目标，脚本将按照tablelist中列表，以及配置的源和目标数据库开始进行数据比对
针对出现问题的数据，可以添加进行数据修复设置，会自动生成修复数据，通过tableimp。将修复数据输送到指定目标数据库中。
针对需要修复的内容可以通过bsddump工具查看

文档更新时间: 2021-11-04 15:28 作者：阿力