mysqldumpでバックアップしたデータを別DBに安全に取り込む方法【IDずらし対応】

MySQLでデータ移行を行うとき、mysqldumpでバックアップを取得し、別のデータベースに取り込むことはよくあります。

しかし、移行先のテーブルに既にデータがあり、AUTO_INCREMENTのIDがバッティングするケースでは注意が必要です。

この記事では、dumpファイルを直接編集せずにIDをずらして安全に取り込む手順を解説します。

実運用でも迷いやすいポイントを整理しておくことで、再発時にスムーズに対応できるようになります。

今回のゴールと前提条件

• dumpファイルはmysqldumpで取得
• 移行元・移行先ともに同じテーブル構成
• 親子テーブルや外部キーは考慮せず、単一テーブルのみを対象にする
• mysqldumpでは--whereで抽出条件を指定するが、カラムはすべて含める

ゴールは、移行元のIDを指定したオフセット分だけずらして取り込み、衝突を回避することです。

なぜdumpファイルを直接編集しないほうがいいのか

dumpファイルのINSERT文をテキスト処理で書き換えることも可能ですが、以下の理由でおすすめしません。

• 複雑なエスケープ処理やバルクINSERT構文で文法エラーを起こしやすい
• 参照関係がある場合、親子テーブルの整合性を壊す可能性が高い
• 後で再現性のある運用が難しい

手順の全体像

作業は以下の流れで進めます。

1. ステージングテーブルを作成
2. dumpファイルをステージングにインポート
3. ステージングテーブルのIDをまとめてオフセット加算
4. ターゲットテーブルに INSERT ... SELECT で取り込み
5. AUTO_INCREMENT の最終確認

この方法なら、元データを壊さず安全に移行できます。

ステージングテーブルを使った安全な移行手順

prod という名前のDB(データベース)にmy_tableというテーブルがあると仮定します。

今回は同様に、stagingという名前のDBを作成し、そこにもmy_tableのテーブルを作成します。

本番などクリティカルな場面では、テーブル名をtmp_my_tableにするなど、間違いが起きないような構成にしておくといいですね。

ステージングテーブルを作成

CREATE DATABASE IF NOT EXISTS staging;

CREATE TABLE staging.my_table LIKE prod.my_table;

dumpをステージングに流し込む

mysql staging < dump.sql

オフセットを加算

移行先でIDが衝突しないよう、十分大きいオフセットを設定します。

USE staging;

SET @OFF := 1000000;  -- 例：100万ずらす

UPDATE my_table SET id = id + @OFF;

もちろん、ベタに数値を書いて足し込んでもいいと思います。

USE staging;

UPDATE my_table SET id = id + 1000000;

ターゲットテーブルに取り込み

INSERT ... SELECT でデータを移行します。

USE prod;

SET FOREIGN_KEY_CHECKS = 0; -- 外部キーがなければ不要

INSERT INTO my_table (id, col1, col2, ...)
SELECT id, col1, col2, ...
FROM staging.my_table;

SET FOREIGN_KEY_CHECKS = 1;

AUTO_INCREMENTの確認

MySQLでは通常、自動的に最大値＋1に更新されますが、挿入したIDが現在のAUTO_INCREMENTより小さい場合は変わらないことがあります。

確認コマンド：

SHOW TABLE STATUS LIKE 'my_table';

必要であれば手動で調整：

ALTER TABLE my_table AUTO_INCREMENT = 1234;

補足：AUTO_INCREMENTが追随しないケースについて

例：現在のAUTO_INCREMENTが100で、ID=50～60を挿入した場合

この場合、次回のINSERTは101から始まります。

もし次回を61から始めたい場合は、以下で調整が必要です。

ALTER TABLE my_table AUTO_INCREMENT = 61;

まとめ

• dumpファイルを直接編集せずにIDをずらすならステージングテーブルを活用するのが安全
• オフセットは衝突しない十分大きい値に設定
• AUTO_INCREMENTは通常自動更新されるが、小さい値を挿入した場合は手動で整える必要あり

この手順を覚えておけば、移行時のID衝突に慌てることなく、スムーズに対応できます。

saratoga

フリーランスエンジニア

XYouTube

仕事にも趣味にも IT を駆使するフリーランスエンジニア。技術的な TIPS や日々の生活の中で深堀りしてみたくなったことを備忘録として残していきます。