Checklist for Migrating from TiDB to Aurora MySQL

Applicability of This Document

Ready-to-Run Commands

-- 対象: TiDB（バージョンにより出力が異なる）
-- 権限: 接続権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能

-- 1) TiDB のバージョン（VERSION() はMySQL互換の文字列、TIDB_VERSION() はTiDB固有の詳細）
SELECT VERSION() AS mysql_compat_version;
SELECT TIDB_VERSION() AS tidb_version;

-- 2) トランザクションモードと分離レベル
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
    'tidb_txn_mode',
    'transaction_isolation',
    'tidb_constraint_check_in_place',
    'tidb_enable_clustered_index'
);

-- 3) 新しい照合順序フレームワークが有効かどうか
--    （無効な環境では utf8mb4_general_ci の比較挙動がMySQLと異なる）
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM mysql.tidb
WHERE VARIABLE_NAME = 'new_collation_enabled';

-- 対象: TiDB（全バージョン）
-- 権限: 対象スキーマのメタデータ参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能

-- 1) 定義そのものを出力し、AUTO_RANDOM / SHARD_ROW_ID_BITS /
--    CLUSTERED・NONCLUSTERED / PRE_SPLIT_REGIONS の有無を目視で確認する
SHOW CREATE TABLE SampleDB.sample_table;

-- 2) 主キーの構造を一覧で確認する（TIDB_PK_TYPE はTiDB独自列。
--    存在しない版ではエラーになるので、その場合は 1) を全テーブル分実行する）
SELECT TABLE_SCHEMA, TABLE_NAME, TIDB_PK_TYPE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'metrics_schema', 'sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- 3) 主キーの無いテーブル（移行後のCDCで問題になる）
SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.TABLES t
LEFT JOIN information_schema.KEY_COLUMN_USAGE k
       ON  k.TABLE_SCHEMA    = t.TABLE_SCHEMA
       AND k.TABLE_NAME      = t.TABLE_NAME
       AND k.CONSTRAINT_NAME = 'PRIMARY'
WHERE t.TABLE_TYPE = 'BASE TABLE'
  AND t.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'metrics_schema', 'sys')
  AND k.COLUMN_NAME IS NULL
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME;

-- 対象: TiDB（全バージョン）
-- 権限: 対象スキーマのメタデータ参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能

-- 1) 外部キー定義の有無（定義があっても実際に強制されているかは版に依存する）
SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME, UPDATE_RULE, DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
ORDER BY CONSTRAINT_SCHEMA, TABLE_NAME;

-- 2) 生成列（GENERATED COLUMN）
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, EXTRA, GENERATION_EXPRESSION
FROM information_schema.COLUMNS
WHERE GENERATION_EXPRESSION IS NOT NULL
  AND GENERATION_EXPRESSION <> ''
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- 3) パーティション定義
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION
FROM information_schema.PARTITIONS
WHERE PARTITION_NAME IS NOT NULL
ORDER BY TABLE_SCHEMA, TABLE_NAME, PARTITION_ORDINAL_POSITION;

-- 対象: TiDB（全バージョン）
-- 権限: information_schema の参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS                                            AS estimated_rows,
    ROUND(DATA_LENGTH  / 1024 / 1024 / 1024, 2)           AS data_gb,
    ROUND(INDEX_LENGTH / 1024 / 1024 / 1024, 2)           AS index_gb,
    TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'metrics_schema', 'sys')
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

# 対象: TiDB（Dumpling によるダンプ）
# 権限: ダンプ対象スキーマへの SELECT（TiDB側）
# 変更作業: なし（移行元は参照のみ）。ただし読み取り負荷がかかる
# Production 実行: 負荷を許容できる時間帯で実施すること

# まず1テーブルだけダンプして所要時間と出力サイズを実測する
tiup dumpling \
  --host 192.0.2.10 \
  --port 4000 \
  --user sample_user \
  --filetype sql \
  --threads 8 \
  --rows 200000 \
  --filter 'SampleDB.sample_table' \
  --output /var/tmp/dump-sample

# 出力サイズと所要時間を確認する（この実測値だけが計画の根拠になる）
du -sh /var/tmp/dump-sample

# 対象: TiCDC（TiDB側）または AWS DMS
# 権限: TiCDC の操作権限、または DMS の IAM 権限とエンドポイント資格情報
# 変更作業: あり（変更データ配信の構成）
# Production 実行: 本番ソースへの設定変更。事前検証と切り戻し手順を用意してから実施

# TiCDC で MySQL 互換のシンク（= Aurora MySQL）へ変更を流す例。
# CLI のオプション名はTiDB/TiCDCのバージョンで変わる（--pd と --server など）ため、
# 必ず自環境のバージョンのドキュメントで確認すること。
tiup ctl cdc changefeed create \
  --server "http://192.0.2.10:8300" \
  --changefeed-id "example-changefeed" \
  --sink-uri "mysql://sample_user@example-rds-endpoint:3306/"

# 作成後は状態と遅延を確認する
tiup ctl cdc changefeed list --server "http://192.0.2.10:8300"

How to Read the Results

