Deploying ClickHouse - 2S-2R | 8lovelife's life

8lovelife's life

M&M

0%

Deploying ClickHouse - 2S-2R

发表于 更新于 分类于
本文字数： 1.2k 阅读时长 ≈ 5 分钟

一般情况下 ClickHouse 1S-2R 的集群模式足够使用，当服务无法 scale up，服务器已经无法继续升级配置，这个时候我们可以选择数据分片来进行服务的 scale out，通过对服务的横向扩展来提升服务能力。本文记录 2S-2R （两个分片两个副本）的集群搭建，以及常用操作

ClickHouse 2S-2R

如下架构图描述

imag

Data Sample

数据的简单测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# db
CREATE DATABASE dp ON CLUSTER cluster_2S_2R;

# base table
CREATE TABLE World on CLUSTER cluster_2S_2R (
  val String
)
ENGINE = ReplicatedMergeTree()
ORDER BY val

# distributed table ｜ rand() 随机分片
CREATE TABLE World_distributed ON Cluster cluster_2S_2R
ENGINE = Distributed(cluster_2S_2R,dp,World,rand());

# data insert
INSERT INTO World_distributed(val) SELECT number FROM numbers(10);

imag

Add New Replicas

在当前 CK 集群中新增 SHARD 副本。新架构图描述如下

imag

Show Table Full Engine

1
2
SELECT * FROM system.tables
WHERE database = 'dp' AND name = 'World' FORMAT Vertical;

imag

Show Replicas Info

1
2
SELECT * FROM system.zookeeper
WHERE path = '/clickhouse/tables/e60cfcf5-27eb-4672-b773-89d06a127422/shard-one/replicas';

imag

Sync Database / Table

1
2
3
4
5
6
7
8
9
10
11
12
13
# on new clickhouse node | clickhouse-05 or clickhouse-06
CREATE DATABASE dp ON CLUSTER cluster_2S_2R;

