rpm安装clickhouse(单机)
1.下载安装介质
下载地址为:https://packagecloud.io/Altinity/clickhouse
或是这里下载
https://mirrors.tuna.tsinghua.edu.cn/clickhouse/rpm/stable/x86_64/
我这里下载的是如下三个包
clickhouse-client-21.6.6.51-2.noarch.rpm
clickhouse-common-static-21.6.6.51-2.x86_64.rpm
clickhouse-server-21.6.6.51-2.noarch.rpm
2.安装
rpm -ivh clickhouse-common-static-21.6.6.51-2.x86_64.rpm
rpm -ivh clickhouse-client-21.6.6.51-2.noarch.rpm
rpm -ivh clickhouse-server-21.6.6.51-2.noarch.rpm
这里会提示输入默认的密码，可以直接回车 不需要密码，后面单独对用户进行设置密码
3.修改数据目录和日志目录
设置数据目录和日志目录
[root@localhost ~]# mkdir -p /home/middle/clickhouse/data
[root@localhost ~]# mkdir -p /home/middle/clickhouse/log
[root@localhost ~]# chown -R clickhouse:clickhouse /home/middle/clickhouse/
修改数据目录
vi /etc/clickhouse-server/config.xml
<!-- Path to data directory, with trailing slash. -->
<path>/home/middle/clickhouse/data/</path>
修改日志目录
<level>error</level>
<log>/home/middle/clickhouse/log/clickhouse-server.log</log>
<errorlog>/home/middle/clickhouse/log/clickhouse-server.err.log</errorlog>
4.配置远程登录
[root@localhost clickhouse]#vi /etc/clickhouse-server/config.xml
把下面2行注释去掉，之前是注释的，现在要开启
<!-- Default values - try listen localhost on IPv4 and IPv6. -->
<listen_host>::1</listen_host>
<listen_host>0.0.0.0</listen_host> ##这个要修改成0.0.0.0 之前的127.0.0.1
5.修改允许删除掉任何表和表分区,将如下两行去掉注释，使其生效
<max_table_size_to_drop>0</max_table_size_to_drop>
<max_partition_size_to_drop>0</max_partition_size_to_drop>
5.启动clickhouse-server
centos 7需要使用systemctl启动
systemctl status clickhouse-server.service
systemctl start clickhouse-server.service
或是使用如下方式启动或停止
[root@localhost]# clickhouse start
[root@localhost]# clickhouse stop
查看端口
[root@localhost clickhouse-server]# ss -nlp|grep 9000
7.停止clickhouse-server：
systemctl stop clickhouse-server.service
8.登陆
clickhouse-client --host localhost
localhost :) show databases;
SHOW DATABASES
Query id: ec5041d8-935d-4b20-bdf8-3f10d468ea73
┌─name────┐
│ default │
│ system │
└─────────┘
2 rows in set. Elapsed: 0.002 sec.
9.配置默认密码(看情况需要)
生产加密密码,我这里明文密码是123456,对其加密后生成的串如下:
[root@elastic1 clickhouse-server]# echo -n 123456 | openssl dgst -sha256
(stdin)= 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
修改配置文件:/etc/clickhouse-server/users.xml
在<users><default></default></users>
加入如下项(红色部分),注释掉password段,加上password_sha256_hex段
<!--<password></password> -->
<password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92</password_sha256_hex>
重新启动
systemctl stop clickhouse-server.service
systemctl start clickhouse-server.service
尝试使用密码登陆
clickhouse-client -h 192.168.1.136 -m -u default --password 123456
10.添加用户
vi /etc/clickhouse-server/users.xml
在default默认的用户下添加用户,注意这里把default用户也开启权限管理模式,方便以后授权
<access_management>1</access_management>
[root@localhost clickhouse]# more /etc/clickhouse-server/users.xml
<?xml version="1.0"?>
<yandex>
<!-- Profiles of settings. -->
<profiles>
<!-- Default settings. -->
<default>
<!-- Maximum memory usage for processing single query, in bytes. -->
<max_memory_usage>10000000000</max_memory_usage>
<!-- How to choose between replicas during distributed query processing.
random - choose random replica from set of replicas with minimum number of errors
nearest_hostname - from set of replicas with minimum number of errors, choose replica
with minimum number of different symbols between replica's hostname and local hostname
(Hamming distance).
in_order - first live replica is chosen in specified order.
first_or_random - if first replica one has higher number of errors, pick a random one from replicas with minimum number of errors.
-->
<load_balancing>random</load_balancing>
</default>
<!-- Profile that allows only read queries. -->
<readonly>
<readonly>1</readonly>
</readonly>
</profiles>
<!-- Users and ACL. -->
<users>
<!-- If user name was not specified, 'default' user is used. -->
<default>
<!-- Password could be specified in plaintext or in SHA256 (in hex format).
If you want to specify password in plaintext (not recommended), place it in 'password' element.
Example: <password>qwerty</password>.
Password could be empty.
If you want to specify SHA256, place it in 'password_sha256_hex' element.
Example: <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>
Restrictions of SHA256: impossibility to connect to ClickHouse using MySQL JS client (as of July 2019).
If you want to specify double SHA1, place it in 'password_double_sha1_hex' element.
Example: <password_double_sha1_hex>e395796d6546b1b65db9d665cd43f0e858dd4303</password_double_sha1_hex>
If you want to specify a previously defined LDAP server (see 'ldap_servers' in the main config) for authentication,
place its name in 'server' element inside 'ldap' element.
Example: <ldap><server>my_ldap_server</server></ldap>
If you want to authenticate the user via Kerberos (assuming Kerberos is enabled, see 'kerberos' in the main config),
place 'kerberos' element instead of 'password' (and similar) elements.
The name part of the canonical principal name of the initiator must match the user name for authentication to succeed.
You can also place 'realm' element inside 'kerberos' element to further restrict authentication to only those requests
whose initiator's realm matches it.
Example: <kerberos />
Example: <kerberos><realm>EXAMPLE.COM</realm></kerberos>
How to generate decent password:
Execute: PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha256sum | tr -d '-'
In first line will be password and in second - corresponding SHA256.
How to generate double SHA1:
Execute: PASSWORD=$(base64 < /dev/urandom | head -c8); echo "$PASSWORD"; echo -n "$PASSWORD" | sha1sum | tr -d '-' | xxd -r -p | sha1sum | tr -d '-'
In first line will be password and in second - corresponding double SHA1.
-->
<!--<password></password> -->
<password_sha256_hex>8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92</password_sha256_hex>
<!-- 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>
<!-- 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>
<devtest>
<password>test123</password>
<access_management>1</access_management>
<networks incl="networks" replace="replace">
<ip>::/0</ip>
</networks>
<profile>default</profile>
<quota>default</quota>
<allow_databases>
<database>db_sentinel</database>
</allow_databases>
</devtest>
</users>
<!-- Quotas. -->
<quotas>
<!-- Name of quota. -->
<default>
<!-- Limits for time interval. You could specify many intervals with different limits. -->
<interval>
<!-- Length of interval. -->
<duration>3600</duration>
<!-- No limits. Just calculate resource usage for time interval. -->
<queries>0</queries>
<errors>0</errors>
<result_rows>0</result_rows>
<read_rows>0</read_rows>
<execution_time>0</execution_time>
</interval>
</default>
</quotas>
</yandex>
重启动
systemctl stop clickhouse-server.service
systemctl start clickhouse-server.service
使用新加的用户登陆
clickhouse-client -h 192.168.1.136 -m -u devtest --password test123
11.创建数据库
使用创建的用户登陆创建数据库
clickhouse-client -h 192.168.1.136 -m -u devtest --password test123
[root@localhost clickhouse]# clickhouse-client -h 192.168.1.136 -m -u devtest --password test123
ClickHouse client version 21.6.6.51 (official build).
Connecting to 192.168.1.136:9000 as user devtest.
Connected to ClickHouse server version 21.6.6 revision 54448.
localhost :) create database db_sentinel;
CREATE DATABASE db_sentinel
Query id: 43c7347e-a008-4689-be43-7abf65bceb57
Ok.
0 rows in set. Elapsed: 0.105 sec.
localhost :) show databases;
SHOW DATABASES
Query id: 10a88870-9a9d-429e-832b-9006a3927fba
┌─name────────┐
│ db_sentinel │
└─────────────┘
1 rows in set. Elapsed: 0.002 sec.
12.修改系统日表保留时间
保留一个月的语法
use system;
ALTER TABLE query_log MODIFY TTL event_date + toIntervalMonth(1);
ALTER TABLE query_thread_log MODIFY TTL event_date + toIntervalMonth(1);
保留天数的语法
use system;
ALTER TABLE query_log MODIFY TTL event_date + toIntervalDay(7);
ALTER TABLE query_thread_log MODIFY TTL event_date + toIntervalDay(7);
ALTER TABLE asynchronous_metric_log MODIFY TTL event_date + toIntervalDay(7);
ALTER TABLE trace_log MODIFY TTL event_date + toIntervalDay(7);
ALTER TABLE metric_log MODIFY TTL event_date + toIntervalDay(7);
select min(event_date) from query_log;
select min(event_date) from query_thread_log;
select min(event_date) from asynchronous_metric_log;
select min(event_date) from trace_log;
select min(event_date) from metric_log;
-- The End --
