## 模块 2: 使用 ADMIN PAUSE DDL 和 ADMIN RESUME DDL 某些 DDL 操作将会持续执行很长时间，同时伴有大量的 CPU 与 IO 负载，例如，为大表增加索引和修改列属性操作。使用 ADMIN PAUSE/RESUME DDL 可以帮助用户暂停或恢复正在执行的 DDL 任务，从而防止其长时间执行影响数据库的工作负载和其他维护工作。 ### 步骤 1. 用两个终端连接到 TiUP 控制机器 + 通过点击以下链接打开两个 SSH 连接到你的实验虚拟机： + [终端 1](ssh://monitor1-ssh#1) + [终端 2](ssh://monitor1-ssh#2) + 现在你将有两个活动的终端会话。 ### 步骤 2. 为表添加索引并修改列的属性 + 登录 [Grafana 监控](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000)，建议保留本步骤中打开的监控页面，并设置监控时间范围为最近 5 分钟，同时将刷新频率改为 5 秒。 ![70%](https://lab-static.pingcap.com/images/2025/7/26/9c787410ab425dd7eb7e7297cc46711ec63ccc22.png) + 了解 DDL 任务执行时 TiKV 的 CPU 和 IO 负载，登录 [Grafana 监控](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000)，进入`Home`，然后选择`tidb-demo`，接着选择`tidb-demo-TiKV-Details`， 打开 `Cluster` 标签， 在 `Cluster` 标签下可以看到 `CPU` 面板中有各个 TiKV 节点的 CPU 使用率情况， 在 `IO utillzation` 面板中有各个节点的 IO 使用率情况。 ![70%](https://lab-static.pingcap.com/images/2025/7/26/4731b3d2183b8d351eae72b449cb547bf23f52ec.png) + 在[终端 1](ssh://monitor1-ssh#1) 上，连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` > [!TIP] > 请注意，`步骤 2` 和 `步骤 3` 中各个命令的时间间隔请不要超过 1 分钟，因为可能无法获得预期的结果。 + 为表 `test.ddl_test1` 添加索引，并修改 `content` 列的属性。注意：不要等待该命令结束。 ```sql ALTER TABLE test.ddl_test1 ADD INDEX idx_content_updated_at(content, updated_at);ALTER TABLE test.ddl_test1 MODIFY `content` VARCHAR(32) DEFAULT '0000000000' COMMENT 'content'; ``` + 回到 [Grafana 监控](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000)，在 `tidb-demo-TiKV-Details` 的 `Cluster` 标签，可以看到各个 TiKV 节点的 CPU 负载 `CPU` 和 IO 使用率 `IO utillzation` 开始上升。 ![70%](https://lab-static.pingcap.com/images/2025/7/26/b5aaf6accffbf68dabd122ece7f53e193a16418d.png) + 在此实验中，为表 `test.ddl_test1` 修改 `content` 列的属性操作大概会持续 3 分钟，所以请在 3 分钟内开始 `步骤 3` ，否则可能无法得到正确结果。 ### 步骤 3. 使用 `ADMIN PAUSE DDL` 暂停 DDL 任务 + 在[终端 2](ssh://monitor1-ssh#2) 上，连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + 通过命令 `ADMIN SHOW DDL JOBS` 找到上一步骤执行的 DDL 任务的 `JOB_ID`，例子中 `JOB_ID` 为 `119`，`DB_NAME` 为 `test`， `TABLE_NAME` 为 `ddl_test1`， `JOB_TYPE` 为 `modify column`， `SCHEMA_STATE STATE` 为 `write reorganization`， `STATE` 为 `running` 表示该 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 | +--------+---------+-----------------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------+-------------+ | 119 | test | ddl_test1 | modify column | write reorganization | 2 | 110 | 0 | 2025-07-26 04:01:29.723000 | 2025-07-26 04:01:29.772000 | NULL | running | | | 118 | test | ddl_test1 | add index | public | 2 | 110 | 4194304 | 2025-07-26 04:01:10.173000 | 2025-07-26 04:01:10.222000 | 2025-07-26 04:01:21.222000 | synced | ingest, DXF | | 117 | test | ddl_test4 | create table | public | 2 | 116 | 0 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.473000 | synced | | | 115 | test | ddl_test3 | create table | public | 2 | 114 | 0 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.423000 | 2025-07-26 03:28:35.423000 | synced | | | 113 | test | ddl_test2 | create table | public | 2 | 112 | 0 | 2025-07-26 03:28:35.322000 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.373000 | synced | | | 111 | test | ddl_test1 | create table | public | 2 | 110 | 0 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.322000 | synced | | | 109 | mysql | tidb_kernel_options | create table | public | 1 | 108 | 0 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | synced | | | 107 | mysql | index_advisor_results | create table | public | 1 | 106 | 0 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.823000 | 2025-07-26 03:22:35.823000 | synced | | | 105 | sys | schema_unused_indexes | create view | public | 102 | 104 | 0 | 2025-07-26 03:22:35.722000 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.772000 | synced | | | 103 | sys | | create schema | public | 102 | 0 | 0 | 2025-07-26 03:22:35.673000 | 2025-07-26 03:22:35.673000 | 2025-07-26 03:22:35.722000 | synced | | | 101 | mysql | tidb_pitr_id_map | create table | public | 1 | 100 | 0 | 2025-07-26 03:22:35.622000 | 2025-07-26 03:22:35.622000 | 2025-07-26 03:22:35.673000 | synced | | +--------+---------+-----------------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------+-------------+ 11 rows in set (0.01 sec) ``` + 通过命令 `ADMIN PAUSE DDL JOBS` 和 DDL 任务的 `JOB_ID` 暂停上一步骤中执行的 DDL 任务。请注意，下面代码中的 `JOB_ID` 需要根据学习者自己的环境指定，可能不是 `119`。 ```sql ADMIN PAUSE DDL JOBS 119; ``` ```output +--------+------------+ | JOB_ID | RESULT | +--------+------------+ | 119 | successful | +--------+------------+ 1 row in set (0.01 sec) ``` + 通过命令 `ADMIN SHOW DDL JOBS` 检查 DDL 任务的状态，例子中 `JOB_ID` 为 `119`，`STATE` 为 `pausing` 表示已经暂停。请注意，你的输出可能和例子中不同。 ```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 | +--------+---------+-----------------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------+-------------+ | 119 | test | ddl_test1 | modify column | write reorganization | 2 | 110 | 0 | 2025-07-26 04:01:29.723000 | 2025-07-26 04:01:29.772000 | NULL | pausing | | | 118 | test | ddl_test1 | add index | public | 2 | 110 | 4194304 | 2025-07-26 04:01:10.173000 | 2025-07-26 04:01:10.222000 | 2025-07-26 04:01:21.222000 | synced | ingest, DXF | | 117 | test | ddl_test4 | create table | public | 2 | 116 | 0 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.473000 | synced | | | 115 | test | ddl_test3 | create table | public | 2 | 114 | 0 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.423000 | 2025-07-26 03:28:35.423000 | synced | | | 113 | test | ddl_test2 | create table | public | 2 | 112 | 0 | 2025-07-26 03:28:35.322000 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.373000 | synced | | | 111 | test | ddl_test1 | create table | public | 2 | 110 | 0 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.322000 | synced | | | 109 | mysql | tidb_kernel_options | create table | public | 1 | 108 | 0 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | synced | | | 107 | mysql | index_advisor_results | create table | public | 1 | 106 | 0 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.823000 | 2025-07-26 03:22:35.823000 | synced | | | 105 | sys | schema_unused_indexes | create view | public | 102 | 104 | 0 | 2025-07-26 03:22:35.722000 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.772000 | synced | | | 103 | sys | | create schema | public | 102 | 0 | 0 | 2025-07-26 03:22:35.673000 | 2025-07-26 03:22:35.673000 | 2025-07-26 03:22:35.722000 | synced | | | 101 | mysql | tidb_pitr_id_map | create table | public | 1 | 100 | 0 | 2025-07-26 03:22:35.622000 | 2025-07-26 03:22:35.622000 | 2025-07-26 03:22:35.673000 | synced | | +--------+---------+-----------------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------+-------------+ 11 rows in set (0.00 sec) ``` + 回到 [Grafana 监控](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000)，在 `tidb-demo-TiKV-Details` 的 `Cluster` 标签，可以看到各个 TiKV 节点的 CPU 负载 `CPU` 和 IO 使用率 `IO utillzation` 开始快速下降。 ![70%](https://lab-static.pingcap.com/images/2025/7/26/dee7ff1ce503ddbf13526726bcae55df17df956d.png) ### 步骤 4. 使用 `ADMIN RESUME DDL` 恢复 DDL 任务 + 在[终端 2](ssh://monitor1-ssh#2) 上，连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + 通过命令 `ADMIN RESUME DDL JOBS` 和 DDL 任务的 `JOB_ID` 恢复上一步骤中被暂停的 DDL 任务。请注意，下面代码中的 `JOB_ID` 需要根据参与者自己的环境指定，可能不是 `119`。 ```sql ADMIN RESUME DDL JOBS 119; ``` ```output +--------+------------+ | JOB_ID | RESULT | +--------+------------+ | 119 | successful | +--------+------------+ 1 row in set (0.01 sec) ``` + 通过命令 `ADMIN SHOW DDL JOBS` 检查 DDL 任务的状态，例子中 `JOB_ID` 为 `119`，`STATE` 为 `queueing` 表示已经恢复运行。请注意，你的输出可能和例子中不同。 ```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 | +--------+---------+-----------------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+----------+-------------+ | 119 | test | ddl_test1 | modify column | write reorganization | 2 | 110 | 0 | 2025-07-26 04:01:29.723000 | 2025-07-26 04:01:29.772000 | NULL | queueing | | | 118 | test | ddl_test1 | add index | public | 2 | 110 | 4194304 | 2025-07-26 04:01:10.173000 | 2025-07-26 04:01:10.222000 | 2025-07-26 04:01:21.222000 | synced | ingest, DXF | | 117 | test | ddl_test4 | create table | public | 2 | 116 | 0 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.473000 | synced | | | 115 | test | ddl_test3 | create table | public | 2 | 114 | 0 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.423000 | 2025-07-26 03:28:35.423000 | synced | | | 113 | test | ddl_test2 | create table | public | 2 | 112 | 0 | 2025-07-26 03:28:35.322000 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.373000 | synced | | | 111 | test | ddl_test1 | create table | public | 2 | 110 | 0 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.322000 | synced | | | 109 | mysql | tidb_kernel_options | create table | public | 1 | 108 | 0 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | synced | | | 107 | mysql | index_advisor_results | create table | public | 1 | 106 | 0 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.823000 | 2025-07-26 03:22:35.823000 | synced | | | 105 | sys | schema_unused_indexes | create view | public | 102 | 104 | 0 | 2025-07-26 03:22:35.722000 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.772000 | synced | | | 103 | sys | | create schema | public | 102 | 0 | 0 | 2025-07-26 03:22:35.673000 | 2025-07-26 03:22:35.673000 | 2025-07-26 03:22:35.722000 | synced | | | 101 | mysql | tidb_pitr_id_map | create table | public | 1 | 100 | 0 | 2025-07-26 03:22:35.622000 | 2025-07-26 03:22:35.622000 | 2025-07-26 03:22:35.673000 | synced | | +--------+---------+-----------------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+----------+-------------+ 11 rows in set (0.02 sec) ``` + 回到 [Grafana 监控](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000)，在 `tidb-demo-TiKV-Details` 的 `Cluster` 标签，可以看到各个 TiKV 节点的 CPU 负载 `CPU` 和 IO 使用率 `IO utillzation` 开始上升。 ![70%](https://lab-static.pingcap.com/images/2025/7/26/bf4c1b298d4d416169b0758365a7ec5023c0fcf2.png) ### 步骤 5. 等待 DDL 任务执行结束 + 在[终端 1](ssh://monitor1-ssh#1) 上，等待 DDL 任务执行结束。 ```output Query OK, 0 rows affected (12.02 sec) Query OK, 0 rows affected (4 min 42.02 sec) ``` + 在[终端 2](ssh://monitor1-ssh#2) 上，通过命令 `ADMIN SHOW DDL JOBS` 检查 DDL 任务的状态，例子中 `JOB_ID` 为 `119`，`STATE` 为 `synced` 表示已经执行完成。请注意，你的输出可能和例子中不同。 ```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 | +--------+---------+-----------------------+---------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+--------+-------------+ | 119 | test | ddl_test1 | modify column | public | 2 | 110 | 4194304 | 2025-07-26 04:01:29.723000 | 2025-07-26 04:01:29.772000 | 2025-07-26 04:25:44.122000 | synced | | | 118 | test | ddl_test1 | add index | public | 2 | 110 | 4194304 | 2025-07-26 04:01:10.173000 | 2025-07-26 04:01:10.222000 | 2025-07-26 04:01:21.222000 | synced | ingest, DXF | | 117 | test | ddl_test4 | create table | public | 2 | 116 | 0 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.473000 | synced | | | 115 | test | ddl_test3 | create table | public | 2 | 114 | 0 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.423000 | 2025-07-26 03:28:35.423000 | synced | | | 113 | test | ddl_test2 | create table | public | 2 | 112 | 0 | 2025-07-26 03:28:35.322000 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.373000 | synced | | | 111 | test | ddl_test1 | create table | public | 2 | 110 | 0 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.322000 | synced | | | 109 | mysql | tidb_kernel_options | create table | public | 1 | 108 | 0 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | synced | | | 107 | mysql | index_advisor_results | create table | public | 1 | 106 | 0 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.823000 | 2025-07-26 03:22:35.823000 | synced | | | 105 | sys | schema_unused_indexes | create view | public | 102 | 104 | 0 | 2025-07-26 03:22:35.722000 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.772000 | synced | | | 103 | sys | | create schema | public | 102 | 0 | 0 | 2025-07-26 03:22:35.673000 | 2025-07-26 03:22:35.673000 | 2025-07-26 03:22:35.722000 | synced | | +--------+---------+-----------------------+---------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+--------+-------------+ 10 rows in set (0.00 sec) ``` + 回到 [Grafana 监控](http://${LAB:HOST_MONITOR1_PUBLIC_IP}:3000)，在 `tidb-demo-TiKV-Details` 的 `Cluster` 标签，可以看到各个 TiKV 节点的 CPU 负载 `CPU` 和 IO 使用率 `IO utillzation` 开始快速下降。 ![70%](https://lab-static.pingcap.com/images/2025/7/26/c9b18218778b0185dc6275b6938ca41b30d9a8b6.png) + 通过命令 `ADMIN PAUSE DDL JOBS` 和 DDL 任务的 `JOB_ID` 再次暂停上一步骤中执行的 DDL 任务， 发现会出先现报错 `error: [ddl:8224]DDL Job:119 not found`。请注意，你的输出可能和例子中不同。 ```sql ADMIN PAUSE DDL JOBS 119; ``` ```output +--------+----------------------------------------+ | JOB_ID | RESULT | +--------+----------------------------------------+ | 119 | error: [ddl:8224]DDL Job:119 not found | +--------+----------------------------------------+ 1 row in set (0.00 sec) ``` ### 步骤 6. 使用 ADMIN CANCEL DDL 命令取消正在运行的 DDL 操作 > [!TIP] > 请注意，本步骤的各个命令执行时间间隔请不要超过 1 分钟，因为可能无法获得预期的结果。 + 在[终端 1](ssh://monitor1-ssh#1) 上，为表 `test.ddl_test2` 添加索引，并修改 `content` 列的属性。注意：不要等待该命令结束。 ```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) 上，通过命令 `ADMIN SHOW DDL JOBS` 检查刚刚开始执行的 DDL 任务的状态，例子中 `JOB_ID` 为 `121`，`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 | +--------+---------+-----------------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------+-------------+ | 121 | test | ddl_test2 | modify column | write reorganization | 2 | 112 | 0 | 2025-07-26 04:47:03.372000 | 2025-07-26 04:47:03.423000 | NULL | running | | | 120 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-26 04:46:38.423000 | 2025-07-26 04:46:38.472000 | 2025-07-26 04:46:49.472000 | synced | ingest, DXF | | 119 | test | ddl_test1 | modify column | public | 2 | 110 | 4194304 | 2025-07-26 04:01:29.723000 | 2025-07-26 04:01:29.772000 | 2025-07-26 04:25:44.122000 | synced | | | 118 | test | ddl_test1 | add index | public | 2 | 110 | 4194304 | 2025-07-26 04:01:10.173000 | 2025-07-26 04:01:10.222000 | 2025-07-26 04:01:21.222000 | synced | ingest, DXF | | 117 | test | ddl_test4 | create table | public | 2 | 116 | 0 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.473000 | synced | | | 115 | test | ddl_test3 | create table | public | 2 | 114 | 0 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.423000 | 2025-07-26 03:28:35.423000 | synced | | | 113 | test | ddl_test2 | create table | public | 2 | 112 | 0 | 2025-07-26 03:28:35.322000 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.373000 | synced | | | 111 | test | ddl_test1 | create table | public | 2 | 110 | 0 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.322000 | synced | | | 109 | mysql | tidb_kernel_options | create table | public | 1 | 108 | 0 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | synced | | | 107 | mysql | index_advisor_results | create table | public | 1 | 106 | 0 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.823000 | 2025-07-26 03:22:35.823000 | synced | | | 105 | sys | schema_unused_indexes | create view | public | 102 | 104 | 0 | 2025-07-26 03:22:35.722000 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.772000 | synced | | +--------+---------+-----------------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------+-------------+ 11 rows in set (0.01 sec) ``` + 在[终端 2](ssh://monitor1-ssh#2) 上，通过命令 `ADMIN CANCEL DDL JOBS` 取消刚刚开始执行的 DDL 任务。请注意，你的 JOB_ID 可能和例子中不同。 ```sql ADMIN CANCEL DDL JOBS 121; ``` ```output +--------+------------+ | JOB_ID | RESULT | +--------+------------+ | 121 | successful | +--------+------------+ 1 row in set (0.01 sec) ``` + 在[终端 2](ssh://monitor1-ssh#2) 上，通过命令 `ADMIN SHOW DDL JOBS` 检查刚刚被取消的 DDL 任务的状态，例子中 `JOB_ID` 为 `121`，`STATE` 为 `cancelling` 表示正在被取消。请注意，你的输出可能和例子中不同。 ```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 | +--------+---------+-----------------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+------------+-------------+ | 121 | test | ddl_test2 | modify column | write reorganization | 2 | 112 | 0 | 2025-07-26 04:47:03.372000 | 2025-07-26 04:47:03.423000 | NULL | cancelling | | | 120 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-26 04:46:38.423000 | 2025-07-26 04:46:38.472000 | 2025-07-26 04:46:49.472000 | synced | ingest, DXF | | 119 | test | ddl_test1 | modify column | public | 2 | 110 | 4194304 | 2025-07-26 04:01:29.723000 | 2025-07-26 04:01:29.772000 | 2025-07-26 04:25:44.122000 | synced | | | 118 | test | ddl_test1 | add index | public | 2 | 110 | 4194304 | 2025-07-26 04:01:10.173000 | 2025-07-26 04:01:10.222000 | 2025-07-26 04:01:21.222000 | synced | ingest, DXF | | 117 | test | ddl_test4 | create table | public | 2 | 116 | 0 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.473000 | synced | | | 115 | test | ddl_test3 | create table | public | 2 | 114 | 0 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.423000 | 2025-07-26 03:28:35.423000 | synced | | | 113 | test | ddl_test2 | create table | public | 2 | 112 | 0 | 2025-07-26 03:28:35.322000 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.373000 | synced | | | 111 | test | ddl_test1 | create table | public | 2 | 110 | 0 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.322000 | synced | | | 109 | mysql | tidb_kernel_options | create table | public | 1 | 108 | 0 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | synced | | | 107 | mysql | index_advisor_results | create table | public | 1 | 106 | 0 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.823000 | 2025-07-26 03:22:35.823000 | synced | | | 105 | sys | schema_unused_indexes | create view | public | 102 | 104 | 0 | 2025-07-26 03:22:35.722000 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.772000 | synced | | +--------+---------+-----------------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+------------+-------------+ 11 rows in set (0.01 sec) ``` + 在[终端 2](ssh://monitor1-ssh#2) 上，请等待 1 分钟，并多次执行命令 `ADMIN SHOW DDL JOBS` 检查刚刚取消的 DDL 任务的状态，例子中 `JOB_ID` 为 `121`，`STATE` 为 `rollback done` 表示任务取消完毕。请注意，你的输出结果可能和例子中不同。 ```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 | +--------+---------+-----------------------+---------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | 121 | test | ddl_test2 | modify column | none | 2 | 112 | 0 | 2025-07-26 04:47:03.372000 | 2025-07-26 04:47:03.423000 | 2025-07-26 04:48:39.472000 | rollback done | | | 120 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-26 04:46:38.423000 | 2025-07-26 04:46:38.472000 | 2025-07-26 04:46:49.472000 | synced | ingest, DXF | | 119 | test | ddl_test1 | modify column | public | 2 | 110 | 4194304 | 2025-07-26 04:01:29.723000 | 2025-07-26 04:01:29.772000 | 2025-07-26 04:25:44.122000 | synced | | | 118 | test | ddl_test1 | add index | public | 2 | 110 | 4194304 | 2025-07-26 04:01:10.173000 | 2025-07-26 04:01:10.222000 | 2025-07-26 04:01:21.222000 | synced | ingest, DXF | | 117 | test | ddl_test4 | create table | public | 2 | 116 | 0 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.423000 | 2025-07-26 03:28:36.473000 | synced | | | 115 | test | ddl_test3 | create table | public | 2 | 114 | 0 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.423000 | 2025-07-26 03:28:35.423000 | synced | | | 113 | test | ddl_test2 | create table | public | 2 | 112 | 0 | 2025-07-26 03:28:35.322000 | 2025-07-26 03:28:35.373000 | 2025-07-26 03:28:35.373000 | synced | | | 111 | test | ddl_test1 | create table | public | 2 | 110 | 0 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.272000 | 2025-07-26 03:28:35.322000 | synced | | | 109 | mysql | tidb_kernel_options | create table | public | 1 | 108 | 0 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | 2025-07-26 03:22:35.872000 | synced | | | 107 | mysql | index_advisor_results | create table | public | 1 | 106 | 0 | 2025-07-26 03:22:35.772000 | 2025-07-26 03:22:35.823000 | 2025-07-26 03:22:35.823000 | synced | | +--------+---------+-----------------------+---------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ 10 rows in set (0.01 sec) ``` + 在[终端 1](ssh://monitor1-ssh#1) 上，发现为表 `test.ddl_test2` 修改 `content` 列的属性的操作被取消了， 并且无法恢复。 ```output Query OK, 0 rows affected (10.47 sec) ERROR 8214 (HY000): Cancelled DDL job ``` + 本模块到此结束。