--- title: "TiDB Zero Downtime Online Upgrade with TiProxy" version: "v2" status: "GA" description: "This hands-on lab showcases TiDB's seamless online rolling upgrade capability, avoiding disruptions to client applications — a common issue with traditional database upgrades. TiDB's cloud-native, loosely coupled architecture allows component-by-component, instance-by-instance upgrades. TiProxy enhances this by decoupling client sessions from stateless TiDB servers. During TiDB server reboots for upgrades, TiProxy seamlessly migrates sessions to active instances, ensuring uninterrupted client operations throughout the upgrade process." --- # TiDB Zero Downtime Online Upgrade with TiProxy ## Overview This lab showcases TiDB's seamless online rolling upgrade capability, avoiding disruptions to client applications — a common issue with traditional database upgrades. TiDB's cloud-native, loosely coupled architecture allows component-by-component, instance-by-instance upgrades. TiProxy enhances this by decoupling client sessions from stateless TiDB servers. During TiDB server reboots for upgrades, TiProxy seamlessly migrates sessions to active instances, ensuring uninterrupted client operations throughout the upgrade process. ![80%](https://lab-static.pingcap.com/quick-demo/demo_005_en.png) ### Duration + This lab should take you approximately 60 minutes to complete. ### Tasks + Review the TiDB cluster status and topology. + Upgrade TiDB cluster to from version 7.5.4 to 8.1.1. + Observe and verify that the TiDB cluster is able to maintain functionality and accepts DML and query operations during the upgrade progress. > [!TIP] > Click **Start** to launch the lab environment. > Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**. > And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks. ## Module 1: Deploy A TiDB Cluster With TiProxy ### Step 1. Log in to the remote Linux + Click here - [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the virtual machine assigned for your hands-on lab. + The banner after you logged in: ```output , #_ ~\_ ####_ Amazon Linux 2 ~~ \_#####\ ~~ \###| ~~ \#/ ___ ~~ V~' '-> ~~~ / A newer version of Amazon Linux is available! ~~._. _/ _/ _/ Amazon Linux 2023, GA and supported until 2028-03-15. _/m/' https://aws.amazon.com/linux/amazon-linux-2023/ ``` ### Step 2. Check the prepared cluster topology YAML file ```bash cat eleven-nodes.yaml ``` ```output global: user: "ec2-user" ssh_port: 22 deploy_dir: "/tidb-deploy" data_dir: "/tidb-data" arch: "amd64" server_configs: pd: replication.max-replicas: 3 tidb: security.auto-tls: true graceful-wait-before-shutdown: 10 pd_servers: - host: 10.90.3.160 client_port: 2379 peer_port: 2380 advertise_client_addr: "http://35.92.30.186:2379" - host: 10.90.1.162 client_port: 2379 peer_port: 2380 advertise_client_addr: "http://52.34.128.150:2379" - host: 10.90.2.223 client_port: 2379 peer_port: 2380 advertise_client_addr: "http://54.245.183.8:2379" tidb_servers: - host: 10.90.1.157 port: 4000 status_port: 10080 deploy_dir: /tidb-deploy/tidb-4000 log_dir: /tidb-deploy/tidb-4000/log - host: 10.90.2.6 port: 4000 status_port: 10080 deploy_dir: /tidb-deploy/tidb-4000 log_dir: /tidb-deploy/tidb-4000/log tikv_servers: - host: 10.90.3.132 port: 20160 status_port: 20180 - host: 10.90.2.171 port: 20160 status_port: 20180 - host: 10.90.1.222 port: 20160 status_port: 20180 monitoring_servers: - host: 10.90.4.33 grafana_servers: - host: 10.90.4.33 alertmanager_servers: - host: 10.90.4.33 tiproxy_servers: - host: 10.90.1.153 ``` ### Step 3. Fix system settings to deploy TiDB cluster ```bash tiup mirror set tidb-community-server-7.5.4-linux-amd64 tiup cluster check eleven-nodes.yaml \ --user ec2-user \ -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \ --apply ``` ```output ... + Try to apply changes to fix failed checks - Applying changes on 10.90.1.162 ... ⠧ Sysctl: host=10.90.1.162 vm.swappiness = 0 - Applying changes on 10.90.2.223 ... ⠧ Sysctl: host=10.90.2.223 vm.swappiness = 0 - Applying changes on 10.90.3.132 ... ⠧ Sysctl: host=10.90.3.132 vm.swappiness = 0 + Try to apply changes to fix failed checks + Try to apply changes to fix failed checks - Applying changes on 10.90.1.162 ... Done - Applying changes on 10.90.2.223 ... Done + Try to apply changes to fix failed checks - Applying changes on 10.90.1.162 ... Done + Try to apply changes to fix failed checks + Try to apply changes to fix failed checks - Applying changes on 10.90.1.162 ... Done - Applying changes on 10.90.2.223 ... Done - Applying changes on 10.90.3.132 ... Done - Applying changes on 10.90.2.171 ... Done - Applying changes on 10.90.1.222 ... Done - Applying changes on 10.90.4.33 ... Done - Applying changes on 10.90.3.160 ... Done - Applying changes on 10.90.1.153 ... Done - Applying changes on 10.90.2.210 ... Done - Applying changes on 10.90.1.157 ... Done - Applying changes on 10.90.2.6 ... Done ``` ### Step 4. Deploy a TiDB cluster ```bash tiup cluster deploy tidb-demo 7.5.4 ./eleven-nodes.yaml \ --user ec2-user \ -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \ --yes ``` ```output ... Cluster `tidb-demo` deployed successfully, you can start it with command: `tiup cluster start tidb-demo --init` ``` ### Step 5. Start the deployed TiDB cluster ```bash tiup cluster start tidb-demo ``` ```output ... Started cluster `tidb-demo` successfully ``` ## Module 2: Apply Read Write Workloads to TiDB Cluster ### Step 1. Review the script of the INSERT workload + In [Terminal 2](ssh://monitor1-ssh#2), review the INSERT workload script. It contains a Java program capable of printing messages to the console in case of any JDBC exceptions. In the final output, you might notice messages such as `Work ? discards connection and try to get a new connection`. It's essential to note that during the demonstration, you shouldn't encounter these messages. TiProxy can migrate the database client sessions before the upgrade process shuts down a TiDB server instance, ensuring uninterrupted operation. ```bash cat scripts/quick-demo-005-jdbc-endless-insert-dummy.sh ``` ```output #!/bin/bash # Run ./quick-demo-005-jdbc-endless-insert-dummy.sh rm -f DemoJdbcEndlessInsertDummyCSPWithLBLongConn.class javac -cp .:misc/mysql-connector-java-5.1.36-bin.jar DemoJdbcEndlessInsertDummyCSPWithLBLongConn.java sleep 1 java -cp .:misc/mysql-connector-java-5.1.36-bin.jar DemoJdbcEndlessInsertDummyCSPWithLBLongConn ${1} ``` ```bash cat scripts/DemoJdbcEndlessInsertDummyCSPWithLBLongConn.java | grep -A10 catch | grep Work ``` ```output System.out.println("Work 1 discards connection and try to get a new connection"); System.out.println("Work 2 discards connection and try to get a new connection"); System.out.println("Work 3 discards connection and try to get a new connection"); System.out.println("Work 4 discards connection and try to get a new connection"); ``` ### Step 2. Run the script to apply the INSERT workload + Stay in [Terminal 2](ssh://monitor1-ssh#2), run the script to apply the sample INSERT workload with 4 workers. Keep the workload running and proceed to the next step. ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} -P 6000 -uroot << EOF DROP TABLE IF EXISTS test.dummy; CREATE TABLE test.dummy (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80), event JSON, tidb_instance VARCHAR(30)); EOF cd scripts ./quick-demo-005-jdbc-endless-insert-dummy.sh ${HOST_TIPROXY1_PRIVATE_IP} ``` ```output Worker 3 -> TiDB:Thu May 16 09:38:55 UTC 2024 at 10.90.1.153 Worker 4 -> TiDB:Thu May 16 09:38:56 UTC 2024 at 10.90.1.153 Worker 2 -> TiDB:Thu May 16 09:38:56 UTC 2024 at 10.90.1.153 Worker 1 -> TiDB:Thu May 16 09:38:56 UTC 2024 at 10.90.1.153 Worker 3 -> TiDB:Thu May 16 09:38:56 UTC 2024 at 10.90.1.153 Worker 4 -> TiDB:Thu May 16 09:38:57 UTC 2024 at 10.90.1.153 Worker 2 -> TiDB:Thu May 16 09:38:57 UTC 2024 at 10.90.1.153 Worker 1 -> TiDB:Thu May 16 09:38:57 UTC 2024 at 10.90.1.153 Worker 3 -> TiDB:Thu May 16 09:38:57 UTC 2024 at 10.90.1.153 Worker 4 -> TiDB:Thu May 16 09:38:58 UTC 2024 at 10.90.1.153 Worker 2 -> TiDB:Thu May 16 09:38:58 UTC 2024 at 10.90.1.153 ... ``` ### Step 3. Run the script to apply the QUERY workload + In [Terminal 3](ssh://monitor1-ssh#3), run the script responsible for the query workload. The script checks how many requests were handled by each TiDB server instance. Keep the workload running and proceed to the next step. ```bash qdb(){ mysql -h ${HOST_TIPROXY1_PRIVATE_IP} -P 6000 -uroot --connect-timeout 1 2>/dev/null << EOF SELECT tidb_instance, COUNT(event) FROM test.dummy GROUP BY tidb_instance ORDER BY tidb_instance; EOF } query(){ echo; date; qdb sleep 2; } while true; do query; done; ``` ```output Thu May 16 09:42:20 UTC 2024 tidb_instance COUNT(event) 10.90.1.124:10080 466 10.90.2.122:10080 466 Thu May 16 09:42:22 UTC 2024 tidb_instance COUNT(event) 10.90.1.124:10080 470 10.90.2.122:10080 470 Thu May 16 09:42:24 UTC 2024 tidb_instance COUNT(event) 10.90.1.124:10080 474 10.90.2.122:10080 474 ... ``` ## Module 3: Upgrade the TiDB Cluster While The Workloads Are Running ### Step 1. Set the TiUP mirror location + Go back to [Terminal 1](ssh://monitor1-ssh#1), run tiup command to change the TiUP local mirror to v8.1.1. ```bash tiup mirror set tidb-community-server-8.1.1-linux-amd64 ``` ```output Successfully set mirror to /home/ec2-user/tidb-community-server-8.1.1-linux-amd64 ``` ### Step 2. Upgrade your TiDB cluster + Run `tiup` command to upgrade your TiDB cluster from v7.5.4 to v8.1.1. While the upgrade is in-progress, switch to [Terminal 2](ssh://monitor1-ssh#2) and [Terminal 3](ssh://monitor1-ssh#3). ```bash tiup cluster upgrade tidb-demo 8.1.1 --yes ``` ```output Before the upgrade, it is recommended to read the upgrade guide at https://docs.tidb.io/tidb/stable/upgrade-tidb-using-tiup and finish the preparation steps. This operation will upgrade tidb v7.5.4 cluster tidb-demo to v8.1.1: will upgrade and restart component " pd" to "v8.1.1", will upgrade and restart component " tikv" to "v8.1.1", will upgrade and restart component " tidb" to "v8.1.1", ... + [ Serial ] - Download: component=grafana, version=v8.1.1, os=linux, arch=amd64 + [ Serial ] - Download: component=tikv, version=v8.1.1, os=linux, arch=amd64 + [ Serial ] - Download: component=tidb, version=v8.1.1, os=linux, arch=amd64 + [ Serial ] - Download: component=prometheus, version=v8.1.1, os=linux, arch=amd64 + [ Serial ] - Download: component=pd, version=v8.1.1, os=linux, arch=amd64 ... Upgrading component pd Restarting instance 10.90.1.135:2379 Restart instance 10.90.1.135:2379 success Restarting instance 10.90.2.182:2379 Restart instance 10.90.2.182:2379 success Restarting instance 10.90.3.231:2379 Restart instance 10.90.3.231:2379 success Upgrading component tikv Evicting 11 leaders from store 10.90.1.55:20160... Still waiting for 11 store leaders to transfer... Still waiting for 11 store leaders to transfer... ... Upgrading component tidb Restarting instance 10.90.1.124:4000 Restart instance 10.90.1.124:4000 success Restarting instance 10.90.2.122:4000 Restart instance 10.90.2.122:4000 success ... ``` ### Step 3. Verify that the INSERT and QUERY workloads are not interrupted + Observe [Terminal 2](ssh://monitor1-ssh#2) and [Terminal 3](ssh://monitor1-ssh#3), that the sample application connections and transactions have never been interrupted during the upgrade. + Wait until the upgrade command in [Terminal 1](ssh://monitor1-ssh#1) completes to proceed to the next step. ```output ... Upgraded cluster `tidb-demo` successfully ``` ### Step 4. Verify the upgrade result + Run the command to get the cluster status and verify your cluster version is v8.1.1. ```bash tiup cluster display tidb-demo ``` ```output Cluster type: tidb Cluster name: tidb-demo Cluster version: v8.1.1 Deploy user: ec2-user SSH type: builtin Dashboard URL: http://18.138.239.33:2379/dashboard Grafana URL: http://10.90.4.8:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.8:9093 alertmanager 10.90.4.8 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.8:3000 grafana 10.90.4.8 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.1.152:2379 pd 10.90.1.152 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.2.73:2379 pd 10.90.2.73 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.3.163:2379 pd 10.90.3.163 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.8:9090 prometheus 10.90.4.8 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.2.180:4000 tidb 10.90.2.180 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.3.89:4000 tidb 10.90.3.89 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.1.88:20160 tikv 10.90.1.88 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.2.20:20160 tikv 10.90.2.20 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.69:20160 tikv 10.90.3.69 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.1.60:6000 tiproxy 10.90.1.60 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 Total nodes: 12 ``` + Press Ctrl+C in [Terminal 2](ssh://monitor1-ssh#2) and [Terminal 3](ssh://monitor1-ssh#3) to stop both workloads. ## Conclusion Since TiDB servers handle SQL layer connections, performing online upgrades on them is the most challenging compared to upgrading TiKV and PD. This exercise proves that with TiProxy's help, such planned downtime can be circumvented. Before upgrading TiDB servers, they retrieve information from the Placement Driver to identify the server to be upgraded. Prior to this, TiProxy migrates all sessions from the affected server to other TiDB servers. Therefore, one node at a time - until all the TiDB servers are upgraded. Thus, the upgrade of the entire TiDB cluster has been completed without any disruption of client sessions. The database downtime and the application downtime was - zero. If you're using TiDB Cloud, all these capabilities, including TiProxy's session management, are readily available out of the box. In TiDB Cloud, you can request PingCAP to enable or disable TiProxy for your TiDB cluster. ```hbsptform { "ui": { "titleLevel": 3 }, "portalId": "4466002", "formId": "89fa8368-eba1-4b84-b91e-eb9eb6613ddf", "region": "na1", "sfdcCampaignId": "701RC00000VqCODYA3" } ```