2021-06-08 clickhouse 安装

服务器环境

OS : CentOS Linux release 7.5.1804 (Core)
CPU: 4
MEM: 8g

clickhouse 版本

ClickHouse client version 21.4.6.55 (official build).
ClickHouse server version 21.4.6 revision 54447.

线上尽量使用lts版本：v21.3.10.1-lts（安装方式相同）
https://github.com/ClickHouse/ClickHouse/releases/tag/v21.3.10.1-lts

离线安装

下载安装包：

rpm -ivh clickhouse-common-static-21.4.6.55-2.x86_64.rpm
rpm -ivh clickhouse-client-21.4.6.55-2.noarch.rpm
rpm -ivh clickhouse-server-21.4.6.55-2.noarch.rpm

如果出现依赖报错，再安装两个包

rpm -ivh libtool-ltdl-2.4.2-22.el7_3.x86_64.rpm
rpm -ivh unixODBC-2.3.1-14.el7.x86_64.rpm

配置文件更改

各配置文件位置及作用

# 服务端配置
/etc/clickhouse-server
  config.xml # 全局配置文件
  user.xml # 用户配置文件
  
# 客户端配置
/etc/clickhouse-client
  config.xml # 客户端配置文件
  
# ClickHouse文件句柄数配置
/etc/security/limits.d/clickhouse.conf
# 默认值262144，应该足够了
clickhouse      soft    nofile  262144
clickhouse      hard    nofile  262144

可修改的配置项（修改与否看需求）

/etc/clickhouse-server/config.xml （全局配置）

    ```
        <!-- Path to data directory, with trailing slash.数据目录 -->
        <path>/var/lib/clickhouse/</path>
        <!-- Path to temporary data for processing hard queries.临时文件目录 -->
        <tmp_path>/var/lib/clickhouse/tmp/</tmp_path>
        <!-- Directory with user provided files that are accessible by 'file' table function. -->
        <user_files_path>/var/lib/clickhouse/user_files/</user_files_path>
        
        <!-- Possible levels: https://github.com/pocoproject/poco/blob/poco-1.9.4-release/Foundation/include/Poco/Logger.h#L105 日志存储位置及配置-->
            <level>trace</level>
            <log>/var/log/clickhouse-server/clickhouse-server.log</log>
            <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
            <!-- Rotation policy
                 See https://github.com/pocoproject/poco/blob/poco-1.9.4-release/Foundation/include/Poco/FileChannel.h#L54-L85
              -->
            <size>1000M</size>
            <count>10</count>
    ```

/etc/clickhouse-server/users.xml （用户配置）

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

            <!-- Settings profile for user. -->
            <profile>default</profile>

            <!-- Quota for user. -->
            <quota>default</quota>

            <!-- User can create other users and grant rights to them. -->
            <!-- <access_management>1</access_management> -->
        </default>
        <fas_rw>
            <password>xxxx</password>
            <ip>::/0</ip>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
                <!-- <ip>10.0.0.38</ip> -->
            </networks>
            <profile>default</profile>
            <quota>default</quota>
            <allow_databases>
                <database>default</database>
                <database>zw_xx</database>
                <database>zw_yy</database>
                <database>zw_kk</database>
            </allow_databases>
            <!-- <access_management>1</access_management> -->
         </fas_rw>

启动方式

以服务方式启动

systemctl enable clickhouse-server
systemctl start clickhouse-server

启动后进程状态

ps -ef | grep clickhouse

clickho+  53674      1  3 18:40 ?        00:00:00 clickhouse-watchdog        --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid
clickho+  53682  53674 24 18:40 ?        00:00:01 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid

查看ClickHouse服务状态(正常状态)

systemctl status clickhouse-server -l

● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
   Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2021-05-07 18:40:57 PDT; 2min 58s ago
 Main PID: 53674 (clckhouse-watch)
    Tasks: 48
   CGroup: /system.slice/clickhouse-server.service
           ├─53674 clickhouse-watchdog        --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid
           └─53682 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid

May 07 18:40:57 localhost.localdomain systemd[1]: Started ClickHouse Server (analytic DBMS for big data).
May 07 18:40:57 localhost.localdomain systemd[1]: Starting ClickHouse Server (analytic DBMS for big data)...
May 07 18:40:58 localhost.localdomain clickhouse-server[53674]: Processing configuration file '/etc/clickhouse-server/config.xml'.
May 07 18:40:58 localhost.localdomain clickhouse-server[53674]: Logging trace to /var/log/clickhouse-server/clickhouse-server.log
May 07 18:40:58 localhost.localdomain clickhouse-server[53674]: Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
May 07 18:40:59 localhost.localdomain clickhouse-server[53674]: Processing configuration file '/etc/clickhouse-server/config.xml'.
May 07 18:40:59 localhost.localdomain clickhouse-server[53674]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/config.xml'.
May 07 18:40:59 localhost.localdomain clickhouse-server[53674]: Processing configuration file '/etc/clickhouse-server/users.xml'.
May 07 18:40:59 localhost.localdomain clickhouse-server[53674]: Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/users.xml'.

接入mysql下游

通过canal接入(暂停于改表问题)

请参看canal部署文档

通过 原生引擎MaterializeMySQL 接入

登录clickhouse 实例，设置参数

SET allow_experimental_database_materialize_mysql=1;

创建同步库(举例)

#同步单位以 库 为单位， 可以做到多个mysql上游的库复制到一个clickhouse实例里面
CREATE DATABASE [database_name] ENGINE = MaterializeMySQL('[ip]:[port]','[database_name]','[user]','[pwd]');

同步机制

#通过gtid,复制，所以上游的mysql源必须大于5.7，并且支持gitd

查看位置点变化

#在/var/lib/clickhouse/metadata 目录下，查看位置点变化
root@CLICKHOUSE-TEST1:/var/lib/clickhouse/metadata/ckdb#cat .metadata 
Version:  2
Binlog File:  mysql-bin.000034
Executed GTID:    37bdf984-8737-11e9-9b67-005056ad64ac:4,a0b7d2f0-887d-11eb-880c-005056b296ae:1-2184942,a2c06443-7271-11eb-8569-005056b296ae:1-206327
Binlog Position:  12922

Data Version: 1

root@CLICKHOUSE-TEST1:/var/lib/clickhouse/metadata/ckdb#cat .metadata 
Version:  2
Binlog File:  mysql-bin.000034
Executed GTID:    37bdf984-8737-11e9-9b67-005056ad64ac:4,a0b7d2f0-887d-11eb-880c-005056b296ae:1-2184943,a2c06443-7271-11eb-8569-005056b296ae:1-206327
Binlog Position:  13517

Data Version: 1