负载均衡用户通过 FE 的查询端口query_port默认 9030使用 MySQL 协议连接 Doris。当部署多个 FE 节点时用户可以在多个 FE 之上部署负载均衡层来实现 Doris 查询的高可用。本文档介绍多种适用于 Doris 的负载均衡方案并介绍如何通过 Proxy Protocol 实现客户端 IP 透传。负载均衡本文使用以下三个 FE 节点作为示例进行步骤演示192.168.1.101:9030 192.168.1.102:9030 192.168.1.103:9030代理服务器所在节点192.168.1.10001 JDBC URL使用 JDBC URL 中自带的负载均衡配置。jdbc:mysql:loadbalance://192.168.1.101:9030,192.168.1.102:9030,192.168.1.103:9030/test_db详细可以参考 MySQL 官网文档02 Nginx使用 Nginx TCP 反向代理实现 Doris 的负载均衡。安装 Nginx请参看 Nginx 官网正确安装 Nginx这里以 Ubuntu 系统中安装 Nginx 1.18.0 版本为例展示 Nginx 的编译安装步骤。安装编译依赖sudoapt-getinstallbuild-essentialsudoapt-getinstalllibpcre3 libpcre3-devsudoapt-getinstallzlib1g-devsudoapt-getinstallopenssl libssl-dev安装 Nginxsudowgethttp://nginx.org/download/nginx-1.18.0.tar.gzsudotarzxvf nginx-1.18.0.tar.gzcdnginx-1.18.0sudo./configure--prefix/usr/local/nginx --with-stream --with-http_ssl_module --with-http_gzip_static_module --with-http_stub_status_modulesudomakemakeinstall配置反向代理新建配置文件vim/usr/local/nginx/conf/default.conf内容如下events { worker_connections 1024; } stream { upstream mysqld { hash $remote_addr consistent; server 192.168.1.101:9030 weight1 max_fails2 fail_timeout60s; server 192.168.1.102:9030 weight1 max_fails2 fail_timeout60s; server 192.168.1.103:9030 weight1 max_fails2 fail_timeout60s; } server { # Proxy port listen 6030; proxy_connect_timeout 300s; proxy_timeout 300s; proxy_pass mysqld; } }启动 Nginx指定配置文件启动cd/usr/local/nginx /usr/local/nginx/sbin/nginx-cconf.d/default.conf验证使用代理端口进行连接mysql-uroot-P6030-h192.168.1.100mysqlshow databases;--------------------|Database|--------------------|information_schema||mysql||test|--------------------2rowsinset(0.00sec)03 HAProxyHAProxy 是一个使用 C 语言编写高性能 TCP/HTTP 负载均衡器。安装 HAProxy下载 HAProxy下载地址https://src.fedoraproject.org/repo/pkgs/haproxy/解压tar-zxvfhaproxy-2.6.15.tar.gz-C/opt/mvhaproxy-2.6.15 haproxycdhaproxy编译yuminstallgcc gcc-c-ymakeTARGETlinux-glibcPREFIX/usr/local/haproxymakeinstallPREFIX/usr/local/haproxy配置 HAProxy配置 haproxy.conf 文件打开配置文件vim/etc/rsyslog.d/haproxy.conf内容如下$ModLoad imudp $UDPServerRun 514 local0.* /usr/local/haproxy/logs/haproxy.log ~开启远程日志vim/etc/sysconfig/rsyslog添加内容SYSLOGD_OPTIONS-c 2 -r -m 0参数说明-c 2使用兼容模式默认是-c 5。-r开启远程日志。·-m 0标记时间戳。单位是分钟为 0 时表示禁用该功能。使修改生效systemctl restart rsyslog编辑负载均衡文件vim/usr/local/haproxy/haproxy.cfgglobal maxconn 2000 ulimit-n 40075 log 127.0.0.1 local0 info uid 200 gid 200 chroot /var/empty daemon group haproxy user haproxy defaults log global mode http retries 3 option redispatch timeout connect 5000 timeout client 5000 timeout server 5000 timeout check 2000 frontend agent-front bind *:6030 mode tcp default_backend forward-fe backend forward-fe mode tcp balance roundrobin server fe-1 192.168.1.101:9030 weight 1 check inter 3000 rise 2 fall 3 server fe-2 192.168.1.102:9030 weight 1 check inter 3000 rise 2 fall 3 server fe-3 192.168.1.103:9030 weight 1 check inter 3000 rise 2 fall 3启动 HAProxy启动服务/opt/haproxy/haproxy -f /usr/local/haproxy/haproxy.cfg查看服务状态netstat -lnatp | grep -i haproxy验证mysql -h 192.168.1.100 -uroot -P6030 -p04 ProxySQLProxySQL 是基于 MySQL 的开源数据库代理软件用 C 语言编写。能实现连接管理、读写分离、负载均衡、故障切换等功能具有高性能、可配置、动态管理等优势常用于 Web 服务、大数据平台、云数据库等场景。安装 ProxySQL请参考 官方文档 正确安装 ProxySQL。配置 ProxySQLProxySQL 包含配置文件/etc/proxysql.cnf与配置数据库文件/var/lib/proxysql/proxysql.db。需特别注意若/var/lib/proxysql目录下存在proxysql.db文件ProxySQL 服务仅在首次启动时读取并解析proxysql.cnf后续启动不再读取。若要使proxysql.cnf配置在重启后生效需先删除/var/lib/proxysql/proxysql.db再重启服务这相当于初始化启动会生成新的proxysql.db文件原配置规则将被清除。以下是配置文件proxysql.cnf的主要内容datadir/var/lib/proxysql #数据目录 admin_variables { admin_credentialsadmin:admin # Admin databse username and password. mysql_ifaces0.0.0.0:6032 # Admin database port, used for connecting admin database of ProxySQL } mysql_variables { threads4 max_connections2048 default_query_delay0 default_query_timeout36000000 have_compresstrue poll_timeout2000 interfaces0.0.0.0:6030 default_schemainformation_schema stacksize1048576 server_version5.7.99 connect_timeout_server3000 monitor_usernamemonitor monitor_passwordmonitor monitor_history600000 monitor_connect_interval60000 monitor_ping_interval10000 monitor_read_only_interval1500 monitor_read_only_timeout500 ping_interval_server_msec120000 ping_timeout_server500 commands_statstrue sessions_sorttrue connect_retries_on_failure10 } mysql_servers ( ) mysql_users: ( ) mysql_query_rules: ( ) scheduler ( ) mysql_replication_hostgroups ( )连接 ProxySQL 管理数据库mysql-uadmin-padmin-P6032-hdoris01ProxySQLshowdatabases;---------------------------------------------------------|seq|name|file|---------------------------------------------------------|0|main|||2|disk|/var/lib/proxysql/proxysql.db||3|stats|||4|monitor|||5|stats_history|/var/lib/proxysql/proxysql_stats.db|---------------------------------------------------------5rowsinset(0.000sec)ProxySQLusemain;ProxySQLshowtables;--------------------------------------------|tables|--------------------------------------------|global_variables||mysql_collations||mysql_group_replication_hostgroups||mysql_query_rules||mysql_query_rules_fast_routing||mysql_replication_hostgroups||mysql_servers||mysql_users||proxysql_servers||runtime_checksums_values||runtime_global_variables||runtime_mysql_group_replication_hostgroups||runtime_mysql_query_rules||runtime_mysql_query_rules_fast_routing||runtime_mysql_replication_hostgroups||runtime_mysql_servers||runtime_mysql_users||runtime_proxysql_servers||runtime_scheduler||scheduler|--------------------------------------------20rowsinset(0.000sec)ProxySQL 配置后端 Doris FE使用 INSERT 语句将需要被代理的 FE 节点和端口添加到mysql_servers表中。其中hostgroup_id为10表示写组为20表示读组。我们这里不需要读写分离所以可以任意设置。mysql-uadmin-padmin-P6032-h127.0.0.1ProxySQLinsertintomysql_servers(hostgroup_id,hostname,port)values(10,192.168.0.101,9030);Query OK,1rowaffected(0.000sec)ProxySQLinsertintomysql_servers(hostgroup_id,hostname,port)values(10,192.168.0.102,9030);Query OK,1rowaffected(0.000sec)ProxySQLinsertintomysql_servers(hostgroup_id,hostname,port)values(10,192.168.0.103,9030);Query OK,1rowaffected(0.000sec)查看结果ProxySQLselecthostgroup_id,hostname,port,status,weightfrommysql_servers;---------------------------------------------------|hostgroup_id|hostname|port|status|weight|---------------------------------------------------|10|192.168.0.101|9030|ONLINE|1||20|192.168.0.102|9030|ONLINE|1||20|192.168.0.103|9030|ONLINE|1|---------------------------------------------------3rowsinset(0.000sec)如果在插入过程中出现报错ERROR 1045 (#2800): UNIQUE constraint failed: mysql_servers.hostgroup_id, mysql_servers.hostname, mysql_servers.port说明可能之前就已经定义了其他配置可以清空这张表或者删除对应 host 的配置ProxySQLselect*frommysql_servers;ProxySQLdeletefrommysql_servers;Query OK,6rowsaffected(0.000sec)保存信息ProxySQLloadmysql serverstoruntime;Query OK,0rowsaffected(0.006sec)ProxySQLsavemysql serverstodisk;Query OK,0rowsaffected(0.348sec)监控 Doris FE 节点配置添加 Doris FE 节点之后还需要监控这些后端节点。首先在 Doris 中创建一个用于监控的用户名mysql-uroot-P9030-h192.168.0.101Doriscreateusermonitor192.168.0.100identifiedbyPssword1!;Query OK,0rowsaffected(0.03sec)DorisgrantADMIN_PRIVon*.*tomonitor192.168.0.100;Query OK,0rowsaffected(0.02sec)然后回到 ProxySQL 代理层节点上配置监控mysql-uadmin-padmin-P6032-h127.0.0.1ProxySQLsetmysql-monitor_usernamemonitor;Query OK,1rowaffected(0.000sec)ProxySQLsetmysql-monitor_passwordPssword1!;Query OK,1rowaffected(0.000sec)保存配置并退出ProxySQLloadmysql serverstoruntime;Query OK,0rowsaffected(0.006sec)ProxySQLsavemysql serverstodisk;Query OK,0rowsaffected(0.348sec)验证监控结果。ProxySQL 监控模块的指标都保存在monitor.log表中。连接监控ProxySQLselect*frommysql_server_connect_log;-------------------------------------------------------------------------------|hostname|port|time_start_us|connect_success_time_us|connect_error|-------------------------------------------------------------------------------|192.168.0.101|9030|1548665195883957|762|NULL||192.168.0.102|9030|1548665195894099|399|NULL||192.168.0.103|9030|1548665195904266|483|NULL||192.168.0.101|9030|1548665255883715|824|NULL||192.168.0.102|9030|1548665255893942|656|NULL||192.168.0.101|9030|1548665495884125|615|NULL||192.168.0.102|9030|1548665495894254|441|NULL||192.168.0.103|9030|1548665495904479|638|NULL||192.168.0.101|9030|1548665512917846|487|NULL||192.168.0.102|9030|1548665512928071|994|NULL||192.168.0.103|9030|1548665512938268|613|NULL|-------------------------------------------------------------------------------20rowsinset(0.000sec)心跳监控ProxySQLselect*frommysql_server_ping_log;-------------------------------------------------------------------------|hostname|port|time_start_us|ping_success_time_us|ping_error|-------------------------------------------------------------------------|192.168.0.101|9030|1548665195883407|98|NULL||192.168.0.102|9030|1548665195885128|119|NULL|...........|192.168.0.102|9030|1548665415889362|106|NULL||192.168.0.103|9030|1548665562898295|97|NULL|-------------------------------------------------------------------------110rowsinset(0.001sec)客户端 IP 透传多数情况下通过代理服务连接到后端 Doris 服务后客户端 IP 信息会丢失Doris 服务端只能获取到代理服务器的 IP 地址信息。自 2.1.1 版本开始Doris 支持 Proxy Protocol 协议。利用这个协议可以是实现客户端 IP 透传从而在经过负载均衡后Doris 依然可以获取客户端的真实 IP实现白名单等权限控制。下面分别介绍如何在 Nginx 和 Haproxy 中开启 Proxy Protocol。Doris 开启 Proxy Protocol 支持在 FE 的fe.conf中添加enable_proxy_protocol true备注仅支持 Proxy Protocol V1。仅支持并作用于 MySQL 协议端口不支持和影响 HTTP、ADBC 等其他协议端口。在 Doris 3.1 版本之前开启后必须使用 Proxy Protocol 协议进行连接否则连接失败。3.1 版本开始开启 Proxy Protocol 后依然可以使用标准的 MySQL 连接协议进行连接。01 Nginx在配置文件的server部分新增proxy_protocol on;events { worker_connections 1024; } stream { upstream mysqld { hash $remote_addr consistent; server 192.168.1.101:9030 weight1 max_fails2 fail_timeout60s; server 192.168.1.102:9030 weight1 max_fails2 fail_timeout60s; server 192.168.1.103:9030 weight1 max_fails2 fail_timeout60s; } server { # Proxy port listen 6030; proxy_connect_timeout 300s; proxy_timeout 300s; proxy_pass mysqld; # Enable Proxy Protocol to the upstream server proxy_protocol on; } }02 HAProxy在haproxy.cfg的backend部分新增send-proxy参数backend forward-fe mode tcp balance roundrobin server fe-1 192.168.1.101:9030 weight 1 check inter 3000 rise 2 fall 3 send-proxy server fe-2 192.168.1.102:9030 weight 1 check inter 3000 rise 2 fall 3 send-proxy server fe-3 192.168.1.103:9030 weight 1 check inter 3000 rise 2 fall 3 send-proxy验证 IP 透传是否成功通过代理连接 Dorismysql-uroot-P6030-h192.168.1.100验证mysqlshowprocesslist;-----------------------------------------------------------------------------------------------------------------------------------------------------------------|CurrentConnected|Id|User|Host|LoginTime|Catalog|Db|Command|Time|State|QueryId|Info|-----------------------------------------------------------------------------------------------------------------------------------------------------------------|Yes|1|root|192.168.1.101:34390|2024-03-1716:32:22|internal||Query|0|OK|82edc460d93f4e28-8bbed058a068e259|showprocesslist|-----------------------------------------------------------------------------------------------------------------------------------------------------------------1rowinset(0.00sec)如果在Host列看到的真实的客户端 IP则说明验证成功。否则只能看到代理服务的 IP 地址。同时在 fe.audit.log 中也会记录真实的客户端 IP。负载均衡 - Apache Doris