clickhouse安装配置
ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。
clickhouse安装包下载地址 (opens new window)
- centos\redhat安装clickhouse-client
curl -o /etc/yum.repos.d/clickhouse.repo https://packages.clickhouse.com/rpm/clickhouse.repo
yum install -y clickhouse-client
2
# clickhouse 部署
# 使用docker-compose部署
mkdir /data/clickhouse && cd /data/clickhouse
cat >docker-compose.yaml<<'EOF'
version: '3'
#宿主机IP: 172.16.100.156
services:
prod_clickhouse_server:
restart: always
image: docker.1ms.run/clickhouse:24.8.12.28
container_name: prod_clickhouse_server
environment:
- TZ=Asia/Shanghai
ulimits:
nofile:
soft: 262144
hard: 262144
volumes:
- /etc/localtime:/etc/localtime
- ./clickhouse_database:/var/lib/clickhouse #所属主:101 ,chown 101.101 clickhouse_database
- ./clickhouse-server:/etc/clickhouse-server #先注释启动容器之后,把目录拷贝出来,再关闭注释
- ./clickhouse_log:/var/log/clickhouse-server
ports:
- "9002:9000"
- "8123:8123"
- "9004:9004"
deploy:
resources:
limits:
memory: 4G
reservations:
memory: 2G
network_mode: bridge
# networks:
# clickhouse:
# ipv4_address: 192.18.0.101
#networks:
# clickhouse:
# external:
# name: "yfk_clickhouse_net"
EOF
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
- 启动
docker-compose -f docker-compose.yaml up -d
拷贝配置目录,拷贝之后修改yaml,重新加载,检测
docker cp prod_clickhouse_server:/etc/clickhouse-server/ ./clickhouse_server/
- 使用mysql命令连接
mysql --protocol tcp -u default -P 9004 -h 192.168.5.112
# 配置密码
修改:user.xml 配置文件
找到:
加密方法
[root@clickhouse-db01 ~]# echo -n 'CickhouseQwe1245' | openssl dgst -sha256 (stdin)= dc5f5b46cd396d91697aa7cf8d9425918056b0c46dcb9e801f6b3874fc040e7a
<password_sha256_hex>dc5f5b46cd396d91697aa7cf8d9425918056b0c46dcb9e801f6b3874fc040e7a</password_sha256_hex>
# 服务器意外断电,clickhouse起不来
该情况部署在容器或宿主机都有可能发生这样的异常
以下是clickhouse 启动其中一段报错
大概解决方法是: 找到 store 里面的一些空的目录,删除
- 查看会看到显示的目录下有许多的空文件:
find ./clickhouse_database/store/ -name checksums.txt -size 0 |sed -r 's#(.*)/checksums.txt#\1#g' |xargs ls -l
- 删除命令:(删除请谨慎!!!)
find ./clickhouse_database/store/ -name checksums.txt -size 0 |sed -r 's#(.*)/checksums.txt#\1#g' |xargs rm -rf
- 重启容器
docker restart prod_clickhouse_server
再次检测,容器没有出现异常重启
- 代理pgsql
代理pgsql文档地址 (opens new window)
# 配置定时任务脚本
通过定时脚本,每分钟监测clickhouse是否有空文件(checksums.txt),进行清理
cat >check_clickhouse.sh<<'EOF'
#!/bin/bash
#容器名称
containerName=prod_clickhouse_server
currTime=`date +"%Y-%m-%d %H:%M:%S"`
Dir=/data/clickhouse/clickhouse_database
cd /tmp
#查看容器是否在重启
exist=`/usr/bin/docker inspect --format '{{.State.Restarting}}' ${containerName}`
if [ -d $Dir/store/ ];then
if [ "${exist}" == "true" ];then
find ${Dir}/store/ -name checksums.txt -size 0 |sed -r 's#(.*)/checksums.txt#\1#g' |xargs rm -rf
sleep 5
/usr/bin/docker restart ${containerName}
#记录
test -f /tmp/docker_monitor.log || touch /tmp/docker_monitor.log
echo "${currTime} clickhouse容器正在重启,容器名称:${containerName}" >> /tmp/docker_monitor.log
else
echo "${containerName} 运行正常"
fi
else
echo "${Dir}/store 路径不存在"
fi
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
EOF
# clickhouse查询
- 查看进程id 相关信息
select query_id,user,read_rows,elapsed,memory_usage,peak_memory_usage,query from system.processes;
- 停止 指定的 query_id
KILL query where query_id='203970a2-5628-4e7f-8885-c896379cae29';
- 停止 default用户所有的query_id
KILL query where user='default'