# table base on FULL ENGINE info
CREATE TABLE World on CLUSTER cluster_2S_2R (
  val String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/e60cfcf5-27eb-4672-b773-89d06a127422/{shard}', '{replica}')
ORDER BY val;

# distributed table
CREATE TABLE World_distributed ON Cluster cluster_2S_2R
ENGINE = Distributed(cluster_2S_2R,dp,World,rand());

Show New Replicas Info

1
2
SELECT * FROM system.zookeeper
WHERE path = '/clickhouse/tables/e60cfcf5-27eb-4672-b773-89d06a127422/shard-one/replicas';

imag

Sync Replicas Data

imag

Test New Replicas

1
INSERT INTO World_distributed(val) SELECT number FROM numbers(10);

imag

搭建过程常见问题

New Replicas 无法同步数据

检查 NEW NODES 中 TABLE 的 FULL ENGINE 是否相同

1
2
SELECT * FROM system.tables
WHERE database = 'dp' AND name = 'World' FORMAT Vertical;

Distributed Table 的权限问题

DISTRIBUTED TABLE 使用的是 ‘default’ 用户进行不同 Clickhouse Node 之间的通信

1
2
3
4
5
6
7
Received exception from server (version 23.6.2):
Code: 516. DB::Exception: Received from localhost:9000. DB::Exception: Received from clickhouse-01:9000. DB::Exception: default: Authentication failed: password is incorrect, or there is no user with such name.

If you have installed ClickHouse and forgot password you can reset it in the configuration file.
The password for default user is typically located at /etc/clickhouse-server/users.d/default-password.xml
and deleting this file will reset the password.
See also /etc/clickhouse-server/users.xml on the server where ClickHouse is installed.

解决方案 1

保留 default 用户密码，修改 config.xml 文件中的 replica，新增 user/password

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<remote_servers>
    <cluster_2S_2R>
        <shard>
            <internal_replication>true</internal_replication>
            <replica>
                <host>clickhouse-01</host>
                <port>9000</port>
                <user>default</user>
                <password>123456</password>                    
            </replica>
        </shard>
        ........         
    </cluster_2S_2R>
</remote_servers>

解决方案 2

设置 default 无密码登录，修改 user.xml 文件中的 networks，指定可访问 IP

1
2
3
4
5
6
7
8
9
10
11
12
<users>
    <default>
        <profile>default</profile>
        <networks>
            <ip>::1</ip>
            <ip>127.0.0.1</ip>
            <ip>192.168.5.1</ip>
            <ip>192.168.5.2</ip>
            <ip>192.168.5.3</ip>
            <ip>192.168.5.4</ip>
            <ip>192.168.5.5</ip>
            <ip>192.168.5.6</ip>

Up ClickHouse Server

通过 docker compose 创建 ClickHouse Server 2S-2R 集群

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
# docker-compose.yml contents
version: '3.8'
services:
  clickhouse-01:
    image: "clickhouse/clickhouse-server:${CHVER:-latest}"
    user: "101:101"
    container_name: clickhouse-01
    hostname: clickhouse-01   
    networks:
      cluster_2S_2R_ch_proxy:
        ipv4_address: 192.168.5.1
    volumes:
      - ./ck-01/config.d/config.xml:/etc/clickhouse-server/config.d/config.xml
      - ./ck-01/users.d/users.xml:/etc/clickhouse-server/users.d/users.xml
      - ./ck-01/data:/var/lib/clickhouse:rw
      - ./ck-01/logs:/var/log/clickhouse-server
    ports:
      - "127.0.0.1:18123:8123"
      - "127.0.0.1:19000:9000"
    depends_on:
      - clickhouse-keeper-01
      - clickhouse-keeper-02
      - clickhouse-keeper-03

  clickhouse-02:
    image: "clickhouse/clickhouse-server:${CHVER:-latest}"
    user: "101:101"
    container_name: clickhouse-02
    hostname: clickhouse-02   
    networks:
      cluster_2S_2R_ch_proxy:
        ipv4_address: 192.168.5.2
    volumes:
      - ./ck-02/config.d/config.xml:/etc/clickhouse-server/config.d/config.xml
      - ./ck-02/users.d/users.xml:/etc/clickhouse-server/users.d/users.xml
      - ./ck-02/data:/var/lib/clickhouse:rw
      - ./ck-02/logs:/var/log/clickhouse-server
    ports:
      - "127.0.0.1:18124:8123"
      - "127.0.0.1:19001:9000"
    depends_on:
      - clickhouse-keeper-01
      - clickhouse-keeper-02
      - clickhouse-keeper-03

  clickhouse-03:
    image: "clickhouse/clickhouse-server:${CHVER:-latest}"
    user: "101:101"
    container_name: clickhouse-03
    hostname: clickhouse-03   
    networks:
      cluster_2S_2R_ch_proxy:
        ipv4_address: 192.168.5.3
    volumes:
      - ./ck-03/config.d/config.xml:/etc/clickhouse-server/config.d/config.xml
      - ./ck-03/users.d/users.xml:/etc/clickhouse-server/users.d/users.xml
      - ./ck-03/data:/var/lib/clickhouse:rw
      - ./ck-03/logs:/var/log/clickhouse-server
    ports:
      - "127.0.0.1:18125:8123"
      - "127.0.0.1:19002:9000"
    depends_on:
      - clickhouse-keeper-01
      - clickhouse-keeper-02
      - clickhouse-keeper-03
    
  clickhouse-04:
    image: "clickhouse/clickhouse-server:${CHVER:-latest}"
    user: "101:101"
    container_name: clickhouse-04
    hostname: clickhouse-04  
    networks:
      cluster_2S_2R_ch_proxy:
        ipv4_address: 192.168.5.4
    volumes:
      - ./ck-04/config.d/config.xml:/etc/clickhouse-server/config.d/config.xml
      - ./ck-04/users.d/users.xml:/etc/clickhouse-server/users.d/users.xml
      - ./ck-04/data:/var/lib/clickhouse:rw
      - ./ck-04/logs:/var/log/clickhouse-server
    ports:
      - "127.0.0.1:18126:8123"
      - "127.0.0.1:19003:9000"
    depends_on:
      - clickhouse-keeper-01
      - clickhouse-keeper-02
      - clickhouse-keeper-03

  clickhouse-05:
    image: "clickhouse/clickhouse-server:${CHVER:-latest}"
    user: "101:101"
    container_name: clickhouse-05
    hostname: clickhouse-05
    networks:
      cluster_2S_2R_ch_proxy:
        ipv4_address: 192.168.5.5
    volumes:
      - ./ck-05/config.d/config.xml:/etc/clickhouse-server/config.d/config.xml
      - ./ck-05/users.d/users.xml:/etc/clickhouse-server/users.d/users.xml
      - ./ck-05/data:/var/lib/clickhouse:rw
      - ./ck-05/logs:/var/log/clickhouse-server
    ports:
      - "127.0.0.1:18127:8123"
      - "127.0.0.1:19004:9000"
    depends_on:
      - clickhouse-keeper-01
      - clickhouse-keeper-02
      - clickhouse-keeper-03

  clickhouse-06:
    image: "clickhouse/clickhouse-server:${CHVER:-latest}"
    user: "101:101"
    container_name: clickhouse-06
    hostname: clickhouse-06
    networks:
      cluster_2S_2R_ch_proxy:
        ipv4_address: 192.168.5.6
    volumes:
      - ./ck-06/config.d/config.xml:/etc/clickhouse-server/config.d/config.xml
      - ./ck-06/users.d/users.xml:/etc/clickhouse-server/users.d/users.xml
      - ./ck-06/data:/var/lib/clickhouse:rw
      - ./ck-06/logs:/var/log/clickhouse-server
    ports:
      - "127.0.0.1:18128:8123"
      - "127.0.0.1:19005:9000"
    depends_on:
      - clickhouse-keeper-01
      - clickhouse-keeper-02
      - clickhouse-keeper-03        

  clickhouse-keeper-01:
    image: "clickhouse/clickhouse-keeper:${CHKVER:-latest-alpine}"
    user: "101:101"
    container_name: clickhouse-keeper-01
    hostname: clickhouse-keeper-01
    networks:
      cluster_2S_2R_ch_proxy:
        ipv4_address: 192.168.5.10   
    volumes:
     - ./ck-kp-01/config/keeper_config.xml:/etc/clickhouse-keeper/keeper_config.xml
     - ./ck-kp-01/config/coordination:/var/lib/clickhouse/coordination
     - ./ck-kp-01/config/log:/var/log/clickhouse-keeper
    ports:
        - "127.0.0.1:19181:9181"

  clickhouse-keeper-02:
    image: "clickhouse/clickhouse-keeper:${CHKVER:-latest-alpine}"
    user: "101:101"
    container_name: clickhouse-keeper-02
    hostname: clickhouse-keeper-02
    networks:
      cluster_2S_2R_ch_proxy:
        ipv4_address: 192.168.5.11  
    volumes:
     - ./ck-kp-02/config/keeper_config.xml:/etc/clickhouse-keeper/keeper_config.xml
     - ./ck-kp-02/config/coordination:/var/lib/clickhouse/coordination
     - ./ck-kp-02/config/log:/var/log/clickhouse-keeper
    ports:
        - "127.0.0.1:19182:9181"
        
  clickhouse-keeper-03:
    image: "clickhouse/clickhouse-keeper:${CHKVER:-latest-alpine}"
    user: "101:101"
    container_name: clickhouse-keeper-03
    hostname: clickhouse-keeper-03
    networks:
      cluster_2S_2R_ch_proxy:
        ipv4_address: 192.168.5.12
    volumes:
     - ./ck-kp-03/config/keeper_config.xml:/etc/clickhouse-keeper/keeper_config.xml
     - ./ck-kp-03/config/coordination:/var/lib/clickhouse/coordination
     - ./ck-kp-03/config/log:/var/log/clickhouse-keeper
    ports:
        - "127.0.0.1:19183:9181"

networks:
  cluster_2S_2R_ch_proxy:
    driver: bridge
    ipam:
      config:
        - subnet: 192.168.5.0/24
          gateway: 192.168.5.254