服務(wù)器環(huán)境
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.
線(xiàn)上盡量使用lts版本：v21.3.10.1-lts（安裝方式相同）
https://github.com/ClickHouse/ClickHouse/releases/tag/v21.3.10.1-lts
離線(xiàn)安裝
下載安裝包：
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
如果出現(xiàn)依賴(lài)報(bào)錯(cuò)，再安裝兩個(gè)包
rpm -ivh libtool-ltdl-2.4.2-22.el7_3.x86_64.rpm
rpm -ivh unixODBC-2.3.1-14.el7.x86_64.rpm
配置文件更改
各配置文件位置及作用
# 服務(wù)端配置
/etc/clickhouse-server
config.xml # 全局配置文件
user.xml # 用戶(hù)配置文件
# 客戶(hù)端配置
/etc/clickhouse-client
config.xml # 客戶(hù)端配置文件
# ClickHouse文件句柄數(shù)配置
/etc/security/limits.d/clickhouse.conf
# 默認(rèn)值262144，應(yīng)該足夠了
clickhouse soft nofile 262144
clickhouse hard nofile 262144
可修改的配置項(xiàng)（修改與否看需求）
/etc/clickhouse-server/config.xml （全局配置）
```
<!-- Path to data directory, with trailing slash.數(shù)據(jù)目錄 -->
<path>/var/lib/clickhouse/</path>
<!-- Path to temporary data for processing hard queries.臨時(shí)文件目錄 -->
<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 日志存儲(chǔ)位置及配置-->
<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 （用戶(hù)配置）
<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>
啟動(dòng)方式
以服務(wù)方式啟動(dòng)
systemctl enable clickhouse-server
systemctl start clickhouse-server
啟動(dòng)后進(jìn)程狀態(tài)
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服務(wù)狀態(tài)(正常狀態(tài))
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下游
通過(guò)canal接入(暫停于改表問(wèn)題)
請(qǐng)參看canal部署文檔
通過(guò) 原生引擎MaterializeMySQL 接入
登錄clickhouse 實(shí)例，設(shè)置參數(shù)
SET allow_experimental_database_materialize_mysql=1;
創(chuàng)建同步庫(kù)(舉例)
#同步單位以 庫(kù) 為單位， 可以做到多個(gè)mysql上游的庫(kù)復(fù)制到一個(gè)clickhouse實(shí)例里面 CREATE DATABASE [database_name] ENGINE = MaterializeMySQL('[ip]:[port]','[database_name]','[user]','[pwd]');同步機(jī)制
#通過(guò)gtid,復(fù)制，所以上游的mysql源必須大于5.7，并且支持gitd查看位置點(diǎn)變化
#在/var/lib/clickhouse/metadata 目錄下，查看位置點(diǎn)變化 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
