[TiCDC MySQL DDLSink] reliable async add index when downstream is not TiDB

[zhangjinpeng87]

Is your feature request related to a problem?

Before #9701 all DDL event will block DML events and result a huge replication lag when TiCDC received a DDL event and execute it downstream and waiting for the DDL accomplish in downstream (MySQL/TiDB/Aurora). #9701 introduced asyncAddIndex if only the downstream is TiDB, asyncAddIndex use an asynchronous go routine to execute the ADD Index operations with retries. TiDB can works because TiDB will save DDL jobs and execute jobs even TiCDC crashed. There are two issues current TiCDC when replicate add index DDL event:

• If the downstream is MySQL, TiCDC's replication will be blocked by such time consuming adding index DDL event, and result in a large replication lag. https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html MySQL also support online ddl which means it has the possibility to run DMLs during adding index to reduce the replication lag.
• Even the downstream TiDB, if asynchronously submitting the add index event to TiDB encountered some errors, and the checkpoint is forwarded, this is a risk of losing this add index DDL event if the TiCDC crashed at that time.

In disaster recovery scenario, many users prefer to use MySQL/Aurora as the secondary database and using TiCDC to replicate data changes to these secondary database. In this scenario, the replication lag matters. If there is an add index DDL event, it will result in large replication lag which is not acceptable for this disaster recovery scenario.

Describe the feature you'd like

Introduce a reliable mechanism to persistent and execute add index and similar time consuming DDL events asynchronously, no matter the downstream is TiDB, or MySQL/Aurora MySQL. To eliminate the replication lag issue when there is an adding index event, and eliminate the risk of losing add index DDL event in some error cases.

Describe alternatives you've considered

No response

Teachability, Documentation, Adoption, Migration Strategy

No response