ClickHouse跨主机复制配置失败：ZooKeeper连接异常求助

问题诊断 #

当前配置存在以下关键问题导致连接失败：

• ClickHouse的ZooKeeper节点仅配置单台服务器且重复，未组成完整Keeper集群
• ClickHouse Keeper的Raft通信端口（8444）未在Docker Compose中映射，导致两个Keeper节点无法建立集群
• 分片与副本配置逻辑错误，未正确设置单分片双副本结构

修正步骤 #

1. 修正两台服务器的ClickHouse config.xml #

更新ZooKeeper节点配置为包含两台服务器，同时调整分片结构为单分片双副本：

服务器1（192.168.101.105）config.xml 修改后

<remote_servers>
    <my_cluster>
        <shard>
            <internal_replication>true</internal_replication>
            <replica>
                <host>192.168.101.105</host>
                <port>9000</port>
                <user>default</user>
                <password>tecate.01</password>
            </replica>
            <replica>
                <host>192.168.101.104</host>
                <port>9000</port>
                <user>default</user>
                <password>tecate.01</password>
            </replica>
        </shard>
    </my_cluster>

<zookeeper>
    <node>
        <host>192.168.101.105</host>
        <port>9181</port>
    </node>
    <node>
        <host>192.168.101.104</host>
        <port>9181</port>
    </node>
</zookeeper>
<macros>
    <shard>01</shard>
    <replica>01</replica>
    <cluster>my_cluster</cluster>
</macros>

服务器2（192.168.101.104）config.xml 修改后

<remote_servers>
    <my_cluster>
        <shard>
            <internal_replication>true</internal_replication>
            <replica>
                <host>192.168.101.105</host>
                <port>9000</port>
                <user>default</user>
                <password>tecate.01</password>
            </replica>
            <replica>
                <host>192.168.101.104</host>
                <port>9000</port>
                <user>default</user>
                <password>tecate.01</password>
            </replica>
        </shard>
    </my_cluster>

<zookeeper>
    <node>
        <host>192.168.101.105</host>
        <port>9181</port>
    </node>
    <node>
        <host>192.168.101.104</host>
        <port>9181</port>
    </node>
</zookeeper>
<macros>
    <shard>01</shard>
    <replica>02</replica>
    <cluster>my_cluster</cluster>
</macros>

2. 修正两台服务器的Docker Compose配置 #

添加ClickHouse Keeper的Raft通信端口映射，确保节点间能互相通信：

服务器1（192.168.101.105）docker-compose.yml 修改后

version: '3.8'

services:
  clickhouse_demo:
    image: yandex/clickhouse-server:latest
    container_name: clickhouse-demo
    hostname: clickhouse-demo
    environment:
      - CLICKHOUSE_CONFIG=/etc/clickhouse-server/config.xml
      - CLICKHOUSE_PASSWORD=tecate.01
    volumes:
      - ./clickhouse_demo/config.xml:/etc/clickhouse-server/config.xml
      - ./clickhouse_node/data:/var/lib/clickhouse
    mem_limit: 32g
    cpus: "6"
    ports:
      - "8123:8123"
      - "9000:9000"
    depends_on:
      - clickhouse-keeper-demo
  clickhouse-keeper-demo:
    image: clickhouse/clickhouse-keeper:latest
    container_name: clickhouse-keeper-demo
    hostname: clickhouse-keeper-demo
    volumes:
     - ./clickhouse_keeper_demo/clickhouse-keeper/keeper_config.xml:/etc/clickhouse-keeper/keeper_config.xml
    ports:
        - "9181:9181"
        - "8444:8444"  # 添加Raft通信端口映射

服务器2（192.168.101.104）docker-compose.yml 修改后

version: '3.8'

services:
  clickhouse_node:
    image: yandex/clickhouse-server:latest
    container_name: clickhouse-node
    hostname: clickhouse-node
    environment:
      - CLICKHOUSE_CONFIG=/etc/clickhouse-server/config.xml
      - CLICKHOUSE_PASSWORD=tecate.01
    volumes:
      - ./clickhouse_node/config.xml:/etc/clickhouse-server/config.xml
      - ./clickhouse_node/data:/var/lib/clickhouse
    mem_limit: 32g
    cpus: "6"
    ports:
      - "8123:8123"
      - "9000:9000"
    depends_on:
      - clickhouse-keeper-node
  clickhouse-keeper-node:
    image: clickhouse/clickhouse-keeper:latest
    container_name: clickhouse-keeper-node
    hostname: clickhouse-keeper-node
    volumes:
     - ./clickhouse_keeper_node/clickhouse-keeper/keeper_config.xml:/etc/clickhouse-keeper/keeper_config.xml
    ports:
        - "9181:9181"
        - "8444:8444"  # 添加Raft通信端口映射

3. 验证Keeper集群状态 #

重启所有容器后，执行以下命令检查集群状态：

# 在服务器1执行
docker exec -it clickhouse-keeper-demo clickhouse-keeper-client --host 192.168.101.105 --port 9181
# 输入命令查看状态
status

正常情况下会显示两个节点，一个为leader，一个为follower。

4. 验证ClickHouse复制 #

创建带副本的表并测试数据同步：

-- 创建副本表
CREATE TABLE test_replica ON CLUSTER my_cluster
(
    id UInt64,
    name String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/test_replica', '{replica}')
ORDER BY id;

-- 在服务器1插入数据
INSERT INTO test_replica VALUES (1, 'test');

-- 在服务器2查询数据
SELECT * FROM test_replica;

能查询到插入数据则说明复制配置成功。

内容的提问来源于stack exchange，提问作者Vilma Zorina Camacho Cagal