## Module 1: The Load Balancer And Session Manager - TiProxy

TiProxy is the official proxy component for TiDB, designed to enhance the database's functionality by providing load balancing, connection persistence, and service discovery. It acts as an intermediary between the client and the TiDB server, ensuring that client connections remain stable and uninterrupted during various maintenance operations such as scaling, rolling upgrades, and restarts. This is particularly useful in scenarios where maintaining continuous client connectivity is crucial, such as during planned server downtime or when balancing workloads across multiple TiDB server instances.

In this module, you will deploy a new TiDB cluster along with the TiProxy component and validate its load balancing and session migration functionalities.

### Step 1. Connect to the TiUP control machine

+ Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab.

+ Get an overview of all server instances IP addresses that had been assigned to you for this hands-on lab. Environment variables prefixes stand for their roles in the TiDB cluster respectively. Note that the IP addresses shown in the `sample_output` will differ from yours.

  + `HOST_MONITOR` will host Prometheus and Grafana.
  + `HOST_CM` will host the TiUP Control Machine.
  + `HOST_PD` for Placement Driver server.
  + `HOST_DB` for TiDB server.
  + `HOST_KV` for TiKV server.
  + `HOST_TIFALSH` for TiFlash server.
  + `HOST_TIPROXY` for TiProxy server.

  ```bash
  cat hosts-env.sh
  ```

  ```output
  export HOST_MONITOR1_PRIVATE_IP=10.90.4.40
  export HOST_MONITOR1_PUBLIC_IP=35.166.47.245
  export HOST_CM_PRIVATE_IP=10.90.4.40
  export HOST_CM_PUBLIC_IP=35.166.47.245
  export HOST_PD1_PRIVATE_IP=10.90.2.51
  export HOST_PD1_PUBLIC_IP=52.27.45.10
  export HOST_DB1_PRIVATE_IP=10.90.2.14
  export HOST_DB1_PUBLIC_IP=44.244.238.105
  export HOST_DB2_PRIVATE_IP=10.90.2.43
  export HOST_DB2_PUBLIC_IP=34.219.136.55
  export HOST_KV1_PRIVATE_IP=10.90.1.93
  export HOST_KV1_PUBLIC_IP=52.12.133.66
  export HOST_KV2_PRIVATE_IP=10.90.1.30
  export HOST_KV2_PUBLIC_IP=18.236.119.16
  export HOST_KV3_PRIVATE_IP=10.90.1.208
  export HOST_KV3_PUBLIC_IP=35.90.161.51
  export HOST_TIFLASH1_PRIVATE_IP=10.90.4.243
  export HOST_TIFLASH1_PUBLIC_IP=34.210.114.26
  export HOST_TIPROXY1_PRIVATE_IP=10.90.4.154
  ```

### Step 2. Create a new TiDB cluster equipped with TiProxy

+ Check the provided topology file `topology-with-tiproxy.yaml`, which is used to deploy the TiDB cluster. Pay attention to the TiProxy settings (`security.auto-tls: true`, `graceful-wait-before-shutdown: 10` under TiDB server configurations, and `tiproxy_servers`) in the topology file. Note that the IP addresses shown in the `sample_output` will differ from yours.

  ```bash
  cat topology-with-tiproxy.yaml
  ```

  ```output
  ...
  server_configs:
  ...
    tidb:
      security.auto-tls: true
      graceful-wait-before-shutdown: 10
  ...
  tiproxy_servers:
    - host: 10.90.4.113
  ```

