clickhouse从MySQL全量同步数据shell脚本
[root@node99 shell_scripts]# cat rsync_from_mysql_to_clickhouse.sh
#!/bin/bash
SOURCE_DB_SCHEMA="hexin"
SOURCE_TABLE=(hexin_uuid hexin_work_bar)
for i in ${SOURCE_TABLE[*]}
do
echo "Current table:${i}"
/usr/bin/clickhouse-client --database $SOURCE_DB_SCHEMA --query "select count(*) from $SOURCE_DB_SCHEMA.${i}"
if [ $? -eq 0 ];then
echo "[$(date +%F_%H:%M:%S)] Start to drop table $SOURCE_DB_SCHEMA.${i}"
/usr/bin/clickhouse-client --database $SOURCE_DB_SCHEMA --query "drop table $SOURCE_DB_SCHEMA.${i}"
echo "[$(date +%F_%H:%M:%S)] Drop table $SOURCE_DB_SCHEMA.${i} OK"
sleep 1
echo "[$(date +%F_%H:%M:%S)] Start to create table $SOURCE_DB_SCHEMA.${i}"
/usr/bin/clickhouse-client --database $SOURCE_DB_SCHEMA --query "create table $SOURCE_DB_SCHEMA.${i} engine=MergeTree order by id as select * from mysql('192.168.1.10:3306','$SOURCE_DB_SCHEMA','${i}','dbmaster','123456')"
echo "[$(date +%F_%H:%M:%S)] Create table $SOURCE_DB_SCHEMA.${i} OK"
echo "================================================================"
else
echo "[$(date +%F_%H:%M:%S)] Table $SOURCE_DB_SCHEMA.${i} doesn't exist"
echo "[$(date +%F_%H:%M:%S)] Start to create table $SOURCE_DB_SCHEMA.${i}"
/usr/bin/clickhouse-client --database $SOURCE_DB_SCHEMA --query "create table $SOURCE_DB_SCHEMA.${i} engine=MergeTree order by id as select * from mysql('192.168.1.10:3306','$SOURCE_DB_SCHEMA','${i}','dbmaster','123456');" echo "[$(date +%F_%H:%M:%S)] Create table $SOURCE_DB_SCHEMA.${i} OK"
fi
done
