CHECKSUM TABLE文を使用してテーブルの変化を検知する

はじめに

MySQLでダンプを投入した後に、テーブルに何かしらの変化があった際、それをどのように検知するのが良いかを調べたところ、CHECKSUM TABLE という文があったため、これを使用してテーブルの変更の検知を検証してみる。

環境

Windows 10 Pro (操作PC)
WSL2 (Ubuntu 22.04 LTS)
MySQL 8.1.0

準備

今回、検証するにあたって、Docker を使用する。
下記のリポジトリに、データベースの勉強用リポジトリとして色々入れているので一緒に試せるようにした。
https://github.com/katsuobushiFPGA/db-study

構築

上記のリポジトリをクローンしてビルドを実行する。

検証

上記のリポジトリを使用すると、test データベースに多くのテーブルができている。
まずは、CHECKSUM TABLE 文がどういうものかを知るために実行する。

CHECKSUM TABLEを使用する

下記のSQLを実行する。

USE test;
CHECKSUM TABLE customers;

すると下記の結果が得られる。

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CHECKSUM TABLE customers;
+----------------+-----------+
| Table          | Checksum  |
+----------------+-----------+
| test.customers | 824733811 |
+----------------+-----------+
1 row in set (0.00 sec)

チェックサムが計算され結果として取得できる。

この状態で、 customers テーブルに INSERT 文を実行してみる。
その後、チェックサムの計算を再度行う。

INSERT INTO customers (first_name, last_name, email, phone_number, address)
VALUES
    ('John', 'Doe', 'john.doe@example.com', '123-456-7890', '123 Main St');

CHECKSUM TABLE customers;

というわけで、下記のようにチェックサムの値が変わっている。

mysql> INSERT INTO customers (first_name, last_name, email, phone_number, address)
    -> VALUES
    ->     ('John', 'Doe', 'john.doe@example.com', '123-456-7890', '123 Main St');
Query OK, 1 row affected (0.02 sec)

mysql> CHECKSUM TABLE customers;
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| test.customers | 4067717611 |
+----------------+------------+
1 row in set (0.00 sec)

これを利用することで、テーブルに変化があったことなどが確認できる。

testデータベースと全く同じデータベースを作成し、チェックサムが一致するか

シチュエーションとして、下記を考えてみる。

mysqldump を利用して、新しいデータべース test2 に test データベースにあったテーブル郡を投入した。
しかし、心配性なのでtestとtest2 データベースのテーブルは本当に一致しているのか。これを確認したい。

準備として、まずはダンプを取得する。

ターミナルで下記を実行する。

docker compose exec db mysqldump -u root -proot test > test.dump

これでtest.dump ができた。
その後、下記を実行して test2 データベースを作成する。

docker compose exec db mysql -u root -proot -e "CREATE DATABASE test2"

そしてダンプを投入する。

docker compose exec -T db mysql -u root -proot test2 < test.dump

期待していたとおりに、同じテーブルを持つデータベース test2 ができた。

では、チェックサムを確認してみる。 testデータベースの customers テーブル

docker compose exec db mysql -u root -proot test -e "CHECKSUM TABLE customers;"

下記の結果が得られた。

+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| test.customers | 4067717611 |
+----------------+------------+

test2データベースの customers テーブル

+-----------------+------------+
| Table           | Checksum   |
+-----------------+------------+
| test2.customers | 4067717611 |
+-----------------+------------+

同じだ！
というわけで検証はOK!

データベースのテーブル全てに CHECKSUM TABLEを実行するシェルスクリプト

一つ一つコマンドを実行したら面倒くさいので、下記にシェルスクリプトを作成した。
これで、各テーブルのチェックサムがわかるようになる。

#!/bin/sh

DB_USER=root
DB_PASS=root
DB_NAME=test

tables=$(mysql -u $DB_USER --password=$DB_PASS --protocol=TCP $DB_NAME -e "SHOW TABLES")

for table in $tables
do
  { mysql -u $DB_USER --password=$DB_PASS --protocol=TCP $DB_NAME -e "CHECKSUM TABLE $table"; } 
done

下記のように出力が得られる。

$ ./checksum_tables.sh 

mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+----------+
| Table               | Checksum |
+---------------------+----------+
| test.Tables_in_test |     NULL |
+---------------------+----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+------------+
| Table      | Checksum   |
+------------+------------+
| test.clubs | 3519502283 |
+------------+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| test.customers | 4067717611 |
+----------------+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------+-----------+
| Table        | Checksum  |
+--------------+-----------+
| test.members | 276978510 |
+--------------+-----------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+------------+
| Table            | Checksum   |
+------------------+------------+
| test.prefectures | 3980074468 |
+------------------+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------+------------+
| Table                      | Checksum   |
+----------------------------+------------+
| test.reservation_histories | 1264663842 |
+----------------------------+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+------------+
| Table             | Checksum   |
+-------------------+------------+
| test.reservations | 1264663842 |
+-------------------+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+------------+
| Table      | Checksum   |
+------------+------------+
| test.rooms | 3984243807 |
+------------+------------+

参考

13.7.3.3 CHECKSUM TABLE ステートメント | MySQL:: MySQL8.0 https://dev.mysql.com/doc/refman/8.0/ja/checksum-table.html
Docker Desktop で起動した MySQL サーバーに、WSL(2) の MySQL CLI からアクセスできない？
https://dotnsf.blog.jp/archives/1079170344.html

おわりに

CHECKSUM TABLEを使ってテーブルの変化を検知するということを行った。
最近、動かないテーブル(登録、更新されないテーブル）が本当にされていないのかというのを検証したいなあと思って、手段を探したところこれに出会った。
MySQLUtilities という mysqldbcompare などがあったが、

を見たところ、MySQL Utilities はもう更新がないみたいだ…。
使いどころがいくつかあるので、これは試しておいてよかった。