When to Use This

• SQL that worked on TiDB produces an error in the Aurora MySQL test environment
• Continuity of ID generation changes after migration, conflicting with application assumptions
• The same index exists, but only after migration does the execution plan change and slow down
• Data that violates a foreign key already exists and cannot be loaded into the target
• The target's storage estimate diverges significantly from the source's reported value
• String comparison or sorting results changed before and after migration

Possible Causes (Most Likely First)

1. 01

   `AUTO_INCREMENT` allocation works differently

   Because TiDB allocates ID ranges in bulk to each node, IDs can be non-sequential. An application that assumes "higher ID = later insertion order" needs re-review regardless of which side it runs on. Since the exact behavior depends on version and configuration, measure sequentiality in your own environment.

2. 02

   MySQL has no equivalent of `AUTO_RANDOM`

   This is a TiDB-specific feature for avoiding hotspots. When migrating to Aurora MySQL, the value-generation scheme itself needs to be redesigned. Also check whether existing ID values can be carried over as-is.

3. 03

   The clustered-primary-key assumption differs

   InnoDB is clustered by the primary key by default, while TiDB lets you choose clustered or non-clustered. A non-clustered table's per-access-pattern performance characteristics change after migration.

4. 04

   Transaction behavior differs

   Optimistic transactions detect conflicts at commit time, while pessimistic transactions take locks at execution time. Which one was in use changes how lock waits and errors manifest after migration.

5. 05

   TiDB-specific syntax, system variables, or hints are in use

   System variables starting with `TIDB_`, TiDB-specific optimizer hints, and `ADMIN`-family administrative commands don't work on MySQL. Full-text search the application and batch SQL to find them.

6. 06

   The statistics and index selection mechanism differs

   Since the optimizer is a different implementation, the execution plan chosen can differ even with the same index configuration. Treat "the same SQL is now slow" after migration as expected, and plan to re-verify execution plans for representative queries on the target.

7. 07

   The storage structure differs

   TiKV keeps replicas and also compresses data. Since the assumptions differ from InnoDB, you cannot mechanically convert the source's usage into the target's required capacity.

Diagnostic Steps

1. 1

   Retrieve TiDB's version and key settings

   Read-only

   Check `TIDB_VERSION()`, `tidb_txn_mode`, and the state of the collation framework. Every subsequent decision is premised on this result.

2. 2

   Save `SHOW CREATE TABLE` for every table

   Read-only

   Some TiDB-specific attributes appear only in the definition statement. Save all of them and search for `AUTO_RANDOM` / `SHARD_ROW_ID_BITS` / `NONCLUSTERED`.

3. 3

   Full-text search the application SQL for TiDB-specific elements

   Read-only

   Search for `TIDB_`, custom hints, and `ADMIN` commands. These aren't visible from the DB side, so they need to be found in the code.

4. 4

   Test in a verification environment whether foreign keys are actually enforced

   Medium

   Run a violating INSERT and check whether it errors. This cannot be determined from the definition alone.

5. 5

   Test-load a representative table on the target and measure the capacity ratio

   Medium

   This measured value alone is the basis for estimating the target's storage.

6. 6

   Run representative queries on the target and compare execution plans and timing

   Medium

   There is no guarantee of the same performance as the source. Include time in the plan to handle queries that show a difference individually.

Solutions

Differences by Version and Environment

If This Doesn't Resolve It

• Check the application's driver and timeout settings

  Changing the connection target can change reconnection and timeout behavior.

• Decide where analytical workloads go

  If analytical queries were co-located on TiDB, Aurora alone may not provide the same performance characteristics. Consider a read replica or a separate platform.

• Re-measure batch processing parallelism and duration

  Since the storage structure changes, the same degree of parallelism is not guaranteed to be optimal.

• Rebuild operational procedures (backup, monitoring, privilege management) for the target

  TiDB-specific procedures cannot be used as-is. Include creating operational procedures in the migration plan.

• Check the conditions and procedure for rolling back

  Document, before the cutover, how far back you can roll back and what gets discarded if you do.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• How to check index differences between MySQL-compatible databases
• Want to know why AUTO_INCREMENT is not sequential in TiDB
• Data volume estimation when migrating from TiDB to RDS
• Does a TiDB foreign key behave the same as in MySQL

What the Risk Labels Mean

• Read-onlyDoes not change data or settings.
• LowImpact is limited, but permissions and load should be checked.
• MediumMay affect performance, locking, or cost.
• HighMay cause an outage, data loss, or require recovery work.
• Expert Review RequiredRequires a separate review before applying to production.

GIIP's Scope of Support

The migration decision itself can proceed just by filling out this article's checklist in your own environment. What's burdensome is the period after cutover spent continuously catching "problems that didn't show up on the source." For a period after cutover, GIIP tracks the execution time, execution plan, and error rate of representative queries alongside their pre-migration values, isolating only the ones that show degradation as something to act on. The migration work itself is decided by a person, while the ongoing comparison and initial investigation afterward are handled by an AI agent.

Related Services

• 移行の相談をする
• FDE運用（設計から運用まで）