clickhouse-readonly-recovery clickhouse 表readonly处理通用方法

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