Applying, auditing, and controlling Online DDL

Vitess provides two interfaces to interacting with Online DDL:

  • SQL commands, via VTGate
  • Command line interface, via vtctl

Supported interactions are:

Running migrations

To run a managed schema migration, you should:

  • Formulate your DDLs (CREATE, ALTER, DROP) queries
  • Choose a ddl_strategy

When the user submits an online DDL, Vitess responds with a UUID, a job Id used to later track or control the migration. The migration does not start immediately. It is queued at the tablets and executed at some point in the future.

Via VTGate/SQL

  1. mysql> set @@ddl_strategy='vitess';
  3. mysql> alter table corder add column ts timestamp not null default current_timestamp;
  4. +--------------------------------------+
  5. | uuid                                 |
  6. +--------------------------------------+
  7. | bf4598ab_8d55_11eb_815f_f875a4d24e90 |
  8. +--------------------------------------+
  10. mysql> drop table customer;
  11. +--------------------------------------+
  12. | uuid                                 |
  13. +--------------------------------------+
  14. | 6848c1a4_8d57_11eb_815f_f875a4d24e90 |
  15. +--------------------------------------+
  • @@ddl_strategy behaves like a MySQL session variable, though is only recognized by VTGate. Setting @@ddl_strategy only applies to that same connection and does not affect other connections. The strategy applies to all migrations executed in that session. You may subsequently set @@ddl_strategy to different value.
  • If you run vtgate without --ddl_strategy, then @@ddl_strategy defaults to 'direct', which implies schema migrations are synchronous. You will need to set @@ddl_strategy='gh-ost' to run followup ALTER TABLE statements via gh-ost.
  • If you run vtgate --ddl_strategy "gh-ost", then @@ddl_strategy defaults to 'gh-ost' in each new session. Any ALTER TABLE will run via gh-ost. You may set @@ddl_strategy='pt-osc' to make migrations run through pt-online-schema-change, or set @@ddl_strategy='direct' to run migrations synchronously.

Via vtctlclient/ApplySchema

You may use vtctl or vtctlclient (the two are interchangeable for the purpose of this document) to apply schema changes. The ApplySchema command supports both synchronous and online schema migrations. To run an online schema migration you will supply the --ddl_strategy command line flag:

  1. $ vtctlclient -- ApplySchema --ddl_strategy "vitess" --sql "ALTER TABLE demo MODIFY id bigint UNSIGNED" commerce
  2. a2994c92_f1d4_11ea_afa3_f875a4d24e90

You my run multiple migrations withing the same ApplySchema command:

  1. $ vtctlclient -- ApplySchema --skip_preflight --ddl_strategy "vitess" --sql "ALTER TABLE demo MODIFY id bigint UNSIGNED; CREATE TABLE sample (id int PRIMARY KEY); DROP TABLE another;" commerce
  2. 3091ef2a_4b87_11ec_a827_0a43f95f28a3

ApplySchema accepts the following flags:

  • --ddl_strategy: by default migrations run directly via MySQL standard DDL. This flag must be aupplied to indicate an online strategy. See also DDL strategies and ddl_strategy flags.
  • --migration_context <unique-value>: all migrations in a ApplySchema command are logically grouped via a unique context. A unique value will be supplied automatically. The user may choose to supply their own value, and it’s their responsibility to provide with a unique value. Any string format is accepted. The context can then be used to search for migrations, via SHOW VITESS_MIGRATIONS LIKE 'the-context'. It is visible in SHOW VITESS_MIGRATIONS ... output as the migration_context column.
  • --skip_preflight: skip an internal Vitess schema validation. When running an online DDL it’s recommended to add --skip_preflight. In future Vitess versions this flag may be removed or default to true.

Tracking migrations

You may track the status of a single or of multiple migrations. Since migrations run asycnhronously, it is the user’s responsibility to audit the progress and state of submitted migrations. Users are likely to want to know when a migration is complete (or failed) so as to be able to deploy code changes or run other operations.

Common patterns are:

  • Show state of a specific migration
  • Show all running, complete or failed migrations
  • Show recent migrations
  • Show migrations ordered by most-recent first.
  • Show n number of migrations, skipping m rows.

Via VTGate/SQL

