How to Plan a 3TB-Scale Database Migration

この文書の適用条件

そのまま実行できるコマンド

-- 対象: MySQL 5.7 / 8.0、Aurora MySQL 2.x / 3.x
-- 権限: information_schema の参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    ENGINE,
    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,
    ROUND(DATA_FREE          / 1024 / 1024 / 1024, 2)       AS free_gb,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS total_gb
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- スキーマ単位の合計（全体像の把握用）
SELECT
    TABLE_SCHEMA,
    COUNT(*)                                                     AS table_count,
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS total_gb
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY TABLE_SCHEMA
ORDER BY total_gb DESC;

-- 対象: SQL Server 2012 以降（RDS / Azure SQL Managed Instance を含む）
-- 権限: VIEW DATABASE STATE
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    SCHEMA_NAME(t.schema_id)                                            AS schema_name,
    t.name                                                              AS table_name,
    SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END)   AS row_count,
    SUM(ps.reserved_page_count) * 8.0 / 1024 / 1024                     AS reserved_gb,
    SUM(ps.used_page_count)     * 8.0 / 1024 / 1024                     AS used_gb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
        ON t.object_id = ps.object_id
GROUP BY t.schema_id, t.name
ORDER BY SUM(ps.reserved_page_count) DESC;

# 対象: MySQL 5.7 / 8.0、Aurora MySQL（mysqldump / mydumper）
# 権限: 対象スキーマへの SELECT
# 変更作業: なし（移行元は参照のみ）。ただし読み取り負荷がかかる
# Production 実行: 負荷を許容できる時間帯で実施すること

# 1) 単一テーブルで所要時間を実測する（--no-tablespaces は MySQL 8.0 で
#    PROCESS 権限を要求されるのを避けるため）
time mysqldump \
  --single-transaction \
  --quick \
  --no-tablespaces \
  --host example-rds-endpoint \
  --user sample_user \
  --password \
  SampleDB sample_table \
  | gzip > /var/tmp/sample_table.sql.gz

# 2) 並列ダンプで実測する場合（資格情報はコマンドラインに書かず設定ファイルで渡す）
mydumper \
  --defaults-file /etc/mysql/sample-migration.cnf \
  --database SampleDB \
  --threads 8 \
  --rows 500000 \
  --compress \
  --outputdir /var/tmp/dump-sample

# 3) 出力サイズを確認し、テーブルサイズとの比率を記録する
du -sh /var/tmp/dump-sample

-- 対象: MySQL 5.7 / 8.0、Aurora MySQL（移行元・移行先の双方で同じSQLを実行する）
-- 権限: 対象テーブルへの SELECT
-- 変更作業: なし（参照のみ）。ただし全行を読むため I/O 負荷がかかる
-- Production 実行: 停止中または低負荷時間帯に実施すること

-- 1) 行数の一致を確認する（最も軽い検証。ここが合わなければ先へ進まない）
SELECT 'sample_table' AS table_name, COUNT(*) AS row_count
FROM SampleDB.sample_table;

-- 2) 主要列の値までを含めた突き合わせ（同一エンジン間の比較に使う）
SELECT
    COUNT(*)                                                   AS row_count,
    SUM(CRC32(CONCAT_WS('|', id, name, DATE_FORMAT(updated_at, '%Y-%m-%d %H:%i:%s')))) AS crc_sum
FROM SampleDB.sample_table;

-- 3) テーブル単位のチェックサム（全行を読む。大きな表では時間がかかる）
CHECKSUM TABLE SampleDB.sample_table EXTENDED;

-- 対象: MySQL 5.7 / 8.0、Aurora MySQL
-- 権限: information_schema の参照権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能

-- 1) 直近に定義が変わったテーブル（凍結期間中に変更が入っていないかの確認に使う）
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_TIME, UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY CREATE_TIME DESC;

-- 2) 二次インデックスの一覧（ロード後に作り直す対象の洗い出し）
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS index_columns,
    MAX(NON_UNIQUE)                                 AS non_unique
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND INDEX_NAME <> 'PRIMARY'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;

結果の読み方

こういう状況で使います

• Asked "how many hours will it take" but unable to give a number backed by evidence
• A schedule has been set without ever rehearsing the migration
• Discussion of the cutover method has started before the acceptable downtime has been decided
• The initial load finished, but catching up on the difference never finishes
• Making the target's capacity equal to the source's ran out of space during the load
• It's undecided who decides to roll back, and when

