When deploying multiple FE nodes, users can deploy a load balancing layer on top of multiple FEs to achieve high availability of Doris.

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.

JDBC Connector

If you use mysql jdbc connector to connect to Doris, you can use jdbc’s automatic retry mechanism:

  1. jdbc:mysql:loadbalance://[host:port],[host:port].../[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue

For details, please refer to Mysql official website document

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.

Install ProxySQL (yum way)

  1.  Configure yum source
  2. # vim /etc/yum.repos.d/proxysql.repo
  3. [proxysql_repo]
  4. name= ProxySQL YUM repository
  5. baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
  6. gpgcheck=1
  7. gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
  9. Perform installation
  10. # yum clean all
  11. # yum makecache
  12. # yum -y install proxysql
  13. View version  
  14. # proxysql --version
  15. ProxySQL version 1.4.13-15-g69d4207, codename Truls
  16. Set up auto start
  17. # systemctl enable proxysql
  18. # systemctl start proxysql      
  19. # systemctl status proxysql
  20. 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).
  21. # netstat -tunlp
  22. Active Internet connections (only servers)
  23. Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name  
  24. tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      23940/proxysql    
  25. tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN

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 restarting the proxysql service (that is, you want proxysql to read and parse the proxysql.cnf configuration file when it restarts), you need to delete /var/lib/proxysql/proxysql first. dbdatabase file, and then restart the proxysql service. This is equivalent to initializing the proxysql service, and a pure proxysql.db database file will be produced again (if proxysql related routing rules, etc. are configured before, it will be erased)

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

  1. # egrep -v "^#|^$" /etc/proxysql.cnf
  2. datadir="/var/lib/proxysql"         #data dir
  3. admin_variables=
  4. {
  5.         admin_credentials="admin:admin"  #User name and password for connecting to the management terminal
  6.         mysql_ifaces="0.0.0.0:6032"    #Management port, used to connect to proxysql management database
  7. }
  8. mysql_variables=
  9. {
  10.         threads=4      #Specify the number of threads opened for the forwarding port
  11.         max_connections=2048
  12.         default_query_delay=0
  13.         default_query_timeout=36000000
  14.         have_compress=true
  15.         poll_timeout=2000
  16.         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
  17.         default_schema="information_schema"
  18.         stacksize=1048576
  19.         server_version="5.5.30"        #Specify the version of the backend mysql
  20.         connect_timeout_server=3000
  21.         monitor_username="monitor"
  22.         monitor_password="monitor"
  23.         monitor_history=600000
  24.         monitor_connect_interval=60000
  25.         monitor_ping_interval=10000
  26.         monitor_read_only_interval=1500
  27.         monitor_read_only_timeout=500
  28.         ping_interval_server_msec=120000
  29.         ping_timeout_server=500
  30.         commands_stats=true
  31.         sessions_sort=true
  32.         connect_retries_on_failure=10
  33. }
  34. mysql_servers =
  35. (
  36. )
  37. mysql_users:
  38. (
  39. )
  40. mysql_query_rules:
  41. (
  42. )
  43. scheduler=
  44. (
  45. )
  46. mysql_replication_hostgroups=
  47. (
  48. )

