Clickhouse高可用集群版三节点两副本部署

happyterry

已于 2023-09-20 17:34:24 修改

阅读量1.7k

点赞数 3

文章标签： clickhouse 数据库架构人工智能

于 2023-09-19 09:16:34 首次发布

本文链接：https://blog.csdn.net/HAPPYTERRY/article/details/133012242

版权

一、安装前准备

1、关闭防火墙（状态确认）

service status firewall systemctl

2、JDK环境配置完成（版本确认）

java -version

3、集群版ZK搭建完成，状态查看

zkServer.sh status

zk接口信息：

4、安装规划

操作系统：centos7.8，三台服务器，三个分片，每个分片2备份。

查看操作系统：cat /etc/centos-*

5、安装包下载(打包上传到指定服务器)

网站rpm安装包下载：Altinity/clickhouse - Packages · packagecloud

这四个下载即可（注意必须同一版本）：

clickhouse-server-20.8.3.18-1.el7.x86_64.rpm

clickhouse-server-common-20.8.3.18-1.el7.x86_64.rpm

clickhouse-common-static-20.8.3.18-1.el7.x86_64.rpm

clickhouse-client-20.8.3.18-1.el7.x86_64.rpm

二、单机安装

1、安装包分别上传到各节点服务器，按顺序进行安装（注意顺序）

默认用root用户操作，或具有sudo权限的用户进行（命令前加 sudo）

rpm -ivh clickhouse-server-20.8.3.18-1.el7.x86_64.rpm

rpm -ivh clickhouse-server-common-20.8.3.18-1.el7.x86_64.rpm

rpm -ivh clickhouse-common-static-20.8.3.18-1.el7.x86_64.rpm

rpm -ivh clickhouse-client-20.8.3.18-1.el7.x86_64.rpm

（如果安装过程中出现rpm包缺少，需要下载指定rpm包）

2、启动和验证ClickHouse服务

按照默认配置对ClickHosue进行启动，确保安装正常（后续调整修改配置）

按默认配置启动服务：service clickhouse-server start

按默认链接信息客户端链接： clickhouse-client -m

3、停止服务

用Kill命令杀死已启动服务，后续进行配置修改

三、配置ClickHouse高可用集群（三台机器分别执行）

1、配置信息修改

切换至配置文件目录： cd /etc/clickhouse-server/

原目录下有2个配置文件：config.xml、users.xml

按照规划，每个设备有1主1备份，需要创建metrika.xml，每套配置信息两份（两套clickHouse服务分别对应）

a、拷贝config.xml重命名为config01.xml

cp -p config.xml config01.xml

b、修改config01.xml配置信息

修改点（clickhouse-juju-1，实例01）：

1、端口tcp_port 10000, http_port 8123,mysql_port 9004, interserver_http_port 9009;同步host设定为本机IP或hostname

放开所有IP访问权限

2、数据目录：/data/clickhouse/server01/data/

3、日志目录：/data/clickhouse/server01/logs/

4、时区设定为：Asia/Shanghai；关联外部配置文件：user01.xml、metrika01.xml

5、修改点总结：

...
        <!--日志信息-->
        <log>/data/clickhouse/server01/logs/clickhouse-server.log</log>
        <errorlog>/data/clickhouse/server01/logs/clickhouse-server.err.log</errorlog>
        <size>1000M</size>
        <count>10</count>
...
    <!--display_name>production</display_name--> <!-- It is the name that will be shown in the client -->
    <http_port>8123</http_port>
    <tcp_port>10000</tcp_port>
    <mysql_port>9004</mysql_port>
    <!-- For HTTPS and SSL over native protocol. -->
...
    <!-- Port for communication between replicas. Used for data exchange. -->
    <interserver_http_port>9009</interserver_http_port>
...
    <interserver_http_host>juju-1</interserver_http_host>

    <!-- Listen specified host. use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere. -->
    <!-- <listen_host>::</listen_host> -->
    <listen_host>::</listen_host>
...
    <!-- Path to data directory, with trailing slash. -->
    <!-- <path>/var/lib/clickhouse/</path> -->
    <path>/data/clickhouse/server01/data/</path>

    <!-- Path to temporary data for processing hard queries. -->
    <!-- <tmp_path>/var/lib/clickhouse/tmp/</tmp_path> -->
    <tmp_path>/data/clickhouse/server01/data/tmp/</tmp_path>

    <!-- Policy from the <storage_configuration> for the temporary files.
         If not set <tmp_path> is used, otherwise <tmp_path> is ignored.

         Notes:
         - move_factor              is ignored
         - keep_free_space_bytes    is ignored
         - max_data_part_size_bytes is ignored
         - you must have exactly one volume in that policy
    -->
    <!-- <tmp_policy>tmp</tmp_policy> -->

    <!-- Directory with user provided files that are accessible by 'file' table function. -->
    <!-- <user_files_path>/var/lib/clickhouse/user_files/</user_files_path> -->
    <user_files_path>/data/clickhouse/server01/data/user_files/</user_files_path>

    <!-- Path to folder where users and roles created by SQL commands are stored. -->
    <!-- <access_control_path>/var/lib/clickhouse/access/</access_control_path> -->
    <access_control_path>/data/clickhouse/server01/access/</access_control_path>
