clickHouse 版本需要大于 21.7
#readonly时请手动删除zk或ck-keeper元数据再执行（只读表多可以直接删除data/app/clickhouse/coordination）
老本请参考https://kb.altinity.com/altinity-kb-setup-and-maintenance/altinity-kb-zookeeper/altinity-kb-recovering-from-complete-metadata-loss-in-zookeeper/
#!/bin/bash
# clickhouse机器的IP和端口
hosts=("192.168.0.110" "192.168.0.111" "192.168.0.112")
ports=("18101")
for host in ${hosts[@]}; do
for port in ${ports[@]}; do
query_result=$(clickhouse-client -u default --password 123456 --port $port -h $host --query "select database,table from system.replicas where is_readonly;")
# 该节点没有readonly状态的表，直接跳过
if [ -z "$query_result" ]; then
echo "$host:$port not have readonly table,skip"
continue
fi
# 将结果按行分割，并遍历每一行
while IFS= read -r line; do
# 分割行中的字段获取到数据库名和表名，并保存到变量中
database=$(echo $line | cut -d ' ' -f 1)
table=$(echo $line | cut -d ' ' -f 2)
# 拼接新的SQL语句数组
new_sql=( "SYSTEM RESTART REPLICA $database.$table;" "SYSTEM RESTORE REPLICA $database.$table;")
# 循环执行新的SQL语句并输出结果
for sql in "${new_sql[@]}"; do
# 执行新的SQL语句并输出结果
echo "Results for $database.$table:"
clickhouse-client -u default --password 123456 --port $port -h $host --query "$sql"
echo ""
done
done <<< "$query_result"
done
done
