服务器环境
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