Examples for a single shard cluster:

  1. mysql> show vitess_migrations like 'bf4598ab_8d55_11eb_815f_f875a4d24e90' \G
  2. *************************** 1. row ***************************
  3.                  id: 23
  4.      migration_uuid: bf4598ab_8d55_11eb_815f_f875a4d24e90
  5.            keyspace: commerce
  6.               shard: 0
  7.        mysql_schema: vt_commerce
  8.         mysql_table: corder
  9. migration_statement: alter table corder add column ts timestamp not null default current_timestamp()
  10.            strategy: vitess
  11.             options: 
  12.     added_timestamp: 2021-03-25 12:35:01
  13. requested_timestamp: 2021-03-25 12:34:58
  14.     ready_timestamp: 2021-03-25 12:35:04
  15.   started_timestamp: 2021-03-25 12:35:04
  16.  liveness_timestamp: 2021-03-25 12:35:06
  17. completed_timestamp: 2021-03-25 12:35:06
  18.   cleanup_timestamp: NULL
  19.    migration_status: complete
  20.            log_path: 
  21.           artifacts: _bf4598ab_8d55_11eb_815f_f875a4d24e90_20210325123504_vrepl,
  22.             retries: 0
  23.              tablet: zone1-0000000100
  24.      tablet_failure: 0
  25.            progress: 100
  26.   migration_context: vtgate:a8352418-8d55-11eb-815f-f875a4d24e90
  27.          ddl_action: alter
  28.             message: 
  29.         eta_seconds: 0
  1. mysql> show vitess_migrations like 'complete' \G
  2. ...
  3. *************************** 21. row ***************************
  4.                  id: 24
  5.      migration_uuid: 6848c1a4_8d57_11eb_815f_f875a4d24e90
  6.            keyspace: commerce
  7.               shard: 0
  8.        mysql_schema: vt_commerce
  9.         mysql_table: customer
  10. migration_statement: drop table customer
  11.            strategy: vitess
  12.             options: 
  13.     added_timestamp: 2021-03-25 12:46:53
  14. requested_timestamp: 2021-03-25 12:46:51
  15.     ready_timestamp: 2021-03-25 12:46:57
  16.   started_timestamp: 2021-03-25 12:46:57
  17.  liveness_timestamp: 2021-03-25 12:46:57
  18. completed_timestamp: 2021-03-25 12:46:57
  19.   cleanup_timestamp: NULL
  20.    migration_status: complete
  21.            log_path: 
  22.           artifacts: _vt_HOLD_6848c1a48d5711eb815ff875a4d24e90_20210326104657,
  23.             retries: 0
  24.              tablet: zone1-0000000100
  25.      tablet_failure: 0
  26.            progress: 100
  27.   migration_context: vtgate:a8352418-8d55-11eb-815f-f875a4d24e90
  28.          ddl_action: drop
  29.             message: 
  30.         eta_seconds: 0
  1. mysql> show vitess_migrations where completed_timestamp > now() - interval 1 day;
  2. +----+--------------------------------------+----------+-------+--------------+-------------+---------------------------------------------------------------------------------+----------+---------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+-------------------+------------------+----------+-------------------------------------------------------------+---------+------------------+----------------+----------+---------------------------------------------+------------+---------+-------------+
  3. | id | migration_uuid                       | keyspace | shard | mysql_schema | mysql_table | migration_statement                                                             | strategy | options | added_timestamp     | requested_timestamp | ready_timestamp     | started_timestamp   | liveness_timestamp  | completed_timestamp | cleanup_timestamp | migration_status | log_path | artifacts                                                   | retries | tablet           | tablet_failure | progress | migration_context                           | ddl_action | message | eta_seconds |
  4. +----+--------------------------------------+----------+-------+--------------+-------------+---------------------------------------------------------------------------------+----------+---------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+-------------------+------------------+----------+-------------------------------------------------------------+---------+------------------+----------------+----------+---------------------------------------------+------------+---------+-------------+
  5. | 23 | bf4598ab_8d55_11eb_815f_f875a4d24e90 | commerce | 0     | vt_commerce  | corder      | alter table corder add column ts timestamp not null default current_timestamp() | online   |         | 2021-03-25 12:35:01 | 2021-03-25 12:34:58 | 2021-03-25 12:35:04 | 2021-03-25 12:35:04 | 2021-03-25 12:35:06 | 2021-03-25 12:35:06 | NULL              | complete         |          | _bf4598ab_8d55_11eb_815f_f875a4d24e90_20210325123504_vrepl, |       0 | zone1-0000000100 |              0 |      100 | vtgate:a8352418-8d55-11eb-815f-f875a4d24e90 | alter      |         |           0 |
  6. | 24 | 6848c1a4_8d57_11eb_815f_f875a4d24e90 | commerce | 0     | vt_commerce  | customer    | drop table customer                                                             | online   |         | 2021-03-25 12:46:53 | 2021-03-25 12:46:51 | 2021-03-25 12:46:57 | 2021-03-25 12:46:57 | 2021-03-25 12:46:57 | 2021-03-25 12:46:57 | NULL              | complete         |          | _vt_HOLD_6848c1a48d5711eb815ff875a4d24e90_20210326104657,   |       0 | zone1-0000000100 |              0 |      100 | vtgate:a8352418-8d55-11eb-815f-f875a4d24e90 | drop       |         |           0 |
  7. | 25 | 6fd57dd3_8d57_11eb_815f_f875a4d24e90 | commerce | 0     | vt_commerce  | customer    | revert 6848c1a4_8d57_11eb_815f_f875a4d24e90                                     | online   |         | 2021-03-25 12:47:08 | 2021-03-25 12:47:04 | 2021-03-25 12:47:12 | 2021-03-25 12:47:12 | 2021-03-25 12:47:12 | 2021-03-25 12:47:12 | NULL              | complete         |          | _vt_HOLD_6848c1a48d5711eb815ff875a4d24e90_20210326104657,   |       0 | zone1-0000000100 |              0 |      100 | vtgate:a8352418-8d55-11eb-815f-f875a4d24e90 | create     |         |           0 |
  8. +----+--------------------------------------+----------+-------+--------------+-------------+---------------------------------------------------------------------------------+----------+---------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+-------------------+------------------+----------+-------------------------------------------------------------+---------+------------------+----------------+----------+---------------------------------------------+------------+---------+-------------+
  • show vitess_migrations shows the entire history of migrations.
  • show vitess_migrations like ... filters migrations by migration_uuid, or migration_context, or migration_status.
  • show vitess_migrations where ... lets the user specify arbitrary conditions.
  • All commands return results for the keyspace (schema) in use.

