解决clickhouse裸奔问题
程序员文章站
2022-05-19 09:57:36
...
上次安装完成以后，遗漏了3个问题
1、查看集群信息为什么会出现这么多test的集群
2、用户鉴权问题
3、如何放开连接的IP地址
问题1： 查看集群信息为什么会出现这么多test的集群。
只需要将/etc/clickhouse-server/config.xml配置文件中的test注释就可以了再次查询就正常了
<remote_servers incl="clickhouse_remote_servers" >
<!-- Test only shard config for testing distributed storage -->
<!-- <test_shard_localhost>
<shard>
<replica>
<host>localhost</host>
<port>9000</port>
</replica>
</shard>
</test_shard_localhost>
<test_cluster_two_shards_localhost>
<shard>
<replica>
<host>localhost</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>localhost</host>
<port>9000</port>
</replica>
</shard>
</test_cluster_two_shards_localhost>
<test_cluster_two_shards>
<shard>
<replica>
<host>127.0.0.1</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>127.0.0.2</host>
<port>9000</port>
</replica>
</shard>
</test_cluster_two_shards>
<test_shard_localhost_secure>
<shard>
<replica>
<host>localhost</host>
<port>9440</port>
<secure>1</secure>
</replica>
</shard>
</test_shard_localhost_secure>
<test_unavailable_shard>
<shard>
<replica>
<host>localhost</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>localhost</host>
<port>1</port>
</replica>
</shard>
</test_unavailable_shard> -->
</remote_servers>
再次查询就没有test集群了
cdh3 :) SELECT * FROM system.clusters;
SELECT *
FROM system.clusters
┌─cluster────────────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name─┬─host_address───┬─port─┬─is_local─┬─user────┬─default_database─┬─errors_count─┬─estimated_recovery_time─┐
│ perftest_3shards_2replicas │ 1 │ 1 │ 1 │ cdh1 │ 192.168.18.160 │ 9000 │ 0 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 2 │ 1 │ 1 │ cdh2 │ 192.168.18.161 │ 9000 │ 0 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 3 │ 1 │ 1 │ cdh3 │ 192.168.18.162 │ 9000 │ 1 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 4 │ 1 │ 1 │ cdh4 │ 192.168.18.163 │ 9000 │ 0 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 5 │ 1 │ 1 │ cdh5 │ 192.168.18.164 │ 9000 │ 0 │ default │ │ 0 │ 0 │
└────────────────────────────┴───────────┴──────────────┴─────────────┴───────────┴────────────────┴──────┴──────────┴─────────┴──────────────────┴──────────────┴─────────────────────────┘
5 rows in set. Elapsed: 0.005 sec.
问题2:用户鉴权问题
修改user.xml文件
如果使用明文直接配置下面这个就可以了，clickhouse还支持sha256和sha1这2种password_sha256_hex、password_double_sha1_hex
<password></password>
这里我选择了password_sha256_hex。需要注释了明文的标签
<!--
<password></password>
-->
<password_sha256_hex>d212ca06a4aa364b46fbbd7464f27866a69009e0c1a82a0caf5771fe55b1de35</password_sha256_hex>
另外增加了一个用户xlucas只读用户
其中部分的配置如下
<users>
<default>
<password_sha256_hex>d212ca06a4aa364b46fbbd7464f27866a69009e0c1a82a0caf5771fe55b1de35</password_sha256_hex>
<networks incl="networks" replace="replace">
<ip>::/0</ip>
</networks>
<quota>default</quota>
</default>
<xlucas>
<password>123456</password>
<networks incl="networks" replace="replace">
<ip>::/0</ip>
</networks>
<profile>readonly</profile>
<quota>default</quota>
</xlucas>
</users>
其中生成sha256sum的Hash值可以执行如下命令（第一行），回车后输出两行信息（第二行和第三行），其中第二行是原始密码，第三行是加密的密文，配置文件使用第三行的字符串，客户端登陆是使用第二行的密码
[[email protected] bin]# PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
hogxNyM9
d212ca06a4aa364b46fbbd7464f27866a69009e0c1a82a0caf5771fe55b1de35
修改metrika.xml文件，在每台机器下面的每个配置都需要增user和password
<internal_replication>true</internal_replication>
<host>cdh2</host>
<port>9000</port>
<user>default</user>
<password>hogxNyM9</password>
重启服务：
/etc/init.d/clickhouse-server stop
/etc/init.d/clickhouse-server start
查看服务是否是running
/etc/init.d/clickhouse-server status
验证
不使用密码连接是会报错的
[[email protected] clickhouse-server]# clickhouse-client
ClickHouse client version 20.1.6.30 (official build).
Connecting to localhost:9000 as user default.
Code: 194. DB::Exception: Received from localhost:9000. DB::Exception: Password required for user default.
使用用户和密码连接
[[email protected] clickhouse-server]# clickhouse-client -h 127.0.0.1 --port 9000 -u default --password hogxNyM9 --multiline
ClickHouse client version 20.1.6.30 (official build).
Connecting to 127.0.0.1:9000 as user default.
Connected to ClickHouse server version 20.1.6 revision 54431.
cdh4 :) select now();
SELECT now()
┌───────────────now()─┐
│ 2020-03-15 20:09:46 │
└─────────────────────┘
1 rows in set. Elapsed: 0.071 sec.
cdh4 :) SELECT * FROM system.clusters;
SELECT *
FROM system.clusters
┌─cluster────────────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name─┬─host_address───┬─port─┬─is_local─┬─user────┬─default_database─┬─errors_count─┬─estimated_recovery_time─┐
│ perftest_3shards_2replicas │ 1 │ 1 │ 1 │ cdh1 │ 192.168.18.160 │ 9000 │ 0 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 2 │ 1 │ 1 │ cdh2 │ 192.168.18.161 │ 9000 │ 0 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 3 │ 1 │ 1 │ cdh3 │ 192.168.18.162 │ 9000 │ 0 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 4 │ 1 │ 1 │ cdh4 │ 192.168.18.163 │ 9000 │ 1 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 5 │ 1 │ 1 │ cdh5 │ 192.168.18.164 │ 9000 │ 0 │ default │ │ 0 │ 0 │
└────────────────────────────┴───────────┴──────────────┴─────────────┴───────────┴────────────────┴──────┴──────────┴─────────┴──────────────────┴──────────────┴─────────────────────────┘
5 rows in set. Elapsed: 0.005 sec.
cdh4 :)
使用非默认用户连接
[[email protected] clickhouse-server]# clickhouse-client -h 127.0.0.1 --port 9000 -u xlucas --password 123456 --multiline -q "SELECT now()"
2020-03-15 20:36:16
问题3：如何放开连接的IP地址
默认情况下面如果是ip加上端口去连接会报错连接不上。如果不指定或者用127.0.0.7去连接是可以连接上的
[[email protected] clickhouse-server]# clickhouse-client -h 192.168.18.162 --port 9000 -u xlucas --password 123456 --multiline -q "SELECT now()"
Code: 210. DB::NetException: Connection refused (192.168.18.162:9000)
需要修改config.xml 将下面这个listen_host打开，对所有IP开放，打开以后重启服务
<!-- Listen specified host. use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere. -->
<listen_host>::</listen_host>
验证：
本机客户端访问
[[email protected] clickhouse-server]# clickhouse-client -h 192.168.18.160 --port 9000 -u default --password hogxNyM9 --multiline
ClickHouse client version 20.1.6.30 (official build).
Connecting to 192.168.18.160:9000 as user default.
Connected to ClickHouse server version 20.1.6 revision 54431.
cdh1 :)
集群其他客户端访问
[[email protected] clickhouse-server]# clickhouse-client -h 192.168.18.160 --port 9000 -u default --password hogxNyM9 --multiline
ClickHouse client version 20.1.6.30 (official build).
Connecting to 192.168.18.160:9000 as user default.
Connected to ClickHouse server version 20.1.6 revision 54431.
cdh1 :) SELECT * FROM system.clusters;
SELECT *
FROM system.clusters
┌─cluster────────────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name─┬─host_address───┬─port─┬─is_local─┬─user────┬─default_database─┬─errors_count─┬─estimated_recovery_time─┐
│ perftest_3shards_2replicas │ 1 │ 1 │ 1 │ cdh1 │ 192.168.18.160 │ 9000 │ 1 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 2 │ 1 │ 1 │ cdh2 │ 192.168.18.161 │ 9000 │ 0 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 3 │ 1 │ 1 │ cdh3 │ 192.168.18.162 │ 9000 │ 0 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 4 │ 1 │ 1 │ cdh4 │ 192.168.18.163 │ 9000 │ 0 │ default │ │ 0 │ 0 │
│ perftest_3shards_2replicas │ 5 │ 1 │ 1 │ cdh5 │ 192.168.18.164 │ 9000 │ 0 │ default │ │ 0 │ 0 │
└────────────────────────────┴───────────┴──────────────┴─────────────┴───────────┴────────────────┴──────┴──────────┴─────────┴──────────────────┴──────────────┴─────────────────────────┘
5 rows in set. Elapsed: 0.090 sec.
更多内容关注公众号"数据专场"
上一篇： PHP用cookies自动登录该如何写
下一篇： RMAN备份学习笔记