考えられる原因（可能性の高い順）

1. 01

   Planning around a total figure like "3TB" without measuring actual size

   Even with the same total, the work involved is completely different between a single giant table and thousands of mid-sized tables. Pin down the per-object size and count first.

2. 02

   Estimating duration from a generic rule of thumb

   Transfer speed varies by orders of magnitude depending on network bandwidth, storage throughput, parallelism, presence of indexes, and compression. Applying someone else's figures to your own environment makes the plan itself unworkable.

3. 03

   The acceptable downtime was not decided first

   The cutover method should be decided based on acceptable downtime. If a long outage is acceptable, a full stop is the simplest and most reliable. Choosing a method without working backward from this leads to rework later.

4. 04

   Loading with indexes still attached

   A bulk load with secondary indexes kept in place triggers an index update on every row insert. Since duration differs from rebuilding them all at once after loading, compare both with a trial run.

5. 05

   Reaching cutover day without deciding the verification method

   "It looks like it's working" is not verification. How far to go with row counts, value comparison, and application-level checks needs to be agreed on in advance.

6. 06

   No point of no return has been defined

   Once writes to the new environment begin, a simple rollback is no longer possible. Spell out in the procedure exactly which point, once passed, leaves only forward progress as an option.

確認手順

1. 1

   Measure actual size and count per object

   参照のみ

   Run the sizing SQL above to pin down the number of tables, the size of the largest table, and what share of the total the top 10 represent.

2. 2

   Finalize the acceptable downtime with stakeholders

   参照のみ

   Before any technical discussion, decide how many hours the business can tolerate being down. Nothing else can be decided until this is set.

3. 3

   Run trials on representative tables with different characteristics

   中

   Measure duration on several tables selected for different traits: large, wide rows, containing BLOBs, many indexes, and so on.

4. 4

   Confirm the target's actual capacity with a trial load

   中

   It will not equal the source's usage. It can shrink from resolved fragmentation or grow from storage structure differences.

5. 5

   Measure the rate at which differences accumulate

   参照のみ

   If using a CDC approach, the volume of changes generated during the initial load determines how long catch-up takes. Measure the daily row-change count.

6. 6

   Run the rehearsal at least once with a production-equivalent procedure

   中

   Gaps in the runbook are found only in a rehearsal. This is also where you pin down the measured duration.

対応方法

バージョン・環境による違い

これで解決しない場合に確認すること

• Check the network path and its actual effective bandwidth

  Measure the actual effective bandwidth of the path between source and target (dedicated line, VPN, internet) by actually transferring data. Catalog figures are not enough to plan with.

• Check the target's temporary and log space

  During loading, the transaction log and temp space grow much larger than normal. Sizing based only on data space will fall short.

• Check the impact on the source while loading

  Read load can slow down the service side. Consider options such as reducing parallelism, splitting across time windows, or reading from a replica.

• Check how character set, collation, and time zone are handled

  If value representation changes across the migration, verification will show mismatches. Decide the standard before migrating.

• Include migrating privileges, connection info, and monitoring settings in the plan

  Moving the data doesn't automatically move users, privileges, monitoring, or backup settings. Include time for this work in the schedule too.

• Decide on stakeholder notification and a point of contact

  Notify people in advance of the freeze period, cutover time, scope of impact, and who to contact. Most of the confusion on the day can be prevented here.

この文書の根拠と限界

よくある質問

この文書がカバーする質問

• How to estimate the time a large-scale database migration will take
• How to choose a cutover method for a database migration
• How to verify data consistency after a migration
• How to estimate the target's storage capacity

リスク表示の意味

• 参照のみデータと設定を変更しません。
• 低影響は限定的ですが、権限と負荷の確認が必要です。
• 中性能・ロック・コストに影響する可能性があります。
• 高障害・データ損失・復旧作業が発生する可能性があります。
• 専門家レビュー必須本番適用前に別途レビューが必須です。

GIIPの対応範囲

The migration plan itself can be built up through repeated measurement and review. What's easy to miss in practice is the weeks after cutover. GIIP saves key metrics collected before cutover (representative query execution time, error rate, storage usage trend) as a baseline, and tracks the same metrics side by side after cutover to catch any drift. Only items that show a difference are isolated for action, with day-to-day comparison and initial investigation handled by an AI agent, while the decision to roll back or change the configuration is made by a person.

関連サービス

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