ClickHouse 副本集群实战

副本介绍

副本的目的主要是保障数据的高可用性，即使一台Clickhouse节点宕机，那么也可以从其他服务器获取想用的数据。

https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/replication/

副本写入流程 ：

客户端写入数据到Clickhouse-1节点上
Clickhouse-1提交写入日志到zookeeper集群
Clickhouse-2和Clickhouse-3会收到写入日志
从目标副本下载新数据

Clickhouse集群安装

环境介绍

主机名	IP	OS	software
clickhouse-1	192.168.1.104	CentOS Linux release 7.7	clickhouse，zookeeper
clickhouse-2	192.168.1.105	CentOS Linux release 7.7	clickhouse，zookeeper
clickhouse-3	192.168.1.106	CentOS Linux release 7.7	clickhouse，zookeeper

准备工作

$ cat /etc/redhat-release  # 查看系统版本
CentOS Linux release 7.7.1908 (Core)

# 关闭防火墙
$ systemctl stop firewalld.service
$ systemctl disable firewalld.service 

# 配置打开文件数限制
# 在所有节点的 /etc/security/limits.conf 文件的末尾加入以下内容
$ vim /etc/security/limits.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072

# 在所有节点的 /etc/security/limits.d/20-nproc.conf 文件的末尾加入以下内容
$ vim /etc/security/limits.d/20-nproc.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072

# 安装依赖
$ yum install -y libtool
$ yum install -y *unixODBC*

# 关闭selinux：
$ sed -i 's/enforcing/disabled/' /etc/selinux/config
$ setenforce 0
 
# 关闭swap：
$ swapoff -a  # 临时
$ sed -ri 's/.*swap.*/#&/' /etc/fstab # 永久

# 根据规划设置主机名：
hostnamectl set-hostname <hostname>

# 配置hosts记录
cat <<EOF >> /etc/hosts
192.168.1.104 clickhouse-1
192.168.1.105 clickhouse-2
192.168.1.106 clickhouse-3
EOF

zookeeper集群安装

# 部署JDK1.8.0_262
$ wget -P /usr/local/src https://github.com/AdoptOpenJDK/openjdk8-upstream-binaries/releases/download/jdk8u262-b10/OpenJDK8U-jdk_x64_linux_8u262b10.tar.gz  # 下载jdk安装包
$ cd /usr/local/src/
$ tar zxf OpenJDK8U-jdk_x64_linux_8u262b10.tar.gz   # 解压jdk安装包
$ mv openjdk-8u262-b10 /application/
# 添加环境变量
$ cat << EOF >> /etc/profile  
export JAVA_HOME=/application/openjdk-8u262-b10
export JRE_HOME=${JAVA_HOME}/jre
export CLASSPATH=.:${JAVA_HOME}/lib:${JRE_HOME}/lib
export PATH=${JAVA_HOME}/bin:$PATH
EOF
$ source /etc/profile
$ java -version   # 如出现如下则安装成功
openjdk version "1.8.0_262"
OpenJDK Runtime Environment (build 1.8.0_262-b10)
OpenJDK 64-Bit Server VM (build 25.262-b10, mixed mode)

### Zookeeper部署(所有节点执行)
 
 
```shell
# Zookeeper软件包下载并解压
$ wget -P /usr/local/src https://mirror.bit.edu.cn/apache/zookeeper/zookeeper-3.6.1/apache-zookeeper-3.6.1-bin.tar.gz
$ cd /usr/local/src/
$ tar zxf apache-zookeeper-3.6.1-bin.tar.gz
$ mv apache-zookeeper-3.6.1-bin /application/zookeeper
 
 
# 创建启动用户并创建存储目录
$ useradd -s /bin/bash -U zookeeper
$ mkdir -p /application/data/zookeeper-data/
$ chown -Rf zookeeper.zookeeper /application/data/zookeeper-data/
$ chown -Rf zookeeper.zookeeper /application/zookeeper
 
 
# 创建ServerID标识
$ echo '1' > /application/data/zookeeper-data/myid     #clickhouse-1上执行
$ echo '2' > /application/data/zookeeper-data/myid     #clickhouse-2上执行
$ echo '3' > /application/data/zookeeper-data/myid     #clickhouse-3上执行
 
 
# 修改ZooKeeper配置
 
 
$ cat << EOF > /application/zookeeper/conf/zoo.cfg
tickTime=2000
initLimit=10
syncLimit=5
dataDir=/application/data/zookeeper-data
dataLogDir=/application/zookeeper/logs
clientPort=2181
maxClientCnxns=60
autopurge.snapRetainCount=3
autopurge.purgeInterval=1
server.1=192.168.1.104:2888:3888
server.2=192.168.1.105:2888:3888
server.3=192.168.1.106:2888:3888
EOF
 
# 创建zookeeper启动文件并启动
 
 
$ cat << EOF > /usr/lib/systemd/system/zookeeper.service
[Unit]
Description=zookeeper.service
After=network.target
 
[Service]
User=zookeeper
Group=zookeeper
Type=forking
ExecStart=/application/zookeeper/bin/zkServer.sh start
ExecStop=/application/zookeeper/bin/zkServer.sh stop
ExecReload=/application/zookeeper/bin/zkServer.sh restart
[Install]
EOF
 
 
$ systemctl start zookeeper.service   # 启动zookeeper
$ systemctl enable zookeeper.service  # 添加开机自启动
$ systemctl status zookeeper.service  # 检测服务状态
```
到此ZK集群搭建完毕

