Shell同步表

最新推荐文章于 2024-01-02 17:32:02 发布

奋斗的一只鸭

最新推荐文章于 2024-01-02 17:32:02 发布

阅读量180

点赞数

分类专栏： Shell 数据库文章标签： Shell 数据库

本文链接：https://blog.csdn.net/qq_19800265/article/details/102592084

版权

数据库同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

Shell

2 篇文章 0 订阅

订阅专栏

不同数据库表同步

rds——>clickhouse

#!/bin/bash

starttime=`date +'%Y-%m-%d %H:%M:%S'`
echo "begin time : ${starttime}"

rds_host="xxxxxx:3306"
rds_database="xxx"
rds_user="xxx"
rds_passwd="xxx"
rds_table="catalog"


ck_host="xxxxxx:8123"
ck_database="xxx"
ck_user="xxx"
ck_passwd="xxx"
ck_table="catalog"

start_time=`date -d "15 minute ago" +'%Y-%m-%d %H:%M:%S'`
end_time=`date +'%Y-%m-%d %H:%M:%S'`

echo "sync data time : ${start_time} ===>>> ${end_time}"

dumpSQL="insert into ${ck_table} select id,item_id,catalog_id,catalog_level,created_at from mysql('${rds_host}', '${rds_database}', '${rds_table}', '${rds_user}', '${rds_passwd}') where created_at>='${start_time}' and created_at<='${end_time}'"

echo "dumpSQL is : ${dumpSQL}"


echo "${dumpSQL}" | curl "http://${ck_host}/?database=${ck_database}&user=${ck_user}&password=${ck_passwd}" -d @-


return_code=$?
echo "return_code: ${return_code}"

if [ $return_code == 0 ]
then
        echo "sync data of ${start_time} ===>>> ${end_time} success finished"
else
        echo  "sync data of ${start_time} ===>>> ${end_time} faild !"
        sh /xxx/ding.sh "sync data of ${rds_table}  ${start_time} ===>>> ${end_time} to clickhouse faild !"   //钉钉报警脚本
fi


optimizeSQL="optimize table ${ck_table}"

echo "start to: ${optimizeSQL} "
echo "${optimizeSQL}" | curl "http://${ck_host}/?database=${ck_database}&user=${ck_user}&password=${ck_passwd}" -d @-

echo "sync end "

endtime=`date +'%Y-%m-%d %H:%M:%S'`
start_seconds=$(date --date="$starttime" +%s);
end_seconds=$(date --date="$endtime" +%s);
echo "本次运行时间： "$((end_seconds-start_seconds))"s"

同一数据库表同步

clickhouse

#!/bin/bash

starttime=`date +'%Y-%m-%d %H:%M:%S'`
echo "begin time : ${starttime}"


ck_host="xxxxx:8123"
ck_database="xx"
ck_user="xxx"
ck_passwd="xxx"
ck_table="catalog"
ck1_table="catalog_test"


start_time=`date -d "15 minute ago" +'%Y-%m-%d %H:%M:%S'`
end_time=`date +'%Y-%m-%d %H:%M:%S'`

echo "sync data time : ${start_time} ===>>> ${end_time}"

dumpSQL="insert into ${ck1_table} select id,name,picture,parent_id,is_active,created_at from mysql('${ck_host}', '${ck_database}', '${ck_table}', '${ck_user}', '${ck_passwd}') where created_at>='${start_time}' and created_at<='${end_time}'"

echo "dumpSQL is : ${dumpSQL}"


echo "${dumpSQL}" | curl "http://${ck_host}/?database=${ck_database}&user=${ck_user}&password=${ck_passwd}" -d @-


return_code=$?
echo "return_code: ${return_code}"

if [ $return_code == 0 ]
then
        echo "sync data of ${start_time} ===>>> ${end_time} success finished"
else
        echo  "sync data of ${start_time} ===>>> ${end_time} faild !"
        sh /xxx/ding.sh "sync data of ${ck_table}  ${start_time} ===>>> ${end_time} to clickhouse faild !"
fi


#optimizeSQL="optimize table ${ck_table}"

#echo "start to: ${optimizeSQL} "
#echo "${optimizeSQL}" | curl "http://${ck_host}/?database=${ck_database}&user=${ck_user}&password=${ck_passwd}" -d @-

echo "sync end "

endtime=`date +'%Y-%m-%d %H:%M:%S'`
start_seconds=$(date --date="$starttime" +%s);
end_seconds=$(date --date="$endtime" +%s);
echo "本次运行时间： "$((end_seconds-start_seconds))"s"```