裴东莞嘘网站汉建设,工商查询官网入口查询,123,网站优化知识尝试搭建双节点clickhouse#xff0c;以做数据存储 环境准备
#创建clickhouse用户与用户组
sudo groupadd clickhouse
sudo useradd -m clickhouse -g clickhouse
#密码为clickhouse
sudo passwd clickhouse#赋予权限
chmod -R 777 /opt/comm_app#配置使用sudo命令的用户
vim… 尝试搭建双节点clickhouse以做数据存储 环境准备
#创建clickhouse用户与用户组
sudo groupadd clickhouse
sudo useradd -m clickhouse -g clickhouse
#密码为clickhouse
sudo passwd clickhouse#赋予权限
chmod -R 777 /opt/comm_app#配置使用sudo命令的用户
vim /etc/sudoers
clickhouse ALL(ALL:ALL) ALL下载安装
#切换为clickhouse操作
export LATEST_VERSION22.3.20.29
#21.2.xx 之后的tgz包路径变成https://repo.clickhouse.com/tgz/stable下
#21.2.xx 之前的还在https://repo.clickhouse.com/tgz下
curl -O https://packages.clickhouse.com/tgz/stable/clickhouse-common-static-$LATEST_VERSION-amd64.tgz
curl -O https://packages.clickhouse.com/tgz/stable/clickhouse-common-static-dbg-$LATEST_VERSION-amd64.tgz
curl -O https://packages.clickhouse.com/tgz/stable/clickhouse-server-$LATEST_VERSION-amd64.tgz
curl -O https://packages.clickhouse.com/tgz/stable/clickhouse-client-$LATEST_VERSION-amd64.tgztar -xzvf clickhouse-common-static-$LATEST_VERSION-amd64.tgz
sudo clickhouse-common-static-$LATEST_VERSION/install/doinst.shtar -xzvf clickhouse-common-static-dbg-$LATEST_VERSION-amd64.tgz
sudo clickhouse-common-static-dbg-$LATEST_VERSION/install/doinst.shtar -xzvf clickhouse-server-$LATEST_VERSION-amd64.tgz
sudo clickhouse-server-$LATEST_VERSION/install/doinst.shtar -xzvf clickhouse-client-$LATEST_VERSION-amd64.tgz
sudo clickhouse-client-$LATEST_VERSION/install/doinst.sh配置调整
1.配置开放外网访问vim /etc/clickhouse-server/config.d/listen.xml
clickhouselisten_host0.0.0.0/listen_host
/clickhouse2.配置修改登录密码vim /etc/clickhouse-server/users.d/default-password.xml
clickhouseusersdefaultpasswordEqcT3KF4kQ?v/password/default/users
/clickhouse3.基础配置3.1 集群分配配置host password port 需调整为正式配置值在remote_servers标签下vim /etc/clickhouse-server/config.xmltransport_clustershard!-- 权重新增一条数据的时候有多大的概率落入该分片默认值1 --weight1/weightinternal_replicationtrue/internal_replicationreplicahost10.22.1.116/hostport1200/portuserdefault/userpasswordclickhouse/passwordcompressiontrue/compression/replica/shardshard!-- 权重新增一条数据的时候有多大的概率落入该分片默认值1 --weight1/weightinternal_replicationtrue/internal_replicationreplicahost10.22.1.117/hostport1200/portuserdefault/userpasswordclickhouse/passwordcompressiontrue/compression/replica/shard/transport_cluster
3.2 zookeeper配置 zookeepernodehost10.22.3.217/hostport2181/port/nodenodehost172.27.2.140/hostport2180/port/nodenodehost172.27.2.140/hostport2179/port/node/zookeeper
3.3配置分片名称 每个机器下分别配置自己的ip与编号 01 02 即可# 10.22.1.116 服务器
macrosshard01/shardreplica10.22.1.116/replica
/macros
# 10.22.1.117 服务器
macrosshard02/shardreplica10.22.1.117/replica
/macros
3.4修改默认端口tcp_port1200/tcp_port服务启动
## 服务启动
sudo service clickhouse-server start# 服务重启
sudo service clickhouse-server restart# 服务停止
sudo service clickhouse-server stop验证
# 查看日志与错误日志
tail -f /var/log/clickhouse-server/clickhouse-server.log
tail -f /var/log/clickhouse-server/clickhouse-server.err.log# 使用client链接数据库进行验证
clickhouse-client -h 10.22.1.116 --port 1200 -u default --password EqcT3KF4kQ?v -m -n
clickhouse-client -h 10.22.1.117 --port 1200 -u default --password EqcT3KF4kQ?v -m -nselect 1# 查询数据文件大小
SELECTdatabase, table,formatReadableSize(sum(bytes_on_disk)) AS sizeFROM system.partsWHERE active 1GROUP BYdatabase, tableORDER BY sum(bytes_on_disk) DESC;# 查询表数据量
SELECT database, table, sum(rows) as rows FROM system.parts
WHERE active 1 GROUP BY database, table ORDER BY rows DESC; 地址jdbc:clickhouse://172.27.2.140:8123defaultclickhouse异常以及处理办法
在查看分片表时报出该异常原因是分配连接需要使用tcp配置的端口进行连接由于配置与设定不一致导致
2024-02-22 17:36:42] Code: 279. DB::NetException: All connection tries failed. Log:
[2024-02-22 17:36:42] Code: 210. DB::NetException: Connection refused (172.27.2.140:1200). (NETWORK_ERROR) (version 22.3.20.29 (official build))
[2024-02-22 17:36:42] Code: 210. DB::NetException: Connection refused (172.27.2.140:1200). (NETWORK_ERROR) (version 22.3.20.29 (official build))
[2024-02-22 17:36:42] Code: 210. DB::NetException: Connection refused (172.27.2.140:1200). (NETWORK_ERROR) (version 22.3.20.29 (official build))
[2024-02-22 17:36:42] : While executing Remote. (ALL_CONNECTION_TRIES_FAILED) (version 22.3.20.29 (official build))
[2024-02-22 17:36:42] , server ClickHouseNode [urihttp://172.27.2.147:8123/default, options{session_idDataGrip_846de358-2c8b-47be-9e5e-193b1bb7a7b7}]-496462918解决办法 在配置文件中修改port中的值确保与tcp_port 标签中的端口一致 remote_serverstranport_clustershard!-- 权重新增一条数据的时候有多大的概率落入该分片默认值1 --weight1/weightinternal_replicationtrue/internal_replicationreplicahost172.27.2.140/hostport1200/portuserdefault/userpasswordclickhouse/passwordcompressiontrue/compression/replica/shardshard!-- 权重新增一条数据的时候有多大的概率落入该分片默认值1 --weight1/weightinternal_replicationtrue/internal_replicationreplicahost172.27.2.147/hostport1200/portuserdefault/userpasswordclickhouse/passwordcompressiontrue/compression/replica/shard/tranport_cluster/remote_servers
在删除表后重新建立表的时候报
[2024-02-22 17:33:14] Code: 253. DB::Exception: Replica /clickhouse/tables/01/location_cluster2/replicas/172.27.2.140 already exists. (REPLICA_IS_ALREADY_EXIST) (version 22.3.20.29 (official build))
[2024-02-22 17:33:14] , server ClickHouseNode [urihttp://172.27.2.140:8123/default, options{session_idDataGrip_ac9993a4-2991-4d5f-9e1f-96cec00436f2}]381624111解决办法
Clickhouse drop table on cluster but not delete on zookeeper - xibuhaohao - 博客园 (cnblogs.com)
日志中报 由于上述删除zk节点导致找不到节点无法再分布式表中插入数据。
2024.02.22 19:23:51.001232 [ 19421 ] {} Error void DB::AsynchronousMetrics::update(std::chrono::system_clock::time_point): Cannot get replica delay for table: default.location_cluster2: Code: 999. Coordination::Exception: No node, path: /clickhouse/tables/02/location_cluster2/replicas. (KEEPER_EXCEPTION), Stack trace (when copying this message, always include the lines below):DB::Exception::Exception(std::__1::basic_stringchar, std::__1::char_traitschar, std::__1::allocatorchar const, int, bool) 0xb3ac1da in /usr/bin/clickhouse
Coordination::Exception::Exception(std::__1::basic_stringchar, std::__1::char_traitschar, std::__1::allocatorchar const, Coordination::Error, int) 0x16bd55b5 in /usr/bin/clickhouse解决办法
重新建立表建立zk节点
分布式表中无法进行mutations操作删除修改等需要到原数据表进行删除。
[07000][48] Code: 48. DB::Exception: Table engine Distributed doesnt support mutations. (NOT_IMPLEMENTED) (version 22.3.20.29 (official build)) , server ClickHouseNode [urihttp://172.27.2.140:8123/default, options{session_idDataGrip_717b36fd-bd96-448e-9292-a0de15ad3a22}]-1614401145解决办法
到源数据表中进行数据删除修改等