Via vtctlclient/ApplySchema

Examples for a 4-shard cluster:

  1. $ vtctlclient OnlineDDL commerce show ab3ffdd5_f25c_11ea_bab4_0242c0a8b007
  2. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  3. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  4. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  5. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:24:33 | 2020-09-09 05:24:34 | complete         |
  6. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete         |
  7. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete         |
  8. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete         |
  9. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  11. $ vtctlclient OnlineDDL commerce show 8a797518_f25c_11ea_bab4_0242c0a8b007
  12. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  13. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  14. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  15. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | running          |
  16. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 | 2020-09-09 05:23:33 | complete         |
  17. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | running          |
  18. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | running          |
  19. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  21. $ vtctlclient OnlineDDL commerce show 8a797518_f25c_11ea_bab4_0242c0a8b007
  22. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  23. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  24. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  25. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  26. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  27. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  28. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 | 2020-09-09 05:23:33 | complete         |
  29. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  31. $ vtctlclient OnlineDDL commerce show recent
  32. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  33. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  34. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  35. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  36. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 | 2020-09-09 05:23:33 | complete         |
  37. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:24:33 | 2020-09-09 05:24:34 | complete         |
  38. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  39. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  40. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete         |
  41. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  42. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  43. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete         |
  44. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  45. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  46. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete         |
  47. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  49. $ vtctlclient OnlineDDL --order descending commerce show all
  50. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  51. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  52. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  53. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete         |
  54. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  55. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  56. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete         |
  57. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  58. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  59. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete         |
  60. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  61. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  62. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:24:33 | 2020-09-09 05:24:34 | complete         |
  63. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 | 2020-09-09 05:23:33 | complete         |
  64. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  65. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  67. $ vtctlclient OnlineDDL commerce show failed
  68. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  69. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  70. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  71. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  72. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  73. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  74. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  76. $ vtctlclient OnlineDDL --limit 5 commerce show recent
  77. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  78. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  79. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  80. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  81. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 | 2020-09-09 05:23:33 | complete         |
  82. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:24:33 | 2020-09-09 05:24:34 | complete         |
  83. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  84. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  85. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  87. $ vtctlclient OnlineDDL --skip 5 --limit 5 commerce show recent
  88. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  89. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  90. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  91. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  92. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:25:13 | 2020-09-09 05:25:14 | complete         |
  93. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 8a797518_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:23:32 |                     | failed           |
  94. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 63b5db0c_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:22:41 | 2020-09-09 05:22:42 | complete         |
  95. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | ab3ffdd5_f25c_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 05:24:33 | 2020-09-09 05:24:34 | complete         |
  96. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+

