He3Proxy安装部署

He3Proxy项目介绍

He3Proxy基于PG协议专为He3DB打造的高性能数据库代理项目，采用Go语言开发，He3Proxy为He3DB提供读写分离、负载均衡、连接池管理、读一致性等基础能力，让用户像使用单机一样使用He3DB集群，并提升集群整体吞吐量、提高节点资源利用率，同时兼具He3DB 集群管理功能，如集群监控、灰度升级、数据分层存储、动态扩缩容等，助力He3DB实现serverless。

同时He3Proxy也兼容其他基于PG协议的主从架构数据库，可作为其数据库中间件使用。

这里只谈一个问题，He3DB没有He3Proxy行不行？如果少了He3Proxy会怎样？答案是，不可以，如果没有He3Proxy，那么用户就会直面He3DB的节点集群，主备等节点，用户就必须自己去完成He3Proxy做的工作，比如读写分离，一致性，负载均衡等，这些问题都是中间件解决的核心问题，有了中间件，用户只面对中间件，其他的工作交给中间件去做，大大简化了应用层的设计和使用难度。

项目开源地址：https://gitee.com/he3db/he3proxy.git
了解更多可以参考移动云文章：初识数据库中间件-He3Proxy

安装部署

步骤如下：
1. 安装Go语言环境（请使用最新版）
2. git clone https://gitee.com/he3db/he3proxy.git $GOPATH/src/gitee.com/he3db/he3proxy
3. cd $GOPATH/src/gitee.com/he3db/he3proxy
4. source ./dev.sh
5. make
6. 设置配置文件
7. 运行he3proxy
./he3proxy -config=./he3proxy.yaml -hba=./hba.conf -opentracing=false -log-level=info -keep-conn=false -readonly=false

我们以PG为主，He3DB为备，为例进行部署，通过He3Proxy，连接PG与He3DB，用户最终通过He3Proxy去访问主备节点。

部署PG为主，He3DB为备

部署PG主节点

1. 安装PG14.2
2. initdb -D masternode
3. 修改配置，pg_hba.conf, postgresql.conf
4. 启动节点

部署He3DB为备节点

1. 编译安装he3db，安装步骤参考云原生数据库He3DB——安装
2. 启动tikv服务，（没有部署集群，开发用）

postgres@slpc:~$ tiup clean --all
postgres@slpc:~$ tiup playground v7.1.0 --mode tikv-slim --kv 1 --pd 1
tiup is checking updates for component playground ...
A new version of playground is available:
   The latest version:         v1.12.3
   Local installed version:    v1.12.2
   Update current component:   tiup update playground
   Update all components:      tiup update --all

Starting component `playground`: /home/postgres/.tiup/components/playground/v1.12.2/tiup-playground v7.1.0 --mode tikv-slim --kv 1 --pd 1
Start pd instance:v7.1.0
Start tikv instance:v7.1.0
PD Endpoints:   127.0.0.1:2379
Grafana:        http://127.0.0.1:3000

4. 执行./bin/pg_basebackup -h 192.168.109.131 -p 9432 -U postgres -F p -P -X stream -R -D pgbaknode -l databak
5. 注释掉自动生成的postgresql.auto.conf，修改配置文件postgresql.conf:

primary_conninfo = 'application_name=pushstandby user=postgres host=127.0.0.1 port=9432 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any'
hot_standby = on
push_standby = on
fsync = off
full_page_writes = off
he3mirror = true
lmdb_page_directory = '/home/postgres/he3db/tmp/lmdb/page'
lmdb_wal_directory = '/home/postgres/he3db/tmp/lmdb/wal'

6. 启动备节点

启动中间件

主备搭建好后，我们准备启动He3Proxy中间件，先创建配置文件：
hba.conf（含义参考pg_hba.conf）：

# TYPE    DATABASE      USER        ADDRESS       METHOD
   host    all          all          0.0.0.0/0   trust

再配置he3proxy.yaml， 具体配置如下：

# he3proxy server listen addr, port
addr: 0.0.0.0:9696    # 中间件的监听地址，端口

# prometheus server listen addr
prometheus_addr: 0.0.0.0:7085

# server user and password, effective under MySQL configuration
user_list:
  - user: postgres
    password: postgres 

# the web api server
web_addr: 0.0.0.0:9797
#HTTP Basic Auth
web_user: admin
web_password: admin

