手把手教你部署 ClickHouse chproxy

这篇文章主要介绍chproxy的部署过程，找到这里相信你已经了解了为什么要用chproxy。简单来说，就是当数据量大的时候，不建议直接写入分布式表，容易造成写放大，把CH累死，所以用chproxy来做负载均衡，缓解压力。

开始前，请确认你已经搭建好了一套ClickHouse集群。请在其中一台写节点上进行以下操作：

1. 下载chproxy

   https://github.com/Vertamedia/chproxy/releases

2. 创建配置文件chproxy.yml

   server:
     http:
         listen_addr: ":9090" #访问chproxy的端口
         allowed_networks: ["10.0.0.0/8"]
   
   users:
   #创建chproxy账号密码并指定对应的CH账号
     - name: "default"
       to_cluster: "write"
       to_user: "default"
       password: "default_password"
       allow_cors: true
       max_concurrent_queries: 1000
       max_execution_time: 1m
     - name: "user1"
       to_cluster: "write"
       to_user: "user1"
       password: "user1_password"
       allow_cors: true
       max_concurrent_queries: 1000
       max_execution_time: 1m
     - name: "readonly"
       to_cluster: "read"
       to_user: "readonly"
       password: "readonly_password"
       allow_cors: true
       max_concurrent_queries: 50
       max_execution_time: 1m
   
   clusters:
   #配置CH读&写节点信息
     - name: "write"
       nodes: ["10.xxx.xxx.xx1:8123","10.xxx.xxx.xx3:8123"]
       #replicas:
       #  - name: "replica1"
       #    nodes: ["10.xxx.xxx.xx1:8123","10.xxx.xxx.xx3:8123"]
       #  - name: "replica2"
       #    nodes: ["10.xxx.xxx.xx2:8123","10.xxx.xxx.xx4:8123"]
       users:
       #这里配置CH集群中的账号密码
         - name: "default"
           password: "defaultpassword"
         - name: "user1"
           password: "user1password"
       kill_query_user:
         name: "default"
         password: "defaultpassword"
         name: "user1"
         password: "user1password"
     - name: "read"
       nodes: ["10.xxx.xxx.xx2:8123","10.xxx.xxx.xx4:8123"]
       users:
         - name: "readonly"
           password: "readonlypassword"
   
   caches:
     - name: "shortterm"
       dir: "/app/chproxy/cache/shortterm"
       max_size: 500Mb
       expire: 130s
   

3. 启动chproxy（注意用后台启动的方式，保持进程常在）

   nohup ./chproxy -config=./conf.d/config.yml >> ./logs/chproxy.log 2>&1 &
   

完成以上操作，就可以用chproxy账号进行读写了。在生产环境中，为防止服务器重启导致进程挂掉，我们可以用keepalived做一个高可用，也可以简单写一个判断脚本放到crontab里。

1. 创建重启脚本restart.sh

   #!/bin/bash
   cd /data/chproxy
   
   CHK_PORT1=$1
   PORT_PROCESS1=`netstat -nlpt | grep :$CHK_PORT1 | wc -l`
   
   if [ $PORT_PROCESS1 -eq 0 ];then
      # ps -ef | grep chproxy | head -2 | tail -1 | awk '{print $2}' | xargs kill -9
      nohup ./chproxy -config=./conf.d/config.yml >> ./logs/chproxy.log 2>&1 &
   else
      exit 0
   fi
   

2. 编辑crontab
   执行 crontab -e 加一条指令 */1 * * * * bash /data/chproxy/restart.sh 9090