The syntax for tracking migrations is:

  1. vtctlclient OnlineDDL [-json] <keyspace> show <migration_id|all|recent|queued|ready|running|complete|failed|cancelled>

Showing migration logs

gh-ost and pt-osc tools generate logs files, which are retrievable for 24 hours after migration completion/failure.

Via VTGate/SQL

  1. mysql> show vitess_migration '3a273866_e867_11eb_ab12_0a43f95f28a3' logs \G
  2. *************************** 1. row ***************************
  3. migration_log: 2021-07-19 07:59:23 INFO starting gh-ost 261355426d8fc31b590733ca8ff8e79012103c18
  4. 2021-07-19 07:59:23 INFO Migrating `vt_commerce`.`corder`
  5. 2021-07-19 07:59:23 INFO executing gh-ost-on-startup hook: /tmp/online-ddl-3a273866_e867_11eb_ab12_0a43f95f28a3-943208852/gh-ost-on-startup
  6. ok
  7. 2021-07-19 07:59:23 INFO inspector connection validated on ip-REDACTED:17100
  8. 2021-07-19 07:59:23 INFO User has SUPER, REPLICATION SLAVE privileges, and has ALL privileges on `vt_commerce`.*
  9. 2021-07-19 07:59:23 INFO binary logs validated on ip-REDACTED:17100
  10. 2021-07-19 07:59:23 INFO Restarting replication on ip-REDACTED:17100 to make sure binlog settings apply to replication thread
  11. 2021-07-19 07:59:23 INFO Inspector initiated on ip-REDACTED:17100, version 5.7.30-log
  12. 2021-07-19 07:59:23 INFO Table found. Engine=InnoDB
  13. ...

Launching a migration

Migrations submitted with --postpone-launch remain queued or ready until told to launch. The user may launch a specific migration or they may launch all postponed migrations:

Via VTGate/SQL

  1. mysql> alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' launch;
  2. Query OK, 1 row affected (0.01 sec)

or

  1. mysql> alter vitess_migration launch all;
  2. Query OK, 1 row affected (0.01 sec)

Via vtctlclient/ApplySchema

Launch a specific migration:

  1. $ vtctlclient -- ApplySchema --sql "alter vitess_migration '9e8a9249_3976_11ed_9442_0a43f95f28a3' launch" commerce

Or launch a specific migration on a specific shard:

  1. $ vtctlclient -- ApplySchema --sql "alter vitess_migration '9e8a9249_3976_11ed_9442_0a43f95f28a3' launch vitess_shards '-40,40-80'" commerce

Or launch all:

  1. $ vtctlclient -- ApplySchema --sql "alter vitess_migration launch all" commerce

Completing a migration

Migrations submitted with --postpone-completion remain ready or running until told to complete. The user may complete a specific migration or they may complete all postponed migrations:

Via VTGate/SQL

  1. mysql> alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' complete;
  2. Query OK, 1 row affected (0.01 sec)

or

  1. mysql> alter vitess_migration complete all;
  2. Query OK, 1 row affected (0.01 sec)

Via vtctlclient/ApplySchema

Complete a specific migration:

  1. $ vtctlclient -- ApplySchema --sql "alter vitess_migration '9e8a9249_3976_11ed_9442_0a43f95f28a3' complete" commerce

Or complete all:

  1. $ vtctlclient -- ApplySchema --sql "alter vitess_migration complete all" commerce

Cancelling a migration

The user may cancel a migration, as follows:

  • If the migration hasn’t started yet (it is queued or ready), then it transitions into cancelled state and doesn’t get executed.
  • If the migration is running, then it is forcibly interrupted. The migration transitions to cancelled state.
  • In all other cases, cancelling a migration has no effect.

