CentOS7.6部署ClickHouse数据库22.7部署和数据库配置和启用表列加密

Centos7.6环境部署clickhouse22.7
首先，您需要添加官方存储库：

yum install -y yum-utils
yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
通过域名源的方式安装clickhouse数据库服务和客户端
yum install -y clickhouse-server clickhouse-client

/etc/init.d/clickhouse-server start
clickhouse-client # or "clickhouse-client --password" if you set up a password.



创建数据和日志存储目录
[root@ ~]# mkdir -p /opt/clickhouse /opt/clickhouse/log/clickhouse-server /opt/clickhouse/clickhouse-server/
[root@ ~]# chown clickhouse:clickhouse -p /opt/clickhouse /opt/clickhouse/log/clickhouse-server /opt/clickhouse/clickhouse-server/
修改clickhouse数据库的配置文件
[root@ ~]# vi /etc/clickhouse-server/config.xml
<!-- Path to data directory, with trailing slash. -->
<path>/data/database/clickhouse/</path>
<!-- Path to temporary data for processing hard queries. -->
<tmp_path>/data/database/clickhouse/tmp/</tmp_path>
<!-- Directory with user provided files that are accessible by 'file' table function. -->
<user_files_path>/data/database/clickhouse/user_files/</user_files_path>
<!-- Directory in <clickhouse-path> containing schema files for various input formats.The directory will be created if it doesn't exist.-->
<format_schema_path>/data/database/clickhouse/format_schemas/</format_schema_path>
<!-- Same for hosts with disabled ipv6.-->
<listen_host>0.0.0.0</listen_host>
<timezone>Asia/Shanghai</timezone>

修改用户管理配置文件
[root@ ~]# vi /etc/clickhouse-server/users.xml
<users>
<!-- If user name was not specified, 'default' user is used. -->
<default>
<!-- 'default'配置一个明文密码 -->
<password>123456</password>
服务启动
[root@ ~]# /etc/init.d/clickhouse-server start
#或者
[root@ ~]# su -s /bin/bash clickhouse -c "/usr/bin/clickhouse-server --daemon \
--pid-file=/var/run/clickhouse-server/clickhouse-server.pid \
--config-file=/etc/clickhouse-server/config.xml"
登录

clickhouse-client -u default -h 127.0.0.1 --password 123456

localhost :) show databases;

SHOW DATABASES

┌─name────┐
│ default │
│ system │
└─────────┘

2 rows in set. Elapsed: 0.007 sec.

新增一个账户
在/etc/clickhouse-server/users.xml的...标签中添加admin用户：

<admin>
<password>123456</password>
<networks incl="networks" replace="replace">
<ip>::/0</ip>
</networks>
<profile>default</profile>
<quota>default</quota>
</admin>
用mysql客户端登录
ClickHouse支持MySQL线协议。 它可以通过启用mysql_port在配置文件中设置:

<mysql_port>9004</mysql_port>
使用mysql命令行工具连接 :

[root@ ~]# mysql --protocol tcp -u admin -p -P 9004
Enter password: ******
mysql> show databases ;
+---------+
| name |
+---------+
| default |
| system |
+---------+
2 rows in set (0.00 sec)
Read 2 rows, 263.00 B in 0.001 sec., 1887 rows/sec., 242.42 KiB/sec.

mysql> \q
Bye


/* 加密列配置 */
加密配置
在/etc/clickhouse-server/config.xml 配置文件里添加
vim /etc/clickhouse-server/config.xml
<encryption_codecs>
<aes_128_gcm_siv>
<key>1234567812345678</key>
</aes_128_gcm_siv>
<aes_256_gcm_siv>
<key>0123456789abcdef0123456789abcdef</key>
</aes_256_gcm_siv>
</encryption_codecs>
--创建带加密列的表

CREATE TABLE mytable
(
x String Codec(AES_128_GCM_SIV)
)
ENGINE = MergeTree ORDER BY x;


CREATE TABLE jmt.jmt1
(n UInt64,
s String CODEC(AES_128_GCM_SIV)
)
ENGINE = MergeTree
ORDER BY n;

CREATE TABLE jmt2
( id UInt64 CODEC(AES_128_GCM_SIV),
name String CODEC(AES_256_GCM_SIV)
)
ENGINE = MergeTree
ORDER BY name;

insert into jmt2 values(1,'ACED');
insert into jmt2 values(2,'ACE');
insert into jmt2 values(3,'OCM');
insert into jmt2 values(4,'OCP');
insert into jmt2 values(5,'OCE');
insert into jmt2 values(6,'OCS');
insert into jmt2 values(7,'OCA');

--修改表列的加解密属性
ALTER TABLE jmt1 MODIFY COLUMN n UInt64 CODEC(AES_128_GCM_SIV);
ALTER TABLE jmt1 MODIFY COLUMN n UInt64;
ALTER TABLE jmt1 MODIFY COLUMN n UInt64 CODEC(AES_256_GCM_SIV);
--添加表的加密列
ALTER TABLE jmt1 ADD COLUMN n1 UInt64 CODEC(AES_256_GCM_SIV);
ALTER TABLE jmt1 ADD COLUMN n0 UInt64 CODEC(AES_256_GCM_SIV) FIRST ;
ALTER TABLE jmt1 ADD COLUMN n2 UInt64 CODEC(AES_256_GCM_SIV) AFTER n;
--删除表列
ALTER TABLE jmt1 DROP COLUMN n1;

备注：
如果您指定AFTER name_after（另一列的名称），则该列将添加到表列列表中的指定列之后。
如果要将列添加到表的开头，请使用该FIRST子句。
否则，该列将添加到表的末尾。对于一系列操作，name_after可以是在先前操作之一中添加的列的名称。

/* 加密列配置完成 */