AR ホームベーカリー

オイラのアウトプット用ホームベーカリー!

MySQL のデータ移行前後で、データの正当性を担保できるのか?!

できるのか? その謎を解き明かすためスタッフはアマゾンのお口へと向かった……。

関係ないけど MacBook Air 2020 出ましたね。

想定するパターン

  • ネットワーク隔絶して現地作業しかできないオンプレミスサーバ
  • 現行の MySQL サーバを新しい筐体の MySQL サーバに移設する作業

つまり mysqldump してデータ移す前後で、「無事に成功してるよなあ、ああーん?」と相手にイチャモンつけられた時用です。(経験済み)

事前に知っておくこと

mysqldump を実行すると、出力された結果は OPTIMIZE されたものになります。

Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.

訳文: 最適化操作を実行する別の方法は、mysqldumpを使用してテーブルをテキストファイルにダンプし、テーブルを削除して、ダンプファイルからテーブルをリロードすることです。

参考: dev.mysql.com

つまり、定期的に OPTIMIZE を用いてメンテナンスしているような環境でない想定です(今回の想定するパターンだと無管理状態とします)。 これがわかってなくて、一回敗北しました……。

確認方法

  • MySQL のクエリに CHECKSUM TABLE というのがあるので、これで確認できる。

  • MySQL CLI からデータベースとテーブルのサイズを取得する、という方法もある

    • 以下に記述する内容によってほぼすべての場合で役に立たないので、ファイルサイズ比較は当てにしない方が良い

MySQL CLI からサイズを取得する

mysqldump を実行すると、 OPTIMIZE によるデフラグが実行された後に SQL が生成される。 そのため、移行前と移行後で以下のようなクエリでファイルサイズ取得すると、差分が生じて「アババババッ!!」となる。

データベース全体の容量を取得する SQL

  SELECT table_schema, SUM(data_length+index_length) /1024 /1024 
      AS MB
    FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length+index_length) DESC;

テーブル毎の容量を取得する SQL

USE データベース名;
  SELECT table_name, engine, table_rows AS tbl_rows,
         avg_row_length AS rlen,
         FLOOR((data_length+index_length)/1024/1024) AS allMB,
         FLOOR((data_length)/1024/1024) AS dMB,
         FLOOR((index_length)/1024/1024) AS iMB
    FROM information_schema.tables
   WHERE table_schema=database()
ORDER BY (data_length+index_length) DESC;

CHECKSUM TABLE による比較

CHECKSUM TABLE 構文は、以下のように SELECT 権限で実行されるとのことなので、フラグメンテーション対象の領域を利用しないはずです。

このテーブルに対する SELECT 権限が必要です。

参考: dev.mysql.com

こちらを用いる事で、 OPTIMIZE による未使用領域回収の影響を受けない、比較に利用できる値を取得できるはずです。 ということで、 mysqldump 実行前後で値を取って、これでなんとかしましょう。

クエリ実行例

  • users: ユーザ情報の入ったテーブル
USE データベース名;
mysql> CHECKSUM TABLE users;
+-------------------------------+------------+
| Table                         | Checksum   |
+-------------------------------+------------+
| example_test_grateful.users   | 1234567890 |
+-------------------------------+------------+
1 row in set (0.00 sec)