Via VTGate/SQL

Examples for a single shard cluster:

  1.                  id: 28
  2.      migration_uuid: aa89f255_8d68_11eb_815f_f875a4d24e90
  3.            keyspace: commerce
  4.               shard: 0
  5.        mysql_schema: vt_commerce
  6.         mysql_table: corder
  7. migration_statement: alter table corder add column handler_id int not null
  8.            strategy: gh-ost
  9.             options: 
  10.     added_timestamp: 2021-03-25 14:50:27
  11. requested_timestamp: 2021-03-25 14:50:24
  12.     ready_timestamp: 2021-03-25 14:50:31
  13.   started_timestamp: 2021-03-25 14:50:32
  14.  liveness_timestamp: 2021-03-25 14:50:32
  15. completed_timestamp: NULL
  16.   cleanup_timestamp: NULL
  17.    migration_status: running
  18. ...
  20. mysql> alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' cancel;
  21. Query OK, 1 row affected (0.01 sec)
  23. mysql> show vitess_migrations like 'aa89f255_8d68_11eb_815f_f875a4d24e90' \G
  24. *************************** 1. row ***************************
  25.                  id: 28
  26.      migration_uuid: aa89f255_8d68_11eb_815f_f875a4d24e90
  27.            keyspace: commerce
  28.               shard: 0
  29.        mysql_schema: vt_commerce
  30.         mysql_table: corder
  31. migration_statement: alter table corder add column handler_id int not null
  32.            strategy: gh-ost
  33.             options: --throttle-flag-file=/tmp/throttle.flag
  34.     added_timestamp: 2021-03-25 14:50:27
  35. requested_timestamp: 2021-03-25 14:50:24
  36.     ready_timestamp: 2021-03-25 14:50:31
  37.   started_timestamp: 2021-03-25 14:50:32
  38.  liveness_timestamp: 2021-03-25 14:50:32
  39. completed_timestamp: NULL
  40.   cleanup_timestamp: NULL
  41.    migration_status: cancelled
  42. ...
  • alter vitess_migration ... cancel takes exactly one migration’s UUID.
  • alter vitess_migration ... cancel responds with number of affected migrations.

Via vtctlclient/ApplySchema

Examples for a 4-shard cluster:

  1. vtctlclient OnlineDDL <keyspace> cancel <migration_id>

Example:

  1. $ vtctlclient OnlineDDL commerce show 2201058f_f266_11ea_bab4_0242c0a8b007
  2. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  3. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  4. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  5. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | running          |
  6. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | running          |
  7. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | running          |
  8. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | running          |
  9. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  11. $ vtctlclient OnlineDDL commerce cancel 2201058f_f266_11ea_bab4_0242c0a8b007
  13. $ vtctlclient OnlineDDL commerce show 2201058f_f266_11ea_bab4_0242c0a8b007
  14. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  15. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  16. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  17. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | cancelled        |
  18. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | cancelled        |
  19. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | cancelled        |
  20. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | cancelled        |
  21. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+

Cancelling all keyspace migrations

The user may cancel all migrations in a keyspace. A migration is cancellable if it is in queued, ready or running states, as described previously. It is a high impact operation and should be used with care.

Via VTGate/SQL

Examples for a single shard cluster:

  1. mysql> alter vitess_migration cancel all;
  2. Query OK, 1 row affected (0.02 sec)

Via vtctlclient/ApplySchema

Examples for a 4-shard cluster:

  1. $ vtctlclient -- ApplySchema --sql "alter vitess_migration cancel all" commerce

Also available via vtctlclient OnlineDDL command:

  1. vtctlclient OnlineDDL <keyspace> cancel-all

