ClickHouse集群搭建（二）

重叠泪痕缄锦字，人生只有情难死。

分布式集群安装

在上一章我们已经完成ClickHouse分布式集群安装，也创建本地表和分布式表进行了测试，但是，假如停掉一个节点会发生神马情况？

node03上kill掉clickhouse-server进程

[root@node03 ~]# ps -ef | grep clickhouse
clickho+  2233     1 73 13:07 ?        00:00:02 clickhouse-server --daemon --pid-file=/var/run/clickhouse-server/clickhouse-server.pid --config-file=/etc/clickhouse-server/config.xml
root      2306  1751  0 13:07 pts/0    00:00:00 grep --color=auto clickhouse
[root@node03 ~]# service clickhouse-server stop
Stop clickhouse-server service: DONE
[root@node03 ~]# ps -ef | grep clickhouse
root      2337  1751  0 13:07 pts/0    00:00:00 grep --color=auto clickhouse

node01上查询分布式表

node01 :) select * from cluster3s1r_all; # node03没有被杀掉时

SELECT *
FROM cluster3s1r_all

┌─id─┬─website─────────────────┬─wechat─────┬─FlightDate─┬─Year─┐
│  2 │ http://www.merryyou.cn/ │ javaganhuo │ 2020-11-28 │ 2020 │
└────┴─────────────────────────┴────────────┴────────────┴──────┘
┌─id─┬─website───────────────┬─wechat───┬─FlightDate─┬─Year─┐
│  1 │ https://niocoder.com/ │ java干货 │ 2020-11-28 │ 2020 │
└────┴───────────────────────┴──────────┴────────────┴──────┘
┌─id─┬─website──────────────┬─wechat─┬─FlightDate─┬─Year─┐
│  3 │ http://www.xxxxx.cn/ │ xxxxx  │ 2020-11-28 │ 2020 │
└────┴──────────────────────┴────────┴────────────┴──────┘

3 rows in set. Elapsed: 0.037 sec. 

node01 :) select * from cluster3s1r_all; # node03节点被杀掉时

SELECT *
FROM cluster3s1r_all

┌─id─┬─website─────────────────┬─wechat─────┬─FlightDate─┬─Year─┐
│  2 │ http://www.merryyou.cn/ │ javaganhuo │ 2020-11-28 │ 2020 │
└────┴─────────────────────────┴────────────┴────────────┴──────┘
↘ Progress: 1.00 rows, 59.00 B (8.87 rows/s., 523.62 B/s.)  0%
Received exception from server (version 20.8.3):
Code: 279. DB::Exception: Received from localhost:9000. DB::Exception: All connection tries failed. Log: 

Code: 32, e.displayText() = DB::Exception: Attempt to read after eof (version 20.8.3.18)
Code: 210, e.displayText() = DB::NetException: Connection refused (node03:9000) (version 20.8.3.18)
Code: 210, e.displayText() = DB::NetException: Connection refused (node03:9000) (version 20.8.3.18)

: While executing Remote. 

1 rows in set. Elapsed: 0.114 sec. 

只返回了node01节点上的数据，node03节点上的两条数据丢失。

数据备份

但在ClickHouse中，replica是挂在shard上的，因此要用多副本，必须先定义shard。

最简单的情况：1个分片多个副本。

修改metrika.xml文件

node01 上修改 /etc/clickhouse-server/metrika.xml集群配置文件

<yandex>
<!-- 集群配置 -->
<clickhouse_remote_servers>
    <!-- 1分片2备份 -->
    <cluster_1shards_2replicas>
        <!-- 数据分片1  -->
        <shard>
		<!-- false代表一次性写入所有副本，true表示写入其中一个副本，配合zk来进行数据复制 -->
		<internal_replication>false</internal_replication>
            <replica>
                <host>node01</host>
                <port>9000</port>
            </replica>
            <replica>
                <host>node02</host>
                <port>9000</port>
            </replica>	
		</shard>
    </cluster_1shards_2replicas>
</clickhouse_remote_servers>
</yandex>

将修改后的配置分发到node02机器上

[root@node01 clickhouse-server]# scp /etc/clickhouse-server/metrika.xml node02:$PWD
metrika.xml                                                                                                                                                  100%  674   618.9KB/s   00:00    

如果配置文件没有问题，是不用重启clickhouse-server的，会自动加载配置文件，node01上查看集群信息

[root@node01 clickhouse-server]# clickhouse-client -m
ClickHouse client version 20.8.3.18.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.8.3 revision 54438.

node01 :) select * from system.clusters;

SELECT *
FROM system.clusters

