公司测试环境同步数据需求量巨大，每天都要导出大量生产数据到测试环境， 写了一个自动导数的工具，支持并行导出并自动导入到 测试环境，使用时需自己指定放有 导出过滤语句的文件，需放开生产到测试的网络环境。
#/bin/bash
source /usr/local/greenplum-db/greenplum_path.sh
export local_dir=/backup/csh
USAGE ()
{
echo ""
echo "Usage: -ds -dt -Ds -Dt -hs -ht -p -pt -us -ut -Us -Ut -f -w";
echo "Copydump Parameter Infomation: ";
echo " Parameter -ds: Source Database Name [源数据库名]";
echo " Parameter -dt: Target Database Name [目标数据库名]";
echo " Parameter -Ds: Source Outputfile Directory [源库数据输出路径]";
echo " Parameter -Dt: Target Copyfile Directory [目标库数据存放路径]";
echo " Parameter -hs: Source Database Host [源库主机IP地址]";
echo " Parameter -ht: Target Database Host [目标库主机IP地址]";
echo " Parameter -p: Source Database Port [源数据库端口号]";
echo " Parameter -pt: Target Database Port [目标数据库端口号]";
echo " Parameter -us: Source Database Username [源数据库用户名]";
echo " Parameter -ut: Target Database Username [目标数据库用户名]";
echo " Parameter -Us: Source Host Username [源库主机的用户名]";
echo " Parameter -Ut: Target Host Username [目标库主机的用户名]";
echo " Parameter -w: Workflow Number [工作流号]";
echo " Parameter -f: Copy SQLFile Directory,Default Value: /backup/csh/copysql.sql [源库copysql文件路径]";
echo " Parameter -pa: Parallel Processes [并行度]";
echo ""
echo " EasyMode: [简易模式：]";
echo " run (copydump 87) can copy data to 192.168.218.87 ['copydump 87'可拷贝数据至开发库]";
echo " run (copydump 163) can copy data to 192.168.218.163 ['copydump 163'可拷贝数据至开发库]";
echo "";
exit 1;
}
if [ $# -lt 2 ]
then
if [ x"$1" == x"87" ]
then
export source_database=dp_bidb
export target_database=dp_dev
export source_ip=10.249.15.22
export target_ip=192.168.218.87
export source_hostuser=gpadmin
export target_hostuser=gpadmin
export source_dbuser=gpadmin
export target_dbuser=gpadmin
export source_port=9432
export target_port=5432
export source_dir=/data2/copy
export target_dir=/u01/csh
echo ""
read -p "Now Input the Workflow Number: " num
if [ x"$num" == x"" ]
then
echo "You Must Input the Workflow Number! Run 'copydump --help' to Get Usage"
echo ""
exit
else
export jira_num="$num"
fi
echo ""
read -p "Now Input Your SQLFile Directory( Default SQLFile is $local_dir/copysql.sql ): " dir
if [ x"$dir" == x"" ]
then
export sql_text=$local_dir/copysql.sql
else
export sql_text="$dir"
fi
echo ""
read -p "Now Input Copy Parallel Processes( Default Parallel is 4 ): " par
if [ x"$par" == x"" ]
then
export parallel=4
else
export parallel="$par"
fi
elif [ x"$1" == x"163" ]
then
export source_database=dp_bidb
export target_database=dp_dw163
export source_ip=10.249.15.22
export target_ip=192.168.218.163
export source_hostuser=gpadmin
export target_hostuser=sgpadmin
export source_dbuser=gpadmin
export target_dbuser=sgpadmin
export source_port=9432
export target_port=6432
export source_dir=/data2/copy
export target_dir=/data1/csh
echo ""
read -p "Now Input the Workflow Number: " num
if [ x"$num" == x"" ]
then
echo "You Must Input the Workflow Number! Run 'copydump --help' to Get Usage"
echo ""
exit
else
export jira_num="$num"
fi
echo ""
read -p "Now Input Your SQLFile Directory( Default SQLFile is $local_dir/copysql.sql ): " dir
if [ x"$dir" == x"" ]
then
export sql_text=$local_dir/copysql.sql
else
export sql_text="$dir"
fi
echo ""
read -p "Now Input Copy Parallel Processes( Default Parallel is 4 ): " par
if [ x"$par" == x"" ]
then
export parallel=4
else
export parallel="$par"
fi
else
USAGE
fi
else
export sql_text=""
export source_database=""
export target_database=""
export source_ip=""
export target_ip=""
export source_hostuser=""
export target_hostuser=""
export source_dbuser=""
export target_dbuser=""
export source_port=""
export target_port=""
export source_dir=""
export target_dir=""
export jira_num=""
while getopts ds:dt:Ds:Dt:hs:ht:p:pa:pt:us:ut:Us:Ut:f:w OPTION ;
do
case "$OPTION" in
ds) source_database="$OPTARG" ;;
dt) target_database="$OPTARG" ;;
Ds) source_dir="$OPTARG" ;;
Dt) target_dir="$OPTARG" ;;
hs) source_ip="$OPTARG" ;;
ht) target_ip="$OPTARG" ;;
p) source_port="$OPTARG" ;;
pa) parallel="$OPTARG" ;;
pt) target_port="$OPTARG" ;;
us) source_dbuser="$OPTARG" ;;
ut) target_dbuser="$OPTARG" ;;
Us) source_hostuser="$OPTARG" ;;
Ut) target_hostuser="$OPTARG" ;;
f) sql_text="$OPTARG" ;;
w) jira_num="$OPTARG" ;;
\?) #usage statement
USAGE;
;;
esac
done
if [ x"$source_database" == x"" ]
then
echo "-ds Source Database Name Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_database" == x"" ]
then
echo "-dt Target Database Name Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$source_dir" == x"" ]
then
echo "-Ds Source Outputfile Directory Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_dir" == x"" ]
then
echo "-Dt Target Outputfile Directory Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$source_ip" == x"" ]
then
echo "-hs Source Database Host Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_ip" == x"" ]
then
echo "-ht Target Database Host Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$parallel" == x"" ]
then
echo "-pa Target Database Parallel Default Value is 4 "
export parallel=4
else
export parallel="$parallel"
fi
if [ x"$source_port" == x"" ]
then
echo "-p Source Database Port Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_port" == x"" ]
then
echo "-pt Target Database Port Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$source_dbuser" == x"" ]
then
echo "-us Source Database Username Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_dbuser" == x"" ]
then
echo "-ut Target Database Username Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$source_hostuser" == x"" ]
then
echo "-Us Source Host Username Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_hostuser" == x"" ]
then
echo "-Ut Target Host Username Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$sql_text" == x"" ]
then
echo "-f Copy SQLFile Directory Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$jira_num" == x"" ]
then
echo "-w Workflow Number Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
fi
#开始复制程序
echo '--------------------------------------------------------------------'
echo ""
echo 'Use ('$sql_text') Copy Data From ('$source_ip'):('$source_database') To ('$target_ip'):('$target_database')'
read -p "Do You Want To Continue ? Please Input (yes\no): " char
if [ "$char" == "yes" ]
then
echo ""
mkdir -p $local_dir/$jira_num
#copy源库数据到指定文件夹下
while read line
do
#获取表名
export table_nm=`echo "$line" | awk '{print $4}'`
echo `date` > $local_dir/copy_error.log 2>&1
#判断查询脚本是否可执行
psql -d "$source_database" -p "$source_port" -h "$source_ip" -U "$source_dbuser" -c " copy ( ${line} limit 1 ) to stdout " >> $local_dir/copy_error.log 2>&1
if [ $? -eq 0 ]
then
echo $jira_num': Test Copy Table '$table_nm' Successfully ' >> $local_dir/copy.log
else
#不可执行抛出异常
echo `date` >> $local_dir/copy.log
echo $jira_num':Test Copy Table '$table_nm' With Error,Check /data1/csh/copy_error.log For More Information' >> $local_dir/copy.log
echo ""
echo '########################################################################################'
echo 'Copy Table '$table_nm' With Error !!! Cat '$local_dir'/copy_error.log For More Information'
echo '########################################################################################'
echo ""
exit
fi
done < $sql_text
if [ "$?" != 0 ]
then
exit
fi
#开并行
i=1
n=`cat "$sql_text" | wc -l`
if [ $n -lt $parallel ]
then
export parallel="$n"
fi
while [ $i -le $n ]
do
scopy()
{
line=`head -n $i $sql_text | tail -n 1`
typeset -l tab
tab="$line"
table_name=`echo "$tab" | awk '{print $4}'`
echo `date` >> $local_dir/copy.log
echo $jira_num': Copy Table '$table_name' Start ' >> $local_dir/copy.log
psql -d $source_database -p $source_port -h $source_ip -U $source_dbuser -c " copy (${tab}) to stdout " | gzip > $local_dir/$jira_num/$table_name.gz
echo `date` >> $local_dir/copy.log
echo 'Copy Table '$table_name
echo $jira_num': Copy Table '$table_name' Successfully ' >> $local_dir/copy.log
}
m=1
while [ $m -le $parallel ]
do
scopy &
i=`expr $i + 1`
m=`expr $m + 1`
done
while [ 1 == 1 ]
do
copy_num=`ps -ef | grep -i copy | grep -v grep | wc -l`
if [ $copy_num -gt 2 ]
then
sleep 1
else
break
fi
done
done
wait
#while read name
#do
#typeset -l tab
#tab="$name"
#export table_name=`echo "$tab" | awk '{print $4}'`
#echo `date` >> $local_dir/copy.log
#echo $jira_num': copy table '$table_name' start ' >> $local_dir/copy.log
#psql -d $source_database -p $source_port -h $source_ip -U $source_dbuser -c " copy (${tab}) to stdout " | gzip > $local_dir/$jira_num/$table_name.gz
#echo `date` >> $local_dir/copy.log
#echo 'copy table '$table_name
#echo $jira_num': copy table '$table_name' successfully ' >> $local_dir/copy.log
#done < $sql_text
#把导出的数据库文件传至58中间库
#echo ""
#echo 'Now Scp the Copy File to 192.168.5.58 !'
#echo '--------------------------------------------------------------------'
#echo ""
#scp -r $source_hostuser@$source_ip:$source_dir/$jira_num $local_dir
#if [ "$?" -eq 0 ]
#then
# echo `date` >> $local_dir/copy.log
# echo $jira_num': Copy File to 192.168.5.58 Start ' >> $local_dir/copy.log
#else
# echo ""
# echo 'scp file to 192.168.5.58 faild,check your password! and run this commond to continue: sh /tmp/scp_error/continue.sh' | tee -a $local_dir/copy.log
# echo ""
# exit
#fi
#echo `date` >> $local_dir/copy.log
#echo $jira_num': copy file to 192.168.5.58 end ' >> $local_dir/copy.log
#把导出的数据文件复制到目标库
echo ""
echo 'Now Scp The Copy File To '$target_ip' !'
echo '--------------------------------------------------------------------'
echo ""
smakdir(){
echo "#!/usr/bin/expect" > $local_dir/spawn_makdir.sh
echo "set password sgpadmin" >> $local_dir/spawn_makdir.sh
echo "spawn ssh $target_hostuser@$target_ip" >> $local_dir/spawn_makdir.sh
echo 'expect {' >> $local_dir/spawn_makdir.sh
echo '"*yes/no" { send "yes\r"; exp_continue}' >> $local_dir/spawn_makdir.sh
echo '"*password:" { send "$password\r" }' >> $local_dir/spawn_makdir.sh
echo '}' >> $local_dir/spawn_makdir.sh
echo 'expect "]$ "' >> $local_dir/spawn_makdir.sh
echo "send \"mkdir -p $target_dir/$jira_num\r\"" >> $local_dir/spawn_makdir.sh
echo 'send "exit\r"' >> $local_dir/spawn_makdir.sh
echo "expect eof" >> $local_dir/spawn_makdir.sh
chmod a+x $local_dir/spawn_makdir.sh
cd $local_dir
./spawn_makdir.sh
}
smakdir
i=1
n=`cat "$sql_text" | wc -l`
if [ $n -lt $parallel ]
then
export parallel="$n"
fi
while [ $i -le $n ]
do
sscp(){
echo "#!/usr/bin/expect" > $local_dir/spawn_scp$i.sh
echo "set password sgpadmin" >> $local_dir/spawn_scp$i.sh
echo "set timeout 100000000" >> $local_dir/spawn_scp$i.sh
echo "spawn scp $local_dir/$jira_num/$table_name.gz $target_hostuser@$target_ip:$target_dir/$jira_num" >> $local_dir/spawn_scp$i.sh
echo 'expect {' >> $local_dir/spawn_scp$i.sh
echo '"*yes/no" { send "yes\r"; exp_continue}' >> $local_dir/spawn_scp$i.sh
echo '"*password:" { send "$password\r" }' >> $local_dir/spawn_scp$i.sh
echo '}' >> $local_dir/spawn_scp$i.sh
echo "expect eof" >> $local_dir/spawn_scp$i.sh
chmod a+x $local_dir/spawn_scp$i.sh
cd $local_dir
./spawn_scp$i.sh
rm $local_dir/spawn_scp$i.sh
}
m=1
while [ $m -le $parallel ]
do
line=`head -n $i $sql_text | tail -n 1`
typeset -l tab
tab="$line"
table_name=`echo "$tab" | awk '{print $4}'`
sscp &
i=`expr $i + 1`
m=`expr $m + 1`
done
while [ 1 == 1 ]
do
scp_num=`ps -ef | grep -i scp | grep -v grep | wc -l`
if [ $scp_num -gt 2 ]
then
sleep 1
else
break
fi
done
done
wait
if [ "$?" -eq 0 ]
then
echo `date` >> $local_dir/copy.log
echo $jira_num': Copy File To '$target_ip' Start ' >> $local_dir/copy.log
else
echo $jira_num': Copy File To '$target_ip' Error ' >> $local_dir/copy.log
mkdir -p /tmp/scp_error/
echo ""
echo 'Scp File To '$target_ip' Faild,Check Your Password! And Run This Commond To Continue: sh /tmp/scp_error/continue.sh' | tee -a $local_dir/copy.log
echo ""
exit
fi
echo `date` >> $local_dir/copy.log
echo $jira_num': Copy File To '$target_ip' End ' >> $local_dir/copy.log
#解压导出的数据文件
echo ""
echo 'Now Gunzip The Copy File !'
echo '--------------------------------------------------------------------'
echo ""
i=1
n=`cat "$sql_text" | wc -l`
if [ $n -lt $parallel ]
then
export parallel="$n"
fi
while [ $i -le $n ]
do
sgunzip(){
echo "#!/usr/bin/expect" > $local_dir/spawn_gunzip$i.sh
echo "set password sgpadmin" >> $local_dir/spawn_gunzip$i.sh
echo "spawn ssh $target_hostuser@$target_ip" >> $local_dir/spawn_gunzip$i.sh
echo 'expect {' >> $local_dir/spawn_gunzip$i.sh
echo '"*yes/no" { send "yes\r"; exp_continue}' >> $local_dir/spawn_gunzip$i.sh
echo '"*password:" { send "$password\r" }' >> $local_dir/spawn_gunzip$i.sh
echo '}' >> $local_dir/spawn_gunzip$i.sh
echo 'expect "]$ "' >> $local_dir/spawn_gunzip$i.sh
echo "send \"gunzip -f $target_dir/$jira_num/$table_name.gz\r\"" >> $local_dir/spawn_gunzip$i.sh
echo 'send "exit\r"' >> $local_dir/spawn_gunzip$i.sh
echo 'expect eof' >> $local_dir/spawn_gunzip$i.sh
chmod a+x $local_dir/spawn_gunzip$i.sh
cd $local_dir
./spawn_gunzip$i.sh
rm $local_dir/spawn_gunzip$i.sh
}
m=1
while [ $m -le $parallel ]
do
line=`head -n $i $sql_text | tail -n 1`
typeset -l tab
tab="$line"
table_name=`echo "$tab" | awk '{print $4}'`
sgunzip &
i=`expr $i + 1`
m=`expr $m + 1`
done
while [ 1 == 1 ]
do
gunzip_num=`ps -ef | grep -i gunzip | grep -v grep | wc -l`
if [ $gunzip_num -gt 2 ]
then
sleep 1
else
break
fi
done
done
wait
if [ "$?" -eq 0 ]
then
echo `date` >> $local_dir/copy.log
echo $jira_num': '$target_ip' Gunzip File Start ' >> $local_dir/copy.log
else
echo $jira_num': Copy File To '$target_ip' Error ' >> $local_dir/copy.log
mkdir -p /tmp/gunzip_error/
echo ""
echo 'Gunzip File On '$target_ip' Faild,Check Your Password! And Run This Commond To Continue: sh /tmp/gunzip_error/continue.sh' | tee -a $local_dir/copy.log
echo ""
exit
fi
echo `date` >> $local_dir/copy.log
echo $jira_num': '$target_ip' Gunzip File End ' >> $local_dir/copy.log
#操作目标库导入数据
echo ""
echo 'Now Copy Data To '$target_ip':'$target_database
echo '--------------------------------------------------------------------'
echo ""
echo `date` >> $local_dir/copy.log
echo $jira_num': Copy Data To '$target_database' Start' >> $local_dir/copy.log
i=1
n=`cat "$sql_text" | wc -l`
if [ $n -lt $parallel ]
then
export parallel="$n"
fi
while [ $i -le $n ]
do
tcopy(){
LINE=`head -n $i $sql_text | tail -n 1`
typeset -l tab
tab="$LINE"
table=`echo "$tab" | awk '{print $4}'`
where=`echo "$tab" | awk '{print $5}'`
#判断是否全表导出
if [ x"$where" == x"where" ]
then
#不是全表，先拼接删除语句
export delete="${LINE/select \* from/delete from}"
echo `date` >> $local_dir/copy.log
echo $jira_num': '"$delete" >> $local_dir/copy.log
#删除数据
echo "$delete"
psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$delete" 2>&1 | tee -a $local_dir/copy.log
#导入数据
echo `date` >> $local_dir/copy.log
export copy="copy $table from '$target_dir/$jira_num/$table'"
echo "$copy"
echo ""
echo $jira_num': '"$copy" >> $local_dir/copy.log
psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$copy" 2>&1 | tee -a $local_dir/copy.log
#确认数据
#echo `date` >> $local_dir/copy.log
#export select="${LINE/select \* from/select count(1) from}"
#echo $jira_num': '"$select" >> $local_dir/copy.log
#psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$select" 2>&1 >> $local_dir/copy.log
#全表导出先truncate表
else
export truncate="truncate table $table"
echo `date` >> $local_dir/copy.log
echo "$truncate"
echo $jira_num': '"$truncate" >> $local_dir/copy.log
psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$truncate" 2>&1 | tee -a $local_dir/copy.log
#导入数据
echo `date` >> $local_dir/copy.log
export copy="copy $table from '$target_dir/$jira_num/$table'"
echo "$copy"
echo ""
echo $jira_num': '"$copy" >> $local_dir/copy.log
psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$copy" 2>&1 | tee -a $local_dir/copy.log
#确认数据
#echo `date` >> $local_dir/copy.log
#export select="${LINE/select \* from/select count(1) from}"
#echo $jira_num': '"$select" >> $local_dir/copy.log
#psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$select" 2>&1 >> $local_dir/copy.log
fi
}
m=1
while [ $m -le $parallel ]
do
tcopy &
i=`expr $i + 1`
m=`expr $m + 1`
done
while [ 1 == 1 ]
do
copy_num=`ps -ef | grep -i copy | grep -v grep | wc -l`
if [ $copy_num -gt 2 ]
then
sleep 1
else
break
fi
done
done
wait
echo ""
echo '##########################################################'
echo 'Copy Data To '$target_ip $target_database' End'
echo '##########################################################'
echo ""
echo `date` >> $local_dir/copy.log
echo $jira_num': Copy Data To '$target_ip $target_database' End' >> $local_dir/copy.log
echo '----------------------------------------------------------' >> $local_dir/copy.log
echo "" >> $local_dir/copy.log
else
echo ""
echo '##########################################################'
echo 'Copydump End With Do Nothing , Check Your Parameter !'
echo '##########################################################'
echo ""
fi
1557
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