+ The commands in the following code block will execute a series of operations in sequence. Which are: 1. Change [TiUP mirror](https://docs.pingcap.com/tidb/stable/production-deployment-using-tiup) setting, 2. Check system settings, 3. Fix the failed checks, 4. Deploy the TiDB cluster, and finally 5. Start the TiDB cluster. It takes approximately 3 minutes to complete.

  ```bash
  export PATH="$PATH:/home/ec2-user/.tiup/bin"
  tiup mirror set tidb-community-server-${LAB:TIDB_VERSION}-linux-amd64
  tiup cluster check ./topology-with-tiproxy.yaml \
   --user ec2-user \
   -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \
   --apply
  tiup cluster deploy tidb-demo v${LAB:TIDB_VERSION} ./topology-with-tiproxy.yaml \
   --user ec2-user \
   -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \
   --yes
  tiup cluster start tidb-demo
  sleep 5;
  tiup cluster display tidb-demo
  ```

  ```output
  Successfully set mirror to /home/ec2-user/tidb-community-server-v${LAB:TIDB_VERSION}-linux-amd64
  /home/ec2-user/tidb-community-server-v${LAB:TIDB_VERSION}-linux-amd64
  ...
  Cluster type:       tidb
  Cluster name:       tidb-demo
  Cluster version:    v${LAB:TIDB_VERSION}
  Deploy user:        ec2-user
  SSH type:           builtin
  Dashboard URL:      http://52.27.45.10:2379/dashboard
  Grafana URL:        http://10.90.4.40:3000
  ID                 Role          Host         Ports                            OS/Arch       Status  Data Dir                      Deploy Dir
  --                 ----          ----         -----                            -------       ------  --------                      ----------
  10.90.4.40:9093    alertmanager  10.90.4.40   9093/9094                        linux/x86_64  Up      /tidb-data/alertmanager-9093  /tidb-deploy/alertmanager-9093
  10.90.4.40:3000    grafana       10.90.4.40   3000                             linux/x86_64  Up      -                             /tidb-deploy/grafana-3000
  10.90.2.51:2379    pd            10.90.2.51   2379/2380                        linux/x86_64  Up|L    /tidb-data/pd-2379            /tidb-deploy/pd-2379
  10.90.4.40:9090    prometheus    10.90.4.40   9090/12020                       linux/x86_64  Up      /tidb-data/prometheus-9090    /tidb-deploy/prometheus-9090
  10.90.2.14:4000    tidb          10.90.2.14   4000/10080                       linux/x86_64  Up      -                             /tidb-deploy/tidb-4000
  10.90.2.43:4000    tidb          10.90.2.43   4000/10080                       linux/x86_64  Up      -                             /tidb-deploy/tidb-4000
  10.90.4.243:9000   tiflash       10.90.4.243  9000/3930/20170/20292/8234/8123  linux/x86_64  Down    /tidb-data/tiflash-9000       /tidb-deploy/tiflash-9000
  10.90.1.208:20160  tikv          10.90.1.208  20160/20180                      linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
  10.90.1.30:20160   tikv          10.90.1.30   20160/20180                      linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
  10.90.1.93:20160   tikv          10.90.1.93   20160/20180                      linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
  10.90.4.154:6000   tiproxy       10.90.4.154  6000/3080                        linux/x86_64  Up      -                             /tidb-deploy/tiproxy-6000
  Total nodes: 11
  ```

+ Note that the `tiproxy` component is also listed in the cluster display output. The Self-Managed cluster consists of one TiProxy instance and two TiDB server instances. Additionally, although we typically use at least three PD servers, only one PD server is used here, as this hands-on lab does not cover PD-related topics.

### Step 3. Verify the load balancing functionality

+ By default, TiProxy instances listen on port 6000 instead of port 4000. Let's connect to TiDB server instance via TiProxy.

  ```bash
  mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root
  ```

  ```output
  mysql>
  ```

+ Create a table named `test.tiproxy_test` and exit the connection. we will use it prove TiProxy's connection load balancing ability.

  ```sql
  DROP TABLE IF EXISTS test.tiproxy_test;
  CREATE TABLE test.tiproxy_test (id INT AUTO_INCREMENT PRIMARY KEY, tidb_server_instance VARCHAR(30));
  EXIT
  ```

  ```output
  Query OK, 0 rows affected, 1 warning (0.00 sec)
  Query OK, 0 rows affected (0.06 sec)
  Bye
  ```

+ Generate 50 database connections via TiProxy, in each connection, it will insert a new row, and then close the connection. Note that the `tidb_server_instance` column in `test.tiproxy_test` table will reveal the TiDB server instance's identity (in the format of **TIDB_SERVER_IP_ADDRESS:10080**) that had accepted the connection and processed the insertion DML.

  ```bash
  cat > insert_one_row.sql <<EOF
  INSERT INTO test.tiproxy_test (tidb_server_instance)
  VALUES (
      (SELECT instance FROM information_schema.cluster_processlist
       WHERE host=(SELECT host FROM information_schema.processlist WHERE id=CONNECTION_ID())
      )
  );
  EOF
  cat > insert_one_row.sh <<EOF
  mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root < insert_one_row.sql
  EOF
  chmod +x insert_one_row.sh
  for c in {1..50}; do
  ./insert_one_row.sh &
  done;
  sleep 1;
  jobs
  ```

  ```output
  ...
  [43]   Done                    ./insert_one_row.sh
  [44]   Done                    ./insert_one_row.sh
  [45]   Done                    ./insert_one_row.sh
  [46]   Done                    ./insert_one_row.sh
  [47]   Done                    ./insert_one_row.sh
  [48]   Done                    ./insert_one_row.sh
  [49]-  Done                    ./insert_one_row.sh
  [50]+  Done                    ./insert_one_row.sh
  ```

+ Connect to TiDB server instance.

  ```bash
  mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root
  ```

+ Check the `test.tiproxy_test` table, you will find that the connections and the insertion operations had been evenly handled by two TiDB server instances. Note that if you notice a slight discrepancy in the connection count, such as 28 vs 22, this is normal and expected.

  ```sql
  SELECT tidb_server_instance, COUNT(1)
  FROM test.tiproxy_test
  GROUP BY tidb_server_instance;
  EXIT
  ```

  ```output
  +----------------------+----------+
  | tidb_server_instance | count(1) |
  +----------------------+----------+
  | 10.90.1.14:10080     |       25 |
  | 10.90.1.43:10080     |       25 |
  +----------------------+----------+
  2 rows in set (0.00 sec)
  Bye
  ```

### Step 4. Verify the session migration functionality

+ During planned maintenance operations, such as TiDB scale-out, scale-in, or cluster upgrades, TiProxy ensures uninterrupted connectivity for your client application by seamlessly migrating existing sessions across active TiDB server instances. The primary goal of TiProxy is to provide zero downtime for your application while maintaining a balanced load across TiDB servers.

+ Review the code of a provided mini Java program, it will initialize 4 threads to run the insertion workloads against table `test.tiproxy_test`.

  ```bash
  cat InsertJobNoExceptionHandling.java | grep -A1 -B1 'new Thread'
  ```

  ```output
          for (int i = 0; i < 4; i++) {
              new Thread(new Worker(tidbHost, port, dbUsername, dbPassword, (i + 1))).start();
          }
  ```

+ In case the thread encounters any exception, it will print the error stack and exit.

  ```bash
  cat InsertJobNoExceptionHandling.java | grep -A3 catch
  ```

  ```output
          } catch (Exception e) {
              e.printStackTrace();
              System.exit(1);
          }
  ```

+ Start the workload against TiProxy at port 6000. Note that it will connect to the TiDB cluster using 4 threads, resulting in 4 database connections.

  ```bash
  rm -f InsertJobNoExceptionHandling.class
  javac -cp .:mysql-connector-java-5.1.36-bin.jar InsertJobNoExceptionHandling.java
  sleep 1
  java -cp .:mysql-connector-java-5.1.36-bin.jar InsertJobNoExceptionHandling ${HOST_TIPROXY1_PRIVATE_IP} 6000 root
  ```

  ```output
  Inserting 0 by worker 3
  Inserting 0 by worker 1
  Inserting 0 by worker 4
  Inserting 0 by worker 2
  Inserting 1 by worker 1
  Inserting 1 by worker 2
  Inserting 1 by worker 3
  Inserting 1 by worker 4
  Inserting 2 by worker 1
  Inserting 2 by worker 3
  Inserting 2 by worker 2
  Inserting 2 by worker 4
  ...
  ```

+ Click on the [Grafana Dashboard](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000) and log in using the username and password (both are "admin"). Navigate to `Home` > `tidb-demo` > `tidb-demo-TiProxy-Summary` > `Balance`. Observe the `Backend Connections` panel, each TiDB server should has 2 connections.

    ![90%](https://lab-static.pingcap.com/quick-demo/TiProxyBalance_2x2.png)

+ Change the `Refresh time` to `10s`. Keep the `Balance` panel open in the browser, and keep the Java program running.

+ Click here - [Terminal 2](ssh://monitor1-ssh#2) to open a new terminal connected to the virtual machine assigned for your hands-on lab.

+ Scale in the SQL layer by removing the TiDB server with the IP address ${HOST_DB2_PRIVATE_IP} from the cluster. Once completed, there is only 1 TiDB server in the cluster.

  ```bash
  export PATH="$PATH:/home/ec2-user/.tiup/bin"
  tiup cluster scale-in tidb-demo --node ${HOST_DB2_PRIVATE_IP}:4000 -y
  sleep 2
  tiup cluster display tidb-demo
  ```

  ```output
  ...
  Stopping component tidb
  ...
  Scaled cluster `tidb-demo` in successfully
  ...
  Cluster type:       tidb
  Cluster name:       tidb-demo
  Cluster version:    v${LAB:TIDB_VERSION}
  Deploy user:        ec2-user
  SSH type:           builtin
  Dashboard URL:      http://13.115.186.238:2379/dashboard
  Grafana URL:        http://10.90.4.53:3000
  ID                 Role          Host         Ports                            OS/Arch       Status  Data Dir                      Deploy Dir
  --                 ----          ----         -----                            -------       ------  --------                      ----------
  10.90.4.53:9093    alertmanager  10.90.4.53   9093/9094                        linux/x86_64  Up      /tidb-data/alertmanager-9093  /tidb-deploy/alertmanager-9093
  10.90.4.53:3000    grafana       10.90.4.53   3000                             linux/x86_64  Down    -                             /tidb-deploy/grafana-3000
  10.90.3.19:2379    pd            10.90.3.19   2379/2380                        linux/x86_64  Up|L    /tidb-data/pd-2379            /tidb-deploy/pd-2379
  10.90.4.53:9090    prometheus    10.90.4.53   9090/12020                       linux/x86_64  Up      /tidb-data/prometheus-9090    /tidb-deploy/prometheus-9090
  10.90.1.202:4000   tidb          10.90.1.202  4000/10080                       linux/x86_64  Up      -                             /tidb-deploy/tidb-4000
  10.90.4.75:9000    tiflash       10.90.4.75   9000/3930/20170/20292/8234/8123  linux/x86_64  Up      /tidb-data/tiflash-9000       /tidb-deploy/tiflash-9000
  10.90.3.10:20160   tikv          10.90.3.10   20160/20180                      linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
  10.90.3.207:20160  tikv          10.90.3.207  20160/20180                      linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
  10.90.3.208:20160  tikv          10.90.3.208  20160/20180                      linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
  10.90.4.56:6000    tiproxy       10.90.4.56   6000/3080                        linux/x86_64  Up      -                             /tidb-deploy/tiproxy-6000
  Total nodes: 10
  ```

+ On [Terminal 1](ssh://monitor1-ssh#1) , the mini Java program continues running without interruption.

  ```output
  ...
  Inserting 1295 by worker 4
  Inserting 1295 by worker 3
  Inserting 1295 by worker 1
  Inserting 1295 by worker 2
  Inserting 1296 by worker 4
  Inserting 1296 by worker 3
  ...
  ```

+ Check the Grafana TiProxy `Balance` panel from your browser. 4 connections are now on single TiDB server. 2 connections had been migrated.

    ![90%](https://lab-static.pingcap.com/quick-demo/TiProxyBalance_4x0.png)

+ Switch to [Terminal 2](ssh://monitor1-ssh#2) , scale out the SQL layer by adding a TiDB server to the cluster. Once completed, there are 2 TiDB servers in the cluster.

  ```bash
  export PATH="$PATH:/home/ec2-user/.tiup/bin"
  tiup cluster scale-out tidb-demo scale-out-tidb.yaml \
   --user ec2-user \
   -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \
   -y
  tiup cluster display tidb-demo
  ```

  ```output
  ...
  Deploy TiDB instance
  ...
  Scaled cluster `tidb-demo` out successfully
  ...
  Cluster type:       tidb
  Cluster name:       tidb-demo
  Cluster version:    v${LAB:TIDB_VERSION}
  Deploy user:        ec2-user
  SSH type:           builtin
  Dashboard URL:      http://13.115.186.238:2379/dashboard
  Grafana URL:        http://10.90.4.53:3000
  ID                 Role          Host         Ports                            OS/Arch       Status  Data Dir                      Deploy Dir
  --                 ----          ----         -----                            -------       ------  --------                      ----------
  10.90.4.53:9093    alertmanager  10.90.4.53   9093/9094                        linux/x86_64  Up      /tidb-data/alertmanager-9093  /tidb-deploy/alertmanager-9093
  10.90.4.53:3000    grafana       10.90.4.53   3000                             linux/x86_64  Up      -                             /tidb-deploy/grafana-3000
  10.90.3.19:2379    pd            10.90.3.19   2379/2380                        linux/x86_64  Up|L    /tidb-data/pd-2379            /tidb-deploy/pd-2379
  10.90.4.53:9090    prometheus    10.90.4.53   9090/12020                       linux/x86_64  Up      /tidb-data/prometheus-9090    /tidb-deploy/prometheus-9090
  10.90.1.202:4000   tidb          10.90.1.202  4000/10080                       linux/x86_64  Up      -                             /tidb-deploy/tidb-4000
  10.90.1.233:4000   tidb          10.90.1.233  4000/10080                       linux/x86_64  Up      -                             /tidb-deploy/tidb-4000
  10.90.4.75:9000    tiflash       10.90.4.75   9000/3930/20170/20292/8234/8123  linux/x86_64  Up      /tidb-data/tiflash-9000       /tidb-deploy/tiflash-9000
  10.90.3.10:20160   tikv          10.90.3.10   20160/20180                      linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
  10.90.3.207:20160  tikv          10.90.3.207  20160/20180                      linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
  10.90.3.208:20160  tikv          10.90.3.208  20160/20180                      linux/x86_64  Up      /tidb-data/tikv-20160         /tidb-deploy/tikv-20160
  10.90.4.56:6000    tiproxy       10.90.4.56   6000/3080                        linux/x86_64  Up      -                             /tidb-deploy/tiproxy-6000
  Total nodes: 11
  ```

+ Return to [Terminal 1](ssh://monitor1-ssh#1)  again, you will find that the mini Java program continues running without interruption.

  ```output
  ...
  Inserting 1707 by worker 1
  Inserting 1707 by worker 3
  Inserting 1706 by worker 4
  Inserting 1708 by worker 2
  Inserting 1708 by worker 1
  Inserting 1708 by worker 3
  Inserting 1707 by worker 4
  ...
  ```

+ Again, check the Grafana TiProxy `Balance` panel from your browser. Each TiDB server has 2 connections. 2 connections had been migrated.

    ![90%](https://lab-static.pingcap.com/quick-demo/TiProxyBalance_2x2_2.png)

+ That concludes the session/connection migration ability of TiProxy. For supporting the planned database online rolling upgrade by using TiProxy, we have another standalone hands-on lab to demonstrate this capability please visit [Database Zero Downtime Online Upgrade with TiProxy](/labs/demo_005) for more detail.

+ Go to [Terminal 1](ssh://monitor1-ssh#1) , press **CTRL-C** to stop the Java workload. Then, proceed to the next module.