Example:

  1. $ vtctlclient OnlineDDL commerce show all
  2. +------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+
  3. |      Tablet      | shard | mysql_schema | mysql_table |            migration_uuid            | strategy | started_timestamp | completed_timestamp | migration_status |
  4. +------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+
  5. | zone1-0000000100 |     0 | vt_commerce  | corder      | 2c581994_353a_11eb_8b72_f875a4d24e90 | online   |                   |                     | queued           |
  6. | zone1-0000000100 |     0 | vt_commerce  | corder      | 2c6420c9_353a_11eb_8b72_f875a4d24e90 | online   |                   |                     | queued           |
  7. | zone1-0000000100 |     0 | vt_commerce  | corder      | 2c7040df_353a_11eb_8b72_f875a4d24e90 | online   |                   |                     | queued           |
  8. | zone1-0000000100 |     0 | vt_commerce  | corder      | 2c7c0572_353a_11eb_8b72_f875a4d24e90 | online   |                   |                     | queued           |
  9. | zone1-0000000100 |     0 | vt_commerce  | corder      | 2c87f7cd_353a_11eb_8b72_f875a4d24e90 | online   |                   |                     | queued           |
  10. +------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+
  12. $ vtctlclient OnlineDDL commerce cancel-all
  14. $ vtctlclient OnlineDDL commerce show all
  15. +------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+
  16. |      Tablet      | shard | mysql_schema | mysql_table |            migration_uuid            | strategy | started_timestamp | completed_timestamp | migration_status |
  17. +------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+
  18. | zone1-0000000100 |     0 | vt_commerce  | corder      | 2c581994_353a_11eb_8b72_f875a4d24e90 | online   |                   |                     | cancelled        |
  19. | zone1-0000000100 |     0 | vt_commerce  | corder      | 2c6420c9_353a_11eb_8b72_f875a4d24e90 | online   |                   |                     | cancelled        |
  20. | zone1-0000000100 |     0 | vt_commerce  | corder      | 2c7040df_353a_11eb_8b72_f875a4d24e90 | online   |                   |                     | cancelled        |
  21. | zone1-0000000100 |     0 | vt_commerce  | corder      | 2c7c0572_353a_11eb_8b72_f875a4d24e90 | online   |                   |                     | cancelled        |
  22. | zone1-0000000100 |     0 | vt_commerce  | corder      | 2c87f7cd_353a_11eb_8b72_f875a4d24e90 | online   |                   |                     | cancelled        |
  23. +------------------+-------+--------------+-------------+--------------------------------------+----------+-------------------+---------------------+------------------+

Retrying a migration

The user may retry running a migration. If the migration is in failed or in cancelled state, Vitess will re-run the migration, with exact same arguments as previously intended. If the migration is in any other state, retry does nothing.

It is not possible to retry a migration with different options. e.g. if the user initially runs ALTER TABLE demo MODIFY id BIGINT with @@ddl_strategy='gh-ost --max-load Threads_running=200' and the migration fails, retrying it will use exact same options. It is not possible to retry with @@ddl_strategy='gh-ost --max-load Threads_running=500'.

Via VTGate/SQL

Examples for a single shard cluster:

  1. *************************** 1. row ***************************
  2.                  id: 28
  3.      migration_uuid: aa89f255_8d68_11eb_815f_f875a4d24e90
  4.            keyspace: commerce
  5.               shard: 0
  6.        mysql_schema: vt_commerce
  7.         mysql_table: corder
  8. migration_statement: alter table corder add column handler_id int not null
  9.            strategy: gh-ost
  10.             options: --throttle-flag-file=/tmp/throttle.flag
  11.     added_timestamp: 2021-03-25 14:50:27
  12. requested_timestamp: 2021-03-25 14:50:24
  13.     ready_timestamp: 2021-03-25 14:56:22
  14.   started_timestamp: 2021-03-25 14:56:22
  15.  liveness_timestamp: 2021-03-25 14:56:22
  16. completed_timestamp: NULL
  17.   cleanup_timestamp: NULL
  18.    migration_status: failed
  19. ...
  21. mysql> alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' retry;
  22. Query OK, 1 row affected (0.00 sec)
  24. mysql> show vitess_migrations like 'aa89f255_8d68_11eb_815f_f875a4d24e90' \G
  25. *************************** 1. row ***************************
  26.                  id: 28
  27.      migration_uuid: aa89f255_8d68_11eb_815f_f875a4d24e90
  28.            keyspace: commerce
  29.               shard: 0
  30.        mysql_schema: vt_commerce
  31.         mysql_table: corder
  32. migration_statement: alter table corder add column handler_id int not null
  33.            strategy: gh-ost
  34.             options: --throttle-flag-file=/tmp/throttle.flag
  35.     added_timestamp: 2021-03-25 14:50:27
  36. requested_timestamp: 2021-03-25 14:50:24
  37.     ready_timestamp: 2021-03-25 14:56:42
  38.   started_timestamp: 2021-03-25 14:56:42
  39.  liveness_timestamp: 2021-03-25 14:56:42
  40. completed_timestamp: NULL
  41.   cleanup_timestamp: NULL
  42.    migration_status: running
  43. ...
  • alter vitess_migration ... retry takes exactly one migration’s UUID.
  • alter vitess_migration ... retry responds with number of affected migrations.

