MySQL移行の一般的な問題とデータ転送エラーのトラブルシューティング

MySQLの移行は、いくつかのよくあるパターンで失敗します。外部キーでインポートが停止する。文字が疑問符に変わる。MySQL 5.7からのダンプがMySQL 8.0にきれいにロードされない。データはロードされるが、ストアドルーチン、トリガー、ユーザー、SQLモードが期待通りに移行されなかったためにアプリケーションが壊れる。これらの問題はどれも珍しいものではありませんが、移行を一度きりのコピーではなく反復可能なプロセスとして扱うと、はるかに簡単に対処できます。

最善の移行習慣はリハーサルを行うことです。実際のバックアップを取得し、ステージングターゲットにリストアし、本番で使用予定の同じインポートコマンドを実行し、すべての警告を書き留めます。リハーサルにより、ダンプが完全かどうか、ターゲット構成が互換性があるかどうか、ロードに実際にどれくらいの時間がかかるかがわかります。また、「メンテナンスウィンドウ中に何とかする」よりも現実的なロールバック計画も得られます。

まずは障害の種類を特定する

移行が中断した場合、ランダムにサーバー変数を変更し始めないでください。エラーを以下のいずれかのカテゴリに分類します。

互換性: バージョンの違い、予約語、削除された機能、変更されたデフォルト。
エンコーディング: 文字セットと照合順序の不一致。
制約: 外部キー、一意キー、チェック制約、生成列。
オブジェクトカバレッジ: トリガー、ルーチン、イベント、ビュー、ユーザー、または権限の欠落。
パフォーマンス: インポートが遅すぎる、ディスクがいっぱい、バイナリログが増大している、インデックスに時間がかかりすぎる。
アプリケーション動作: データはインポートされたが、クエリや書き込みの動作が異なる。

この分類により、次に実行するコマンドが決まります。重複キーエラーと破損した絵文字はどちらも「移行の問題」ですが、その原因はまったく異なります。

バージョンの不一致: MySQL 5.7から8.0および類似のジャンプ

メジャーバージョンアップグレードでは、多くの驚きが発生します。MySQL 8.0では、5.7と比較してデフォルト、予約語、認証動作、データディクショナリ内部、オプティマイザ動作が変更されました。古い構文の中にはまだ動作するものもあれば、動作しないものもあります。MariaDBはさらに別の互換性レイヤーを追加します。これは、すべてのMySQL機能のドロップイン代替品ではないためです。

移行前に、ソース設定を取得します。

SHOW VARIABLES LIKE 'version';
SHOW VARIABLES LIKE 'sql_mode';
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

ターゲットでも同じチェックを実行し、比較します。sql_modeは特に注意が必要です。寛容なソースでロードされるダンプが、より厳格なターゲットでは、無効な日付、NOT NULL列のデフォルト値の欠落、ターゲットモードで受け入れられなくなったゼロ日付などのエラーで失敗する可能性があります。

次のようなエラーが発生した場合:

ERROR 1067 (42000): Invalid default value for 'created_at'

すぐにsql_modeを恒久的に緩和しないでください。まずテーブル定義とデータを検査します。デフォルトを修正したり、ゼロ日付を変換したり、アプリケーションの前提条件を更新したりする必要があるかもしれません。インポート中にソースのsql_modeを一時的に一致させると、段階的なリストアを完了するのに役立ちますが、本番環境は、アプリケーションがテストされた既知の明示的なモードに移行する必要があります。

予約語も古いスキーマを壊す可能性があります。rank、groups、またはその他の新しい予約語という名前の列またはテーブルは、引用符で囲むか名前を変更する必要がある場合があります。DDLでエラーが発生した場合は、ダンプから正確なステートメントを検査し、修正バージョンをターゲットでテストします。

認証プラグインの問題

アプリケーションの切り替えを含む移行は、クライアントが認証できないためにクエリが実行される前に失敗することがよくあります。MySQL 8.0はデフォルトでcaching_sha2_passwordを使用することが一般的ですが、古いクライアントはmysql_native_passwordを期待する場合があります。

ターゲットユーザーを確認します:

SELECT user, host, plugin FROM mysql.user;

通常は、クライアントライブラリまたはドライバーを更新する方が良い修正方法です。切り替え前にそれが不可能な場合は、一時的な互換性アカウントが必要になる場合があります:

ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'new_secret';

これは、一般的なベストプラクティスではなく、互換性の決定として扱ってください。認証設定はセキュリティに影響を与え、正しい答えはクライアントのバージョンとリスクモデルによって異なります。

文字セットと照合順序の問題

文字セットの問題は、インポートが正常に完了してもデータがすでに破損している可能性があるため、厄介です。典型的な症状は、?、文字化け、アクセント付き文字の破損、または絵文字を含む挿入の失敗です。