...
    <users_config>users01.xml</users_config>
...
    <!-- <timezone>Europe/Moscow</timezone> -->
    <timezone>Asia/Shanghai</timezone>
...
    <!-- If element has 'incl' attribute, then for it's value will be used corresponding substitution from another file.
         By default, path to file with substitutions is /etc/metrika.xml. It could be changed in config in 'include_from' element.
         Values for substitutions are specified in /yandex/name_of_substitution elements in that file.
      -->
    <include_from>/etc/clickhouse-server/metrika01.xml</include_from> <!--外部配置文件-->

c、修改config02.xml配置信息

参照config01.xml修改内容，对config02.xml进行修改；修改点（clickhouse-juju-1，实例02）：

...
        <!--日志信息-->
        <log>/data/clickhouse/server02/logs/clickhouse-server.log</log>
        <errorlog>/data/clickhouse/server02/logs/clickhouse-server.err.log</errorlog>
        <size>1000M</size>
        <count>10</count>
...
    <!--display_name>production</display_name--> <!-- It is the name that will be shown in the client -->
    <http_port>8124</http_port>
    <tcp_port>11000</tcp_port>
    <mysql_port>9005</mysql_port>
    <!-- For HTTPS and SSL over native protocol. -->
...
    <!-- Port for communication between replicas. Used for data exchange. -->
    <interserver_http_port>9010</interserver_http_port>
...
    <interserver_http_host>juju-1</interserver_http_host>

    <!-- Listen specified host. use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere. -->
    <!-- <listen_host>::</listen_host> -->
    <listen_host>::</listen_host>
...
    <!-- Path to data directory, with trailing slash. -->
    <!-- <path>/var/lib/clickhouse/</path> -->
    <path>/data/clickhouse/server02/data/</path>

    <!-- Path to temporary data for processing hard queries. -->
    <!-- <tmp_path>/var/lib/clickhouse/tmp/</tmp_path> -->
    <tmp_path>/data/clickhouse/server02/data/tmp/</tmp_path>

    <!-- Policy from the <storage_configuration> for the temporary files.
         If not set <tmp_path> is used, otherwise <tmp_path> is ignored.

         Notes:
         - move_factor              is ignored
         - keep_free_space_bytes    is ignored
         - max_data_part_size_bytes is ignored
         - you must have exactly one volume in that policy
    -->
    <!-- <tmp_policy>tmp</tmp_policy> -->

    <!-- Directory with user provided files that are accessible by 'file' table function. -->
    <!-- <user_files_path>/var/lib/clickhouse/user_files/</user_files_path> -->
    <user_files_path>/data/clickhouse/server02/data/user_files/</user_files_path>

    <!-- Path to folder where users and roles created by SQL commands are stored. -->
    <!-- <access_control_path>/var/lib/clickhouse/access/</access_control_path> -->
    <access_control_path>/data/clickhouse/server02/access/</access_control_path>
...
    <users_config>users02.xml</users_config>
...
    <!-- <timezone>Europe/Moscow</timezone> -->
    <timezone>Asia/Shanghai</timezone>
...
    <!-- If element has 'incl' attribute, then for it's value will be used corresponding substitution from another file.
         By default, path to file with substitutions is /etc/metrika.xml. It could be changed in config in 'include_from' element.
         Values for substitutions are specified in /yandex/name_of_substitution elements in that file.
      -->
    <include_from>/etc/clickhouse-server/metrika02.xml</include_from> <!--外部配置文件-->

d、修改metrika01.xml配置信息

metrika01.xml文件需要创建，并配置zookeeper信息、分片信息、备份信息等;信息置于标签之内

1、zk信息配置（所有服务节点此配置信息相同）

zk侧根目录存在确认

2、集群分片及副本信息设定（所有服务节点此配置信息相同）

3、设定Macros配置信息

shard是分片ID，参照规划进行区分；replica是副本信息，需要对同一分片上副本进行区分标识，可按照：cluster_[分片号]_[副本号]命名

Macros信息基本可以复用，不同集群和副本服务之间，唯一区别的就是步骤3配置的shard和replica信息；样例如下：

<yandex>
<!-- zookeeper-servers 配置  -->
<zookeeper-servers>
    <node index="1">
        <host>juju-1</host>
        <port>2181</port>
    </node>
    <node index="2">
        <host>juju-2</host>
        <port>2181</port>
    </node>
    <node index="3">
        <host>juju-3</host>
        <port>2181</port>
    </node>
    <session_timeout_ms>30000</session_timeout_ms>  <!--客户端会话的最大超时（以毫秒为单位）-->
    <operation_timeout_ms>10000</operation_timeout_ms>
    <root>/path/to/zookeeper/node</root>   <!--用作ClickHouse服务器使用的znode的根的znode-->
 </zookeeper-servers>
