Important

You are viewing the archived documentation of TiDB, which no longer receives updates. It is recommended that you use the latest LTS version of the TiDB database.

DM online-ddl-scheme

This document introduces the online-ddl-scheme feature of DM.

Overview

DDL statements are always used in the database applications. MySQL 5.6 and later versions support online-ddl, but there are limitations for usage. For example, to acquire the MDL lock, some DDLs still need to be copied. In production scenario, the table lock during DDL execution can block the reads or writes to and from the database to a certain extent.

By using gh-ost and pt-osc, DDLs can be executed on the MySQL database more gracefully, and the impact on reads and writes is reduced as much as possible.

TiDB is implemented based on the online asynchronous schema change algorithm of Google F1. It does not block reads and writes during the DDL execution. Therefore, the large amount of intermediate table data and binlog events generated by gh-ost and pt-osc in the process of online-schema-change is not needed during the replication from MySQL to TiDB.

For Data Migration (DM), which supports the data replication from MySQL to TiDB, the online-ddl-scheme feature is to perform special processing on the above two online-schema-change tools (gh-ost and pt-osc). This way, the required DDL replication can be completed more rapidly.

Configuration

In the task configuration file, online-ddl-scheme is at the same level of name. For example:

# ----------- Global configuration -----------
## ********* Basic configuration *********
name: test                      # The name of the task. Should be globally unique.
task-mode: all                  # The task mode. Can be set to `full`/`incremental`/`all`.
is-sharding: true               # Whether it is a task to merge shards.
meta-schema: "dm_meta"          # The downstream database that stores the `meta` information.
remove-meta: false              # Whether to remove the `meta` information (`checkpoint` and `onlineddl`) corresponding to the task name before starting the replication task.
enable-heartbeat: false         # Whether to enable the heartbeat feature.
online-ddl-scheme: "gh-ost"     # Only "gh-ost" and "pt" are currently supported.
target-database:                # Configuration of the downstream database instance.
  host: "192.168.0.1"
  port: 4000
  user: "root"
  password: ""                  # The password must be encrypted using dmctl if it is not empty.

For the advanced configuration and the description of each configuration parameter, refer to DM advanced task configuration file template.

online-schema-change: gh-ost

When gh-ost implements online-schema-change, 3 types of tables are created:

gho: used to apply DDLs. When the data is fully replicated and the gho table is consistent with the origin table, the origin table is replaced by renaming.
ghc: used to store information that is related to online-schema-change.
del: created by renaming the origin table.

In the process of replication, DM divides the above tables into 3 categories:

ghostTable: \_\*\_gho
trashTable: \_\*\_ghc, \_\*\_del
realTable: the origin table that executes online-ddl.

The SQL statements mostly used by gh-ost and the corresponding operation of DM are as follows:

Create the _ghc table:

Create /* gh-ost */ table `test`.`_test4_ghc` (
                        id bigint auto_increment,
                        last_update timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        hint varchar(64) charset ascii not null,
                        value varchar(4096) charset ascii not null,
                        primary key(id),
                        unique key hint_uidx(hint)
                ) auto_increment=256 ;

DM does not create the _test4_ghc table.

Create the _gho table:
```
Create /* gh-ost */ table `test`.`_test4_gho` like `test`.`test4` ;
```
DM does not create the _test4_gho table. DM deletes the dm_meta.{task_name}\_onlineddl record in the downstream according to ghost_schema, ghost_table, and the server_id of dm_worker, and clears the related information in memory.
```
DELETE FROM dm_meta.{task_name}_onlineddl WHERE id = {server_id} and ghost_schema = {ghost_schema} and ghost_table = {ghost_table};
```

Apply the DDL that needs to be executed in the _gho table:

Alter /* gh-ost */ table `test`.`_test4_gho` add column cl1 varchar(20) not null ;

DM does not perform the DDL operation of _test4_gho. It records this DDL in dm_meta.{task_name}\_onlineddl and memory.

REPLACE INTO dm_meta.{task_name}_onlineddl (id, ghost_schema , ghost_table , ddls) VALUES (......);

Write data to the _ghc table, and replicate the origin table data to the _gho table:

Insert /* gh-ost */ into `test`.`_test4_ghc` values (......);
Insert /* gh-ost `test`.`test4` */ ignore into `test`.`_test4_gho` (`id`, `date`, `account_id`, `conversion_price`, `ocpc_matched_conversions`, `ad_cost`, `cl2`)
  (select `id`, `date`, `account_id`, `conversion_price`, `ocpc_matched_conversions`, `ad_cost`, `cl2` from `test`.`test4` force index (`PRIMARY`)
    where (((`id` > _binary'1') or ((`id` = _binary'1'))) and ((`id` < _binary'2') or ((`id` = _binary'2')))) lock in share mode
  )   ;

DM does not execute DML statements that are not for realtable.

After the replication is completed, both the origin table and _gho table are renamed, and the online DDL operation is completed:
```
Rename /* gh-ost */ table `test`.`test4` to `test`.`_test4_del`, `test`.`_test4_gho` to `test`.`test4`;
```
DM performs the following two operations:
- DM splits the above rename operation into two SQL statements.
```
rename test.test4 to test._test4_del;
rename test._test4_gho to test.test4;
```
- DM does not execute rename to _test4_del. When executing rename ghost_table to origin table, DM takes the following steps:
  - Read the DDL recorded in memory in Step 3
  - Replace ghost_table and ghost_schema with origin_table and its corresponding schema
  - Execute the DDL that has been replaced