Via vtctlclient/ApplySchema

  1. $ vtctlclient -- ApplySchema --sql "alter vitess_migration '2201058f_f266_11ea_bab4_0242c0a8b007' retry" commerce

Also available via vtctlclient OnlineDDL command:

Examples for a 4-shard cluster:

  1. $ vtctlclient OnlineDDL commerce show 2201058f_f266_11ea_bab4_0242c0a8b007
  2. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  3. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  4. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  5. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | failed           |
  6. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | failed           |
  7. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | failed           |
  8. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:32:31 |                     | failed           |
  9. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  11. $ vtctlclient OnlineDDL commerce retry 2201058f_f266_11ea_bab4_0242c0a8b007
  13. $ vtctlclient OnlineDDL commerce show 2201058f_f266_11ea_bab4_0242c0a8b007
  14. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+-------------------+---------------------+------------------+
  15. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy | started_timestamp | completed_timestamp | migration_status |
  16. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+-------------------+---------------------+------------------+
  17. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   |                   |                     | queued           |
  18. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   |                   |                     | queued           |
  19. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   |                   |                     | queued           |
  20. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   |                   |                     | queued           |
  21. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+-------------------+---------------------+------------------+
  23. $ vtctlclient OnlineDDL commerce show 2201058f_f266_11ea_bab4_0242c0a8b007
  24. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  25. |     Tablet      | shard | mysql_schema | mysql_table | ddl_action |            migration_uuid            | strategy |  started_timestamp  | completed_timestamp | migration_status |
  26. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+
  27. | test-0000000101 |   -40 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:37:33 |                     | running          |
  28. | test-0000000401 | c0-   | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:37:33 |                     | running          |
  29. | test-0000000201 | 40-80 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:37:33 |                     | running          |
  30. | test-0000000301 | 80-c0 | vt_commerce  | demo        | alter      | 2201058f_f266_11ea_bab4_0242c0a8b007 | online   | 2020-09-09 06:37:33 |                     | running          |
  31. +-----------------+-------+--------------+-------------+------------+--------------------------------------+----------+---------------------+---------------------+------------------+

Cleaning migration artifacts

Migrations yield artifacts: these are leftover tables, such as the ghost or shadow tables in an ALTER DDL. These tables are audited and collected as part of table lifecycle.

The artifacts are essential to Reverting a migration, and are kept intact for a while before destroyed.

However, the artifacts also consume disk space. If the user is convinced they will not need the artifacts, they may explicitly request that the artifacts are dropped sooner.

Once cleanup is requested, the migration cannot be reverted.

The artifact tables are not purged immediately. Rather, they are sent for processing into the lifecycle mechanism.

Via VTGate/SQL

Per migration, request artifact cleanup via:

  1. mysql> alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' cleanup;
  2. Query OK, 1 row affected (0.00 sec)

Reverting a migration

Vitess offers lossless revert for online schema migrations: the user may regret a table migration after completion, and roll back the table’s schema to previous state without loss of data. See Revertible Migrations.

Via VTGate/SQL

