Zenn
👈

小ネタ／MySQL でバージョンアップ時のデータ整合確認に mysqldump を使う

2022/07/07に公開
MySQL
バージョンアップ
移行
mysqldump
tech

データ移行作業そのものではなく、データ移行作業時の整合確認に mysqldump を使う小ネタです。

いつ使う？

MySQL（または RDS / Aurora などの互換サービス）でバージョンアップを行う際に、binlog あるいは DMS（CDC）などによって データを旧バージョンのサーバ → 新バージョンのサーバ（事前に立てておく）にリアルタイムレプリケーションし、バージョン切り替え時のダウンタイム短縮をはかる ケースがあります。

図：Aurora MySQL v1 → v3 バージョンアップに DMS(CDC)レプリケーションを使う例

その際、（サービスのダウンタイムとのトレードオフで）ある程度 データの整合確認をした上で新バージョンへ切り替えたい こともあるでしょう。

同じバージョンの MySQL サーバ間のテーブルデータ整合確認には CHECKSUM TABLE コマンドが使えますが、バージョンを跨ぐ場合はサーバの内部的なデータフォーマットの変更によってチェックサムに差異が生じることがあるので、必ず使えるわけではありません。
また、バージョンアップではありませんが、例えば文字セットを 3 バイトの utf8 から utf8mb4 に変更するケースでも使えません。

そんなときに今回の小ネタを使うと良いかもしれません。

どう使う？

① サイズが小さいテーブルや、重要度が高いテーブルを対象として mysqldump でデータをダンプする

  • mysqldump -u ユーザ名 -h サーバのアドレス／エンドポイント -p --default-character-set=デフォルト文字セット -t --skip-comments --max_allowed_packet=1G --hex-blob データベース名 [テーブル名] > 出力ファイル
    • できるだけデータベース（スキーマ）単位でまとめて出力しておくと、後の比較（ハッシュ値による）が楽

② サイズが大きいか、重要度がそれほど高くないテーブルは行数（カウント）と、新しいほうから一定行数のデータを SELECT でテキストファイルに書き出す

  • 行数（カウント） : SELECT COUNT(*) FROM データベース名.テーブル名
    • ただし MySQL 8.0 のマイナーバージョンによっては COUNT(*) のパラレル処理に不具合がある 関係で非常に遅くなるか停止してしまう可能性がある
      • Aurora MySQL 3.02.0（MySQL 8.0.23 がベース）でもこの問題が生じる模様で、CPU 使用率が 100% に達するとともに Aurora MySQL v1（MySQL 5.6.10a がベース）の数倍時間が掛かることがある
      • このようなケースに当たってしまったときは、別の方法で整合確認を行う
  • 新しいほうから一定行数のデータ : SELECT * FROM データベース名.テーブル名 ORDER BY ※主キー列など DESC LIMIT 行数
    • ※主キー列が AUTO_INCREMENT ならそれをそのまま使い、そうでなければ 登録日時列 DESC, 主キー列 DESC のようにする
    • テーブルに BLOB 列がある場合はその列をSHA2() 関数などでハッシュ化して書き出す

③ 実行時間を短縮するため、サーバ負荷が 100% に到達しない範囲でダンプ＆テキストファイル書き出しを並列で実行する

  • それぞれがほぼ均等な時間で出力完了するように

④ 最終的にこれらの結果のハッシュ値を MD5 や SHA2 で計算して新旧データの整合性確認

  • Linux のマシンからダンプを取る場合は、出力先のディレクトリに移動して md5sum * > 出力ファイル または sha224sum * > 出力ファイル
    • 新旧のハッシュ値を比較する
  • ハッシュ値の差異が見つかったら、（対象のファイルだけどちらか一方から他方へコピーして） diff で比較し差異があるテーブル・行を特定する
    • 容量より比較のしやすさを優先するのであれば mysqldump に --extended-insert または -e オプションを付けて出力する

注意点

① MySQL 5.6 ⇔ MySQL 8.0 の場合、8.0 クライアント付属の mysqldump で 5.6 サーバのデータをダンプできない

  • エラーを無視するオプションを付けて強制的に実行した場合、 TEXT 列の値がバイナリ値としてダンプされてしまうので比較ができない
  • そのため 2 台のクライアントマシンを用意し、MySQL 5.6 サーバには MySQL 5.6 クライアント付属の mysqldump を、MySQL 8.0 サーバには MySQL 8.0 クライアント付属の mysqldump を使う
    • このケースでは、GTID に関連する出力など一部不一致行が生じるので、MySQL 8.0 では以下のように sed で置換／行削除する
      • mysqldump -u ユーザ名 -h エンドポイント -p --default-character-set=デフォルト文字セット -t --skip-comments --max_allowed_packet=1G --hex-blob データベース名 [テーブル名] | sed -e '/^SET @/d' -e '5s/50503/40101/' > 出力ファイル

② ダンプを実行するマシンにはダンプ容量＋差異の比較時に必要な容量分のストレージが必要

③ 作業後のダンプファイル・出力ファイルは確実に削除する

  • うっかり放置すると情報漏洩のリスクが
  • （比較のしやすさとのトレードオフになるが）そのままファイル出力せずハッシュ化した状態でファイル出力するのも手

2022/10/15 追記：

④ 主キーのないテーブルをダンプすると、ダンプファイルに出力される行の順序が変わることがある

  • 常に同じ順序で出力されるわけではない（同じテーブルをダンプしても変化が生じることがある）
  • テーブルに主キーをつけるか、ダンプではなく一意の順序になるようORDER BYで列指定してSELECTしたものを比較する
GitHubで編集を提案

Discussion