ソースデータベースとテーブル定義を確認します:

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = 'appdb';

列も確認します:

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = 'appdb'
  AND character_set_name IS NOT NULL;

ほとんどの最新アプリケーションでは、utf8mb4が適切なターゲット文字セットです。これは、絵文字を含む完全なUnicode範囲をサポートするためです。MySQLの古いutf8という名前は、古いバージョンでは完全なUTF-8と同じではありません。通常は3バイトの文字セットです。

ダンプおよびインポート時には、明示的に指定します:

mysqldump --default-character-set=utf8mb4 -u user -p appdb > appdb.sql
mysql --default-character-set=utf8mb4 -u user -p appdb < appdb.sql

ソースデータが実際にlatin1である場合、盲目的にutf8mb4と宣言して期待しないでください。まず、ソースエンコーディングでバイトが有効かどうかを判断します。一部の古いシステムには「二重エンコード」されたデータが含まれており、列はある文字セットを主張しているが、アプリケーションは別の文字セットのバイトを保存しています。これには、グローバルな検索と置換ではなく、テスト済みの変換が必要です。

照合順序の違いも動作を変える可能性があります。ソート順、一意性の比較、大文字小文字の区別は、照合順序によって異なる場合があります。移行中に一意インデックスが失敗した場合は、ターゲットの照合順序がソースでは等しくない2つの文字列を等しいと扱っていないか確認します。

外部キーの失敗

外部キーエラーは通常、次の4つのいずれかを意味します:

子テーブルが親テーブルより先にインポートされた。
ダンプが部分的で、参照行が欠落している。
ソースデータにすでに一貫性のない参照があった。
ターゲットスキーマがソースと異なる。

一般的な一括ロードの回避策は次のとおりです:

SET FOREIGN_KEY_CHECKS = 0;
-- データをインポート
SET FOREIGN_KEY_CHECKS = 1;

これは、信頼できるダンプからの完全な論理リストアには適切な場合があります。これはクリーンアップツールではありません。FOREIGN_KEY_CHECKSを再度有効にしても、多くの人が想定する方法ですべての既存の行が完全に再検証されるわけではないため、不良なリレーションシップをインポートして後で気付かない可能性があります。

データをマージしている場合やスキーマの一部のみをインポートしている場合は、可能な限りチェックを有効にしたままにして、最初に親テーブルをロードします。チェックを無効にする必要がある場合は、後で検証クエリを実行します。例:

SELECT c.*
FROM orders c
LEFT JOIN customers p ON p.id = c.customer_id
WHERE c.customer_id IS NOT NULL
  AND p.id IS NULL
LIMIT 20;

実際のリレーションシップ、特にorders、payments、accounts、permissionsなどの重要なテーブルに対して、このようなクエリを使用します。

重複キーエラー

重複キーエラーは、ターゲットにすでに受信データが挿入しようとしている値が存在することを意味します:

ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'

ターゲットが正確なコピーであるべき場合、通常はターゲットデータベースを削除して再作成し、再度インポートするのがクリーンな修正方法です。半分ロードされたターゲットは、プロセスが再開用に設計されていない限り、2回目の試行の良い出発点ではありません。

データをマージする場合は、インポート前に競合ポリシーを決定します。INSERT IGNOREは行をスキップして重複を隠します。REPLACE INTOは既存の行を削除して新しい行を挿入します。これにより、カスケードが発生したり、自動更新列が変更されたりする可能性があります。ON DUPLICATE KEY UPDATEはより明示的ですが、それでも注意深いルールが必要です。

移行の場合、マージにはステージングテーブルを使用することをお勧めします。受信データをstaging_*テーブルにロードし、競合を検査してから、意図的なINSERT ... SELECTまたはUPDATE ... JOINステートメントを作成します。設計には時間がかかりますが、データが静かに破棄されるのを防ぎます。

トリガー、ルーチン、イベント、ビューの欠落

テーブルと行が存在するため移行は成功したように見えても、重要なデータベースロジックが欠落している場合があります。mysqldumpオプションが重要です:

mysqldump -u user -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  appdb > appdb.sql

ビューとルーチンは、定義者アカウントが原因でインポートに失敗する可能性があります。ビューが次のように参照する場合があります:

DEFINER=`old_user`@`old_host`

そのアカウントがターゲットに存在しない場合、オブジェクトの作成に失敗するか、使用時に失敗する可能性があります。必要な定義者アカウントを適切な権限で作成するか、制御された移行プロセス中に定義者を調整します。アプリケーションのセキュリティモデルを理解せずに、盲目的に定義者を削除しないでください。

インポート後、オブジェクト数を比較します:

SELECT ROUTINE_TYPE, COUNT(*)
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'appdb'
GROUP BY ROUTINE_TYPE;

