一、需求
为适应公司测试环境的需求，部署ClickHouse-20.8.3.18单节点到服务器上
二、环境准备
| 主机 |
角色 |
IP |
| stg-ck001 |
clickhouse公司内部服务器 |
172.31.40.155 |
三、相关软件版本选择
四、基本优化
#1.修改主机名
[root@ip-172-31-40-155 ~]# hostnamectl set-hostname stg-ck001
#2.查看主机名
[root@ip-172-31-40-155 ~]# hostname
stg-ck001
#3.备份yum源
[root@stg-ck001 ~]# mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
#4.配置yum源
[root@stg-ck001 ~]# curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 2523 100 2523 0 0 15923 0 --:--:-- --:--:-- --:--:-- 15867
[root@stg-ck001 ~]# curl -o /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 664 100 664 0 0 4347 0 --:--:-- --:--:-- --:--:-- 4368
#5.查看yum源
[root@stg-ck001 ~]# ll /etc/yum.repos.d/
total 44
-rw-r--r-- 1 root root 2523 Mar 12 08:34 CentOS-Base.repo
-rw-r--r--. 1 root root 1664 May 17 2018 CentOS-Base.repo.backup
-rw-r--r--. 1 root root 664 Mar 12 08:35 epel.repo
#6.安装常用软件
[root@stg-ck001 ~]# yum -y install tree nmap sysstat lrzsz telnet bash-completion bash-completion-extras vim lsof net-tools rsync ntpdate nfs-utils wget
#7.同步系统时间
1.手动同步系统时间
[root@stg-ck001 ~]# ntpdate ntp.aliyun.com
25 Mar 03:33:23 ntpdate[15909]: adjust time server 203.107.6.88 offset 0.012535 sec
[root@stg-ck001 ~]# date
Thu Mar 25 03:33:25 UTC 2021
2.做定时同步系统时间
[root@stg-ck001 ~]# echo '#Timing synchronization time' >>/var/spool/cron/root
[root@stg-ck001 ~]# echo '*/5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com &>/dev/null' >>/var/spool/cron/root
3.查看定时任务
[root@stg-ck001 ~]# crontab -l
#Timing synchronization time
*/5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com &>/dev/null
#8.取消打开文件数限制
[root@stg-ck001 ~]# vim /etc/security/limits.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
[root@stg-ck001 ~]# vim /etc/security/limits.d/20-nproc.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
#9.关闭selinux
1）临时关闭
[root@stg-ck001 ~]# setenforce 0
2）永久关闭
[root@stg-ck001 ~]# sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
五、修改本地解析
[root@stg-ck001 ~]# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.31.40.155 stg-inner-ck001
六、格式化文件系统
#1.查看所有磁盘分区情况
[root@stg-ck001 ~]# fdisk -l
Disk /dev/nvme1n1: 107.4 GB, 107374182400 bytes, 209715200 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk /dev/nvme0n1: 53.7 GB, 53687091200 bytes, 104857600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000acf0a
Device Boot Start End Blocks Id System
/dev/nvme0n1p1 * 2048 104857566 52427759+ 83 Linux
#2.进行磁盘分区
[root@stg-ck001 ~]# fdisk /dev/nvme1n1
Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0x39e17a4f.
Command (m for help): n
Partition type:
p primary (0 primary, 0 extended, 4 free)
e extended
Select (default p): p
Partition number (1-4, default 1):
First sector (2048-209715199, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-209715199, default 209715199):
Using default value 209715199
Partition 1 of type Linux and of size 100 GiB is set
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
#3.重读分区表
[root@stg-ck001 ~]# partprobe
#4.磁盘格式化
[root@stg-ck001 ~]# mkfs.xfs /dev/nvme1n1p1 -f
meta-data=/dev/nvme1n1p1 isize=512 agcount=4, agsize=6553536 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0, sparse=0
data = bsize=4096 blocks=26214144, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal log bsize=4096 blocks=12799, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
#5.新建数据目录data
[root@stg-ck001 ~]# mkdir /data
#6.挂载目录
[root@stg-ck001 ~]# mount /dev/nvme1n1p1 /data/
#7.查看挂载点
[root@stg-ck001 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/nvme0n1p1 50G 1.3G 49G 3% /
devtmpfs 7.5G 0 7.5G 0% /dev
tmpfs 7.5G 0 7.5G 0% /dev/shm
tmpfs 7.5G 17M 7.5G 1% /run
tmpfs 7.5G 0 7.5G 0% /sys/fs/cgroup
tmpfs 1.5G 0 1.5G 0% /run/user/1000
/dev/nvme1n1p1 100G 33M 100G 1% /data
#8.实现永久挂载
1.查看UUID
[root@stg-ck001 ~]# blkid
/dev/nvme0n1p1: UUID="8c1540fa-e2b4-407d-bcd1-59848a73e463" TYPE="xfs"
/dev/nvme1n1: PTTYPE="dos"
/dev/nvme1n1p1: UUID="5e817a4e-5a07-429c-a359-fba32d40f716" TYPE="xfs"
/dev/nvme0n1: PTTYPE="dos"
2.编辑永久挂载点文件
[root@stg-ck001 ~]# vi /etc/fstab
#
# /etc/fstab
# Created by anaconda on Tue Jun 5 14:06:12 2018
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
UUID=8c1540fa-e2b4-407d-bcd1-59848a73e463 / xfs defaults 0 0
UUID=5e817a4e-5a07-429c-a359-fba32d40f716 / xfs defaults 0 0
七、安装ClickHouse数据库
1.检查是否支持安装
#1.检查系统是否支持clickhouse安装
[root@stg-ck001 ~]# grep -q sse4_2 /proc/cpuinfo && echo “SSE 4.2 supported” || echo “SSE 4.2 not supported
“SSE 4.2 supported”
“SSE 4.2 supported” 代表可以安装，ClickHouse需要使用SSE硬件指令集加速，大大加快了CPU寄存器计算效率。
2.安装依赖
[root@stg-ck001 ~]# yum install -y libtool
[root@stg-ck001 ~]# yum install -y *unixODBC*
3.下载安装包
官网：https://clickhouse.tech/
下载地址：https://repo.clickhouse.com/rpm/stable/x86_64/
#1.新建rpm包所在目录
[root@stg-ck001 ~]# mkdir -p /opt/software/clickhouse/
[root@stg-ck001 ~]# cd /opt/software/clickhouse/
#2.下载rpm包
[root@stg-ck001 clickhouse]# wget https://repo.clickhouse.com/rpm/stable/x86_64/clickhouse-client-20.8.3.18-2.noarch.rpm
[root@stg-ck001 clickhouse]# wget https://repo.clickhouse.com/rpm/stable/x86_64/clickhouse-server-20.8.3.18-2.noarch.rpm
[root@stg-ck001 clickhouse]# wget https://repo.clickhouse.com/rpm/stable/x86_64/clickhouse-common-static-20.8.3.18-2.x86_64.rpm
[root@stg-ck001 clickhouse]# wget https://repo.clickhouse.com/rpm/stable/x86_64/clickhouse-common-static-dbg-20.8.3.18-2.x86_64.rpm
[root@stg-ck001 clickhouse]# ll
总用量 1660076
-rw-r--r-- 1 root root 117705 9月 18 2020 clickhouse-client-20.8.3.18-2.noarch.rpm
-rw-r--r-- 1 root root 144128776 9月 18 2020 clickhouse-common-static-20.8.3.18-2.x86_64.rpm
-rw-r--r-- 1 root root 1550988399 9月 18 2020 clickhouse-common-static-dbg-20.8.3.18-2.x86_64.rpm
-rw-r--r-- 1 root root 142403 9月 18 2020 clickhouse-server-20.8.3.18-2.noarch.rpm
4.安装ClickHouse
#1.安装clickhouse所有安装包
[root@stg-ck001 clickhouse]# rpm -ivh *.rpm
#2.查看已安装的clickhouse包
[root@stg-ck001 clickhouse]# rpm -qa |grep clickhouse
clickhouse-common-static-dbg-20.8.3.18-2.x86_64
clickhouse-common-static-20.8.3.18-2.x86_64
clickhouse-server-20.8.3.18-2.noarch
clickhouse-client-20.8.3.18-2.noarch
5.启动ClickHouse
#1.启动ClickHouse服务状态
[root@stg-ck001 clickhouse]# systemctl start clickhouse-server.service
#2.查看ClickHouse服务状态
[root@stg-ck001 clickhouse]# systemctl status clickhouse-server.service
● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled; vendor preset: disabled)
Active: active (running) since 四 2021-11-11 15:19:22 CST; 2s ago
Main PID: 27714 (clickhouse-serv)
CGroup: /system.slice/clickhouse-server.service
└─27714 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-serve...
11月 11 15:19:22 dev-outlook systemd[1]: Started ClickHouse Server (analytic DBMS for big data).
11月 11 15:19:22 dev-outlook clickhouse-server[27714]: Processing configuration file '/etc/clickhouse-server/config.xml'.
11月 11 15:19:22 dev-outlook clickhouse-server[27714]: Include not found: clickhouse_remote_servers
11月 11 15:19:22 dev-outlook clickhouse-server[27714]: Include not found: clickhouse_compression
11月 11 15:19:23 dev-outlook clickhouse-server[27714]: Logging trace to /var/log/clickhouse-server/clickhouse-server.log
11月 11 15:19:23 dev-outlook clickhouse-server[27714]: Logging errors to /var/log/clickhouse-server/clickhouse-serve....log
Hint: Some lines were ellipsized, use -l to show in full.
八、配置ClickHouse
1.备份配置文件和用户文件
#1.备份配置文件
[root@stg-ck001 clickhouse]# cp -a /etc/clickhouse-server/config.xml /etc/clickhouse-server/config.xml.bak
#2.备份用户文件
[root@stg-ck001 clickhouse]# cp -a /etc/clickhouse-server/user.xml /etc/clickhouse-server/user.xml.bak
2.修改配置文件
[root@stg-ck001 clickhouse]# vim /etc/clickhouse-server/config.xml
<?xml version="1.0"?>
<yandex>
<!-- 日志 -->
<logger>
<level>trace</level>
<log>/data/clickhouse/log/clickhouse-server/clickhouse-server.log</log>
<errorlog>/data/clickhouse/log/clickhouse-server/clickhouse-server.err.log</errorlog>
<size>1000M</size>
<count>10</count>
</logger>
<!-- 端口 -->
<http_port>8123</http_port>
<tcp_port>9000</tcp_port>
<interserver_http_port>9009</interserver_http_port>
<!-- 本机域名 -->
<interserver_http_host>这里需要用域名，如果后续用到复制的话</interserver_http_host>
<!-- 监听IP -->
<listen_host>::</listen_host>
<!-- 最大并发查询数 -->
<max_concurrent_queries>16</max_concurrent_queries>
<!-- 单位是B -->
<uncompressed_cache_size>8589934592</uncompressed_cache_size>
<mark_cache_size>10737418240</mark_cache_size>
<!-- 存储路径 -->
<path>/data/clickhouse/</path>
<tmp_path>/data/clickhouse/tmp/</tmp_path>
<user_files_path>/data/clickhouse/user_files/</user_files_path>
<access_control_path>/var/lib/clickhouse/access/</access_control_path>
<!-- user配置 -->
<users_config>users.xml</users_config>
<default_profile>default</default_profile>
<log_queries>1</log_queries>
<default_database>default</default_database>
<remote_servers incl="clickhouse_remote_servers" />
<zookeeper incl="zookeeper-servers" optional="true" />
<macros incl="macros" optional="true" />
... ...
<include_from>/data1/clickhouse/metrika.xml</include_from>
</yandex>
3.修改用户文件
[root@stg-ck001 clickhouse]# vim /etc/clickhouse-server/users.xml
... ...
<!-- 授权default用户管理员权限 -->
<access_management>1</access_management>
... ...
4.创建相关数据目录
[root@stg-ck001 clickhouse]# mkdir /data/clickhouse/log/clickhouse-server/ -p
[root@stg-ck001 clickhouse]# mkdir /data/clickhouse/tmp/
[root@stg-ck001 clickhouse]# mkdir /data/clickhouse/user_files/
5.重启服务
#1.重启clickhouse服务
[root@stg-ck001 clickhouse]# systemctl restart clickhouse-server
6.验证服务
#1.查看服务进程
[root@stg-ck001 clickhouse]# ps -ef |grep click
root 10455 18441 0 10:21 pts/3 00:00:00 grep --color=auto click
root 11441 1 1 Mar26 ? 01:15:08 clickhouse-server --config-file=/etc/clickhouse-server/config.xml
#2.查看端口进程
[root@stg-ck001 clickhouse]# netstat -lntp |grep click
tcp6 0 0 :::8123 :::* LISTEN 11441/clickhouse-se
tcp6 0 0 :::9000 :::* LISTEN 11441/clickhouse-se
tcp6 0 0 :::9004 :::* LISTEN 11441/clickhouse-se
tcp6 0 0 :::9009 :::* LISTEN 11441/clickhouse-se
tcp6 0 0 ::1:9018 :::* LISTEN 20854/clickhouse-od
九、登录ClickHouse数据库
1.登录clickhouse数据库
#1.登录clickhouse数据库
[root@stg-ck001 clickhouse]# clickhouse-client
ClickHouse client version 20.8.3.18.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.8.3 revision 54438.
stg-ck001 :)
2.创建数据库角色和授权用户
#1.创建数据库角色和授权用户
stg-ck001 :) create role manager
stg-ck001 :) set allow_introspection_functions=1
stg-ck001 :) grant all on *.* to manager
stg-ck001 :) create user clickhouse_user001 IDENTIFIED WITH PLAINTEXT_PASSWORD BY '密码'
stg-ck001 :) grant manager to clickhouse_user001
3.查看用户
#1.查看用户
stg-ck001 :) show users
SHOW USERS
┌─name───────────────┐
│ clickhouse_user001 │
│ default │
└────────────────────┘
2 rows in set. Elapsed: 0.002 sec.
4.改用用户名密码登录
#1.使用用户clickhouse_user001登录
[root@stg-ck001 clickhouse]# clickhouse-client -u clickhouse_user001 --password 密码 -h 16.0.3.132
ClickHouse client version 20.8.3.18.
Connecting to 16.0.3.132:9000 as user clickhouse_user001.
Connected to ClickHouse server version 20.8.3 revision 54438.
stg-ck001 :)