┌─cluster───────────────────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name─┬─host_address───┬─port─┬─is_local─┬─user────┬─default_database─┬─errors_count─┬─estimated_recovery_time─┐
│ cluster_1shards_2replicas         │         1 │            1 │           1 │ node01    │ 192.168.10.100 │ 9000 │        1 │ default │                  │            0 │                       0 │
│ cluster_1shards_2replicas         │         1 │            1 │           2 │ node02    │ 192.168.10.110 │ 9000 │        0 │ default │                  │            0 │                       0 │
│ test_cluster_two_shards           │         1 │            1 │           1 │ 127.0.0.1 │ 127.0.0.1      │ 9000 │        1 │ default │                  │            0 │                       0 │
│ test_cluster_two_shards           │         2 │            1 │           1 │ 127.0.0.2 │ 127.0.0.2      │ 9000 │        0 │ default │                  │            0 │                       0 │
│ test_cluster_two_shards_localhost │         1 │            1 │           1 │ localhost │ ::1            │ 9000 │        1 │ default │                  │            0 │                       0 │
│ test_cluster_two_shards_localhost │         2 │            1 │           1 │ localhost │ ::1            │ 9000 │        1 │ default │                  │            0 │                       0 │
│ test_shard_localhost              │         1 │            1 │           1 │ localhost │ ::1            │ 9000 │        1 │ default │                  │            0 │                       0 │
│ test_shard_localhost_secure       │         1 │            1 │           1 │ localhost │ ::1            │ 9440 │        0 │ default │                  │            0 │                       0 │
│ test_unavailable_shard            │         1 │            1 │           1 │ localhost │ ::1            │ 9000 │        1 │ default │                  │            0 │                       0 │
│ test_unavailable_shard            │         2 │            1 │           1 │ localhost │ ::1            │    1 │        0 │ default │                  │            0 │                       0 │
└───────────────────────────────────┴───────────┴──────────────┴─────────────┴───────────┴────────────────┴──────┴──────────┴─────────┴──────────────────┴──────────────┴─────────────────────────┘

10 rows in set. Elapsed: 0.018 sec.

测试数据备份

在node01和node02上分别创建本地表cluster1s2r_local

CREATE TABLE default.cluster1s2r_local
(
	`id` Int32,
    `website` String,
    `wechat` String,
	`FlightDate` Date,
	Year UInt16
)
ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192);

在node01机器上创建分布式表,注意集群名称

CREATE TABLE default.cluster1s2r_all AS cluster1s2r_local
ENGINE = Distributed(cluster_1shards_2replicas, default, cluster1s2r_local, rand());

往分布式表cluster1s2r_all插入数据,cluster1s2r_all 会全部插入到node01和node02节点的cluster1s2r_local里

插入数据

INSERT INTO default.cluster1s2r_all (id,website,wechat,FlightDate,Year)values(1,'https://niocoder.com/','java干货','2020-11-28',2020);
INSERT INTO default.cluster1s2r_all (id,website,wechat,FlightDate,Year)values(2,'http://www.merryyou.cn/','javaganhuo','2020-11-28',2020);
INSERT INTO default.cluster1s2r_all (id,website,wechat,FlightDate,Year)values(3,'http://www.xxxxx.cn/','xxxxx','2020-11-28',2020);

查询分布式表和本地表

node01 :) select * from cluster1s2r_all; # 查询分布式表

SELECT *
FROM cluster1s2r_all

┌─id─┬─website──────────────┬─wechat─┬─FlightDate─┬─Year─┐
│  3 │ http://www.xxxxx.cn/ │ xxxxx  │ 2020-11-28 │ 2020 │
└────┴──────────────────────┴────────┴────────────┴──────┘
┌─id─┬─website─────────────────┬─wechat─────┬─FlightDate─┬─Year─┐
│  2 │ http://www.merryyou.cn/ │ javaganhuo │ 2020-11-28 │ 2020 │
└────┴─────────────────────────┴────────────┴────────────┴──────┘
┌─id─┬─website───────────────┬─wechat───┬─FlightDate─┬─Year─┐
│  1 │ https://niocoder.com/ │ java干货 │ 2020-11-28 │ 2020 │
└────┴───────────────────────┴──────────┴────────────┴──────┘

3 rows in set. Elapsed: 0.018 sec. 

node01 :) select * from cluster1s2r_local; # node01节点查询本地表

SELECT *
FROM cluster1s2r_local

┌─id─┬─website─────────────────┬─wechat─────┬─FlightDate─┬─Year─┐
│  2 │ http://www.merryyou.cn/ │ javaganhuo │ 2020-11-28 │ 2020 │
└────┴─────────────────────────┴────────────┴────────────┴──────┘
┌─id─┬─website───────────────┬─wechat───┬─FlightDate─┬─Year─┐
│  1 │ https://niocoder.com/ │ java干货 │ 2020-11-28 │ 2020 │
└────┴───────────────────────┴──────────┴────────────┴──────┘
┌─id─┬─website──────────────┬─wechat─┬─FlightDate─┬─Year─┐
│  3 │ http://www.xxxxx.cn/ │ xxxxx  │ 2020-11-28 │ 2020 │
└────┴──────────────────────┴────────┴────────────┴──────┘

3 rows in set. Elapsed: 0.015 sec. 

node02 :)  select * from cluster1s2r_local;