Connect to the ProxySQL management port test

  1. # mysql -uadmin -padmin -P6032 -hdoris01
  2. View the global_variables table information of the main library (it is in this library after login by default)
  3. MySQL [(none)]> show databases;
  4. +-----+---------------+-------------------------------------+
  5. | seq | name          | file                                |
  6. +-----+---------------+-------------------------------------+
  7. | 0   | main          |                                     |
  8. | 2   | disk          | /var/lib/proxysql/proxysql.db       |
  9. | 3   | stats         |                                     |
  10. | 4   | monitor       |                                     |
  11. | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
  12. +-----+---------------+-------------------------------------+
  13. 5 rows in set (0.000 sec)
  14. MySQL [(none)]> use main;
  15. Reading table information for completion of table and column names
  16. You can turn off this feature to get a quicker startup with -A
  18. Database changed
  19. MySQL [main]> show tables;
  20. +--------------------------------------------+
  21. | tables                                     |
  22. +--------------------------------------------+
  23. | global_variables                           |
  24. | mysql_collations                           |
  25. | mysql_group_replication_hostgroups         |
  26. | mysql_query_rules                          |
  27. | mysql_query_rules_fast_routing             |
  28. | mysql_replication_hostgroups               |
  29. | mysql_servers                              |
  30. | mysql_users                                |
  31. | proxysql_servers                           |
  32. | runtime_checksums_values                   |
  33. | runtime_global_variables                   |
  34. | runtime_mysql_group_replication_hostgroups |
  35. | runtime_mysql_query_rules                  |
  36. | runtime_mysql_query_rules_fast_routing     |
  37. | runtime_mysql_replication_hostgroups       |
  38. | runtime_mysql_servers                      |
  39. | runtime_mysql_users                        |
  40. | runtime_proxysql_servers                   |
  41. | runtime_scheduler                          |
  42. | scheduler                                  |
  43. +--------------------------------------------+
  44. 20 rows in set (0.000 sec)

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.

  1. [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
  2. ............
  3. MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.9.211',9030);
  4. Query OK, 1 row affected (0.000 sec)
  6. MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.9.212',9030);
  7. Query OK, 1 row affected (0.000 sec)
  9. MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.9.213',9030);
  10. Query OK, 1 row affected (0.000 sec)
  12. If an error occurs during the insertion process:
  13. ERROR 1045 (#2800): UNIQUE constraint failed: mysql_servers.hostgroup_id, mysql_servers.hostname, mysql_servers.port
  15. It means that other configurations may have been defined before, you can clear this table or delete the configuration of the corresponding host
  16. MySQL [(none)]> select * from mysql_servers;
  17. MySQL [(none)]> delete from mysql_servers;
  18. Query OK, 6 rows affected (0.000 sec)
  20. Check whether these 3 nodes are inserted successfully and their status.
  21. MySQL [(none)]> select * from mysql_servers\G;
  22. *************************** 1. row ***************************
  23.        hostgroup_id: 10
  24.            hostname: 192.168.9.211
  25.                port: 9030
  26.              status: ONLINE
  27.              weight: 1
  28.         compression: 0
  29.     max_connections: 1000
  30. max_replication_lag: 0
  31.             use_ssl: 0
  32.      max_latency_ms: 0
  33.             comment:
  34. *************************** 2. row ***************************
  35.        hostgroup_id: 10
  36.            hostname: 192.168.9.212
  37.                port: 9030
  38.              status: ONLINE
  39.              weight: 1
  40.         compression: 0
  41.     max_connections: 1000
  42. max_replication_lag: 0
  43.             use_ssl: 0
  44.      max_latency_ms: 0
  45.             comment:
  46. *************************** 3. row ***************************
  47.        hostgroup_id: 10
  48.            hostname: 192.168.9.213
  49.                port: 9030
  50.              status: ONLINE
  51.              weight: 1
  52.         compression: 0
  53.     max_connections: 1000
  54. max_replication_lag: 0
  55.             use_ssl: 0
  56.      max_latency_ms: 0
  57.             comment:
  58. 6 rows in set (0.000 sec)
  60. ERROR: No query specified
  62. 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
  63. MySQL [(none)]> load mysql servers to runtime;
  64. Query OK, 0 rows affected (0.006 sec)
  66. MySQL [(none)]> save mysql servers to disk;
  67. Query OK, 0 rows affected (0.348 sec)

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

  1. Execute on the node of the doris fe master master database:
  2. # mysql -P9030 -uroot -p 
  3. mysql> create user monitor@'192.168.9.%' identified by 'P@ssword1!';
  4. Query OK, 0 rows affected (0.03 sec)
  5. mysql> grant ADMIN_PRIV on *.* to monitor@'192.168.9.%';
  6. Query OK, 0 rows affected (0.02 sec)
  8. Then go back to the mysql-proxy proxy layer node to configure monitoring
  9. # mysql -uadmin -padmin -P6032 -h127.0.0.1
  10. MySQL [(none)]> set mysql-monitor_username='monitor';
  11. Query OK, 1 row affected (0.000 sec)
  13. MySQL [(none)]> set mysql-monitor_password='P@ssword1!';
  14. Query OK, 1 row affected (0.000 sec)
  16. After modification, load to RUNTIME and save to disk
  17. MySQL [(none)]> load mysql variables to runtime;
  18. Query OK, 0 rows affected (0.001 sec)
  20. MySQL [(none)]> save mysql variables to disk;
  21. Query OK, 94 rows affected (0.079 sec)
  23. Verify the monitoring results: The indicators of the ProxySQL monitoring module are stored in the log table of the monitor library.
  24. The following is the monitoring of whether the connection is normal (monitoring of connect indicators):
  25. 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.
  26. MySQL [(none)]> select * from mysql_server_connect_log;
  27. +---------------+------+------------------+-------------------------+---------------+
  28. | hostname      | port | time_start_us    | connect_success_time_us | connect_error |
  29. +---------------+------+------------------+-------------------------+---------------+
  30. | 192.168.9.211 | 9030 | 1548665195883957 | 762                     | NULL          |
  31. | 192.168.9.212 | 9030 | 1548665195894099 | 399                     | NULL          |
  32. | 192.168.9.213 | 9030 | 1548665195904266 | 483                     | NULL          |
  33. | 192.168.9.211 | 9030 | 1548665255883715 | 824                     | NULL          |
  34. | 192.168.9.212 | 9030 | 1548665255893942 | 656                     | NULL          |
  35. | 192.168.9.211 | 9030 | 1548665495884125 | 615                     | NULL          |
  36. | 192.168.9.212 | 9030  | 1548665495894254 | 441                     | NULL          |
  37. | 192.168.9.213 | 9030 | 1548665495904479 | 638                     | NULL          |
  38. | 192.168.9.211 | 9030 | 1548665512917846 | 487                     | NULL          |
  39. | 192.168.9.212 | 9030 | 1548665512928071 | 994                     | NULL          |
  40. | 192.168.9.213 | 9030 | 1548665512938268 | 613                     | NULL          |
  41. +---------------+------+------------------+-------------------------+---------------+
  42. 20 rows in set (0.000 sec)
  43. The following is the monitoring of heartbeat information (monitoring of ping indicators)
  44. MySQL [(none)]> select * from mysql_server_ping_log;
  45. +---------------+------+------------------+----------------------+------------+
  46. | hostname      | port | time_start_us    | ping_success_time_us | ping_error |
  47. +---------------+------+------------------+----------------------+------------+
  48. | 192.168.9.211 | 9030 | 1548665195883407 | 98                   | NULL       |
  49. | 192.168.9.212 | 9030 | 1548665195885128 | 119                  | NULL       |
  50. ...........
  51. | 192.168.9.213 | 9030 | 1548665415889362 | 106                  | NULL       |
  52. | 192.168.9.213 | 9030 | 1548665562898295 | 97                   | NULL       |
  53. +---------------+------+------------------+----------------------+------------+
  54. 110 rows in set (0.001 sec)
  56. The read_only log is also empty at this time (normally, when the new environment is configured, this read-only log is empty)
  57. MySQL [(none)]> select * from mysql_server_read_only_log;
  58. Empty set (0.000 sec)
  60. All 3 nodes are in the group with hostgroup_id=10.
  61. Now, load the modification of the mysql_replication_hostgroups table just now to RUNTIME to take effect.
  62. MySQL [(none)]> load mysql servers to runtime;
  63. Query OK, 0 rows affected (0.003 sec)
  65. MySQL [(none)]> save mysql servers to disk;
  66. Query OK, 0 rows affected (0.361 sec)
  69. MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
  70. +--------------+---------------+------+--------+--------+
  71. | hostgroup_id | hostname      | port | status | weight |
  72. +--------------+---------------+------+--------+--------+
  73. | 10           | 192.168.9.211 | 9030 | ONLINE | 1      |
  74. | 20           | 192.168.9.212 | 9030 | ONLINE | 1      |
  75. | 20           | 192.168.9.213 | 9030 | ONLINE | 1      |
  76. +--------------+---------------+------+--------+--------+
  77. 3 rows in set (0.000 sec)

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.

  1. First, execute on the Doris FE master master database node:
  2. # mysql -P9030 -uroot -p
  3. .........
  5. mysql> create user doris@'%' identified by 'P@ssword1!';
  6. Query OK, 0 rows affected, 1 warning (0.04 sec)
  8. mysql> grant ADMIN_PRIV on *.* to doris@'%';
  9. Query OK, 0 rows affected, 1 warning (0.03 sec)
  12. 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.
  13. admin> insert into mysql_users(username,password,default_hostgroup) values('root','',10);
  14. Query OK, 1 row affected (0.001 sec)
  16. admin> insert into mysql_users(username,password,default_hostgroup) values('doris','P@ssword1!',10);
  17. Query OK, 1 row affected (0.000 sec)
  19. admin> load mysql users to runtime;
  20. Query OK, 0 rows affected (0.001 sec)
  22. admin> save mysql users to disk;
  23. Query OK, 0 rows affected (0.108 sec)
  25. The mysql_users table has many fields. The three main fields are username, password, and default_hostgroup:
  26.       -username: The username used by the front-end to connect to ProxySQL and ProxySQL to route SQL statements to MySQL.
  27.       -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.
  28.       -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
  29.     In this case, it will be routed to a node in the hostgroup_id=10 group.
  31. admin> select * from mysql_users\G
  32. *************************** 1. row ***************************
  33.               username: root
  34.               password: 
  35.                 active: 1
  36.                use_ssl: 0
  37.      default_hostgroup: 10
  38.         default_schema: NULL
  39.          schema_locked: 0
  40. transaction_persistent: 1
  41.           fast_forward: 0
  42.                backend: 1
  43.               frontend: 1
  44.        max_connections: 10000
  45. *************************** 2. row ***************************
  46.               username: doris
  47.               password: P@ssword1!
  48.                 active: 1
  49.                use_ssl: 0
  50.      default_hostgroup: 10
  51.         default_schema: NULL
  52.          schema_locked: 0
  53. transaction_persistent: 1
  54.           fast_forward: 0
  55.                backend: 1
  56.               frontend: 1
  57.        max_connections: 10000
  58. 2 rows in set (0.000 sec)
  60. 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.
  62. MySQL [(none)]> load mysql users to runtime;
  63. Query OK, 0 rows affected (0.001 sec)
  65. MySQL [(none)]> save mysql users to disk;
  66. Query OK, 0 rows affected (0.123 sec)
  68. In this way, you can use the doris username and password to connect to ProxySQL through the sql client

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!

  1. #mysql -uroot -p -P6033 -hdoris01 -e "show databases;"
  2. Enter password: 
  3. ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 10 after 10000ms
  4. At this time, an error was found, and it was not forwarded to the real doris fe on the backend.
  5. Through the log, you can see that there is set autocommit=0 to open the transaction
  6. Check the configuration found:
  8. mysql-forward_autocommit=false
  9. mysql-autocommit_false_is_transaction=false
  11. We don’t need to read and write separation here, just turn these two parameters into true directly through the following statement.
  12. mysql> UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-forward_autocommit';
  13. Query OK, 1 row affected (0.00 sec)
  15. mysql> UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-autocommit_false_is_transaction';
  16. Query OK, 1 row affected (0.01 sec)
  18. mysql>  LOAD MYSQL VARIABLES TO RUNTIME;
  19. Query OK, 0 rows affected (0.00 sec)
  21. mysql> SAVE MYSQL VARIABLES TO DISK;
  22. Query OK, 98 rows affected (0.12 sec)
  24. Then we try again and it shows success
  25. [root@doris01 ~]# mysql -udoris -pP@ssword1! -P6033 -h192.168.9.211  -e "show databases;"
  26. Warning: Using a password on the command line interface can be insecure.
  27. +--------------------+
  28. | Database           |
  29. +--------------------+
  30. | doris_audit_db     |
  31. | information_schema |
  32. | retail             |
  33. +--------------------+

OK, that’s the end, you can use Mysql client, JDBC, etc. to connect to ProxySQL to operate your doris.

Nginx TCP reverse proxy method

Overview

Nginx can implement load balancing of HTTP and HTTPS protocols, as well as load balancing of TCP protocol. So, the question is, can the load balancing of the Apache Doris database be achieved through Nginx? The answer is: yes. Next, let’s discuss how to use Nginx to achieve load balancing of Apache Doris.

Environmental preparation

Note: Using Nginx to achieve load balancing of Apache Doris database, the premise is to build an Apache Doris environment. The IP and port of Apache Doris FE are as follows. Here I use one FE to demonstrate, multiple FEs only You need to add multiple FE IP addresses and ports in the configuration

The Apache Doris and port to access MySQL through Nginx are shown below.

  1. IP: 172.31.7.119 
  2. 端口: 9030

Install dependencies

  1. sudo apt-get install build-essential
  2. sudo apt-get install libpcre3 libpcre3-dev 
  3. sudo apt-get install zlib1g-dev
  4. sudo apt-get install openssl libssl-dev

Install Nginx

  1. sudo wget http://nginx.org/download/nginx-1.18.0.tar.gz
  2. sudo tar zxvf nginx-1.18.0.tar.gz
  3. cd nginx-1.18.0
  4. sudo ./configure --prefix=/usr/local/nginx --with-stream --with-http_ssl_module --with-http_gzip_static_module --with-http_stub_status_module
  5. sudo make && make install

Configure reverse proxy

Here is a new configuration file

  1. vim /usr/local/nginx/conf/default.conf

Then add the following in it

  1. events {  
  2. worker_connections 1024;  
  3. }  
  4. stream {  
  5.   upstream mysqld {  
  6.       hash $remote_addr consistent;  
  7.       server 172.31.7.119:9030 weight=1 max_fails=2 fail_timeout=60s;  
  8.       ## Note: If there are multiple FEs, just load them here.  
  9.   }  
  10.   ### Configuration for proxy port, timeout, etc.  
  11.   server {  
  12.       listen 6030;  
  13.       proxy_connect_timeout 300s;  
  14.       proxy_timeout 300s;  
  15.       proxy_pass mysqld;  
  16.   }  
  17. }

Start Nginx

Start the specified configuration file

  1. cd /usr/local/nginx
  2. /usr/local/nginx/sbin/nginx -c conf.d/default.conf

verify

  1. mysql -uroot -P6030 -h172.31.7.119

Parameter explanation:

  • -u specifies the Doris username
  • -p specifies the Doris password, my password here is empty, so there is no
  • -h specifies the Nginx proxy server IP
  • -P specifies the port
  1. mysql -uroot -P6030 -h172.31.7.119
  2. Welcome to the MySQL monitor.  Commands end with ; or \g.
  3. Your MySQL connection id is 13
  4. Server version: 5.1.0 Doris version 0.15.1-rc09-Unknown
  6. Copyright (c) 2000, 2022, Oracle and/or its affiliates.
  8. Oracle is a registered trademark of Oracle Corporation and/or its
  9. affiliates. Other names may be trademarks of their respective
  10. owners.
  12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  14. mysql> show databases;
  15. +--------------------+
  16. | Database           |
  17. +--------------------+
  18. | information_schema |
  19. | test               |
  20. +--------------------+
  21. 2 rows in set (0.00 sec)
  23. mysql> use test;
  24. Reading table information for completion of table and column names
  25. You can turn off this feature to get a quicker startup with -A
  27. Database changed
  28. mysql> show tables;
  29. +------------------+
  30. | Tables_in_test   |
  31. +------------------+
  32. | dwd_product_live |
  33. +------------------+
  34. 1 row in set (0.00 sec)
  35. mysql> desc dwd_product_live;
  36. +-----------------+---------------+------+-------+---------+---------+
  37. | Field           | Type          | Null | Key   | Default | Extra   |
  38. +-----------------+---------------+------+-------+---------+---------+
  39. | dt              | DATE          | Yes  | true  | NULL    |         |
  40. | proId           | BIGINT        | Yes  | true  | NULL    |         |
  41. | authorId        | BIGINT        | Yes  | true  | NULL    |         |
  42. | roomId          | BIGINT        | Yes  | true  | NULL    |         |
  43. | proTitle        | VARCHAR(1024) | Yes  | false | NULL    | REPLACE |
  44. | proLogo         | VARCHAR(1024) | Yes  | false | NULL    | REPLACE |
  45. | shopId          | BIGINT        | Yes  | false | NULL    | REPLACE |
  46. | shopTitle       | VARCHAR(1024) | Yes  | false | NULL    | REPLACE |
  47. | profrom         | INT           | Yes  | false | NULL    | REPLACE |
  48. | proCategory     | BIGINT        | Yes  | false | NULL    | REPLACE |
  49. | proPrice        | DECIMAL(18,2) | Yes  | false | NULL    | REPLACE |
  50. | couponPrice     | DECIMAL(18,2) | Yes  | false | NULL    | REPLACE |
  51. | livePrice       | DECIMAL(18,2) | Yes  | false | NULL    | REPLACE |
  52. | volume          | BIGINT        | Yes  | false | NULL    | REPLACE |
  53. | addedTime       | BIGINT        | Yes  | false | NULL    | REPLACE |
  54. | offTimeUnix     | BIGINT        | Yes  | false | NULL    | REPLACE |
  55. | offTime         | BIGINT        | Yes  | false | NULL    | REPLACE |
  56. | createTime      | BIGINT        | Yes  | false | NULL    | REPLACE |
  57. | createTimeUnix  | BIGINT        | Yes  | false | NULL    | REPLACE |
  58. | amount          | DECIMAL(18,2) | Yes  | false | NULL    | REPLACE |
  59. | views           | BIGINT        | Yes  | false | NULL    | REPLACE |
  60. | commissionPrice | DECIMAL(18,2) | Yes  | false | NULL    | REPLACE |
  61. | proCostPrice    | DECIMAL(18,2) | Yes  | false | NULL    | REPLACE |
  62. | proCode         | VARCHAR(1024) | Yes  | false | NULL    | REPLACE |
  63. | proStatus       | INT           | Yes  | false | NULL    | REPLACE |
  64. | status          | INT           | Yes  | false | NULL    | REPLACE |
  65. | maxPrice        | DECIMAL(18,2) | Yes  | false | NULL    | REPLACE |
  66. | liveView        | BIGINT        | Yes  | false | NULL    | REPLACE |
  67. | firstCategory   | BIGINT        | Yes  | false | NULL    | REPLACE |
  68. | secondCategory  | BIGINT        | Yes  | false | NULL    | REPLACE |
  69. | thirdCategory   | BIGINT        | Yes  | false | NULL    | REPLACE |
  70. | fourCategory    | BIGINT        | Yes  | false | NULL    | REPLACE |
  71. | minPrice        | DECIMAL(18,2) | Yes  | false | NULL    | REPLACE |
  72. | liveVolume      | BIGINT        | Yes  | false | NULL    | REPLACE |
  73. | liveClick       | BIGINT        | Yes  | false | NULL    | REPLACE |
  74. | extensionId     | VARCHAR(128)  | Yes  | false | NULL    | REPLACE |
  75. | beginTime       | BIGINT        | Yes  | false | NULL    | REPLACE |
  76. | roomTitle       | TEXT          | Yes  | false | NULL    | REPLACE |
  77. | beginTimeUnix   | BIGINT        | Yes  | false | NULL    | REPLACE |
  78. | nickname        | TEXT          | Yes  | false | NULL    | REPLACE |
  79. +-----------------+---------------+------+-------+---------+---------+
  80. 40 rows in set (0.06 sec)

Haproxy way

HAProxy is a free and open source software written in C language that provides high availability, load balancing, and application proxy based on TCP and HTTP.

Install

  1. Download HAProxy

    download link: https://src.fedoraproject.org/repo/pkgs/haproxy/

  2. Unzip

    1. tar -zxvf haproxy-2.6.15.tar.gz -C /opt/
    2. mv haproxy-2.6.15 haproxy

  3. Compile

    Enter the haproxy directory

    1. yum install gcc gcc-c++ -y
    3. make TARGET=linux-glibc PREFIX=/usr/local/haproxy
    5. make install PREFIX=/usr/local/haproxy

Configuration

  1. Configure the haproxy.conf file

    vim /etc/rsyslog.d/haproxy.conf

    1. $ModLoad imudp 
    2. $UDPServerRun 514
    3. local0.* /usr/local/haproxy/logs/haproxy.log
    4. &~

  2. Enable remote logging

    vim /etc/sysconfig/rsyslog

    SYSLOGD_OPTIONS="-c 2 -r -m 0"

    Parameter analysis:

    • -c 2 Use compatibility mode, default is -c 5. -r turns on remote logging

    • -m 0 mark timestamp. The unit is minutes. When it is 0, it means the function is disabled.

  3. Make changes effective

    systemctl restart rsyslog

  4. Edit load balancing file

    vim /usr/local/haproxy/haproxy.cfg

    ``` #

    haproxy is deployed on 172.16.0.3, this machine, and is used to proxy 172.16.0.8, 172.16.0.6, 172.16.0.4, the three machines where fe is deployed.

    #

    global maxconn 2000 ulimit-n 40075 log 127.0.0.1 local0 info uid 200 gid 200 chroot /var/empty daemon group haproxy user haproxy

  1. defaults
  2. # Global log configuration
  3. log global
  4. mode http
  5. retries 3          # health examination. If the connection fails three times, the server is considered unavailable, mainly through the subsequent check.
  6. option redispatch  # Redirect to other healthy servers after service becomes unavailable
  7. # Timeout configuration
  8. timeout connect 5000
  9. timeout client 5000
  10. timeout server 5000
  11. timeout check 2000
  13. frontend agent-front
  14. bind *:9030             # Translation port on proxy machine
  15. mode tcp
  16. default_backend forward-fe
  18. backend forward-fe
  19. mode tcp
  20. balance roundrobin
  21. server fe-1 172.16.0.8:9030 weight 1 check inter 3000 rise 2 fall 3
  22. server fe-2 172.16.0.4:9030 weight 1 check inter 3000 rise 2 fall 3
  23. server fe-3 172.16.0.6:9030 weight 1 check inter 3000 rise 2 fall 3
  25. listen http_front              # haproxy client page
  26. bind *:8888                    # IP address of HAProxy WEB
  27. mode http
  28. log 127.0.0.1 local0 err
  29. option httplog
  30. stats uri /haproxy             # The url of the customized page (that is, the address when accessing is: 172.16.0.3:8888/haproxy)
  31. stats auth admin:admin         # Control panel account password Account: admin
  32. stats refresh 10s
  33. stats enable
  34. ```

Start up

  1. Start service

    /opt/haproxy/haproxy -f /usr/local/haproxy/haproxy.cfg

  2. Check service status

    netstat -lnatp | grep -i haproxy

  3. WEB access

    ip:8888/haproxy

    Login password: admin: admin

    Note: The WEB login port, account, and password need to be configured in the haproxy.cfg file

  4. Test whether the port conversion is successful

    mysql -h 172.16.0.3 -uroot -P3307 -p