This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new acad8e6 [Doc] Add a catalog of best practices, and the implementation
of Doris FE load balancing (#6231)
acad8e6 is described below
commit acad8e6367431bac4c8de5dea948300a6b036524
Author: jiafeng.zhang <[email protected]>
AuthorDate: Wed Jul 21 10:51:50 2021 +0800
[Doc] Add a catalog of best practices, and the implementation of Doris FE
load balancing (#6231)
Add a catalog of best practices, and the implementation of Doris FE load
balancing
---
docs/.vuepress/sidebar/en.js | 7 +
docs/.vuepress/sidebar/zh-CN.js | 7 +
docs/en/best-practices/fe-load-balance.md | 471 +++++++++++++++++++++++++++
docs/zh-CN/best-practices/fe-load-balance.md | 470 ++++++++++++++++++++++++++
4 files changed, 955 insertions(+)
diff --git a/docs/.vuepress/sidebar/en.js b/docs/.vuepress/sidebar/en.js
index 04d5ee4..98da472 100644
--- a/docs/.vuepress/sidebar/en.js
+++ b/docs/.vuepress/sidebar/en.js
@@ -191,6 +191,13 @@ module.exports = [
sidebarDepth: 1,
},
{
+ title: "Bast Practices",
+ directoryPath: "best-practices/",
+ children: [
+ "fe-load-balance"
+ ],
+ },
+ {
title: "Extending Ability",
directoryPath: "extending-doris/",
children: [
diff --git a/docs/.vuepress/sidebar/zh-CN.js b/docs/.vuepress/sidebar/zh-CN.js
index 6de5ccb..c3f7bcd 100644
--- a/docs/.vuepress/sidebar/zh-CN.js
+++ b/docs/.vuepress/sidebar/zh-CN.js
@@ -193,6 +193,13 @@ module.exports = [
sidebarDepth: 1,
},
{
+ title: "最佳实践",
+ directoryPath: "best-practices/",
+ children: [
+ "fe-load-balance"
+ ],
+ },
+ {
title: "扩展功能",
directoryPath: "extending-doris/",
children: [
diff --git a/docs/en/best-practices/fe-load-balance.md
b/docs/en/best-practices/fe-load-balance.md
new file mode 100644
index 0000000..40edf99
--- /dev/null
+++ b/docs/en/best-practices/fe-load-balance.md
@@ -0,0 +1,471 @@
+---
+{
+ "title": "Doris FE load balance",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# FE load balance
+
+When deploying multiple FE nodes, users can deploy a load balancing layer on
top of multiple FEs to achieve high availability of Doris.
+
+## 1. Code method
+
+Retry and load balance yourself in the application layer code. For example, if
a connection is found to be down, it will automatically retry on other
connections. Application layer code retry requires the application to configure
multiple doris front-end node addresses.
+
+## 2. JDBC Connector
+
+If you use mysql jdbc connector to connect to Doris, you can use jdbc's
automatic retry mechanism:
+
+```
+jdbc:mysql:loadbalance://[host:port],[host:port].../[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue
+```
+
+For details, please refer to [Mysql official website
document](https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-j2ee-concepts-managing-load-balanced-connections.html)
+
+## 3. ProxySQL method
+
+ProxySQL is a flexible and powerful MySQL proxy layer. It is a MySQL
middleware that can be actually used in a production environment. It can
realize read-write separation, support Query routing function, support dynamic
designation of a certain SQL for cache, support dynamic loading configuration,
failure Switching and some SQL filtering functions.
+
+Doris's FE process is responsible for receiving user connections and query
requests. It itself is horizontally scalable and highly available, but it
requires users to set up a proxy on multiple FEs to achieve automatic
connection load balancing.
+
+### 3.1 Install ProxySQL (yum way)
+
+```shell
+ Configure yum source
+# vim /etc/yum.repos.d/proxysql.repo
+[proxysql_repo]
+name= ProxySQL YUM repository
+baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
+gpgcheck=1
+gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
+
+Perform installation
+# yum clean all
+# yum makecache
+# yum -y install proxysql
+View version
+# proxysql --version
+ProxySQL version 1.4.13-15-g69d4207, codename Truls
+Set up auto start
+# systemctl enable proxysql
+# systemctl start proxysql
+# systemctl status proxysql
+After startup, it will listen to two ports, the default is 6032 and 6033. Port
6032 is the management port of ProxySQL, and 6033 is the port for ProxySQL to
provide external services (that is, the forwarding port connected to the real
database of the forwarding backend).
+# netstat -tunlp
+Active Internet connections (only servers)
+Proto Recv-Q Send-Q Local Address Foreign Address State
PID/Program name
+tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN
23940/proxysql
+tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN
+```
+
+### 3.2 **ProxySQL Config**
+
+ProxySQL has a configuration file `/etc/proxysql.cnf` and a configuration
database file `/var/lib/proxysql/proxysql.db`. **Special attention is needed
here**: If there is a `"proxysql.db"` file (under the `/var/lib/proxysql`
directory), the ProxySQL service will only be read when it is started for the
first time The `proxysql.cnf file` and parse it; after startup, the
`proxysql.cnf` file will not be read! If you want the configuration in the
proxysql.cnf file to take effect after restart [...]
+
+#### 3.2.1 View and modify configuration files
+
+Here are mainly a few parameters, which have been commented out below, and you
can modify them according to your needs
+
+```sql
+# egrep -v "^#|^$" /etc/proxysql.cnf
+datadir="/var/lib/proxysql" #data dir
+admin_variables=
+{
+ admin_credentials="admin:admin" #User name and password for
connecting to the management terminal
+ mysql_ifaces="0.0.0.0:6032" #Management port, used to connect to
proxysql management database
+}
+mysql_variables=
+{
+ threads=4 #Specify the number of threads opened for the
forwarding port
+ max_connections=2048
+ default_query_delay=0
+ default_query_timeout=36000000
+ have_compress=true
+ poll_timeout=2000
+ interfaces="0.0.0.0:6033" #Specify the forwarding port, used to
connect to the back-end mysql database, which is equivalent to acting as a proxy
+ default_schema="information_schema"
+ stacksize=1048576
+ server_version="5.5.30" #Specify the version of the backend
mysql
+ connect_timeout_server=3000
+ monitor_username="monitor"
+ monitor_password="monitor"
+ monitor_history=600000
+ monitor_connect_interval=60000
+ monitor_ping_interval=10000
+ monitor_read_only_interval=1500
+ monitor_read_only_timeout=500
+ ping_interval_server_msec=120000
+ ping_timeout_server=500
+ commands_stats=true
+ sessions_sort=true
+ connect_retries_on_failure=10
+}
+mysql_servers =
+(
+)
+mysql_users:
+(
+)
+mysql_query_rules:
+(
+)
+scheduler=
+(
+)
+mysql_replication_hostgroups=
+(
+)
+```
+
+#### 3.2.2 Connect to the ProxySQL management port test
+
+```sql
+
+# mysql -uadmin -padmin -P6032 -hdoris01
+View the global_variables table information of the main library (it is in this
library after login by default)
+MySQL [(none)]> show databases;
++-----+---------------+-------------------------------------+
+| 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 |
++-----+---------------+-------------------------------------+
+5 rows in set (0.000 sec)
+MySQL [(none)]> use main;
+Reading table information for completion of table and column names
+You can turn off this feature to get a quicker startup with -A
+
+Database changed
+MySQL [main]> show tables;
++--------------------------------------------+
+| 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 |
++--------------------------------------------+
+20 rows in set (0.000 sec)
+
+```
+
+#### 3.2.3 ProxySQL configuration backend Doris FE
+
+
+Use the insert statement to add the host to the mysql_servers table, where:
hostgroup_id is 10 for the write group, and 20 for the read group. We don't
need to read and write the license here, and it doesn't matter which one can be
set randomly.
+
+```sql
+[root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
+............
+MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)
values(10,'192.168.9.211',9030);
+Query OK, 1 row affected (0.000 sec)
+
+MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)
values(10,'192.168.9.212',9030);
+Query OK, 1 row affected (0.000 sec)
+
+MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)
values(10,'192.168.9.213',9030);
+Query OK, 1 row affected (0.000 sec)
+
+If an error occurs during the insertion process:
+ERROR 1045 (#2800): UNIQUE constraint failed: mysql_servers.hostgroup_id,
mysql_servers.hostname, mysql_servers.port
+
+It means that other configurations may have been defined before, you can clear
this table or delete the configuration of the corresponding host
+MySQL [(none)]> select * from mysql_servers;
+MySQL [(none)]> delete from mysql_servers;
+Query OK, 6 rows affected (0.000 sec)
+
+Check whether these 3 nodes are inserted successfully and their status.
+MySQL [(none)]> select * from mysql_servers\G;
+*************************** 1. row ***************************
+ hostgroup_id: 10
+ hostname: 192.168.9.211
+ port: 9030
+ status: ONLINE
+ weight: 1
+ compression: 0
+ max_connections: 1000
+max_replication_lag: 0
+ use_ssl: 0
+ max_latency_ms: 0
+ comment:
+*************************** 2. row ***************************
+ hostgroup_id: 10
+ hostname: 192.168.9.212
+ port: 9030
+ status: ONLINE
+ weight: 1
+ compression: 0
+ max_connections: 1000
+max_replication_lag: 0
+ use_ssl: 0
+ max_latency_ms: 0
+ comment:
+*************************** 3. row ***************************
+ hostgroup_id: 10
+ hostname: 192.168.9.213
+ port: 9030
+ status: ONLINE
+ weight: 1
+ compression: 0
+ max_connections: 1000
+max_replication_lag: 0
+ use_ssl: 0
+ max_latency_ms: 0
+ comment:
+6 rows in set (0.000 sec)
+
+ERROR: No query specified
+
+After the above modification, load it to RUNTIME and save it to disk. The
following two steps are very important, otherwise your configuration
information will be gone after you exit and must be saved
+MySQL [(none)]> load mysql servers to runtime;
+Query OK, 0 rows affected (0.006 sec)
+
+MySQL [(none)]> save mysql servers to disk;
+Query OK, 0 rows affected (0.348 sec)
+```
+
+#### 3.2.4 Monitor Doris FE node configuration
+
+After adding doris fe nodes, you also need to monitor these back-end nodes.
For multiple FE high-availability load balancing environments on the backend,
this is necessary because ProxySQL needs to be automatically adjusted by the
read_only value of each node
+
+Whether they belong to the read group or the write group.
+
+First create a user name for monitoring on the back-end master main data node
+
+```sql
+Execute on the node of the doris fe master master database:
+# mysql -P9030 -uroot -p
+mysql> create user monitor@'192.168.9.%' identified by 'P@ssword1!';
+Query OK, 0 rows affected (0.03 sec)
+mysql> grant ADMIN_PRIV on *.* to monitor@'192.168.9.%';
+Query OK, 0 rows affected (0.02 sec)
+
+Then go back to the mysql-proxy proxy layer node to configure monitoring
+# mysql -uadmin -padmin -P6032 -h127.0.0.1
+MySQL [(none)]> set mysql-monitor_username='monitor';
+Query OK, 1 row affected (0.000 sec)
+
+MySQL [(none)]> set mysql-monitor_password='P@ssword1!';
+Query OK, 1 row affected (0.000 sec)
+
+After modification, load to RUNTIME and save to disk
+MySQL [(none)]> load mysql variables to runtime;
+Query OK, 0 rows affected (0.001 sec)
+
+MySQL [(none)]> save mysql variables to disk;
+Query OK, 94 rows affected (0.079 sec)
+
+Verify the monitoring results: The indicators of the ProxySQL monitoring
module are stored in the log table of the monitor library.
+The following is the monitoring of whether the connection is normal
(monitoring of connect indicators):
+Note: There may be many connect_errors, this is because there is an error when
the monitoring information is not configured. After the configuration, if the
result of connect_error is NULL, it means normal。
+MySQL [(none)]> select * from mysql_server_connect_log;
++---------------+------+------------------+-------------------------+---------------+
+| hostname | port | time_start_us | connect_success_time_us |
connect_error |
++---------------+------+------------------+-------------------------+---------------+
+| 192.168.9.211 | 9030 | 1548665195883957 | 762 | NULL
|
+| 192.168.9.212 | 9030 | 1548665195894099 | 399 | NULL
|
+| 192.168.9.213 | 9030 | 1548665195904266 | 483 | NULL
|
+| 192.168.9.211 | 9030 | 1548665255883715 | 824 | NULL
|
+| 192.168.9.212 | 9030 | 1548665255893942 | 656 | NULL
|
+| 192.168.9.211 | 9030 | 1548665495884125 | 615 | NULL
|
+| 192.168.9.212 | 9030 | 1548665495894254 | 441 | NULL
|
+| 192.168.9.213 | 9030 | 1548665495904479 | 638 | NULL
|
+| 192.168.9.211 | 9030 | 1548665512917846 | 487 | NULL
|
+| 192.168.9.212 | 9030 | 1548665512928071 | 994 | NULL
|
+| 192.168.9.213 | 9030 | 1548665512938268 | 613 | NULL
|
++---------------+------+------------------+-------------------------+---------------+
+20 rows in set (0.000 sec)
+The following is the monitoring of heartbeat information (monitoring of ping
indicators)
+MySQL [(none)]> select * from mysql_server_ping_log;
++---------------+------+------------------+----------------------+------------+
+| hostname | port | time_start_us | ping_success_time_us | ping_error |
++---------------+------+------------------+----------------------+------------+
+| 192.168.9.211 | 9030 | 1548665195883407 | 98 | NULL |
+| 192.168.9.212 | 9030 | 1548665195885128 | 119 | NULL |
+...........
+| 192.168.9.213 | 9030 | 1548665415889362 | 106 | NULL |
+| 192.168.9.213 | 9030 | 1548665562898295 | 97 | NULL |
++---------------+------+------------------+----------------------+------------+
+110 rows in set (0.001 sec)
+
+The read_only log is also empty at this time (normally, when the new
environment is configured, this read-only log is empty)
+MySQL [(none)]> select * from mysql_server_read_only_log;
+Empty set (0.000 sec)
+
+All 3 nodes are in the group with hostgroup_id=10.
+Now, load the modification of the mysql_replication_hostgroups table just now
to RUNTIME to take effect。
+MySQL [(none)]> load mysql servers to runtime;
+Query OK, 0 rows affected (0.003 sec)
+
+MySQL [(none)]> save mysql servers to disk;
+Query OK, 0 rows affected (0.361 sec)
+
+
+MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from
mysql_servers;
++--------------+---------------+------+--------+--------+
+| hostgroup_id | hostname | port | status | weight |
++--------------+---------------+------+--------+--------+
+| 10 | 192.168.9.211 | 9030 | ONLINE | 1 |
+| 20 | 192.168.9.212 | 9030 | ONLINE | 1 |
+| 20 | 192.168.9.213 | 9030 | ONLINE | 1 |
++--------------+---------------+------+--------+--------+
+3 rows in set (0.000 sec)
+```
+
+#### 3.2.5 Configure Doris users
+
+All the above configurations are about the back-end Doris FE node. Now you can
configure the SQL statements, including: the user who sends the SQL statement,
the routing rules of the SQL statement, the cache of the SQL query, the
rewriting of the SQL statement, and so on.
+
+This section is the user configuration used by the SQL request, such as the
root user. This requires that we need to add relevant users to the back-end
Doris FE node first. Here are examples of two user names root and doris.
+
+```sql
+First, execute on the Doris FE master master database node:
+# mysql -P9030 -uroot -p
+.........
+
+mysql> create user doris@'%' identified by 'P@ssword1!';
+Query OK, 0 rows affected, 1 warning (0.04 sec)
+
+mysql> grant ADMIN_PRIV on *.* to doris@'%';
+Query OK, 0 rows affected, 1 warning (0.03 sec)
+
+
+Then go back to the mysql-proxy proxy layer node, configure the mysql_users
table, and add the two users just now to the table.
+admin> insert into mysql_users(username,password,default_hostgroup)
values('root','',10);
+Query OK, 1 row affected (0.001 sec)
+
+admin> insert into mysql_users(username,password,default_hostgroup)
values('doris','P@ssword1!',10);
+Query OK, 1 row affected (0.000 sec)
+
+admin> load mysql users to runtime;
+Query OK, 0 rows affected (0.001 sec)
+
+admin> save mysql users to disk;
+Query OK, 0 rows affected (0.108 sec)
+
+The mysql_users table has many fields. The three main fields are username,
password, and default_hostgroup:
+ -username: The username used by the front-end to connect to ProxySQL and
ProxySQL to route SQL statements to MySQL.
+ -password: the password corresponding to the user name. It can be a
plain text password or a hash password. If you want to use the hash password,
you can execute it on a MySQL node first select password(PASSWORD), and then
copy the encryption result to this field.
+ -default_hostgroup: The default routing destination of the username. For
example, when the field value of the specified root user is 10, the SQL
statement sent by the root user is used by default
+ In this case, it will be routed to a node in the hostgroup_id=10 group.
+
+admin> select * from mysql_users\G
+*************************** 1. row ***************************
+ username: root
+ password:
+ active: 1
+ use_ssl: 0
+ default_hostgroup: 10
+ default_schema: NULL
+ schema_locked: 0
+transaction_persistent: 1
+ fast_forward: 0
+ backend: 1
+ frontend: 1
+ max_connections: 10000
+*************************** 2. row ***************************
+ username: doris
+ password: P@ssword1!
+ active: 1
+ use_ssl: 0
+ default_hostgroup: 10
+ default_schema: NULL
+ schema_locked: 0
+transaction_persistent: 1
+ fast_forward: 0
+ backend: 1
+ frontend: 1
+ max_connections: 10000
+2 rows in set (0.000 sec)
+
+Although the mysql_users table is not described in detail here, only users
with active=1 are valid users, and the default active is 1.
+
+MySQL [(none)]> load mysql users to runtime;
+Query OK, 0 rows affected (0.001 sec)
+
+MySQL [(none)]> save mysql users to disk;
+Query OK, 0 rows affected (0.123 sec)
+
+In this way, you can use the doris username and password to connect to
ProxySQL through the sql client
+```
+
+#### 3.2.6 Connect to Doris through ProxySQL for testing
+
+Next, use the root user and doris user to test whether they can be routed to
the default hostgroup_id=10 (it is a write group) to read data. The following
is connected through the forwarding port 6033, the connection is forwarded to
the real back-end database!
+
+```sql
+#mysql -uroot -p -P6033 -hdoris01 -e "show databases;"
+Enter password:
+ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching
hostgroup 10 after 10000ms
+At this time, an error was found, and it was not forwarded to the real doris
fe on the backend.
+Through the log, you can see that there is set autocommit=0 to open the
transaction
+Check the configuration found:
+
+mysql-forward_autocommit=false
+mysql-autocommit_false_is_transaction=false
+
+We don’t need to read and write separation here, just turn these two
parameters into true directly through the following statement.
+mysql> UPDATE global_variables SET variable_value='true' WHERE
variable_name='mysql-forward_autocommit';
+Query OK, 1 row affected (0.00 sec)
+
+mysql> UPDATE global_variables SET variable_value='true' WHERE
variable_name='mysql-autocommit_false_is_transaction';
+Query OK, 1 row affected (0.01 sec)
+
+mysql> LOAD MYSQL VARIABLES TO RUNTIME;
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> SAVE MYSQL VARIABLES TO DISK;
+Query OK, 98 rows affected (0.12 sec)
+
+Then we try again and it shows success
+[root@doris01 ~]# mysql -udoris -pP@ssword1! -P6033 -h192.168.9.211 -e "show
databases;"
+Warning: Using a password on the command line interface can be insecure.
++--------------------+
+| Database |
++--------------------+
+| doris_audit_db |
+| information_schema |
+| retail |
++--------------------+
+```
+
+OK, that's the end, you can use Mysql client, JDBC, etc. to connect to
ProxySQL to operate your doris.
diff --git a/docs/zh-CN/best-practices/fe-load-balance.md
b/docs/zh-CN/best-practices/fe-load-balance.md
new file mode 100644
index 0000000..37e27e5
--- /dev/null
+++ b/docs/zh-CN/best-practices/fe-load-balance.md
@@ -0,0 +1,470 @@
+---
+{
+ "title": "FE负载均衡",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# FE负载均衡
+
+当部署多个 FE 节点时，用户可以在多个 FE 之上部署负载均衡层来实现 Doris 的高可用。
+
+## 1. 代码方式
+
+自己在应用层代码进行重试和负载均衡。比如发现一个连接挂掉，就自动在其他连接上进行重试。应用层代码重试需要应用自己配置多个 doris 前端节点地址。
+
+## 2. JDBC Connector
+
+如果使用 mysql jdbc connector 来连接 Doris，可以使用 jdbc 的自动重试机制:
+
+```
+jdbc:mysql:loadbalance://[host:port],[host:port].../[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue
+```
+
+详细可以参考[Mysql官网文档](https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-j2ee-concepts-managing-load-balanced-connections.html)
+
+## 3. ProxySQL 方式
+
+ProxySQL是灵活强大的MySQL代理层, 是一个能实实在在用在生产环境的MySQL中间件，可以实现读写分离，支持 Query
路由功能，支持动态指定某个 SQL 进行 cache，支持动态加载配置、故障切换和一些 SQL的过滤功能。
+
+Doris 的 FE 进程负责接收用户连接和查询请求，其本身是可以横向扩展且高可用的，但是需要用户在多个 FE 上架设一层
proxy，来实现自动的连接负载均衡。
+
+### 3.1 安装ProxySQL （yum方式）
+
+```shell
+配置yum源
+# vim /etc/yum.repos.d/proxysql.repo
+[proxysql_repo]
+name= ProxySQL YUM repository
+baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
+gpgcheck=1
+gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
+
+执行安装
+# yum clean all
+# yum makecache
+# yum -y install proxysql
+查看版本
+# proxysql --version
+ProxySQL version 1.4.13-15-g69d4207, codename Truls
+设置开机自启动
+# systemctl enable proxysql
+# systemctl start proxysql
+# systemctl status proxysql
+启动后会监听两个端口， 默认为6032和6033。6032端口是ProxySQL的管理端口，6033是ProxySQL对外提供服务的端口
(即连接到转发后端的真正数据库的转发端口)。
+# netstat -tunlp
+Active Internet connections (only servers)
+Proto Recv-Q Send-Q Local Address Foreign Address State
PID/Program name
+tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN
23940/proxysql
+tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN
+```
+
+### 3.2 **ProxySQL 配置**
+
+ProxySQL 有配置文件 `/etc/proxysql.cnf`
和配置数据库文件`/var/lib/proxysql/proxysql.db`。**这里需要特别注意**：如果存在如果存在`"proxysql.db"`文件(在`/var/lib/proxysql`目录下)，则
ProxySQL
服务只有在第一次启动时才会去读取`proxysql.cnf文件`并解析；后面启动会就不会读取`proxysql.cnf`文件了！如果想要让proxysql.cnf
文件里的配置在重启 proxysql 服务后生效(即想要让 proxysql 重启时读取并解析 proxysql.cnf配置文件)，则需要先删除
`/var/lib/proxysql/proxysql.db `数据库文件，然后再重启 proxysql 服务。这样就相当于初始化启动 proxysql
服务了，会再次生产一个纯净的 proxysql.db 数据库文件(如果之前配置了 proxysql 相关路由规则等，则就会被抹掉)
+
+#### 3.2.1 查看及修改配置文件
+
+这里主要是是几个参数，在下面已经注释出来了，可以根据自己的需要进行修改
+
+```conf
+# egrep -v "^#|^$" /etc/proxysql.cnf
+datadir="/var/lib/proxysql" #数据目录
+admin_variables=
+{
+ admin_credentials="admin:admin" #连接管理端的用户名与密码
+ mysql_ifaces="0.0.0.0:6032" #管理端口，用来连接proxysql的管理数据库
+}
+mysql_variables=
+{
+ threads=4 #指定转发端口开启的线程数量
+ max_connections=2048
+ default_query_delay=0
+ default_query_timeout=36000000
+ have_compress=true
+ poll_timeout=2000
+ interfaces="0.0.0.0:6033" #指定转发端口，用于连接后端mysql数据库的，相当于代理作用
+ default_schema="information_schema"
+ stacksize=1048576
+ server_version="5.5.30" #指定后端mysql的版本
+ connect_timeout_server=3000
+ monitor_username="monitor"
+ monitor_password="monitor"
+ monitor_history=600000
+ monitor_connect_interval=60000
+ monitor_ping_interval=10000
+ monitor_read_only_interval=1500
+ monitor_read_only_timeout=500
+ ping_interval_server_msec=120000
+ ping_timeout_server=500
+ commands_stats=true
+ sessions_sort=true
+ connect_retries_on_failure=10
+}
+mysql_servers =
+(
+)
+mysql_users:
+(
+)
+mysql_query_rules:
+(
+)
+scheduler=
+(
+)
+mysql_replication_hostgroups=
+(
+)
+```
+
+#### 3.2.2 连接 ProxySQL 管理端口测试
+
+```sql
+
+# mysql -uadmin -padmin -P6032 -hdoris01
+查看main库（默认登陆后即在此库）的global_variables表信息
+MySQL [(none)]> show databases;
++-----+---------------+-------------------------------------+
+| 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 |
++-----+---------------+-------------------------------------+
+5 rows in set (0.000 sec)
+MySQL [(none)]> use main;
+Reading table information for completion of table and column names
+You can turn off this feature to get a quicker startup with -A
+
+Database changed
+MySQL [main]> show tables;
++--------------------------------------------+
+| 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 |
++--------------------------------------------+
+20 rows in set (0.000 sec)
+
+```
+
+#### 3.2.3 ProxySQL 配置后端 Doris FE
+
+
+使用 insert 语句添加主机到 mysql_servers 表中，其中：hostgroup_id
为10表示写组，为20表示读组，我们这里不需要读写分许，无所谓随便设置哪一个都可以。
+
+```sql
+[root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
+............
+MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)
values(10,'192.168.9.211',9030);
+Query OK, 1 row affected (0.000 sec)
+
+MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)
values(10,'192.168.9.212',9030);
+Query OK, 1 row affected (0.000 sec)
+
+MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port)
values(10,'192.168.9.213',9030);
+Query OK, 1 row affected (0.000 sec)
+
+如果在插入过程中，出现报错：
+ERROR 1045 (#2800): UNIQUE constraint failed: mysql_servers.hostgroup_id,
mysql_servers.hostname, mysql_servers.port
+
+说明可能之前就已经定义了其他配置，可以清空这张表 或者 删除对应host的配置
+MySQL [(none)]> select * from mysql_servers;
+MySQL [(none)]> delete from mysql_servers;
+Query OK, 6 rows affected (0.000 sec)
+
+查看这3个节点是否插入成功，以及它们的状态。
+MySQL [(none)]> select * from mysql_servers\G;
+*************************** 1. row ***************************
+ hostgroup_id: 10
+ hostname: 192.168.9.211
+ port: 9030
+ status: ONLINE
+ weight: 1
+ compression: 0
+ max_connections: 1000
+max_replication_lag: 0
+ use_ssl: 0
+ max_latency_ms: 0
+ comment:
+*************************** 2. row ***************************
+ hostgroup_id: 10
+ hostname: 192.168.9.212
+ port: 9030
+ status: ONLINE
+ weight: 1
+ compression: 0
+ max_connections: 1000
+max_replication_lag: 0
+ use_ssl: 0
+ max_latency_ms: 0
+ comment:
+*************************** 3. row ***************************
+ hostgroup_id: 10
+ hostname: 192.168.9.213
+ port: 9030
+ status: ONLINE
+ weight: 1
+ compression: 0
+ max_connections: 1000
+max_replication_lag: 0
+ use_ssl: 0
+ max_latency_ms: 0
+ comment:
+6 rows in set (0.000 sec)
+
+ERROR: No query specified
+
+如上修改后，加载到RUNTIME，并保存到disk，下面两步非常重要，不然退出以后你的配置信息就没了，必须保存
+MySQL [(none)]> load mysql servers to runtime;
+Query OK, 0 rows affected (0.006 sec)
+
+MySQL [(none)]> save mysql servers to disk;
+Query OK, 0 rows affected (0.348 sec)
+```
+
+#### 3.2.4 监控Doris FE节点配置
+
+添 doris fe 节点之后，还需要监控这些后端节点。对于后端多个FE高可用负载均衡环境来说，这是必须的，因为 ProxySQL 需要通过每个节点的
read_only 值来自动调整
+
+它们是属于读组还是写组。
+
+首先在后端master主数据节点上创建一个用于监控的用户名
+
+```sql
+在doris fe master主数据库节点行执行：
+# mysql -P9030 -uroot -p
+mysql> create user monitor@'192.168.9.%' identified by 'P@ssword1!';
+Query OK, 0 rows affected (0.03 sec)
+mysql> grant ADMIN_PRIV on *.* to monitor@'192.168.9.%';
+Query OK, 0 rows affected (0.02 sec)
+
+然后回到mysql-proxy代理层节点上配置监控
+# mysql -uadmin -padmin -P6032 -h127.0.0.1
+MySQL [(none)]> set mysql-monitor_username='monitor';
+Query OK, 1 row affected (0.000 sec)
+
+MySQL [(none)]> set mysql-monitor_password='P@ssword1!';
+Query OK, 1 row affected (0.000 sec)
+
+修改后，加载到RUNTIME，并保存到disk
+MySQL [(none)]> load mysql variables to runtime;
+Query OK, 0 rows affected (0.001 sec)
+
+MySQL [(none)]> save mysql variables to disk;
+Query OK, 94 rows affected (0.079 sec)
+
+验证监控结果：ProxySQL监控模块的指标都保存在monitor库的log表中。
+以下是连接是否正常的监控(对connect指标的监控)：
+注意：可能会有很多connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为NULL则表示正常。
+MySQL [(none)]> select * from mysql_server_connect_log;
++---------------+------+------------------+-------------------------+---------------+
+| hostname | port | time_start_us | connect_success_time_us |
connect_error |
++---------------+------+------------------+-------------------------+---------------+
+| 192.168.9.211 | 9030 | 1548665195883957 | 762 | NULL
|
+| 192.168.9.212 | 9030 | 1548665195894099 | 399 | NULL
|
+| 192.168.9.213 | 9030 | 1548665195904266 | 483 | NULL
|
+| 192.168.9.211 | 9030 | 1548665255883715 | 824 | NULL
|
+| 192.168.9.212 | 9030 | 1548665255893942 | 656 | NULL
|
+| 192.168.9.211 | 9030 | 1548665495884125 | 615 | NULL
|
+| 192.168.9.212 | 9030 | 1548665495894254 | 441 | NULL
|
+| 192.168.9.213 | 9030 | 1548665495904479 | 638 | NULL
|
+| 192.168.9.211 | 9030 | 1548665512917846 | 487 | NULL
|
+| 192.168.9.212 | 9030 | 1548665512928071 | 994 | NULL
|
+| 192.168.9.213 | 9030 | 1548665512938268 | 613 | NULL
|
++---------------+------+------------------+-------------------------+---------------+
+20 rows in set (0.000 sec)
+以下是对心跳信息的监控(对ping指标的监控)
+MySQL [(none)]> select * from mysql_server_ping_log;
++---------------+------+------------------+----------------------+------------+
+| hostname | port | time_start_us | ping_success_time_us | ping_error |
++---------------+------+------------------+----------------------+------------+
+| 192.168.9.211 | 9030 | 1548665195883407 | 98 | NULL |
+| 192.168.9.212 | 9030 | 1548665195885128 | 119 | NULL |
+...........
+| 192.168.9.213 | 9030 | 1548665415889362 | 106 | NULL |
+| 192.168.9.213 | 9030 | 1548665562898295 | 97 | NULL |
++---------------+------+------------------+----------------------+------------+
+110 rows in set (0.001 sec)
+
+read_only日志此时也为空(正常来说，新环境配置时，这个只读日志是为空的)
+MySQL [(none)]> select * from mysql_server_read_only_log;
+Empty set (0.000 sec)
+
+3个节点都在hostgroup_id=10的组中。
+现在，将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。
+MySQL [(none)]> load mysql servers to runtime;
+Query OK, 0 rows affected (0.003 sec)
+
+MySQL [(none)]> save mysql servers to disk;
+Query OK, 0 rows affected (0.361 sec)
+
+现在看结果
+MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from
mysql_servers;
++--------------+---------------+------+--------+--------+
+| hostgroup_id | hostname | port | status | weight |
++--------------+---------------+------+--------+--------+
+| 10 | 192.168.9.211 | 9030 | ONLINE | 1 |
+| 20 | 192.168.9.212 | 9030 | ONLINE | 1 |
+| 20 | 192.168.9.213 | 9030 | ONLINE | 1 |
++--------------+---------------+------+--------+--------+
+3 rows in set (0.000 sec)
+```
+
+#### 3.2.5 配置Doris用户
+
+上面的所有配置都是关于后端 Doris FE 节点的，现在可以配置关于 SQL 语句的，包括：发送 SQL 语句的用户、SQL 语句的路由规则、SQL
查询的缓存、SQL 语句的重写等等。
+
+本小节是 SQL 请求所使用的用户配置，例如 root 用户。这要求我们需要先在后端 Doris FE 节点添加好相关用户。这里以 root 和 doris
两个用户名为例.
+
+```sql
+首先，在Doris FE master主数据库节点上执行：
+# mysql -P9030 -uroot -p
+.........
+mysql> create user doris@'%' identified by 'P@ssword1!';
+Query OK, 0 rows affected, 1 warning (0.04 sec)
+
+mysql> grant ADMIN_PRIV on *.* to doris@'%';
+Query OK, 0 rows affected, 1 warning (0.03 sec)
+
+
+然后回到mysql-proxy代理层节点，配置mysql_users表，将刚才的两个用户添加到该表中。
+admin> insert into mysql_users(username,password,default_hostgroup)
values('root','',10);
+Query OK, 1 row affected (0.001 sec)
+
+admin> insert into mysql_users(username,password,default_hostgroup)
values('doris','P@ssword1!',10);
+Query OK, 1 row affected (0.000 sec)
+
+加载用户到运行环境中，并将用户信息保存到磁盘
+admin> load mysql users to runtime;
+Query OK, 0 rows affected (0.001 sec)
+
+admin> save mysql users to disk;
+Query OK, 0 rows affected (0.108 sec)
+
+mysql_users表有不少字段，最主要的三个字段为username、password和default_hostgroup：
+- username：前端连接ProxySQL，以及ProxySQL将SQL语句路由给MySQL所使用的用户名。
+- password：用户名对应的密码。可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个MySQL节点上执行
+ select password(PASSWORD)，然后将加密结果复制到该字段。
+- default_hostgroup：该用户名默认的路由目标。例如，指定root用户的该字段值为10时，则使用root用户发送的SQL语句默认
+ 情况下将路由到hostgroup_id=10组中的某个节点。
+
+admin> select * from mysql_users\G
+*************************** 1. row ***************************
+ username: root
+ password:
+ active: 1
+ use_ssl: 0
+ default_hostgroup: 10
+ default_schema: NULL
+ schema_locked: 0
+transaction_persistent: 1
+ fast_forward: 0
+ backend: 1
+ frontend: 1
+ max_connections: 10000
+*************************** 2. row ***************************
+ username: doris
+ password: P@ssword1!
+ active: 1
+ use_ssl: 0
+ default_hostgroup: 10
+ default_schema: NULL
+ schema_locked: 0
+transaction_persistent: 1
+ fast_forward: 0
+ backend: 1
+ frontend: 1
+ max_connections: 10000
+2 rows in set (0.000 sec)
+
+虽然这里没有详细介绍mysql_users表，但只有active=1的用户才是有效的用户。
+
+MySQL [(none)]> load mysql users to runtime;
+Query OK, 0 rows affected (0.001 sec)
+
+MySQL [(none)]> save mysql users to disk;
+Query OK, 0 rows affected (0.123 sec)
+
+这样就可以通过sql客户端，使用doris的用户名密码去连接了ProxySQL了
+```
+
+#### 3.2.6 通过 ProxySQL 连接 Doris 进行测试
+
+下面，分别使用 root 用户和 doris 用户测试下它们是否能路由到默认的 hostgroup_id=10 (它是一个写组)读数据。下面是通过转发端口
6033 连接的，连接的是转发到后端真正的数据库!
+
+```sql
+#mysql -uroot -p -P6033 -hdoris01 -e "show databases;"
+Enter password:
+ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching
hostgroup 10 after 10000ms
+这个时候发现出错，并没有转发到后端真正的doris fe上
+通过日志看到有set autocommit=0 这样开启事务
+检查配置发现：
+mysql-forward_autocommit=false
+mysql-autocommit_false_is_transaction=false
+我们这里不需要读写分离，只需要将这两个参数通过下面语句直接搞成true就可以了
+mysql> UPDATE global_variables SET variable_value='true' WHERE
variable_name='mysql-forward_autocommit';
+Query OK, 1 row affected (0.00 sec)
+
+mysql> UPDATE global_variables SET variable_value='true' WHERE
variable_name='mysql-autocommit_false_is_transaction';
+Query OK, 1 row affected (0.01 sec)
+
+mysql> LOAD MYSQL VARIABLES TO RUNTIME;
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> SAVE MYSQL VARIABLES TO DISK;
+Query OK, 98 rows affected (0.12 sec)
+
+然后我们在重新试一下，显示成功
+[root@doris01 ~]# mysql -udoris -pP@ssword1! -P6033 -h192.168.9.211 -e "show
databases;"
+Warning: Using a password on the command line interface can be insecure.
++--------------------+
+| Database |
++--------------------+
+| doris_audit_db |
+| information_schema |
+| retail |
++--------------------+
+```
+
+OK，到此就结束了，你就可以用 Mysql 客户端，JDBC 等任何连接 mysql 的方式连接 ProxySQL 去操作你的 doris 了
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
