一、产品介绍
clickhouse分片介绍、clickhouse副本介绍、clickhouse安装、clickhouse复制表、clickhouse分布式总表、clickhouse测试
1、为什么需要使用分片（shard）
https://clickhouse.com/docs/en/concepts/why-clickhouse-is-so-fast
clickhouse会利用整台服务器上面所有资源，如果有大的SSD、CPU、内存clikchouse可以处理TB级别的数据，而且具有高速度的插入和查询。
当服务器资源不够的时候，比如说服务器存储64TB然后做了raid10，变成32TB之后，数据量还是超过这个量的时候，可以通过分片来解决，所以clickhouse首先尽量为扩容磁盘，之后在增加分片。
clickhouse分片介绍
2、CPU利用率应该是多少？
ClickHouse没有标准的CPU利用率目标。利用iostat等工具来测量平均CPU使用率，并相应地调整服务器的大小以管理意外的流量峰值。但是，对于具有即使查询的分析或数据仓库用例，您应该将CPU利用率定为10-20%。
3、 我应该使用多少CPU核心？
应该使用的CPU数量取决于您的工作负载。但是，我们通常会根据您的CPU类型推荐以下内存与CPU核心比率：
M型（通用用例）：4：1的内存与CPU核心比率
R型（数据仓库用例）：8：1的内存与CPU核心比率
C型（计算优化的用例）：2：1的内存与CPU核心比率
例如，当使用M型CPU时，我们建议为每25个CPU内核配置100 GB内存。要确定适合应用程序的内存量，必须分析内存使用情况。
二、 如何吧数据写入到两台服务器的不同分片
有2种方式可以吧数据写入到不同的分片里面
1.第一种通过分布式总表的方式写入，用户把数据写入到分布式总表，然后通过分布式总表的分配键来进行分发到不同分片里面，一般常用的是rand(),这个会平均的把数据分配到每个分片。
2.第二种直接写入两个分片的本地表中。
如何查询所有不同分片的数据
clickhouse的可以通过查询分布式总表的方式来检索所有，这也是clickhouse的优势点。
clickhouse副本介绍
副本可以实现数据的完整性和自动故障转移
比如一下异常情况
数据库磁盘坏了
数据库内存OOM导致数据库挂了
主机宕机
......
一台主机坏了，另外一台主机上有副本则可以保证业务的实时稳定
当出问题的主机启动，修复完毕时则可以自动与其他副本同步数据
这些步骤都是通过clickhouse keeper或者zookeeper来完成的，并且clickhouse keeper与zookeeper完全兼容
clickhouse分片和副本配置
分片和副本的配置介绍
1.cluster配置是在config.xml里面配置。
2.可以通过 on cluster 来为所有节点创建本地表、分布式总表、数据库......
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1] [COMMENT 'comment for column'],
name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2] [COMMENT 'comment for column'],
...
) ENGINE = engine
COMMENT 'comment for table'
1.分片和副本的简单配置如下，下面配置cluster1的集群
2.分片和副本的简单配置如下，需要再所有主机配置，可以通过分布式DDL查询，创建等。
3. 需要重点介绍参数<internal_replication>true</internal_replication>
这个参数保证使用复制表的时候只在同一个副本进行写数据，之后副本直接回自动同步数据。
分布式查询会直接使用分布式总表的模版，可以直接查询，就会调用所有数据
安装部署
本测试环境
192.168.137.64
192.168.137.68
Centos 7.9 8G 2个cpu
安装 zookeeper
1. 安装jdk
cd /usr/local
tar -xf jdk-8u351-linux-x64.tar.gz
2. 安装 zookeeper
下载
解压
tar -zxvf apache-zookeeper-3.7.0-bin.tar.gz -C /usr/local/
cd /usr/local/apache-zookeeper-3.7.0-bin/conf
cp zoo_sample.cfg zoo.cfg
[root@node2 conf]# vi zoo.cfg
tickTime=2000
initLimit=10
syncLimit=5
dataDir=/usr/local/apache-zookeeper-3.7.0-bin/data
dataLogDir=/usr/local/apache-zookeeper-3.7.0-bin/logs
clientPort=2181
maxClientCnxns=60
autopurge.snapRetainCount=3
autopurge.purgeInterval=0
server.1=xxxx.xxxx.xxxx.xxxx:2888:3888
server.2=xxxx.xxxx.xxxx.xxxx:2888:3888
#autopurge.snapRetainCount=3
# Purge task interval in hours
# Set to "0" to disable auto purge feature
autopurge.purgeInterval=1
##建数据目录和日志目录
mkdir -p /usr/local/apache-zookeeper-3.7.0-bin/data
mkdir -p /usr/local/apache-zookeeper-3.7.0-bin/logs
cd /usr/local/apache-zookeeper-3.7.0-bin/data
[root@node1 data]# cat myid ### 根据上面的 server.1 数值去是建立，根据具体的标注对应不同的值.
1
最后再zoo.cfg中 dataDir=*** 指定的目录下创建myid文件，里面加上zk节点的序号，注意每个节点都不一样。
另外如果想修改zk的日志存储地址，可以修改log4j.properties中的相关属性配置
注意事项: zoo.cfg 里面的参数所有节点顺序都保持一致.
###添加环境变量
vi /etc/profile
export PATH=/usr/local/jdk1.8.0_351/bin:$PATH
export JAVA_HOME=/usr/local/jdk1.8.0_351
export JRE_HOME=$JAVA_HOME/jre
export CLASSPATH=$JAVA_HOME/lib:$JAVA_HOME/jre/lib:$CLASSPATH
export ZK_HOME=/usr/local/apache-zookeeper-3.7.0-bin
export PATH=$PATH:$ZK_HOME/bin
source /etc/profile
###启动 zookeeper 一定查看状态及日志是否正常.
/usr/local/apache-zookeeper-3.7.0-bin/bin
[root@node1 bin]# ./zkServer.sh start
###查看状态
[root@node1 bin]# ./zkServer.sh status
二 安装 clickhouse
##环境检查脚本
vi ac_install_ck_cluster.sh
#!/bin/bash
#########################################
# File Name: install_ck_.sh
# Version: v1.4
# CreateTime:2024-07-3
#########################################
current_time=$(date "+%Y-%m-%d %H:%M:%S")
configPath=config
ips=`awk -F= -v k=ips '{ if ( $1 == k ) print $2; }' $configPath`
ck_mount_dir=`awk -F= -v k=ck_mount_dir '{ if ( $1 == k ) print $2; }' $configPath`
package_dir=`awk -F= -v k=package_dir '{ if ( $1 == k ) print $2; }' $configPath`
ck_password=`awk -F= -v k=ck_password '{ if ( $1 == k ) print $2; }' $configPath`
share=`awk -F= -v k=share '{ if ( $1 == k ) print $2; }' $configPath`
replicas=`awk -F= -v k=replicas '{ if ( $1 == k ) print $2; }' $configPath`
zookeeper_ip=`awk -F= -v k=zookeeper_ip '{ if ( $1 == k ) print $2; }' $configPath`
zookeeper_port=`awk -F= -v k=zookeeper_port '{ if ( $1 == k ) print $2; }' $configPath`
eval $(echo $ips | awk '{split($0, arr, ","); for(i in arr) print "array["i-1"]="arr[i]}')
eval $(echo $zookeeper_ip | awk '{split($0, arr1, ","); for(i in arr1) print "array_zk["i-1"]="arr1[i]}')
function menu()
{
echo -e "\033[34m -------------------菜单功能选项------------------------ \033[0m"
echo -e "\033[34m 0 显示功能菜单 \033[0m"
echo -e "\033[34m 1 修改操作系统相关配置 \033[0m"
echo -e "\033[34m 2 安装clickhouse \033[0m"
echo -e "\033[34m 3 退出 \033[0m"
echo -e "\033[34m ------------------------------------------------------- \033[0m"
}
function selector()
{
while :
do
read -p "请输入要操作的菜单编号[0-3],查看功能菜单输入0：" key
case $key in
0)
menu ;;
1)
change_system ;;
2)
install_clickhouse
get_local_ip
change_ck_server_config
change_remote_servers_zk_mocros
create_systemctl_file
init_create_user ;;
3)
exit ;;
esac
done
}
function output_logs()
{
if [ "$?" != 0 ] ; then
echo -e "\033[31m $current_time $1 \033[0m"
exit 1
else
echo -e "\033[32m $current_time $2 \033[0m"
fi
}
function change_system()
{
echo 'madvise' | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
output_logs 禁用透明大页失败 禁用透明大页成功
echo 'vm.swappiness = 1' >> /etc/sysctl.conf
sysctl -p /etc/sysctl.conf
output_logs 禁用swap失败 禁用swap成功
setenforce 0
#output_logs 禁用SElinux失败 禁用SElinux成功
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
output_logs 修改SElinux配置文件disabled失败 修改SElinux配置文件disabled成功
echo '* soft nofile 1048576
* hard nofile 1048576
* soft nproc 131072
* hard nproc 131072' >> /etc/security/limits.d/20-nproc.conf
# 安装clickhouse依赖包
yum install -y yum-utils libtool *unixODBC*
output_logs clickhouse安装依赖失败 clickhouse安装依赖成功
systemctl stop firewalld.service
output_logs 禁用iptables失败 禁用iptables成功
systemctl disable firewalld.service
output_logs 禁用iptables开机自启动失败 禁用iptables开机自启动成功
}
function install_clickhouse()
{
# 创建数据目录、日志目录
mkdir -p $ck_mount_dir/clickhouse/data
output_logs 创建clickhouse数据目录失败 创建clickhouse数据目录成功
mkdir -p $ck_mount_dir/clickhouse-1/data
output_logs 创建clickhouse-1数据目录失败 创建clickhouse-1数据目录成功
mkdir -p $ck_mount_dir/clickhouse/log
output_logs 创建clickhouse日志目录失败 创建clickhouse日志目录成功
mkdir -p $ck_mount_dir/clickhouse-1/log
output_logs 创建clickhouse-1日志目录失败 创建clickhouse-1日志目录成功
# 解压tgz包安装clickhouse
cd $package_dir
output_logs 进入安装包目录失败 进入安装包目录成功
clickhouse_client=`ls -l |grep 'clickhouse-client'|awk '{print $9}'`
clickhouse_common_static=`ls -l |grep 'clickhouse-common-static'|grep -v dbg|awk '{print $9}'`
clickhouse_common_static_dbg=`ls -l |grep 'clickhouse-common-static-dbg'|awk '{print $9}'`
clickhouse_server=`ls -l |grep 'clickhouse-server'|awk '{print $9}'`
echo -e "\033[32m $current_time 开始解压压缩包 \033[0m"
tar -zxvf $clickhouse_client -C /opt
output_logs 解压$clickhouse_client包失败 解压$clickhouse_client包成功
tar -zxvf $clickhouse_common_static -C /opt
output_logs 解压$clickhouse_common_static包失败 解压$clickhouse_common_static包成功
tar -zxvf $clickhouse_common_static_dbg -C /opt
output_logs 解压$clickhouse_common_static_dbg包失败 解压$clickhouse_common_static_dbg包成功
tar -zxvf $clickhouse_server -C /opt
output_logs 解压$clickhouse_server包失败 解压$clickhouse_server包成功
clickhouse_client_dir=`ls -l /opt |grep 'clickhouse-client'|awk '{print $9}'`
clickhouse_common_static_dir=`ls -l /opt |grep 'clickhouse-common-static'|grep -v dbg|awk '{print $9}'`
clickhouse_common_static_dbg_dir=`ls -l /opt|grep 'clickhouse-common-static-dbg'|awk '{print $9}'`
clickhouse_server_dir=`ls -l /opt|grep 'clickhouse-server'|awk '{print $9}'`
# [已废弃] echo -e "\033[32m $current_time 开始安装clickhouse,安装 clcikhouse-servert 过程中提示为 default 用户设置密码，直接回车即可。 \033[0m"
/opt/$clickhouse_common_static_dir/install/doinst.sh
output_logs 安装$clickhouse_common_static失败 安装$clickhouse_common_static成功
/opt/$clickhouse_common_static_dbg_dir/install/doinst.sh
output_logs 安装$clickhouse_common_static_dbg失败 安装$clickhouse_common_static_dbg成功
#sed -i "s:/var/lib/clickhouse:$ck_mount_dir/clickhouse/data:g" /opt/$clickhouse_server_dir/install/doinst.sh
expect -c "spawn /opt/$clickhouse_server_dir/install/doinst.sh;expect \"Enter password for the default user\";send \"\n\";expect eof"
output_logs 安装$clickhouse_server失败 安装$clickhouse_server成功
/opt/$clickhouse_client_dir/install/doinst.sh
output_logs 安装$clickhouse_client失败 安装$clickhouse_client成功
echo -e "\033[32m $current_time 开始拷贝clickhouse-1副本目录。 \033[0m"
cp -a /etc/clickhouse-server /etc/clickhouse-server-1
output_logs 默认配置目录拷贝失败 默认配置目录拷贝成功
#cp -a /var/lib/clickhouse /var/lib/clickhouse-1
cp -a /var/log/clickhouse-server /var/log/clickhouse-server-1
output_logs 日志目录拷贝失败 日志目录拷贝成功
#cp -a /etc/rc.d/init.d/clickhouse-server /etc/rc.d/init.d/clickhouse-server-1
#output_logs 启动脚本拷贝失败 启动脚本拷贝成功
cp -a /etc/security/limits.d/clickhouse.conf /etc/security/limits.d/clickhouse-1.conf
output_logs 文件数限制文件拷贝失败 文件数限制文件拷贝成功
#cp -a /etc/cron.d/clickhouse-server /etc/cron.d/clickhouse-server-1
#output_logs 定时任务文件拷贝失败 定时任务文件拷贝成功
if [[ $replicas = 3 ]];then
cp -a /etc/clickhouse-server /etc/clickhouse-server-2
output_logs 默认配置目录拷贝失败 默认配置目录拷贝成功
#cp -a /var/lib/clickhouse /var/lib/clickhouse-2
cp -a /var/log/clickhouse-server /var/log/clickhouse-server-2
output_logs 日志目录拷贝失败 日志目录拷贝成功
#cp -a /etc/rc.d/init.d/clickhouse-server /etc/rc.d/init.d/clickhouse-server-2
#output_logs 启动脚本拷贝失败 启动脚本拷贝成功
cp -a /etc/security/limits.d/clickhouse.conf /etc/security/limits.d/clickhouse-2.conf
output_logs 文件数限制文件拷贝失败 文件数限制文件拷贝成功
mkdir -p /$ck_mount_dir/clickhouse-2/data/tmp
fi
mkdir -p /$ck_mount_dir/clickhouse/data/tmp
mkdir -p /$ck_mount_dir/clickhouse-1/data/tmp
output_logs 副本tmp目录创建失败 副本tmp目录创建成功
echo -e "\033[32m $current_time clickhouse-1副本目录拷贝完成。 \033[0m"
}
function get_local_ip()
{
nums=${#array[@]}
for ((i=0; i<$nums; i++))
do
if [[ ${array[$i]} = $(ip a | grep ${array[$i]} | awk '{print $2}' | cut -d/ -f1) ]];then
local_ip=${array[$i]}
fi
done
}
function change_ck_server_config()
{
# 计算内存和cpu
men1=`echo $(($(free -b|grep Mem|awk '{print $2}')*7/10/$replicas))`
output_logs max_memory_usage计算失败 max_memory_usage计算成功
men2=`echo $(($(free -b|grep Mem|awk '{print $2}')*5/10/$replicas))`
output_logs max_bytes_before_external_group_by计算失败 max_bytes_before_external_group_by计算成功
cpu_num2=`echo $(grep 'processor' /proc/cpuinfo | sort -u | wc -l)`
if [[ $cpu_num2 -le 15 ]];then
cpu_num2=16
fi
output_logs cpu核数计算失败 cpu核数计算成功
# 修改主节点config.xml文件
echo -e "\033[32m $current_time 开始修改主节点config.xml文件 \033[0m"
awk '/<prometheus>/{print NR-1}' /etc/clickhouse-server/config.xml | while read line; do sed -i "${line}d" /etc/clickhouse-server/config.xml; done
awk '/<\/prometheus>/{print NR+1}' /etc/clickhouse-server/config.xml | while read line; do sed -i "${line}d" /etc/clickhouse-server/config.xml; done
sed -i "s#<log>/var/log/clickhouse-server/clickhouse-server.log</log>#<log>$ck_mount_dir/clickhouse/log/clickhouse-server.log</log>#g" /etc/clickhouse-server/config.xml
sed -i "s#<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>#<errorlog>$ck_mount_dir/clickhouse/log/clickhouse-server.err.log</errorlog>#g" /etc/clickhouse-server/config.xml
sed -i "s#<remote_servers>#<remote_servers_del>#g" /etc/clickhouse-server/config.xml
sed -i "s#</remote_servers>#</remote_servers_del>#g" /etc/clickhouse-server/config.xml
sed -i "s#<!-- <listen_host>::</listen_host> -->#<listen_host>::</listen_host>#g" /etc/clickhouse-server/config.xml
sed -i "s#<keep_alive_timeout>3</keep_alive_timeout>#<keep_alive_timeout>600</keep_alive_timeout>#g" /etc/clickhouse-server/config.xml
sed -i "s#<max_concurrent_queries>100</max_concurrent_queries>#<max_concurrent_queries>150</max_concurrent_queries>#g" /etc/clickhouse-server/config.xml
sed -i "s#<!-- <max_table_size_to_drop>0</max_table_size_to_drop> -->#<max_table_size_to_drop>0</max_table_size_to_drop>#g" /etc/clickhouse-server/config.xml
sed -i "s#<path>/var/lib/clickhouse/</path>#<path>$ck_mount_dir/clickhouse/data/</path>#g" /etc/clickhouse-server/config.xml
sed -i "s#/var/lib/clickhouse/tmp/#<path>$ck_mount_dir/clickhouse/data/tmp/</path>#g" /etc/clickhouse-server/config.xml
sed -i "s#<user_files_path>/var/lib/clickhouse/user_files/</user_files_path>#<user_files_path>$ck_mount_dir/clickhouse/data/user_files/</user_files_path>#g" /etc/clickhouse-server/config.xml
sed -i "s#<path>/var/lib/clickhouse/access/</path>#<path>$ck_mount_dir/clickhouse/data/access/</path>#g" /etc/clickhouse-server/config.xml
sed -i "s#<format_schema_path>/var/lib/clickhouse/format_schemas/</format_schema_path>#<format_schema_path>$ck_mount_dir/clickhouse/data/format_schemas/</format_schema_path>#g" /etc/clickhouse-server/config.xml
sed -i '''/<\/send_crash_reports>/a\ <default_replica_path>/clickhouse/tables/{shard}/{database}/{table}</default_replica_path>''' /etc/clickhouse-server/config.xml
sed -i '''/<\/send_crash_reports>/a\ <default_replica_name>{replica}</default_replica_name>''' /etc/clickhouse-server/config.xml
sed -i '''/<\/send_crash_reports>/a\ <compression incl="clickhouse_compression" optional="true"/>''' /etc/clickhouse-server/config.xml
sed -i '''/<\/send_crash_reports>/a\ <networks incl="networks" optional="true"/>''' /etc/clickhouse-server/config.xml
sed -i '''/<\/send_crash_reports>/a\ <macros incl="macros" optional="true" />''' /etc/clickhouse-server/config.xml
sed -i '''/<\/send_crash_reports>/a\ <remote_servers incl="clickhouse_remote_servers" optional="true" />''' /etc/clickhouse-server/config.xml
sed -i '''/<\/send_crash_reports>/a\ <zookeeper incl="zookeeper-servers" optional="true" />''' /etc/clickhouse-server/config.xml
sed -i '''/<\/send_crash_reports>/a\ <include_from>/etc/clickhouse-server/metrika.xml</include_from>''' /etc/clickhouse-server/config.xml
sed -i "/<interserver_http_port>9009<\/interserver_http_port>/a\ <interserver_http_host>$local_ip<\/interserver_http_host>" /etc/clickhouse-server/config.xml
sed -i "/<\/max_thread_pool_size>/a\ <background_distributed_schedule_pool_size>$cpu_num2<\/background_distributed_schedule_pool_size>" /etc/clickhouse-server/config.xml
sed -i "/<\/max_thread_pool_size>/a\ <background_schedule_pool_size>$cpu_num2<\/background_schedule_pool_size>" /etc/clickhouse-server/config.xml
sed -i "/<\/max_thread_pool_size>/a\ <background_pool_size>$cpu_num2<\/background_pool_size>" /etc/clickhouse-server/config.xml
echo -e "\033[32m $current_time 成功修改主节点config.xml文件 \033[0m"
# 修改主节点user.xml文件
echo -e "\033[32m $current_time 开始修改主节点user.xml文件 \033[0m"
first=$(egrep -n '<default>' /etc/clickhouse-server/users.xml | head -1 | cut -d: -f1)
sed -i "$first a\ <max_memory_usage>$men1<\/max_memory_usage>" /etc/clickhouse-server/users.xml
sed -i "$first a\ <max_bytes_before_external_group_by>$men2<\/max_bytes_before_external_group_by>" /etc/clickhouse-server/users.xml
sed -i "$first a\ <max_execution_time>300<\/max_execution_time>" /etc/clickhouse-server/users.xml
sed -i "$first a\ <distributed_product_mode>local<\/distributed_product_mode>" /etc/clickhouse-server/users.xml
sed -i "$first a\ <async_insert>1<\/async_insert>" /etc/clickhouse-server/users.xml
sed -i "s#<password></password>#<password>$ck_password</password>#g" /etc/clickhouse-server/users.xml
sed -i "s#<!-- <access_management>1</access_management> -->#<access_management>1</access_management>#g" /etc/clickhouse-server/users.xml
echo -e "\033[32m $current_time 成功修改主节点user.xml文件 \033[0m"
# 修改副本节点config.xml文件
echo -e "\033[32m $current_time 开始修改副本节点config.xml文件 \033[0m"
awk '/<prometheus>/{print NR-1}' /etc/clickhouse-server-1/config.xml | while read line; do sed -i "${line}d" /etc/clickhouse-server-1/config.xml; done
sed -i 's/9363/9364/g' /etc/clickhouse-server-1/config.xml
awk '/<\/prometheus>/{print NR+1}' /etc/clickhouse-server-1/config.xml | while read line; do sed -i "${line}d" /etc/clickhouse-server-1/config.xml; done
sed -i "s#<log>/var/log/clickhouse-server/clickhouse-server.log</log>#<log>$ck_mount_dir/clickhouse-1/log/clickhouse-server.log</log>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>#<errorlog>$ck_mount_dir/clickhouse-1/log/clickhouse-server.err.log</errorlog>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<remote_servers>#<remote_servers_del>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#</remote_servers>#</remote_servers_del>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<http_port>8123</http_port>#<http_port>8124</http_port>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<tcp_port>9000</tcp_port>#<tcp_port>9001</tcp_port>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<mysql_port>9004</mysql_port>#<mysql_port>9006</mysql_port>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<postgresql_port>9005</postgresql_port>#<postgresql_port>9007</postgresql_port>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<interserver_http_port>9009</interserver_http_port>#<interserver_http_port>9010</interserver_http_port>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<keep_alive_timeout>3</keep_alive_timeout>#<keep_alive_timeout>600</keep_alive_timeout>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<max_concurrent_queries>100</max_concurrent_queries>#<max_concurrent_queries>150</max_concurrent_queries>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<path>/var/lib/clickhouse/</path>#<path>$ck_mount_dir/clickhouse-1/data/</path>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<tmp_path>/var/lib/clickhouse/tmp/</tmp_path>#<tmp_path>$ck_mount_dir/clickhouse-1/data/tmp/</tmp_path>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<user_files_path>/var/lib/clickhouse/user_files/</user_files_path>#<user_files_path>$ck_mount_dir/clickhouse-1/data/user_files/</user_files_path>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<path>/var/lib/clickhouse/access/</path>#<path>$ck_mount_dir/clickhouse-1/data/access/</path>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<format_schema_path>/var/lib/clickhouse/format_schemas/</format_schema_path>#<format_schema_path>$ck_mount_dir/clickhouse-1/data/format_schemas/</format_schema_path>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<!-- <listen_host>::</listen_host> -->#<listen_host>::</listen_host>#g" /etc/clickhouse-server-1/config.xml
sed -i "s#<!-- <max_table_size_to_drop>0</max_table_size_to_drop> -->#<max_table_size_to_drop>0</max_table_size_to_drop>#g" /etc/clickhouse-server-1/config.xml
sed -i '''/<\/send_crash_reports>/a\ <default_replica_path>/clickhouse/tables/{shard}/{database}/{table}</default_replica_path>''' /etc/clickhouse-server-1/config.xml
sed -i '''/<\/send_crash_reports>/a\ <default_replica_name>{replica}</default_replica_name>''' /etc/clickhouse-server-1/config.xml
sed -i '''/<\/send_crash_reports>/a\ <compression incl="clickhouse_compression" optional="true"/>''' /etc/clickhouse-server-1/config.xml
sed -i '''/<\/send_crash_reports>/a\ <networks incl="networks" optional="true"/>''' /etc/clickhouse-server-1/config.xml
sed -i '''/<\/send_crash_reports>/a\ <macros incl="macros" optional="true" />''' /etc/clickhouse-server-1/config.xml
sed -i '''/<\/send_crash_reports>/a\ <remote_servers incl="clickhouse_remote_servers" optional="true" />''' /etc/clickhouse-server-1/config.xml
sed -i '''/<\/send_crash_reports>/a\ <zookeeper incl="zookeeper-servers" optional="true" />''' /etc/clickhouse-server-1/config.xml
sed -i '''/<\/send_crash_reports>/a\ <include_from>/etc/clickhouse-server-1/metrika.xml</include_from>''' /etc/clickhouse-server-1/config.xml
sed -i "/<interserver_http_port>9010<\/interserver_http_port>/a\ <interserver_http_host>$local_ip<\/interserver_http_host>" /etc/clickhouse-server-1/config.xml
sed -i "/<\/max_thread_pool_size>/a\ <background_distributed_schedule_pool_size>$cpu_num2<\/background_distributed_schedule_pool_size>" /etc/clickhouse-server-1/config.xml
sed -i "/<\/max_thread_pool_size>/a\ <background_schedule_pool_size>$cpu_num2<\/background_schedule_pool_size>" /etc/clickhouse-server-1/config.xml
sed -i "/<\/max_thread_pool_size>/a\ <background_pool_size>$cpu_num2<\/background_pool_size>" /etc/clickhouse-server-1/config.xml
echo -e "\033[32m $current_time 成功修改副本节点config.xml文件 \033[0m"
# 修改副本节点user.xml文件
echo -e "\033[32m $current_time 开始修改副本节点user.xml文件 \033[0m"
first=$(egrep -n '<default>' /etc/clickhouse-server/users.xml | head -1 | cut -d: -f1)
sed -i "$first a\ <max_memory_usage>$men1<\/max_memory_usage>" /etc/clickhouse-server-1/users.xml
sed -i "$first a\ <max_bytes_before_external_group_by>$men2<\/max_bytes_before_external_group_by>" /etc/clickhouse-server-1/users.xml
sed -i "$first a\ <max_execution_time>300<\/max_execution_time>" /etc/clickhouse-server-1/users.xml
sed -i "$first a\ <distributed_product_mode>local<\/distributed_product_mode>" /etc/clickhouse-server-1/users.xml
sed -i "$first a\ <async_insert>1<\/async_insert>" /etc/clickhouse-server-1/users.xml
sed -i "s#<password></password>#<password>$ck_password</password>#g" /etc/clickhouse-server-1/users.xml
sed -i "s#<!-- <access_management>1</access_management> -->#<access_management>1</access_management>#g" /etc/clickhouse-server-1/users.xml
echo -e "\033[32m $current_time 成功修改副本节点user.xml文件 \033[0m"
if [[ $replicas = 3 ]];then
# 修改副本2节点config.xml文件
echo -e "\033[32m $current_time 开始修改副本节点config.xml文件 \033[0m"
awk '/<prometheus>/{print NR-1}' /etc/clickhouse-server-2/config.xml | while read line; do sed -i "${line}d" /etc/clickhouse-server-2/config.xml; done
sed -i 's/9363/9365/g' /etc/clickhouse-server-2/config.xml
awk '/<\/prometheus>/{print NR+1}' /etc/clickhouse-server-2/config.xml | while read line; do sed -i "${line}d" /etc/clickhouse-server-2/config.xml; done
sed -i "s#<log>/var/log/clickhouse-server/clickhouse-server.log</log>#<log>$ck_mount_dir/clickhouse-2/log/clickhouse-server.log</log>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>#<errorlog>$ck_mount_dir/clickhouse-2/log/clickhouse-server.err.log</errorlog>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<remote_servers>#<remote_servers_del>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#</remote_servers>#</remote_servers_del>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<http_port>8123</http_port>#<http_port>8125</http_port>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<tcp_port>9000</tcp_port>#<tcp_port>9002</tcp_port>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<mysql_port>9004</mysql_port>#<mysql_port>9003</mysql_port>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<postgresql_port>9005</postgresql_port>#<postgresql_port>9008</postgresql_port>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<interserver_http_port>9009</interserver_http_port>#<interserver_http_port>9011</interserver_http_port>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<keep_alive_timeout>3</keep_alive_timeout>#<keep_alive_timeout>600</keep_alive_timeout>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<max_concurrent_queries>100</max_concurrent_queries>#<max_concurrent_queries>150</max_concurrent_queries>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<path>/var/lib/clickhouse/</path>#<path>$ck_mount_dir/clickhouse-2/data/</path>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<tmp_path>/var/lib/clickhouse/tmp/</tmp_path>#<tmp_path>$ck_mount_dir/clickhouse-2/data/tmp/</tmp_path>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<user_files_path>/var/lib/clickhouse/user_files/</user_files_path>#<user_files_path>$ck_mount_dir/clickhouse-2/data/user_files/</user_files_path>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<path>/var/lib/clickhouse/access/</path>#<path>$ck_mount_dir/clickhouse-2/data/access/</path>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<format_schema_path>/var/lib/clickhouse/format_schemas/</format_schema_path>#<format_schema_path>$ck_mount_dir/clickhouse-2/data/format_schemas/</format_schema_path>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<!-- <listen_host>::</listen_host> -->#<listen_host>::</listen_host>#g" /etc/clickhouse-server-2/config.xml
sed -i "s#<!-- <max_table_size_to_drop>0</max_table_size_to_drop> -->#<max_table_size_to_drop>0</max_table_size_to_drop>#g" /etc/clickhouse-server-2/config.xml
sed -i '''/<\/send_crash_reports>/a\ <default_replica_path>/clickhouse/tables/{shard}/{database}/{table}</default_replica_path>''' /etc/clickhouse-server-2/config.xml
sed -i '''/<\/send_crash_reports>/a\ <default_replica_name>{replica}</default_replica_name>''' /etc/clickhouse-server-2/config.xml
sed -i '''/<\/send_crash_reports>/a\ <compression incl="clickhouse_compression" optional="true"/>''' /etc/clickhouse-server-2/config.xml
sed -i '''/<\/send_crash_reports>/a\ <networks incl="networks" optional="true"/>''' /etc/clickhouse-server-2/config.xml
sed -i '''/<\/send_crash_reports>/a\ <macros incl="macros" optional="true" />''' /etc/clickhouse-server-2/config.xml
sed -i '''/<\/send_crash_reports>/a\ <remote_servers incl="clickhouse_remote_servers" optional="true" />''' /etc/clickhouse-server-2/config.xml
sed -i '''/<\/send_crash_reports>/a\ <zookeeper incl="zookeeper-servers" optional="true" />''' /etc/clickhouse-server-2/config.xml
sed -i '''/<\/send_crash_reports>/a\ <include_from>/etc/clickhouse-server-2/metrika.xml</include_from>''' /etc/clickhouse-server-2/config.xml
sed -i "/<interserver_http_port>9010<\/interserver_http_port>/a\ <interserver_http_host>$local_ip<\/interserver_http_host>" /etc/clickhouse-server-2/config.xml
sed -i "/<\/max_thread_pool_size>/a\ <background_distributed_schedule_pool_size>$cpu_num2<\/background_distributed_schedule_pool_size>" /etc/clickhouse-server-2/config.xml
sed -i "/<\/max_thread_pool_size>/a\ <background_schedule_pool_size>$cpu_num2<\/background_schedule_pool_size>" /etc/clickhouse-server-2/config.xml
sed -i "/<\/max_thread_pool_size>/a\ <background_pool_size>$cpu_num2<\/background_pool_size>" /etc/clickhouse-server-2/config.xml
echo -e "\033[32m $current_time 成功修改副本节点config.xml文件 \033[0m"
# 修改副本2节点user.xml文件
echo -e "\033[32m $current_time 开始修改副本节点user.xml文件 \033[0m"
first=$(egrep -n '<default>' /etc/clickhouse-server/users.xml | head -1 | cut -d: -f1)
sed -i "$first a\ <max_memory_usage>$men1<\/max_memory_usage>" /etc/clickhouse-server-2/users.xml
sed -i "$first a\ <max_bytes_before_external_group_by>$men2<\/max_bytes_before_external_group_by>" /etc/clickhouse-server-2/users.xml
sed -i "$first a\ <max_execution_time>300<\/max_execution_time>" /etc/clickhouse-server-2/users.xml
sed -i "$first a\ <distributed_product_mode>local<\/distributed_product_mode>" /etc/clickhouse-server-2/users.xml
sed -i "$first a\ <async_insert>1<\/async_insert>" /etc/clickhouse-server-2/users.xml
sed -i "s#<password></password>#<password>$ck_password</password>#g" /etc/clickhouse-server-2/users.xml
sed -i "s#<!-- <access_management>1</access_management> -->#<access_management>1</access_management>#g" /etc/clickhouse-server-2/users.xml
echo -e "\033[32m $current_time 成功修改副本2节点user.xml文件 \033[0m"
fi
chown -R clickhouse:clickhouse $ck_mount_dir
}
function change_remote_servers_zk_mocros()
{
cat > /etc/clickhouse-server/metrika.xml <<EOF
<?xml version="1.0"?>
<yandex>
<zookeeper-servers>
</zookeeper-servers>
<macros>
<layer>1</layer>
<shard>1</shard>
<replica>clickhouse-1-1</replica>
</macros>
<clickhouse_remote_servers>
<!-- 集群配置 3分片2副本 -->
<perftest_3shards_2replicas>
</perftest_3shards_2replicas>
</clickhouse_remote_servers>
<networks>
<ip>::/0</ip>
</networks>
<clickhouse_compression>
<case>
<min_part_size>10000000000</min_part_size>
<min_part_size_ratio>0.01</min_part_size_ratio>
<method>lz4</method>
</case>
</clickhouse_compression>
</yandex>
EOF
cat /etc/clickhouse-server/metrika.xml > /etc/clickhouse-server-1/metrika.xml
if [[ $replicas = 3 ]];then
cat /etc/clickhouse-server/metrika.xml > /etc/clickhouse-server-2/metrika.xml
fi
#集群主机数量
nums=${#array[@]}
# 开始修改主节点metrika.xml文件配置
echo -e "\033[32m $current_time 开始修改主节点metrika.xml文件配置 \033[0m"
for ((i=0; i<$nums; i++))
do
sed -i "/<\/perftest_3shards_2replicas>/i\ <shard>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <internal_replication>true<\/internal_replication>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <weight>1<\/weight>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <replica>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <host>${array[$i]}<\/host>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <user>default<\/user>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <password>${ck_password}<\/password>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <port>9000<\/port>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <priority>1<\/priority>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/replica>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <replica>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <host>${array[$i-1]}<\/host>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <user>default<\/user>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <password>${ck_password}<\/password>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <port>9001<\/port>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <priority>1<\/priority>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/replica>" /etc/clickhouse-server/metrika.xml
if [[ $replicas = 3 ]];then
sed -i "/<\/perftest_3shards_2replicas>/i\ <replica>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <host>${array[$i-2]}<\/host>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <user>default<\/user>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <password>${ck_password}<\/password>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <port>9002<\/port>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <priority>1<\/priority>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/replica>" /etc/clickhouse-server/metrika.xml
fi
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/shard>" /etc/clickhouse-server/metrika.xml
done
sed -i "s#\# 集群配置 3分片2副本#\# 集群配置 ${share}分片${replicas}副本#g" /etc/clickhouse-server/metrika.xml
sed -i "s#<perftest_3shards_2replicas>#<perftest_${share}shards_${replicas}replicas>#g" /etc/clickhouse-server/metrika.xml
sed -i "s#<\/perftest_3shards_2replicas>#<\/perftest_${share}shards_${replicas}replicas>#g" /etc/clickhouse-server/metrika.xml
#read -p "请输入当前主机IP: " local_ip
for ((i=0; i<$nums; i++))
do
if [[ ${array[$i]} = $local_ip ]];then
a=$((($i+1)%$share))
fi
done
if [[ $a = 0 ]];then
a=$share
fi
sed -i "s#<shard>1<\/shard>#<shard>${a}<\/shard>#g" /etc/clickhouse-server/metrika.xml
sed -i "s#<replica>clickhouse-1-1<\/replica>#<replica>clickhouse-${a}-1<\/replica>#g" /etc/clickhouse-server/metrika.xml
#zk数量
zk_nums=${#array_zk[@]}
for ((i=0; i<$zk_nums; i++))
do
sed -i "/<\/zookeeper-servers>/i\ <node>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/zookeeper-servers>/i\ <host>${array_zk[$i]}<\/host>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/zookeeper-servers>/i\ <port>${zookeeper_port}<\/port>" /etc/clickhouse-server/metrika.xml
sed -i "/<\/zookeeper-servers>/i\ <\/node>" /etc/clickhouse-server/metrika.xml
done
echo -e "\033[32m $current_time 主节点metrika.xml文件配置完成 \033[0m"
# 开始修改副本节点metrika.xml文件配置
echo -e "\033[32m $current_time 开始修改副本节点metrika.xml文件配置 \033[0m"
for ((i=0; i<$nums; i++))
do
sed -i "/<\/perftest_3shards_2replicas>/i\ <shard>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <internal_replication>true<\/internal_replication>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <weight>1<\/weight>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <replica>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <host>${array[$i]}<\/host>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <user>default<\/user>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <password>${ck_password}<\/password>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <port>9000<\/port>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <priority>1<\/priority>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/replica>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <replica>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <host>${array[$i-1]}<\/host>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <user>default<\/user>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <password>${ck_password}<\/password>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <port>9001<\/port>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <priority>1<\/priority>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/replica>" /etc/clickhouse-server-1/metrika.xml
if [[ $replicas = 3 ]];then
sed -i "/<\/perftest_3shards_2replicas>/i\ <replica>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <host>${array[$i-2]}<\/host>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <user>default<\/user>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <password>${ck_password}<\/password>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <port>9002<\/port>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <priority>1<\/priority>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/replica>" /etc/clickhouse-server-1/metrika.xml
fi
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/shard>" /etc/clickhouse-server-1/metrika.xml
done
sed -i "s#\# 集群配置 3分片2副本#\# 集群配置 ${nums}分片${replicas}副本#g" /etc/clickhouse-server-1/metrika.xml
sed -i "s#<perftest_3shards_2replicas>#<perftest_${nums}shards_${replicas}replicas>#g" /etc/clickhouse-server-1/metrika.xml
sed -i "s#<\/perftest_3shards_2replicas>#<\/perftest_${nums}shards_${replicas}replicas>#g" /etc/clickhouse-server-1/metrika.xml
#read -p "请输入当前主机IP: " local_ip
for ((i=0; i<$nums; i++))
do
if [[ ${array[$i]} = $local_ip ]];then
a=$((($i+2)%$share))
fi
done
if [[ $a = 0 ]];then
a=$share
fi
sed -i "s#<shard>1<\/shard>#<shard>${a}<\/shard>#g" /etc/clickhouse-server-1/metrika.xml
sed -i "s#<replica>clickhouse-1-1<\/replica>#<replica>clickhouse-${a}-2<\/replica>#g" /etc/clickhouse-server-1/metrika.xml
#zk数量
zk_nums=${#array_zk[@]}
for ((i=0; i<$zk_nums; i++))
do
sed -i "/<\/zookeeper-servers>/i\ <node>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/zookeeper-servers>/i\ <host>${array_zk[$i]}<\/host>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/zookeeper-servers>/i\ <port>${zookeeper_port}<\/port>" /etc/clickhouse-server-1/metrika.xml
sed -i "/<\/zookeeper-servers>/i\ <\/node>" /etc/clickhouse-server-1/metrika.xml
done
echo -e "\033[32m $current_time 副本1节点metrika.xml文件配置完成 \033[0m"
if [[ $replicas = 3 ]];then
# 开始修改副本2节点metrika.xml文件配置
echo -e "\033[32m $current_time 开始修改副本节点metrika.xml文件配置 \033[0m"
for ((i=0; i<$nums; i++))
do
sed -i "/<\/perftest_3shards_2replicas>/i\ <shard>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <internal_replication>true<\/internal_replication>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <weight>1<\/weight>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <replica>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <host>${array[$i]}<\/host>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <user>default<\/user>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <password>${ck_password}<\/password>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <port>9000<\/port>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <priority>1<\/priority>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/replica>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <replica>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <host>${array[$i-1]}<\/host>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <user>default<\/user>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <password>${ck_password}<\/password>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <port>9001<\/port>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <priority>1<\/priority>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/replica>" /etc/clickhouse-server-2/metrika.xml
if [[ $replicas = 3 ]];then
sed -i "/<\/perftest_3shards_2replicas>/i\ <replica>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <host>${array[$i-2]}<\/host>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <user>default<\/user>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <password>${ck_password}<\/password>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <port>9002<\/port>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <priority>1<\/priority>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/replica>" /etc/clickhouse-server-2/metrika.xml
fi
sed -i "/<\/perftest_3shards_2replicas>/i\ <\/shard>" /etc/clickhouse-server-2/metrika.xml
done
sed -i "s#\# 集群配置 3分片2副本#\# 集群配置 ${nums}分片${replicas}副本#g" /etc/clickhouse-server-2/metrika.xml
sed -i "s#<perftest_3shards_2replicas>#<perftest_${nums}shards_${replicas}replicas>#g" /etc/clickhouse-server-2/metrika.xml
sed -i "s#<\/perftest_3shards_2replicas>#<\/perftest_${nums}shards_${replicas}replicas>#g" /etc/clickhouse-server-2/metrika.xml
#read -p "请输入当前主机IP: " local_ip
for ((i=0; i<$nums; i++))
do
if [[ ${array[$i]} = $local_ip ]];then
a=$((($i+3)%$share))
fi
done
if [[ $a = 0 ]];then
a=$share
fi
sed -i "s#<shard>1<\/shard>#<shard>${a}<\/shard>#g" /etc/clickhouse-server-2/metrika.xml
sed -i "s#<replica>clickhouse-1-1<\/replica>#<replica>clickhouse-${a}-3<\/replica>#g" /etc/clickhouse-server-2/metrika.xml
#zk数量
zk_nums=${#array_zk[@]}
for ((i=0; i<$zk_nums; i++))
do
sed -i "/<\/zookeeper-servers>/i\ <node>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/zookeeper-servers>/i\ <host>${array_zk[$i]}<\/host>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/zookeeper-servers>/i\ <port>${zookeeper_port}<\/port>" /etc/clickhouse-server-2/metrika.xml
sed -i "/<\/zookeeper-servers>/i\ <\/node>" /etc/clickhouse-server-2/metrika.xml
done
echo -e "\033[32m $current_time 副本2节点metrika.xml文件配置完成 \033[0m"
fi
chown -R clickhouse:clickhouse /var/log/clickhouse*
chown -R clickhouse:clickhouse /etc/clickhouse*
chown -R clickhouse:clickhouse /usr/bin/clickhouse*
rm -rf /etc/clickhouse-server/config.d/
rm -rf /etc/clickhouse-server-1/config.d/*
rm -rf /etc/clickhouse-server-2/config.d/*
echo -e "\033[32m $current_time 副本节点metrika.xml文件配置完成 \033[0m"
}
function create_systemctl_file()
{
cat > /etc/systemd/system/clickhouse-server.service <<EOF
[Unit]
Description=ClickHouse Server (analytic DBMS for big data)
Requires=network-online.target
After=network-online.target
[Service]
Type=simple
User=clickhouse
Group=clickhouse
Restart=always
RestartSec=30
RuntimeDirectory=clickhouse-server
ExecStart=/usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=$ck_mount_dir/clickhouse/data/clickhouse-server.pid
LimitCORE=infinity
LimitNOFILE=500000
CapabilityBoundingSet=CAP_NET_ADMIN CAP_IPC_LOCK CAP_SYS_NICE
[Install]
WantedBy=multi-user.target
EOF
echo -e "\033[32m $current_time clickhouse-server启动文件创建完成，可以通过systemctl start|stop|restart|status clickhouse-server启停clickhouse \033[0m"
cat > /etc/systemd/system/clickhouse-server-1.service <<EOF
[Unit]
Description=ClickHouse Server (analytic DBMS for big data)
Requires=network-online.target
After=network-online.target
[Service]
Type=simple
User=clickhouse
Group=clickhouse
Restart=always
RestartSec=30
RuntimeDirectory=clickhouse-server
ExecStart=/usr/bin/clickhouse-server --config=/etc/clickhouse-server-1/config.xml --pid-file=$ck_mount_dir/clickhouse-1/data/clickhouse-server.pid
LimitCORE=infinity
LimitNOFILE=500000
CapabilityBoundingSet=CAP_NET_ADMIN CAP_IPC_LOCK CAP_SYS_NICE
[Install]
WantedBy=multi-user.target
EOF
echo -e "\033[32m $current_time clickhouse-server-1启动文件创建完成，可以通过systemctl start|stop|restart|status clickhouse-server-1启停clickhouse副本 \033[0m"
if [[ $replicas = 3 ]];then
cat > /etc/systemd/system/clickhouse-server-2.service <<EOF
[Unit]
Description=ClickHouse Server (analytic DBMS for big data)
Requires=network-online.target
After=network-online.target
[Service]
Type=simple
User=clickhouse
Group=clickhouse
Restart=always
RestartSec=30
RuntimeDirectory=clickhouse-server
ExecStart=/usr/bin/clickhouse-server --config=/etc/clickhouse-server-2/config.xml --pid-file=$ck_mount_dir/clickhouse-2/data/clickhouse-server.pid
LimitCORE=infinity
LimitNOFILE=500000
CapabilityBoundingSet=CAP_NET_ADMIN CAP_IPC_LOCK CAP_SYS_NICE
[Install]
WantedBy=multi-user.target
EOF
echo -e "\033[32m $current_time clickhouse-server-2启动文件创建完成，可以通过systemctl start|stop|restart|status clickhouse-server-2启停clickhouse副本 \033[0m"
fi
rm -rf /usr/lib/debug/usr/bin/clickhouse.debug
chown -R clickhouse.clickhouse /usr/share/clickhouse
chmod 755 /etc/systemd/system/clickhouse*
systemctl daemon-reload
systemctl start clickhouse-server.service
systemctl start clickhouse-server-1.service
if [[ $replicas = 3 ]];then
systemctl start clickhouse-server-2.service
fi
}
function init_create_user()
{
sleep 30
cls_name=`clickhouse-client --password="$ck_password" -q "show clusters;"`
clickhouse-client --password="$ck_password" -q "create user if not exists aiuap identified WITH sha256_password BY '9#Osru6xRUMBt2t' on cluster $cls_name;"
clickhouse-client --password="$ck_password" -q "GRANT alter,alter user,create USER,DROP USER, SHOW USERS ON *.* TO aiuap on cluster $cls_name;"
}
function main()
{
while true
do
menu
selector
done
}
main
软件存放目录:
/data/clickhouse
把脚本 ac_install_ck_cluster.sh 跟软件放同一个目录, 本测试是: 默认是2个节点 2个分片一副本
-rwxr-xr-x 1 clickhouse clickhouse 46706 6月 21 16:27 ac_install_ck_cluster.sh
-rw-r--r-- 1 clickhouse clickhouse 101629 6月 21 16:24 clickhouse-client-23.9.1.1854-amd64.tgz
-rw-r--r-- 1 clickhouse clickhouse 281894382 6月 21 16:24 clickhouse-common-static-23.9.1.1854-amd64.tgz
-rw-r--r-- 1 clickhouse clickhouse 1153277476 6月 21 16:24 clickhouse-common-static-dbg-23.9.1.1854-amd64.tgz
-rw-r--r-- 1 clickhouse clickhouse 125819 6月 21 16:25 clickhouse-server-23.9.1.1854-amd64.tgz
-rw-r--r-- 1 clickhouse clickhouse 228 6月 21 17:04 config
[root@node2 clickhouse]# pwd
/data/clickhouse
[root@node2 clickhouse]# cat config
ips=192.168.137.64,192.168.137.68
local_ip=192.168.137.68
ck_mount_dir=/data/clickhouse
package_dir=/data/clickhouse
ck_password=1mbi0ArnRthrWXBV
share=2
replicas=2
zookeeper_ip=192.168.137.68,192.168.137.64
zookeeper_port=2181
脚本说明
1. 该集群部署脚本⼀共包含2部分
◦ ：具体安装脚本，主要包含两个功能
▪ 修改操作系统相关配置：禁⽤⼤⻚、禁⽤swap、禁⽤SElinux、禁⽤iptables、取消打开⽂
件数限制、安装依赖、关闭numa。
▪ 安装clickhouse：解压安装包执⾏初始化脚本、根据分⽚以及副本数量修改config和user配置⽂件、⽣成集群配置⽂件、创建system启动⽂件。
config：安装模板，包含集群的环境信息（请勿更改名字）。
2. 可通过变更安装模板config的信息来改变部署集群的规格（通过修改share和replicas），如下如下的配置信息为2分⽚2副本集群。
注意：⽬前仅⽀持多分⽚2副本、多分⽚2副本两种模式；config检查请勿出现空格。
◦ ips：ck部署机器ip
◦ local_ip：当前运⾏脚本的主机ip
◦ ck_mount_dirck：ck据据⽬录
◦ package_dirck：安装包
◦ ck_password：ck数据库⽤⼾default密码
◦ share：集群分⽚数量
◦ replicas：集群分⽚下的副本数量
◦ zookeeper_ip：zk集群ip
◦ zookeeper_port：zk端⼝1 ips=192.168.137.64,192.168.137.68
脚本使⽤
1. 在需要部署的机器上解压好ck安装包，上传部署脚本，编写config⽂件。
2. 赋予脚本可执⾏的权限后，便可直接运⾏ac_install_ck_cluster.sh，选择1修改系统环境，最后⼀项为退出，需要重启才⽣效，请选择是否⽴即重启。
1 [root@py install_ck]# ./ac_install_ck_cluster.sh
-------------------菜单功能选项------------------------
0 显⽰功能菜单
1 修改操作系统相关配置
2 安装clickhouse
3 退出7 -------------------------------------------------------
请输⼊要操作的菜单编号[0-3],查看功能菜单输⼊0：1
2024-07-03 16:55:10 禁⽤透明⼤⻚成功
vm.swappiness = 1
2024-07-03 16:55:10 禁⽤swap成功
setenforce: SELinux is disabled
2024-07-03 16:55:10 修改SElinux配置⽂件disabled成功
2024-07-03 16:55:10 ⽆iptables服务
Generating grub configuration file ...
Found linux image: /boot/vmlinuz-3.10.0-862.14.4.el7.x86_64
Found initrd image: /boot/initramfs-3.10.0-862.14.4.el7.x86_64.img
Found linux image: /boot/vmlinuz-0-rescue-5003025f93c1a84914ea5ae66519c100
Found initrd image: /boot/initramfs-0-rescue-5003025f93c1a84914ea5ae66519c100.im
done
numa已经关闭，是否⽴即重启主机，请输⼊y或者n: y
2024-07-03 16:55:10 主机正在重启，请稍等
Connection closing...Socket close.
Connection closed by foreign host.
Disconnected from remote host(py) at 16:55:21.
继续选择2，安装ck集群，其中在安装 clcikhouse-server 过程中提⽰为 default ⽤⼾设置密码，直
接回⻋即可，请勿输⼊密码，default密码直接从config获取。
[root@py install_ck]# ./ac_install_ck_cluster.sh
-------------------菜单功能选项------------------------
0 显⽰功能菜单
1 修改操作系统相关配置
2 安装clickhouse
3 退出
请输?要操作的菜单编号[0-3],查看功能菜单输 ：2
2024-07-03 11:11:18 创建clickhouse数据?录成功
2024-07-03 11:11:18 创建clickhouse-1数据?录成功
2024-07-03 11:11:18 创建clickhouse?志?录成功
2024-07-03 11:11:18 创建clickhouse-1?志?录成功
2024-07-03 11:11:18 进?安装包?录成功
2024-07-03 11:11:18 开始解压压缩包
.......
2024-07-03 11:11:18 开始拷?clickhouse-1副本?录。
2024-07-03 11:11:18 默认配置?录拷?成功
2024-07-03 11:11:18 ?志?录拷?成功
2024-07-03 11:11:18 ?件数限制?件拷?成功
2024-07-03 11:11:18 默认配置?录拷?成功21 2024-07-03 11:11:18 ?志?录拷?成功
2024-07-03 11:11:18 ?件数限制?件拷?成功
2024-07-03 11:11:18 副本tmp?录创建成功
2024-07-03 11:11:18 clickhouse-1副本?录拷?完成。
2024-07-03 11:11:18 开始修改主节点config.xml?件
2024-07-03 11:11:18 成功修改主节点config.xml?件
2024-07-03 11:11:18 开始修改主节点user.xml?件
2024-07-03 11:11:18 max_memory_usage计算成功
2024-07-03 11:11:18 max_bytes_before_external_group_by计算成功
2024-07-03 11:11:18 cpu核数计算成功
2024-07-03 11:11:18 成功修改主节点user.xml?件
2024-07-03 11:11:18 开始修改副本节点config.xml?件
2024-07-03 11:11:18 成功修改副本节点config.xml?件
2024-07-03 11:11:18 开始修改副本节点user.xml?件
2024-07-03 11:11:18 max_memory_usage计算成功
2024-07-03 11:11:18 max_bytes_before_external_group_by计算成功
2024-07-03 11:11:18 cpu核数计算成功
2024-07-03 11:11:18 成功修改副本节点user.xml?件
2024-07-03 11:11:18 开始修改副本节点config.xml?件
2024-07-03 11:11:18 成功修改副本节点config.xml?件
2024-07-03 11:11:18 开始修改副本节点user.xml?件
2024-07-03 11:11:18 max_memory_usage计算成功
2024-07-03 11:11:18 max_bytes_before_external_group_by计算成功
2024-07-03 11:11:18 cpu核数计算成功
2024-07-03 11:11:18 成功修改副本2节点user.xml?件
2024-07-03 11:11:18 开始修改主节点metrika.xml?件配置
2024-07-03 11:11:18 主节点metrika.xml?件配置完成
2024-07-03 11:11:18 开始修改副本节点metrika.xml?件配置
2024-07-03 11:11:18 副本1节点metrika.xml?件配置完成
2024-07-03 11:11:18 开始修改副本节点metrika.xml?件配置
2024-07-03 11:11:18 副本2节点metrika.xml?件配置完成
2024-07-03 11:11:18 副本节点metrika.xml?件配置完成
2024-07-03 11:11:18 clickhouse-server启动?件创建完成，可以通过systemctl start|sto
2024-07-03 11:11:18 clickhouse-server-1启动?件创建完成，可以通过systemctl start|st
2024-07-03 11:11:18 clickhouse-server-2启动?件创建完成，可以通过systemctl start|st
4. 依次在需要部署的机器上执⾏上述过程即可完成部署，部署好的环境信息如下
##配置⽂件
[root@py etc]# ll clickhouse-server*
clickhouse-server:
-r-------- 1 clickhouse clickhouse 62839 Apr 11 11:12 config.xml
-rw-r--r-- 1 clickhouse clickhouse 3827 Apr 11 11:12 metrika.xml
dr-x------ 2 clickhouse clickhouse 6 Apr 11 11:11 users.d8 -r-------- 1 clickhouse clickhouse 6635 Apr 11 11:12 users.xml
clickhouse-server-1:
dr-x------ 2 clickhouse clickhouse 6 Apr 11 11:12 config.d
-r-------- 1 clickhouse clickhouse 62842 Apr 11 11:12 config.xml
-rw-r--r-- 1 clickhouse clickhouse 3827 Apr 11 11:12 metrika.xml
dr-x------ 2 clickhouse clickhouse 6 Apr 11 11:11 users.d
-r-------- 1 clickhouse clickhouse 6635 Apr 11 11:12 users.xml
dr-x------ 2 clickhouse clickhouse 6 Apr 11 11:12 config.d
-r-------- 1 clickhouse clickhouse 62778 Apr 11 11:12 config.xml
-rw-r--r-- 1 clickhouse clickhouse 3827 Apr 11 11:12 metrika.xml
dr-x------ 2 clickhouse clickhouse 6 Apr 11 11:11 users.d
-r-------- 1 clickhouse clickhouse 6635 Apr 11 11:12 users.xml
##数据⽬录
[root@py etc]# ll /opt/
total 0
drwxr-xr-x 4 clickhouse clickhouse 29 Apr 11 11:11 clickhouse
drwxr-xr-x 4 clickhouse clickhouse 29 Apr 11 11:11 clickhouse-1
drwxr-xr-x 4 clickhouse clickhouse 29 Apr 11 11:12 clickhouse-2
## 查看集群信息
clickhouse-client --user default --password xxxxxx --port=9000
SELECT * FROM system.clusters
Query id: 4ce02271-897f-40a2-8b68-f2468abba6a8
┌─cluster────────────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─
│ perftest_2shards_1replicas │ 1 │ 1 │ 1 │ 192.168.
│ perftest_2shards_1replicas │ 1 │ 1 │ 2 │ 192.168.
│ perftest_2shards_1replicas │ 2 │ 1 │ 1 │ 192.168.
│ perftest_2shards_1replicas │ 2 │ 1 │ 2 │ 192.168.
复制表会使用相同的zookeeper路径和不同的副本名称
相同的zookeeper路径和不同的副本名称之间会相互复制
clickhouse分布式总表
1.分布式总表可以通过。（分布式总表不存储任何数据）
create table my_db.hits_distributed on cluster cluster1 as my_db.his
engine = distributed(cluster1,my_db,hits,rand());
集群名称：cluster1
数据库名称：my_db
表名：hits
分配数据的函数：rand()
如果使用分布式总表和rand()函数会平均分配所有分片。
1.建表
创建分布式总表
clickhouse测试
查看数据
####测试
每个分片和副本上都创建该库，该库的名字是由参数 /etc/clickhouse-server/metrika.xml
</perftest_2shards_1replicas> --定义的
</clickhouse_remote_servers>
create database perftest_2shards_1replicas;
create table goods_cluster on cluster perftest_2shards_1replicas (id int,name String,price int)ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/perftest_2shards_1replicas/goods_cluster','{replica}') ORDER BY id;
insert into goods_cluster values(4,'测试',8888);
CREATE TABLE goods_cluster_distributed ON CLUSTER perftest_2shards_2replicas AS perftest_2shards_2replicas.goods_cluster ENGINE = Distributed(perftest_2shards_1replicas, perftest_2shards_1replicas, goods_cluster,rand());
insert into goods_cluster_distributed values(11,'测试',11111);
insert into goods_cluster_distributed values(22,'测试',22222);
insert into goods_cluster_distributed values(33,'测试',33333);
查看表的数据分布
select * from system.replicas;
select * from system.replicas where table='goods_cluster';
查看 zookeeper
select * from system.zookeeper where path in ('/');
查看表是否会同步复制
select database,table,replica_is_active from system.replicas;