Examples for a single shard cluster:

  1. mysql> show create table corder\G
  3. Create Table: CREATE TABLE `corder` (
  4.   `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  5.   `customer_id` bigint(20) DEFAULT NULL,
  6.   `sku` varbinary(128) DEFAULT NULL,
  7.   `price` bigint(20) DEFAULT NULL,
  8.   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  9.   PRIMARY KEY (`order_id`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  11. 1 row in set (0.01 sec)
  13. mysql> alter table corder drop column ts, add key customer_idx(customer_id);
  14. +--------------------------------------+
  15. | uuid                                 |
  16. +--------------------------------------+
  17. | 1a689113_8d77_11eb_815f_f875a4d24e90 |
  18. +--------------------------------------+
  20. mysql> show create table corder\G
  22. Create Table: CREATE TABLE `corder` (
  23.   `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  24.   `customer_id` bigint(20) DEFAULT NULL,
  25.   `sku` varbinary(128) DEFAULT NULL,
  26.   `price` bigint(20) DEFAULT NULL,
  27.   PRIMARY KEY (`order_id`),
  28.   KEY `customer_idx` (`customer_id`)
  29. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  30. 1 row in set (0.00 sec)
  32. mysql> revert vitess_migration '1a689113_8d77_11eb_815f_f875a4d24e90';
  33. +--------------------------------------+
  34. | uuid                                 |
  35. +--------------------------------------+
  36. | a02e6612_8d79_11eb_815f_f875a4d24e90 |
  37. +--------------------------------------+
  39. mysql> show create table corder\G
  41. Create Table: CREATE TABLE `corder` (
  42.   `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  43.   `customer_id` bigint(20) DEFAULT NULL,
  44.   `sku` varbinary(128) DEFAULT NULL,
  45.   `price` bigint(20) DEFAULT NULL,
  46.   `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  47.   PRIMARY KEY (`order_id`)
  48. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • A revert is its own migration, hence has its own UUID

Via vtctlclient/ApplySchema

  1. $ vtctlclient -- ApplySchema --ddl_strategy "vitess" --sql "revert vitess_migration '1a689113_8d77_11eb_815f_f875a4d24e90'" commerce

Controlling throttling

Managed migrations use the tablet throttler to ensure a sustainable impact to the MySQL servers and replication stream. Normally, the user doesn’t need to get involved, as the throttler auto-identifies load scenarios, and pushes back on migration progress. However, Vitess makes available these commands for additional control over migration throttling:

  1. alter vitess_migration '<uuid>' throttle [expire '<duration>'] [ratio <ratio>];
  2. alter vitess_migration throttle all [expire '<duration>'] [ratio <ratio>];
  3. alter vitess_migration '<uuid>' unthrottle;
  4. alter vitess_migration unthrottle all;
  5. show vitess_throttled_apps;

Note: the tablet throttler must be enabled for these command to run.

Throttling a migration

To fully throttle a migration, run:

  1. mysql> alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' throttle;
  2. Query OK, 1 row affected (0.00 sec)

From this point on, the migration will not make row copy progress and will not apply binary logs. By default, this command does not expire, and it takes an explicit unthrottle command to resume migration progress. Because MySQL binary logs are rotated, a migration may only survive a full throttling up to the point where the binary log it last processed is purged.

You may supply either or both these options: expire, ratio:

  • alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' throttle expire '2h' will fully throttle the migration for the next 2 hours, after which the migration resumes normal work. You may specify these units: s (seconds), m (minutes), h (hours) or combinations. Example values: 90s, 30m, 1h, 1h30m, etc.
  • alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' throttle ratio 0.7 will partially throttle the migration. This instructs the throttler to reject, on average, 7 migration throttling check requests out of 10. Any value between 0 (no throttling at all) and 1.0 (fully throttled) are allowed. This is a fine tune way to slow down a migration.

Throttling all migrations

It’s likely that you will want to throttle migrations in general, and not a specific migration. Use:

  • alter vitess_migration throttle all; to fully throttle any and all migrations from this point on
  • alter vitess_migration throttle all expire '90m'; to fully throttle any and all migrations from this point on and for the next 90 minutes.
  • alter vitess_migration throttle all ratio 0.8; to severely slow down all migrations from this point on (4 out of 5 migrations requests to the throttler are denied)
  • alter vitess_migration throttle all duration '10m' ratio 0.2; to lightly slow down all migrations from this point on (1 out of 5 migrations requests to the throttler are denied) for the next 10 minutes.

Unthrottling

Use:

  • alter vitess_migration 'aa89f255_8d68_11eb_815f_f875a4d24e90' unthrottle; to allow the specified migration to resume working as normal
  • alter vitess_migration unthrottle all; to unthrottle all migrations.

Note that this does not disable throttling altogether. If, for example, replication lag grows on replicas, the throttler may still throttle the migration until replication is caught up. Unthrottling only cancels an explicit throttling request as described above.

Showing throttled apps

The command show vitess_throttled_apps; is a general purpose throttler command, and shows all apps for which there are throttling rules. It will list any specific or general migration throttling status.