## 模块 3: 自动暂停 DDL 任务 在某些场景下，例如版本升级、关闭数据库等，DDL 任务可以自动暂停并恢复。 ### 步骤 1. 用两个终端连接到 TiUP 控制机器 + 通过点击以下链接打开两个 SSH 连接到你的实验虚拟机： + [终端 1](ssh://monitor1-ssh#1) + [终端 2](ssh://monitor1-ssh#2) + 现在你将有两个活动的终端会话。 ### 步骤 2. 为表添加索引并修改列的属性 > [!TIP] > 请注意，本步骤的各个命令执行时间间隔请不要超过 1 分钟，因为可能无法获得预期的结果。 + 在[终端 1](ssh://monitor1-ssh#1) 上，连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + 在[终端 1](ssh://monitor1-ssh#1) 上，为表 `test.ddl_test2` 添加索引，并修改 `content` 列的属性。注意：不要等待该命令结束。 ```sql ALTER TABLE test.ddl_test2 DROP INDEX idx_content_updated_at; ``` ```output Query OK, 0 rows affected (0.16 sec) ``` ```sql ALTER TABLE test.ddl_test2 ADD INDEX idx_content_updated_at(content, updated_at);ALTER TABLE test.ddl_test2 MODIFY `content` VARCHAR(32) DEFAULT '0000000000' COMMENT 'content'; ``` + 在[终端 2](ssh://monitor1-ssh#2) 上，连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + 在[终端 2](ssh://monitor1-ssh#2) 上，通过命令 `ADMIN SHOW DDL JOBS` 检查刚刚开始执行的 DDL 任务的状态，例子中 `JOB_ID` 为 `180`，`STATE` 为 `running` 表示正在运行。请注意，你的输出可能和例子中不同。 ```sql ADMIN SHOW DDL JOBS; EXIT; ``` ```output +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME | START_TIME | END_TIME | STATE | COMMENTS | +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | 180 | test | ddl_test2 | modify column | write reorganization | 2 | 112 | 0 | 2025-07-27 06:34:35.931000 | 2025-07-27 06:34:35.931000 | NULL | running | | | 179 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 06:34:26.931000 | 2025-07-27 06:34:26.981000 | 2025-07-27 06:34:35.880000 | synced | ingest, DXF | | 178 | test | ddl_test2 | drop index | none | 2 | 112 | 0 | 2025-07-27 06:32:36.731000 | 2025-07-27 06:32:36.781000 | 2025-07-27 06:32:36.881000 | synced | | | 177 | test | ddl_test4 | modify column | public | 2 | 116 | 7250922 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:20:49.080000 | synced | | | 176 | test | ddl_test4 | add index | public | 2 | 116 | 4194304 | 2025-07-27 06:08:28.731000 | 2025-07-27 06:10:53.130000 | 2025-07-27 06:16:59.731000 | synced | ingest, DXF | | 175 | test | ddl_test3 | modify column | public | 2 | 114 | 4194304 | 2025-07-27 06:08:20.181000 | 2025-07-27 06:08:20.231000 | 2025-07-27 06:15:07.480000 | synced | | | 174 | test | ddl_test3 | add index | public | 2 | 114 | 4194304 | 2025-07-27 06:08:03.880000 | 2025-07-27 06:08:03.931000 | 2025-07-27 06:08:20.130000 | synced | ingest, DXF | | 173 | test | ddl_test2 | modify column | none | 2 | 112 | 0 | 2025-07-27 05:58:49.530000 | 2025-07-27 05:58:49.530000 | 2025-07-27 06:02:37.780000 | rollback done | | | 172 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 05:58:39.031000 | 2025-07-27 05:58:39.081000 | 2025-07-27 05:58:49.480000 | synced | ingest, DXF | | 171 | test | ddl_test1 | modify column | public | 2 | 110 | 4194304 | 2025-07-27 05:49:49.981000 | 2025-07-27 05:49:50.031000 | 2025-07-27 05:54:29.281000 | synced | | | 170 | test | ddl_test1 | add index | public | 2 | 110 | 4194304 | 2025-07-27 05:49:37.981000 | 2025-07-27 05:49:37.981000 | 2025-07-27 05:49:47.830000 | synced | ingest, DXF | +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ 11 rows in set (0.01 sec) ``` ### 步骤 3. 关闭与启动 TiDB 数据库 + 在[终端 2](ssh://monitor1-ssh#2) 上，关闭 TiDB 数据库。 ```bash export PATH="$PATH:/home/ec2-user/.tiup/bin" tiup cluster stop tidb-demo ``` ```output Will stop the cluster tidb-demo with nodes: , roles: . Do you want to continue? [y/N]:(default=N) y + [ Serial ] - SSHKeySet: privateKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-demo/ssh/id_rsa, publicKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-demo/ssh/id_rsa.pub + [Parallel] - UserSSH: user=ec2-user, host=10.90.1.181 + [Parallel] - UserSSH: user=ec2-user, host=10.90.1.46 ... Stop 10.90.2.169 success Stop 10.90.4.234 success Stopped cluster `tidb-demo` successfully ``` + 在[终端 1](ssh://monitor1-ssh#1) 上，我们发现客户端与数据库的连接中断了，但无法知道 DDL 任务是否执行完毕。 ```output Query OK, 0 rows affected (8.96 sec) ERROR 2013 (HY000): Lost connection to MySQL server during query ``` + 在[终端 2](ssh://monitor1-ssh#2) 上，开启 TiDB 数据库。 ```bash export PATH="$PATH:/home/ec2-user/.tiup/bin" tiup cluster start tidb-demo ``` ```output Starting cluster tidb-demo... + [ Serial ] - SSHKeySet: privateKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-demo/ssh/id_rsa, publicKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-demo/ssh/id_rsa.pub + [Parallel] - UserSSH: user=ec2-user, host=10.90.1.181 + [Parallel] - UserSSH: user=ec2-user, host=10.90.2.169 ... Start 10.90.2.119 success Start 10.90.4.234 success + [ Serial ] - UpdateTopology: cluster=tidb-demo Started cluster `tidb-demo` successfully ``` ### 步骤 4. 查询 DDL 任务 + 在[终端 2](ssh://monitor1-ssh#2) 上，连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + 在[终端 2](ssh://monitor1-ssh#2) 上，通过命令 `ADMIN SHOW DDL JOBS` 检查数据库重启前执行的 DDL 任务的状态，例子中 `JOB_ID` 为 `180`，`STATE` 为 `running` 表示正在运行。请注意，你的输出可能和例子中不同。 ```sql ADMIN SHOW DDL JOBS; ``` ```output +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME | START_TIME | END_TIME | STATE | COMMENTS | +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | 180 | test | ddl_test2 | modify column | write reorganization | 2 | 112 | 0 | 2025-07-27 06:34:35.931000 | 2025-07-27 06:34:35.931000 | NULL | running | | | 179 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 06:34:26.931000 | 2025-07-27 06:34:26.981000 | 2025-07-27 06:34:35.880000 | synced | ingest, DXF | | 178 | test | ddl_test2 | drop index | none | 2 | 112 | 0 | 2025-07-27 06:32:36.731000 | 2025-07-27 06:32:36.781000 | 2025-07-27 06:32:36.881000 | synced | | | 177 | test | ddl_test4 | modify column | public | 2 | 116 | 7250922 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:20:49.080000 | synced | | | 176 | test | ddl_test4 | add index | public | 2 | 116 | 4194304 | 2025-07-27 06:08:28.731000 | 2025-07-27 06:10:53.130000 | 2025-07-27 06:16:59.731000 | synced | ingest, DXF | | 175 | test | ddl_test3 | modify column | public | 2 | 114 | 4194304 | 2025-07-27 06:08:20.181000 | 2025-07-27 06:08:20.231000 | 2025-07-27 06:15:07.480000 | synced | | | 174 | test | ddl_test3 | add index | public | 2 | 114 | 4194304 | 2025-07-27 06:08:03.880000 | 2025-07-27 06:08:03.931000 | 2025-07-27 06:08:20.130000 | synced | ingest, DXF | | 173 | test | ddl_test2 | modify column | none | 2 | 112 | 0 | 2025-07-27 05:58:49.530000 | 2025-07-27 05:58:49.530000 | 2025-07-27 06:02:37.780000 | rollback done | | | 172 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 05:58:39.031000 | 2025-07-27 05:58:39.081000 | 2025-07-27 05:58:49.480000 | synced | ingest, DXF | | 171 | test | ddl_test1 | modify column | public | 2 | 110 | 4194304 | 2025-07-27 05:49:49.981000 | 2025-07-27 05:49:50.031000 | 2025-07-27 05:54:29.281000 | synced | | | 170 | test | ddl_test1 | add index | public | 2 | 110 | 4194304 | 2025-07-27 05:49:37.981000 | 2025-07-27 05:49:37.981000 | 2025-07-27 05:49:47.830000 | synced | ingest, DXF | +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ 11 rows in set (0.02 sec) ``` + 在[终端 2](ssh://monitor1-ssh#2) 上，请多次执行命令 `ADMIN SHOW DDL JOBS`，直到例子中 `JOB_ID` 为 `180` 的 DDL 任务的 `STATE` 为 `synced` 表示 DDL 任务执行完毕。请注意，你的输出结果可能和例子中不同。 ```sql ADMIN SHOW DDL JOBS; ``` ```output +--------+---------+------------+---------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME | START_TIME | END_TIME | STATE | COMMENTS | +--------+---------+------------+---------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | 180 | test | ddl_test2 | modify column | public | 2 | 112 | 4194304 | 2025-07-27 06:34:35.931000 | 2025-07-27 06:34:35.931000 | 2025-07-27 06:47:39.090000 | synced | | | 179 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 06:34:26.931000 | 2025-07-27 06:34:26.981000 | 2025-07-27 06:34:35.880000 | synced | ingest, DXF | | 178 | test | ddl_test2 | drop index | none | 2 | 112 | 0 | 2025-07-27 06:32:36.731000 | 2025-07-27 06:32:36.781000 | 2025-07-27 06:32:36.881000 | synced | | | 177 | test | ddl_test4 | modify column | public | 2 | 116 | 7250922 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:20:49.080000 | synced | | | 176 | test | ddl_test4 | add index | public | 2 | 116 | 4194304 | 2025-07-27 06:08:28.731000 | 2025-07-27 06:10:53.130000 | 2025-07-27 06:16:59.731000 | synced | ingest, DXF | | 175 | test | ddl_test3 | modify column | public | 2 | 114 | 4194304 | 2025-07-27 06:08:20.181000 | 2025-07-27 06:08:20.231000 | 2025-07-27 06:15:07.480000 | synced | | | 174 | test | ddl_test3 | add index | public | 2 | 114 | 4194304 | 2025-07-27 06:08:03.880000 | 2025-07-27 06:08:03.931000 | 2025-07-27 06:08:20.130000 | synced | ingest, DXF | | 173 | test | ddl_test2 | modify column | none | 2 | 112 | 0 | 2025-07-27 05:58:49.530000 | 2025-07-27 05:58:49.530000 | 2025-07-27 06:02:37.780000 | rollback done | | | 172 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 05:58:39.031000 | 2025-07-27 05:58:39.081000 | 2025-07-27 05:58:49.480000 | synced | ingest, DXF | | 171 | test | ddl_test1 | modify column | public | 2 | 110 | 4194304 | 2025-07-27 05:49:49.981000 | 2025-07-27 05:49:50.031000 | 2025-07-27 05:54:29.281000 | synced | | +--------+---------+------------+---------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ 10 rows in set (0.01 sec) ``` + 在[终端 1](ssh://monitor1-ssh#1) 上，检查 `test.ddl_test2` 的表定义，发现为 `test.ddl_test2` 添加索引和修改 `content` 列的属性的操作都成功了。 ```sql SHOW CREATE TABLE test.ddl_test2; ``` ```output *************************** 1. row *************************** Table: ddl_test2 Create Table: CREATE TABLE `ddl_test2` ( `id` bigint DEFAULT NULL, `content` varchar(32) DEFAULT '0000000000' COMMENT 'content', `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, `version` int DEFAULT NULL, KEY `idx_content_updated_at` (`content`,`updated_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.01 sec) ERROR: No query specified ``` + 本模块到此结束。