レプリケーション環境でデータの不整合が発生するのはよくあることでしょう。pt-table-checksumやpt-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つあります。
- departmentsテーブルで、どちらのサーバのテーブルにも既に存在していたUNIQUEキーを一度削除してからまた追加しています。これは時間とリソースの無駄です。
- 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を使うのがよいでしょう。