<!-- 集群配置  -->
<clickhouse_remote_servers>
    <!-- 集群名称，可以修改，集群只有一个名字 -->
    <cluster_3shards_2replica>
    <!-- 配置3个分片，每个分片对应一台机器-->
        <shard> <!--分片1-->
            <!-- 是否内部复制 -->
            <internal_replication>true</internal_replication>
            <replica><!--副本1-->
                <host>juju-1</host>
                <port>10000</port>
                <user>default</user>
                <password>[自定义密码]</password>
            </replica>
            <replica><!--副本2-->
                <host>juju-2</host>
                <port>11000</port>
                <user>default</user>
                <password>[自定义密码]</password>
            </replica>
        </shard>
        <shard> <!--分片2-->
            <replica>
                <internal_replication>true</internal_replication>
                <host>juju-2</host>
                <port>10000</port>
                <user>default</user>
                <password>[自定义密码]</password>
            </replica>
            <replica>
                <host>juju-3</host>
                <port>11000</port>
                <user>default</user>
                <password>[自定义密码]</password>
            </replica>
        </shard>
        <shard> <!--分片3-->
            <internal_replication>true</internal_replication>
            <replica>
                <host>juju-3</host>
                <port>10000</port>
                <user>default</user>
                <password>[自定义密码]</password>
            </replica>
            <replica>
                <host>juju-1</host>
                <port>11000</port>
                <user>default</user>
                <password>[自定义密码]</password>
            </replica>
        </shard>
    </cluster_3shards_2replica>
</clickhouse_remote_servers>

<macros>
    <shard>01</shard>
    <replica>cluster_01_1</replica> <!--副本区分-->
</macros>

<networks>
    <ip>::/0</ip>
</networks>

<!--压缩相关配置-->
<clickhouse_compression><!--MergeTree引擎表的数据压缩设置。配置模板如-->
    <case>
        <!-- 数据部分的最小大小 -->
        <min_part_size>10000000000</min_part_size>
        <!-- 数据部分大小与表大小的比率 -->
        <min_part_size_ratio>0.01</min_part_size_ratio>
        <method>lz4</method> <!--压缩算法lz4压缩比zstd快, 更占磁盘-->
    </case>
</clickhouse_compression>
</yandex>

e、修改metrika02.xml配置信息

metrika02.xml内容跟metrika01.xml信息基本相同，只需调整分片和副本命名（参照规划）。

cp -p metrika01.xml metrika02.xml

<macros>
    <shard>03</shard>
    <replica>cluster_03_2</replica> <!--当前节点主机名-->
</macros>

f、修改users01.xml配置信息

1、用户信息如果设定密码，只需要在标签中设定密码信息即可

2、增加用户时候，用户ID为标签，增加相关信息即可

3、增加用户bonc_app，置于标签之内

    <users>
    ...
        <bonc_app>
            <password>[自定义密码]</password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
            <access_management>1</access_management>
        </bonc_app>
      <users>

同步复制users02.xml信息，完全同于users01.xml

cp -p users01.xml users02.xml

h、删除config.xml和users.xml文件（可选，防止默认启动读取）

2、其它主机配置信息修改

把config01.xml、config02.xml、users01.xml、users02.xml、metrika01.xml、metrika02.xml 分别复制到juju-2、juju-3对应的配置目录下，修改对应的配置信息

juju-2上config01.xml、config02.xml、users01.xml、users02.xml内容不需要修改；metrika配置内容修改如下：

1、metrika01.xm修改内容（分片及备份信息）

<macros>
    <shard>02</shard>
    <replica>cluster_02_1</replica> <!--当前节点主机名-->
</macros>

2、metrika02.xm修改内容（分片及备份信息）

<macros>
    <shard>01</shard>
    <replica>cluster_01_2</replica> <!--当前节点主机名-->
</macros>

juju-3上config01.xml、config02.xml、users01.xml、users02.xml内容不需要修改；metrika配置内容修改如下：

1、metrika01.xm修改内容（分片及备份信息）

<macros>
    <shard>03</shard>
    <replica>cluster_03_1</replica> <!--当前节点主机名-->
</macros>

2、metrika02.xm修改内容（分片及备份信息）

<macros>
    <shard>02</shard>
    <replica>cluster_02_2</replica> <!--当前节点主机名-->
</macros>

3、启动服务

确认服务器上没有clickhouse服务启动后，用如下命令进行启动（没台服务器两套服务）

nohup clickhouse-server --config-file=/etc/clickhouse-server/config01.xml >null 2>&1 &

nohup clickhouse-server --config-file=/etc/clickhouse-server/config02.xml >null 2>&1 &

启动后确认是否正常：

客户端登录确认：

clickhouse-client -u bonc_app --password [自定义密码] --port 10000 -m -h juju-1

clickhouse客户端常用参数

客户端参数	含义
-h -host	指定服务器主机
--port	指定端口，默认值：9000
-u -user	指定用户，默认：default
--password	密码默认：空字符串
-d -database	指定操作的数据库，默认：default
-q -query	非交互式下的查询语句
-m -multiline	允许多行的居于查询
-f -format	使用指定的默认格式输出结果
-t -time	非交互式下会打印查询执行的时间窗口
-stacktrace	如果出现异常，会打印堆栈跟踪信息
-config-file	配置文件的名称