## 模块 1：TiDB Server 扩容过程中的 TiProxy 服务发现 本模块展示 TiProxy 的服务发现能力。TiDB Server 执行扩容时，如果使用普通负载均衡器，则需要手动更新 TiDB Server 列表；而使用 TiProxy 时，无需人工干预，TiProxy 会自动调整 TiDB Server 列表。 下图为本实验环境中的 TiDB 拓扑。红色虚线标注的 TiDB Server 为即将扩容新增的实例。 ![demo018_splash_image_aa_003_20250926.png 85%](https://lab-static.pingcap.com/images/2025/9/26/359c3af54e50e857a606f12425b9849e2fcff4ba.png) ### 步骤 1. 连接到 TiUP 控制机 + 点击此处打开两个与实验虚机相连的新终端：[Terminal 1](ssh://monitor1-ssh#1) 与 [Terminal 2](ssh://monitor1-ssh#2)。 + 除非另有说明，后续步骤均在 [Terminal 1](ssh://monitor1-ssh#1) 中执行。 ### 步骤 2. 查看集群拓扑 + 当前，你应拥有 1 个 TiDB Server、3 个 TiKV Server 和 1 个 TiProxy。 + TiProxy 部署在监控服务所在的虚机上，该虚机同时充当本实验的控制机。此配置仅用于实验演示；在生产环境中，TiProxy 通常部署在独立节点上。 + TiProxy 使用端口 `6000`。 ```bash ~/.tiup/bin/tiup cluster display tidb-demo ``` ```output Cluster type: tidb Cluster name: tidb-demo Cluster version: v${LAB:TIDB_VERSION} Deploy user: ec2-user SSH type: builtin Dashboard URL: http://${LAB:HOST_PD1_PUBLIC_IP}:2379/dashboard Grafana URL: http://${LAB:HOST_MONITOR1_PRIVATE_IP}:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- ${LAB:HOST_MONITOR1_PRIVATE_IP}:9093 alertmanager ${LAB:HOST_MONITOR1_PRIVATE_IP} 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 ${LAB:HOST_MONITOR1_PRIVATE_IP}:3000 grafana ${LAB:HOST_MONITOR1_PRIVATE_IP} 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 ${LAB:HOST_PD1_PRIVATE_IP}:2379 pd ${LAB:HOST_PD1_PRIVATE_IP} 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 ${LAB:HOST_MONITOR1_PRIVATE_IP}:9090 prometheus ${LAB:HOST_MONITOR1_PRIVATE_IP} 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 ${LAB:HOST_DB1_PRIVATE_IP}:4000 tidb ${LAB:HOST_DB1_PRIVATE_IP} 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 ${LAB:HOST_KV1_PRIVATE_IP}:20160 tikv ${LAB:HOST_KV1_PRIVATE_IP} 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 ${LAB:HOST_KV2_PRIVATE_IP}:20160 tikv ${LAB:HOST_KV2_PRIVATE_IP} 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 ${LAB:HOST_KV3_PRIVATE_IP}:20160 tikv ${LAB:HOST_KV3_PRIVATE_IP} 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 ${LAB:HOST_MONITOR1_PRIVATE_IP}:6000 tiproxy ${LAB:HOST_MONITOR1_PRIVATE_IP} 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000 Total nodes: 9 ``` + 查看用于部署该 TiDB 集群的 `yaml` 文件。 + 在 `server_configs` 段落中，我们将 `graceful-wait-before-shutdown` 设置为 `15` 秒；关于该配置将在 模块2 进一步解释。 + `yaml` 文件底部展示了 TiProxy 的相关配置。 ```bash cat topology.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: 15 pd_servers: - host: ${LAB:HOST_PD1_PRIVATE_IP} client_port: 2379 peer_port: 2380 advertise_client_addr: "${LAB:HOST_PD1_PUBLIC_IP}:2379" tidb_servers: - host: ${LAB:HOST_DB1_PRIVATE_IP} port: 4000 status_port: 10080 deploy_dir: /tidb-deploy/tidb-4000 log_dir: /tidb-deploy/tidb-4000/log tikv_servers: - host: ${LAB:HOST_KV1_PRIVATE_IP} port: 20160 status_port: 20180 - host: ${LAB:HOST_KV2_PRIVATE_IP} port: 20160 status_port: 20180 - host: ${LAB:HOST_KV3_PRIVATE_IP} port: 20160 status_port: 20180 monitoring_servers: - host: ${LAB:HOST_MONITOR1_PRIVATE_IP} grafana_servers: - host: ${LAB:HOST_MONITOR1_PRIVATE_IP} alertmanager_servers: - host: ${LAB:HOST_MONITOR1_PRIVATE_IP} tiproxy_servers: - host: ${LAB:HOST_MONITOR1_PRIVATE_IP} ``` ### 步骤 3. 为负载准备表结构 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```sql USE test CREATE TABLE `users` ( `id` int, `name` varchar(100), `balance` int ); INSERT INTO users VALUES (1, 'Alice', 10); INSERT INTO users VALUES (2, 'Bob', 10); INSERT INTO users VALUES (3, 'Charlie', 10); INSERT INTO users VALUES (4, 'Doug', 10); INSERT INTO users VALUES (5, 'Edison', 10); INSERT INTO users VALUES (6, 'Frank', 10); INSERT INTO users VALUES (7, 'Grace', 10); INSERT INTO users VALUES (8, 'Hannah', 10); INSERT INTO users VALUES (9, 'Iris', 10); INSERT INTO users VALUES (10, 'Jack', 10); SELECT * FROM users; exit ``` ```output ... mysql> SELECT * FROM users; +------+---------+---------+ | id | name | balance | +------+---------+---------+ | 1 | Alice | 10 | | 2 | Bob | 10 | | 3 | Charlie | 10 | | 4 | Doug | 10 | | 5 | Edison | 10 | | 6 | Frank | 10 | | 7 | Grace | 10 | | 8 | Hannah | 10 | | 9 | Iris | 10 | | 10 | Jack | 10 | +------+---------+---------+ 10 rows in set (0.00 sec) ... ``` ### Step 4. Read the Python code ```bash cat demo_tiproxy_no_retry.py ``` + 在下面的代码片段中，主函数（位于代码底部）会启动 4 个线程。 ```output num_threads = 4 print(f"Starting {num_threads} database worker threads...") # Create and start threads threads = [] for i in range(num_threads): thread = threading.Thread( target=database_worker, args=(i + 1,), daemon=True ) threads.append(thread) thread.start() time.sleep(0.5) ``` * 下面的代码将每个线程连接到 TiProxy 的 `6000` 端口，随后由 TiProxy 路由到 TiDB Server。 * 与直连 TiDB 一样，您也可以在连接中包含用户名和密码。 ```output conn = connect( database="test", host="${LAB:HOST_MONITOR1_PRIVATE_IP}", port=6000, user="root", password="", ) cursor = conn.cursor(prepared=True) ``` + 下述代码会打印出线程所连接 TiDB Server 的实际 IP 与端口，便于观察 TiProxy 的路由行为。 ```output cursor.execute("SELECT @@hostname as hostname, @@port as port, CONNECTION_ID() as connection_id") server_info = cursor.fetchone() hostname, port, connection_id = server_info # Extract IP address from hostname (format: ip-10-90-2-124.ap-northeast-1.compute.internal) if hostname.startswith('ip-'): ip_parts = hostname.split('.')[0].split('-')[1:] # Split by '.' then by '-', skip 'ip' ip_address = '.'.join(ip_parts) else: ip_address = hostname ``` + 在以下代码中，每个线程会随机更新表中数据。 + 每个事务都会显式开启，并持续超过 4 秒（`time.sleep(4)`）。 ```output random_integer = random.randint(0, 10) random_id=random.randint(1, 10) query = "UPDATE test.users SET balance = " + str(random_integer) + " WHERE id = " + str(random_id) + ";" cursor.execute("BEGIN") cursor.execute(query) time.sleep(4) try: cursor.execute("COMMIT") print(f"Thread {thread_id}: Connected to TiDB server {ip_address}:{port}. User {random_id} balance updated to: {random_integer}") except Error as e: print(f"Thread {thread_id}: Connected to TiDB server {ip_address}:{port}. Error during query execution: {e}") ... ``` ### 步骤 5. 运行负载 + 此时可以看到，所有线程均连接到同一台 TiDB Server，其 IP 为 `${LAB:HOST_DB1_PRIVATE_IP}`，端口为 `4000`。 ```bash python3 demo_tiproxy_no_retry.py ``` ```output ... Thread 1: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 6 balance updated to: 4 Thread 2: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 3 balance updated to: 3 Thread 4: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 8 balance updated to: 3 Thread 1: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 4 balance updated to: 5 Thread 3: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 8 balance updated to: 7 ... ``` ### 步骤 6. 扩容一台 TiDB Server + 在 [Terminal 2](ssh://monitor1-ssh#2) 中，查看用于扩容的 yaml 文件。 ```bash cat scale-out-tidb-2.yaml ``` ```output tidb_servers: - host: ${LAB:HOST_DB2_PRIVATE_IP} port: 4000 status_port: 10080 deploy_dir: /tidb-deploy/tidb-4000 log_dir: /tidb-deploy/tidb-4000/log ``` + 在 [Terminal 2](ssh://monitor1-ssh#2) 中执行扩容一台 TiDB Server。 ```bash ~/.tiup/bin/tiup cluster scale-out tidb-demo scale-out-tidb-2.yaml \ --user ec2-user \ -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \ -y ``` ```output ... Scaled cluster `tidb-demo` in successfully ``` + 等待数分钟后，回到 [Terminal 1](ssh://monitor1-ssh#1)。此时应可看到部分线程已连接到新增的 TiDB Server。整个过程无需人工干预。 ```output ... Thread 3: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 6 balance updated to: 9 Thread 4: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 1 balance updated to: 9 Thread 2: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 9 balance updated to: 5 Thread 1: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 3 balance updated to: 2 Thread 4: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 1 balance updated to: 6 Thread 3: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 9 balance updated to: 8 Thread 1: Connected to TiDB server ${LAB:HOST_DB1_PRIVATE_IP}:4000. User 10 balance updated to: 5 Thread 2: Connected to TiDB server ${LAB:HOST_DB2_PRIVATE_IP}:4000. User 1 balance updated to: 3 ... ``` > [!TIP] > TiProxy 将连接均衡路由到两台 TiDB Server 需要数分钟，请耐心等待。 + 请不要停止 [Terminal 1](ssh://monitor1-ssh#1) 中的负载，让其保持运行。 + 现在请继续进入模块 2。 ### 小结 + 本实验展示了 TiProxy 能够发现新增的 TiDB SQL 层服务，并将负载均衡分配到多台 TiDB Server 上。