Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
9年弱前投稿 修正あり

mysqldiffでテーブル定義の違いをチェックする

MySQL Performance Blogの翻訳。MySQL Utilitiesに含まれるmysqldiffコマンドを使うと、サーバ間あるいは同一サーバ内でのテーブル定義の違いをチェックし、一致させるにはどうするべきかが分かる。使い方と簡単な仕組みの解説。

原文
Checking table definition consistency with mysqldiff (English)
原文ライセンス
CC BY-NC-SA
翻訳依頼者
D98ee74ffe0fafbdc83b23907dda3665
翻訳者
D98ee74ffe0fafbdc83b23907dda3665 doublemarket
原著者への翻訳報告
未報告


レプリケーション環境でデータの不整合が発生するのはよくあることでしょう。pt-table-checksumpt-table-syncを使ってそういった問題を解決する方法を解説した記事はたくさんあります。多くの場合、データの整合性には気を使うと思いますが、サポート窓口では、以下のような質問を受けることもよくあります。

サーバ間でテーブル定義が一致しているか調べる方法は?

レプリケーションをしていても、マスタスレーブ間で異なったテーブル定義にしておくことは可能です。例えば、スレーブでは参照クエリのためにインデックスが必要だけれども、マスタでは必要ないという場合が考えられるでしょう。しかし、テーブル定義の違いは修正しなければならない間違いであるということもあります。

OracleのMySQL Utilitiesに含まれているmysqldiffコマンドを使うと、こういった違いを発見しやすくなり、何を修正すべきかが分かります。この記事では、どのようにmysqldiffを使うのか、例を挙げて紹介します。

テーブル定義の相違を見つける

mysqldiffでは、同一サーバの異なるデータベースあるいは異なるサーバ間でのテーブルの違いをチェックして報告してくれます。以後の例では、server1とserver2ということなる2台のサーバ間でのテーブル定義の違いを探してみることにします。

コマンドは簡単です。

「test」データベースのテーブルを比較

mysqldiff --server1=user@host1 --server2=user@host2 test:test

データベース名が違う場合

mysqldiff --server1=user@host1 --server2=user@host2 testdb:anotherdb

テーブル名が違う場合

mysqldiff --server1=user@host1 --server2=user@host2 testdb.table1:anotherdb.anothertable

ここで、2サーバ間でのテーブル定義が一致していることを調べるとします。データベース名は「employees」としましょう。