SELECT TRIGGER_SCHEMA, COUNT(*)
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'appdb'
GROUP BY TRIGGER_SCHEMA;

アプリケーションがスケジュールされたイベントに依存している場合は、それも確認します:

SHOW EVENTS FROM appdb;

遅いインポートと大きなテーブル

大規模なインポートは通常、ディスクI/O、インデックスメンテナンス、バイナリログ、外部キーチェック、またはトランザクションサイズによって制限されます。チューニングの前に、ターゲットを監視します:

iostat -xz 1
df -h
top

論理ダンプの場合は、拡張挿入を使用します。mysqldumpはほとんどの場合デフォルトでこれを行いますが、速度よりも人間が読める差分が必要でない限り、--skip-extended-insertを使用していないことを確認します。

InnoDBインポートの場合、ターゲットにメモリが利用可能であれば、より大きなinnodb_buffer_pool_sizeが役立ちます。OSがスワップを開始するほど高く設定しないでください。1回限りのロード中に、一部のチームはinnodb_flush_log_at_trx_commitなどの耐久性設定を一時的に緩和したり、インポートセッション中にバイナリログを無効にしたりします。これらの選択は、クラッシュリカバリまたはポイントインタイムリカバリを速度と引き換えにするため、既知のバックアップからインポートを再開できる場合にのみ使用する必要があります。

ターゲットがレプリケーションソースでもある場合は、バイナリログに注意してください。バイナリログを無効にするとインポートが高速化される可能性がありますが、ダウンストリームレプリカはそれらの変更を受信しません。レプリカがあるトポロジでは、ログをオフにする前に、インポートをどこで実行するか、変更をどのようにフローするかを決定します。

非常に大きなテーブルの場合は、プレーンなmysqldumpの代わりに、物理バックアップツールまたはMySQL Shellのダンプおよびロードユーティリティを検討してください。論理ダンプは移植性が高く、検査が簡単ですが、数百ギガバイトのデータセットに対して常に最速の方法であるとは限りません。

ディスク容量の失敗

移行中のディスク障害は一般的であり、回避可能です。ダンプファイル、インポートされたデータ、インデックス、一時ファイル、バイナリログ、場合によってはテーブルが再構築されている間の二重ストレージのためのスペースが必要です。

インポート前に確認します:

df -h
du -sh /var/lib/mysql

MySQL内で、テーブルサイズを確認します:

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_gb DESC;

ディスクがいっぱいになったためにインポートが失敗した場合、データディレクトリからランダムなファイルを削除しないでください。安全に空き容量を確保し、ターゲットが部分的にロードされているかどうかを検査し、最初から再開するかどうかを決定します。

移行後の検証

移行は、インポートコマンドが終了した時点で完了したわけではありません。結果を検証します。

重要なテーブルの行数から始めます:

SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM payments;

行数だけでは十分ではありません。ビジネスクリティカルなデータの合計またはチェックサムを比較します:

SELECT COUNT(*), SUM(total_amount), MIN(created_at), MAX(created_at)
FROM orders;

最終的な静定期間中に、ソースとターゲットで同じクエリを実行します。移行中に変更が続くテーブルの場合は、計画されたフリーズ、レプリケーションのキャッチアップ、またはアプリケーションレベルの調整を使用します。

切り替え前に、ターゲットに対してアプリケーションワークフローをテストします:

ログインとセッション作成。
コアレコードの作成と更新。
照合順序やインデックスに依存する検索とレポート。
バックグラウンドジョブ、トリガー、スケジュールされたイベント。
権限チェックと管理アクション。

アプリケーションテストは重要です。データベースは技術的にインポートされていても、動作的に間違っている可能性があるためです。

実用的な移行トリアージチェックリスト

移行エラーが発生した場合は、次の順序を使用します:

正確なエラーメッセージと、可能であれば失敗したSQLステートメントを保存します。
カテゴリを特定します: 互換性、エンコーディング、制約、オブジェクトカバレッジ、パフォーマンス、またはアプリケーション動作。
ソースとターゲットのMySQLバージョン、sql_mode、文字セット、照合順序を比較します。
制約エラーの場合は、特定の親子行または重複キーを検査します。
エンコーディングの問題の場合は、ソースバイトが有効かどうか、およびクライアント接続がそれらをどのように解釈しているかを把握するまで、インポートを停止します。
遅いインポートの場合は、ランダムな変数を変更する前に、ディスク、メモリ、バイナリログ、インデックスメンテナンスを確認します。
修正後、本番に適用する前にステージングで移行を再実行します。

最も信頼性の高いMySQL移行は、破棄して繰り返すことができるものです。コマンドをスクリプトに保持し、構成の変更を文書化し、検証を計画の一部にし、最後の希望的観測にしないでください。