Clickhouse同步mysql数据失败时，自动处理SHELL脚本
Clickhouse通过物化引擎同步MySQL的数据，但如果Clickhouse遇到不能解析的EVENTS，则同步会失败，唯一的解决方法就是重新创建数据同步，但重新创建数据同步流程较多，以下这个脚本就是一个自动处理此类事件的，如下：
填写需要监控的数据库名称：
[root@node220 rsync_mysql_to_clickhouse]# pwd
/server/shell_scripts/clickhouse/rsync_mysql_to_clickhouse
[root@node220 rsync_mysql_to_clickhouse]# ls
db_name.txt test.sh
[root@node220 rsync_mysql_to_clickhouse]# cat db_name.txt
test rsc
监控clickhouse与mysql复制异常的库，如果同步异常，则重新创建
后台监控进程脚本：
#!/bin/bash
while true
do
for i in `cat /server/shell_scripts/clickhouse/rsync_mysql_to_clickhouse/db_name.txt`
do
table_name=`clickhouse-client --host=localhost --port=9000 -u default --password=123456 --query="select name from system.tables where database='$i' order by name desc limit 1;"`
return_data=`clickhouse-client --host=localhost --port=9000 -u default --password=123456 --query "select count(*) from $i.$table_name;"`
if [ $? -eq 0 ]; then
echo "`date`: DB:$i ok"
else
echo "`date`: DB Rsync Error, DB:$i is not rsync from mysql"
echo "`date`: Start to rebuild rsync database and tables"
/bin/bash /server/shell_scripts/clickhouse/rebuild_database.sh $i
echo "`date`: DB:$i Rsync ok"
echo "`date`: sleep 10"
sleep 10
fi
done
echo "`date`:start to sleep 10"
sleep 10
done
库表重建脚本：
[root@node220 clickhouse]# pwd
/server/shell_scripts/clickhouse
[root@node220 clickhouse]# ls
rebuild_database.sh rsync_mysql_to_clickhouse/
[root@node220 clickhouse]# cat rebuild_database.sh
if [ ! -n "$1" ] ;then
echo "Usage $0 [test|rsc]"
else
database=$1
echo "Start to truncate database $database"
for i in `clickhouse-client --host=172.16.1.220 --user=default --password=123456 --database $database --query "show tables"`;do clickhouse-client --host=172.16.1.220 --user=default --password=123456 --database $database --query "truncate table $i" && echo "truncate table $i OK" && sleep 0.5 ;done
echo "Drop database $database"
clickhouse-client --host=172.16.1.220 --user=default --password=123456 --query "drop database $database"
sleep 5
echo "Start to create database $database"
clickhouse-client --host=172.16.1.220 --user=default --password=123456 --query "CREATE DATABASE $database ENGINE = MaterializeMySQL('172.16.1.221:3306', "$database", 'ch_reader', '123456');"
fi
