clickhouse报错 Connection refused: connect

一般报这种错误，基本都是配置问题导致。常见的配置有用户名、密码、host、port、防火墙等。代码方面排查时这四个点可能都要看一下。而在clickhouse中，则着重看一下防火墙以及clickhouse的host和port是否对外开放。我的报错如下：

我这里代码检查过是没问题的，而且用户名密码类的问题一般都有更清晰的提示。所以这里接着看clickhouse服务器防火墙以及host和port配置。

systemctl status firewalld

 防火墙没有打开，接着看clickhouse-server的host和port配置。该配置文件一般默认在/etc/clickhouse-server路径下，查看config.xml中以下两项配置：

<!-- Listen specified address.
         Use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere.
         Notes:
         If you open connections from wildcard address, make sure that at least one of the following measures applied:
         - server is protected by firewall and not accessible from untrusted networks;
         - all users are restricted to subset of network addresses (see users.xml);
         - all users have strong passwords, only secure (TLS) interfaces are accessible, or connections are only made via TLS interfaces.
         - users without password have readonly access.
         See also: https://www.shodan.io/search?query=clickhouse
      -->
<listen_host>::</listen_host>

    <!-- Port for interaction by native protocol with:
         - clickhouse-client and other native ClickHouse tools (clickhouse-benchmark, clickhouse-copier);
         - clickhouse-server with other clickhouse-servers for distributed query processing;
         - ClickHouse drivers and applications supporting native protocol
         (this protocol is also informally called as "the TCP protocol");
         See also 'tcp_port_secure' for secure connections.
    -->
<tcp_port>9000</tcp_port>

可以看到主机和端口都是正常配置的，注意这里还可以多看一下users.xml文件的网络配置。它也可以控制对clickhouse-server的访问，我的配置如下：

 <!-- List of networks with open access.

                 To open access from everywhere, specify:
                    <ip>::/0</ip>

                 To open access only from localhost, specify:
                    <ip>::1</ip>
                    <ip>127.0.0.1</ip>

                 Each element of list has one of the following forms:
                 <ip> IP-address or network mask. Examples: 213.180.204.3 or 10.0.0.1/8 or 10.0.0.1/255.255.255.0
                     2a02:6b8::3 or 2a02:6b8::3/64 or 2a02:6b8::3/ffff:ffff:ffff:ffff::.
                 <host> Hostname. Example: server01.yandex.ru.
                     To check access, DNS query is performed, and all received addresses compared to peer address.
                 <host_regexp> Regular expression for host names. Example, ^server\d\d-\d\d-\d\.yandex\.ru$
                     To check access, DNS PTR query is performed for peer address and then regexp is applied.
                     Then, for result of PTR query, another DNS query is performed and all received addresses compared to peer address.
                     Strongly recommended that regexp is ends with $
                 All results of DNS requests are cached till server restart.
            -->
<networks>
   <ip>::/0</ip>
</networks>

可以看到我的配置也是没问题的，然后我发现直接通过:
        clickhouse-client
可以访问数据库，但是通过 :
        clickhouse-client -h 192.168.71.136 --port 9000
不能访问。于是判断是端口问题。通过
        lsof -i:9000
命令查看有java进程在使用该端口，如下：

通过ps命令
ps -ef|grep 1704
查看进程的详细信息如下：

 可以推测出9000端口其实是被hadoop集群使用了，所以这里更改一下tcp连接的端口。这里我先是通过
        lsof -i:9001
查看9001端口没有被占用，然后修改config.xml中的<tcp_port>属性值。
再次通过命令
        clickhouse-client -h 192.168.71.136 --port 9001
连接后发现还是如下报错：

ClickHouse client version 21.7.2.7 (official build).
Connecting to 192.168.71.136:9001 as user default.
Code: 516. DB::Exception: Received from 192.168.71.136:9001. DB::Exception: default: Authentication failed: password is incorrect or there is no user with such name. 

猜测可能需要重启服务，通过如下命令
        systemctl restart clickhouse-server
重启后连接成功