Clickhouse安装

# 安装Clickhouse
```shell
$ yum install yum-utils
$ rpm --import https://repo.clickhouse.tech/CLICKHOUSE-KEY.GPG
$ yum-config-manager --add-repo https://repo.clickhouse.tech/rpm/clickhouse.repo
$ yum install clickhouse-server clickhouse-client
```

Clickhouse集群配置

在所有节点的的/etc/clickhouse-server/config.d 目录下创建一个名为 metrika.xml 的配置文件,内容如下：

<?xml version="1.0"?>
<yandex>
  <zookeeper-servers>
     <node index="1">
       <host>192.168.1.104</host>
       <port>2181</port>
     </node>
     <node index="2">
        <host>192.168.1.105</host>
        <port>2181</port>
     </node>
        <node index="3">
        <host>192.168.1.106</host>
        <port>2181</port>
     </node>
</zookeeper-servers>
</yandex>

在所有节点的的/etc/clickhouse-server/config.xml 中修改配置

 726 <zookeeper incl="zookeeper-servers" optional="true" />
 727 <include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>
 
  162 <listen_host>0.0.0.0</listen_host>

启动clickhouse集群在所有节点中执行：

$ /etc/init.d/clickhouse-server start   # 启动服务
$ /etc/init.d/clickhouse-server status  # 查看服务状态

Clickhouse副本集群验证

在Clickhouse节点分别建表：

# 连接数据库
[root@clickhouse-1 ~]# clickhouse-client -h clickhouse-1 -m
ClickHouse client version 21.7.3.14 (official build).
Connecting to clickhouse-1:9000 as user default.
Connected to ClickHouse server version 21.7.3 revision 54449.


# clickhouse-1 上执行
create table t_order_rep (
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2),
 create_time Datetime
) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_104')
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id,sku_id);


# clickhouse-2 上执行
create table t_order_rep (
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2),
 create_time Datetime
) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_105')
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id,sku_id);

# clickhouse-3 上执行
create table t_order_rep (
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2),
 create_time Datetime
) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_106')
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id,sku_id);

参数解释：

ReplicatedMergeTree 中，

第一个参数是分片的 zk_path 一般按照：/clickhouse/table// 的格式写，如果只有一个分片就写 01 即可；

第二个参数是副本名称，相同的分片副本名称不能相同。

在clickhouse-1上执行insert语句

insert into t_order_rep values
(101,'sre_001',1000.00,'2021-10-21 12:00:00'),
(102,'sre_002',2000.00,'2021-10-21 12:00:00'),
(103,'sre_004',2500.00,'2021-10-21 12:00:00'),
(104,'sre_002',2000.00,'2021-06-21 12:00:00'),
(105,'sre_003',600.00,'2021-06-22 12:00:00');

在clickhouse-1和clickhouse-2查看数据是否已同步成功:


clickhouse-2 :) select * from t_order_rep;

SELECT *
FROM t_order_rep

Query id: 0da07432-d0ae-4c23-bc15-d2db6c5e9fcb

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 105 │ sre_003 │       600.00 │ 2021-06-22 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sre_001 │      1000.00 │ 2021-10-21 12:00:00 │
│ 102 │ sre_002 │      2000.00 │ 2021-10-21 12:00:00 │
│ 103 │ sre_004 │      2500.00 │ 2021-10-21 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 104 │ sre_002 │      2000.00 │ 2021-06-21 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

5 rows in set. Elapsed: 0.006 sec. 

clickhouse-3 :) select * from t_order_rep;

SELECT *
FROM t_order_rep

Query id: d07db1ef-2408-4eb6-8e6e-7768daaace26

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sre_001 │      1000.00 │ 2021-10-21 12:00:00 │
│ 102 │ sre_002 │      2000.00 │ 2021-10-21 12:00:00 │
│ 103 │ sre_004 │      2500.00 │ 2021-10-21 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 105 │ sre_003 │       600.00 │ 2021-06-22 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 104 │ sre_002 │      2000.00 │ 2021-06-21 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

5 rows in set. Elapsed: 0.005 sec.

到此Clickhouse副本集群已部署完毕！