```
alter table test._test4_gho add column cl1 varchar(20) not null;
-- Replaced with:
alter table test.test4 add column cl1 varchar(20) not null;
```

Note

The specific SQL statements of gh-ost vary with the parameters used in the execution. This document only lists the major SQL statements. For more details, refer to the gh-ost documentation.

online-schema-change: pt

When pt-osc implements online-schema-change, 2 types of tables are created:

new: used to apply DDL. When the data is fully replicated and the new table is consistent with the origin table, the origin table is replaced by renaming.
old: created by renaming the origin table.
3 kinds of Trigger: pt_osc\_\*\_ins, pt_osc\_\*\_upd, pt_osc\_\*\_del. In the process of pt_osc, the new data generated by the origin table is replicated to new by the Trigger.

In the process of replication, DM divides the above tables into 3 categories:

ghostTable: \_\*\_new
trashTable: \_\*\_old
realTable: the origin table that executes online-ddl.

The SQL statements mostly used by pt-osc and the corresponding operation of DM are as follows:

Create the _new table:

CREATE TABLE `test`.`_test4_new` ( id int(11) NOT NULL AUTO_INCREMENT,
date date DEFAULT NULL, account_id bigint(20) DEFAULT NULL, conversion_price decimal(20,3) DEFAULT NULL,  ocpc_matched_conversions bigint(20) DEFAULT NULL, ad_cost decimal(20,3) DEFAULT NULL,cl2 varchar(20) COLLATE utf8mb4_bin NOT NULL,cl1 varchar(20) COLLATE utf8mb4_bin NOT NULL,PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

DM does not create the _test4_new table. DM deletes the dm_meta.{task_name}\_onlineddl record in the downstream according to ghost_schema, ghost_table, and the server_id of dm_worker, and clears the related information in memory.

DELETE FROM dm_meta.{task_name}_onlineddl WHERE id = {server_id} and ghost_schema = {ghost_schema} and ghost_table = {ghost_table};

Execute DDL in the _new table:

ALTER TABLE `test`.`_test4_new` add column c3 int;

DM does not perform the DDL operation of _test4_new. Instead, it records this DDL in dm_meta.{task_name}\_onlineddl and memory.

REPLACE INTO dm_meta.{task_name}_onlineddl (id, ghost_schema , ghost_table , ddls) VALUES (......);

Create 3 Triggers used for data replication:

CREATE TRIGGER `pt_osc_test_test4_del` AFTER DELETE ON `test`.`test4` ...... ;
CREATE TRIGGER `pt_osc_test_test4_upd` AFTER UPDATE ON `test`.`test4` ...... ;
CREATE TRIGGER `pt_osc_test_test4_ins` AFTER INSERT ON `test`.`test4` ...... ;

DM does not execute Trigger operations that are not supported in TiDB.

Replicate the origin table data to the _new table:

INSERT LOW_PRIORITY IGNORE INTO `test`.`_test4_new` (`id`, `date`, `account_id`, `conversion_price`, `ocpc_matched_conversions`, `ad_cost`, `cl2`, `cl1`) SELECT `id`, `date`, `account_id`, `conversion_price`, `ocpc_matched_conversions`, `ad_cost`, `cl2`, `cl1` FROM `test`.`test4` LOCK IN SHARE MODE /*pt-online-schema-change 3227 copy table*/

DM does not execute the DML statements that are not for realtable.

After the data replication is completed, the origin table and _new table are renamed, and the online DDL operation is completed:
```
RENAME TABLE `test`.`test4` TO `test`.`_test4_old`, `test`.`_test4_new` TO `test`.`test4`
```
DM performs the following two operations:
- DM splits the above rename operation into two SQL statements:
```
 rename test.test4 to test._test4_old; 
 rename test._test4_new to test.test4;
```
- DM does not execute rename to _test4_old. When executing rename ghost_table to origin table, DM takes the following steps:
  - Read the DDL recorded in memory in Step 2
  - Replace ghost_table and ghost_schema with origin_table and its corresponding schema
  - Execute the DDL that has been replaced
```
ALTER TABLE `test`.`_test4_new` add column c3 int;
-- Replaced with:
ALTER TABLE `test`.`test4` add column c3 int;
```

Delete the _old table and 3 Triggers of the online DDL operation:

DROP TABLE IF EXISTS `test`.`_test4_old`;
DROP TRIGGER IF EXISTS `pt_osc_test_test4_del` AFTER DELETE ON `test`.`test4` ...... ;
DROP TRIGGER IF EXISTS `pt_osc_test_test4_upd` AFTER UPDATE ON `test`.`test4` ...... ;
DROP TRIGGER IF EXISTS `pt_osc_test_test4_ins` AFTER INSERT ON `test`.`test4` ...... ;

DM does not delete _test4_old and Triggers.

Note

The specific SQL statements of pt-osc vary with the parameters used in the execution. This document only lists the major SQL statements. For more details, refer to the pt-osc documentation.