# mysqldiff --force --server1=root:msandbox@127.0.0.1:21489 --server2=root:msandbox@127.0.0.1:21490 employees:employees
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 127.0.0.1: ... connected.
# server2 on 127.0.0.1: ... connected.
# Comparing `employees` to `employees`                             [PASS]
# Comparing `employees`.`departments` to `employees`.`departments`   [FAIL]
# Object definitions differ. (--changes-for=server1)
#
--- `employees`.`departments`
+++ `employees`.`departments`
@@ -1,6 +1,6 @@
 CREATE TABLE `departments` (
   `dept_no` char(4) NOT NULL,
-  `dept_name` varchar(40) NOT NULL,
+  `dept_name` varchar(256) DEFAULT NULL,
   PRIMARY KEY (`dept_no`),
   UNIQUE KEY `dept_name` (`dept_name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Comparing `employees`.`dept_emp` to `employees`.`dept_emp`       [PASS]
# Comparing `employees`.`dept_manager` to `employees`.`dept_manager`   [PASS]
# Comparing `employees`.`employees` to `employees`.`employees`     [FAIL]
# Object definitions differ. (--changes-for=server1)
#
--- `employees`.`employees`
+++ `employees`.`employees`
@@ -5,5 +5,6 @@
   `last_name` varchar(16) NOT NULL,
   `gender` enum('M','F') NOT NULL,
   `hire_date` date NOT NULL,
-  PRIMARY KEY (`emp_no`)
+  PRIMARY KEY (`emp_no`),
+  KEY `last_name` (`last_name`,`first_name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Comparing `employees`.`salaries` to `employees`.`salaries`       [PASS]
# Comparing `employees`.`titles` to `employees`.`titles`           [PASS]
Compare failed. One or more differences found.

少なくとも2つの違いがあることが分かります。1つ目はdepartmentsテーブル、2つ目はemployeesテーブルです。出力はdiffコマンドのものに似ています。デフォルトでは、1つ目の違いが発見された時点でツールの実行は終了します。この例では、全テーブルをチェックするまで実行を続けるように、--forceオプションを付けています。

実行結果から、departmentsテーブルのdept_name列が、server1ではvarchar(40)で、server2ではvarchar(256)なのが分かります。employeesテーブルについても、server2にはKEY (last_name, first_name)がある一方でserver1には存在しないことが分かります。ところでなぜserver1をベースに比較をしているのでしょうか。理由はここにあります。

# Object definitions differ. (--changes-for=server1)

つまり、表示されているdiffの結果はserver1に対するものなのです。server2を基本としてserver1側の違いを表示したい場合は、--changes-for=server2のオプションが必要になります。

diff表示が欲しいわけではなく、サーバに変更を加えるSQLクエリが必要な時もあるでしょう。そんな時は、--difftype=sqlオプションをコマンドに加えます。

# mysqldiff --force --difftype=sql --server1=root:msandbox@127.0.0.1:21489 --server2=root:msandbox@127.0.0.1:21490 employees:employees
[...]
# Comparing `employees`.`departments` to `employees`.`departments`   [FAIL]
# Transformation for --changes-for=server1:
ALTER TABLE `employees`.`departments`
  DROP INDEX dept_name,
  ADD UNIQUE INDEX dept_name (dept_name),
  CHANGE COLUMN dept_name dept_name varchar(256) NULL;
[...]
# Comparing `employees`.`employees` to `employees`.`employees`     [FAIL]
# Transformation for --changes-for=server1:
#
ALTER TABLE `employees`.`employees`
  DROP PRIMARY KEY,
  ADD PRIMARY KEY(`emp_no`),
  ADD INDEX last_name (last_name,first_name);

見ての通り、結果は完璧とは言えません。問題が2つあります。

  1. departmentsテーブルで、どちらのサーバのテーブルにも既に存在していたUNIQUEキーを一度削除してからまた追加しています。これは時間とリソースの無駄です。
  2. employeesテーブルで、プライマリキーを作成し直していますが、これも全く意味のないことです。

これに関してのバグ報告を出しましたが、いい勉強にもなります。確認なしに単にコマンドをコピペしてはならないということです。

mysqldiffはどのように動いているのか

ほとんどはINFORMATION_SCHEMAに対するクエリになっています。以下は、departmentsテーブルの違いをチェックする時の例です。

SHOW CREATE TABLE `departments`;
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, CHECKSUM, TABLE_COLLATION, TABLE_COMMENT, ROW_FORMAT, CREATE_OPTIONS
  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';
SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
         COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLUMN_KEY
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';
SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION,
         SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD,
         PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';
SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA,
         REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments' AND
        REFERENCED_TABLE_SCHEMA IS NOT NULL;
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, CHECKSUM, TABLE_COLLATION, TABLE_COMMENT, ROW_FORMAT, CREATE_OPTIONS
  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';
SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
         COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLUMN_KEY
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';
SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION,
         SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD,
         PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';

簡単に言うと、パーティション、行のフォーマット、照合順序、制約などを調べているわけです。

まとめ

目的ごとに色々なツールがあります。テーブルのデータの整合性チェックには、pt-table-checksumやpt-table-syncを使うべきですが、テーブル定義の違いをチェックするには、mysqldiffを使うのがよいでしょう。

次の記事
MySQLインデックスの基礎 : ひとつのテーブルに対するクエリの最適化法
前の記事
MySQLのPerformance Schemaでsetup_actorsに除外条件を設定する

Feed small 記事フィード

新着記事Twitterアカウント