# if set log_path, the sql log will write into log_path/sql.log,the system log
# will write into log_path/sys.log
log_path: /home/postgres/he3db/he3proxy  # 日志路径

# log level[debug|info|warn|error],default error
log_level: debug

# if set log_sql(on|off) off,the sql log will not output
log_sql: on

# only log the query that take more than slow_log_time ms
#slow_log_time : 100

# the path of blacklist sql file
# all these sqls in the file will been forbidden by he3proxy
# sql黑名单文件路径
# 所有在该文件中的sql都会被拒绝转发
#blacklist_sql_file: /home/he3proxy/blacklist    

# only allow this ip list ip to connect he3proxy
# support ip and ip segment
allow_ips: 127.0.0.1,192.168.109.131/24

# the charset of he3proxy, if you don't set this item
# the default charset of he3proxy is utf8.
# Effective under MySQL configuration
#proxy_charset: gbk

# node is an agenda for real remote mysql server.
nodes:
  - # current request name as node1, do not modify
    name: node1
    #pg pool max connection idle time (unit: min)
    max_conns_idle_time: 60
    # default max connections for db server
    max_conns_limit: 2000
    # db pool max num
    max_pool_num: 4
    init_conn_count: 16

    # all db in a node must have the same user and password
    # current request user name as postgres. Otherwise, it cannot be obtained LSN
    user: postgres
    password: 

    # master represents a real db master server, can not be realm name
    master: 192.168.109.131:9432         # 主节点

    # slave represents a real db salve server,and the number after '@' is
    # read load weight of this slave.
    slave: 192.168.109.131:8432@1     # 备节点
    # unit: second(s)
    down_after_noalive: 32
    # load balance mode, Optional: weight, metric， lsn, cache
    # weight: load balance by node balance as configuration
    # metric: load balance by node load
    # lsn: load balance by speed of node log playback to meet read consistency
    # cache: prefer lb to data cached node, otherwise change mode to weight
    # random: random for a slave node
    # master: only exec in master node
    load_balance_mode: weight
    # request LSN and table cache node relationship metadata time interval, unit: ms
    lsn_cache_req_interval: 60
    # Optional: strong, session, default session. Come into effect when load_balance_mode=lsn.
    # strong: global consistency
    # session: session consistency
    read_consistence_level: session
    # param for strong consistence, consistence_timeout means waiting for slave catch up master's lsn. unit: ms
    # must more than value of lsn_cache_req_interval,  default: 100
    consistence_timeout: 100
    # param for strong consistence, consistence_timeout_action means action for timeout,
    # 0: put request to master node, default value;
    # >=1: proxy will return an error "wait replication complete timeout, please retry";
    consistence_timeout_action: 0

    # [configuration about remote postgres prometheus addr & metrics]
    # pg prometheus addr
    #pg_prometheus_addr: http://x.x.10.41:9090/api/v1/query
    # monitoring pg node & pg_exporter port & node_exporter port
    # format like ip@pg_exporter_port@node_exporter_port   x.x.10.42@9187@9100
    # the node order must consist with configuration slave【节点顺序一定要与slave配置顺序一致】
    # monitor_pg_node: [ "x.x.10.42@9187@9100", "x.x.10.41@9187@63000" ]
    # pg_exporter & node_exporter name, keep default except necessary
    pg_exporter_name: "pg_exporter"
    node_exporter_name: "node_exporter"
    node_cpu_mode: "idle"
    statistics_time_interval: "5m"
    # modify according actual environment
    pg_data_disk_device_name: "sdb"
    # request metrics time interval, unit: second(s)
    metrics_req_interval: 60

# schema defines sharding rules, the db is the sharding table database.
schema_list:
  # he3proxy default user, do not delete and modify
  - user: he3proxy
    nodes: [ node1 ]
    default: node1

  - user: postgres
    nodes: [ node1 ]
    default: node1

He3Proxy刚开源不久，资料还比较少，因He3Proxy基于kingshard开发，可以参考其文档kingshard/KingDoc，配置文件可以参考kingshard使用指南。

参考文档

如果想深入理解He3Proxy，可参考移动云发布的下列文章：
初识数据库中间件-He3Proxy
He3Proxy协议层解读（PostgreSQL 前后端通信协议）
He3Proxy SQL解析器原理及实现
He3Proxy读一致性方案设计
He3Proxy性能调优