ども、cloudpack の 自称インフラエンジニアかっぱ (@inokara) です。
インフラエンジニアに定年は無い
ので MySQL の join 位は挙動を抑えておきたいのでどさくさ紛れに動作確認を行ったのでその際のメモ。レプリケーションが狂ってしまった!等の時にマスターとスレーブの差分をチェックしなければいけないって状況になった時に役立ちそうな気がするけど…そんな事も知らんのかって怒られそうで怖い。
あと、mysqldiff という Perl 製のツールが紹介されていたので試してみる。
参考
メモ
動作確認準備
create database d1; use d1; create table t1(id int, user_id varchar(20)); create database d2; use d2; create table t1(id int, user_id varchar(20)); insert into t1(id, user_id) values(1, 100); insert into t1(id, user_id) values(3, 102); use d1; insert into t1(id, user_id) values(1, 100); insert into t1(id, user_id) values(2, 101); insert into t1(id, user_id) values(3, 102); insert into t1(id, user_id) values(4, 103); insert into t1(id, user_id) values(5, 104);
d1.t1 のレコード
mysql> select * from d1.t1; +------+---------+ | id | user_id | +------+---------+ | 1 | 100 | | 2 | 101 | | 3 | 102 | | 4 | 103 | | 5 | 104 | +------+---------+ 5 rows in set (0.00 sec)
d2.t1 のレコード
mysql> select * from d2.t1; +------+---------+ | id | user_id | +------+---------+ | 1 | 100 | | 3 | 102 | +------+---------+ 2 rows in set (0.00 sec)
とりあえず left join
d1.t1 を軸に d2.t1 を join
させる。d2.t1 にレコードが存在しない場合には該当するデータは NULL
となる。
mysql> select * from d1.t1 left join d2.t1 on d1.t1.user_id=d2.t1.user_id; +------+---------+------+---------+ | id | user_id | id | user_id | +------+---------+------+---------+ | 1 | 100 | 1 | 100 | | 2 | 101 | NULL | NULL | | 3 | 102 | 3 | 102 | | 4 | 103 | NULL | NULL | | 5 | 104 | NULL | NULL | +------+---------+------+---------+ 5 rows in set (0.00 sec)
ちなみに d2.t1 を軸に d1.t1 を join
させると以下のような状態になる。
mysql> select * from d2.t1 left join d1.t1 on d1.t1.user_id=d2.t1.user_id; +------+---------+------+---------+ | id | user_id | id | user_id | +------+---------+------+---------+ | 1 | 100 | 1 | 100 | | 3 | 102 | 3 | 102 | +------+---------+------+---------+ 2 rows in set (0.00 sec)
d2.t1 に存在しないレコードは検索結果として表示されない。
とりあえず left join して d1 と d2 の差分を確認
d2 に存在しない d1 のレコードを検索する場合。
mysql> select * from d1.t1 left join d2.t1 on d1.t1.user_id=d2.t1.user_id where d2.t1.user_id is null; +------+---------+------+---------+ | id | user_id | id | user_id | +------+---------+------+---------+ | 2 | 101 | NULL | NULL | | 4 | 103 | NULL | NULL | | 5 | 104 | NULL | NULL | +------+---------+------+---------+ 3 rows in set (0.00 sec)
上記のように join
するテーブル(d2.t1)は NULL
となるので検索条件として where d2.t1.user_id is null
を指定することで抽出することが出来る。 テーブル間の差分チェックに利用出来そう。
left もあるなら right も
d2.t1 を軸に join される為、d1.t1 にしかないレコードは検索結果として出力されない。
mysql> select * from d1.t1 right join d2.t1 on d1.t1.user_id=d2.t1.user_id; +------+---------+------+---------+ | id | user_id | id | user_id | +------+---------+------+---------+ | 1 | 100 | 1 | 100 | | 3 | 102 | 3 | 102 | +------+---------+------+---------+ 2 rows in set (0.00 sec)
今までフワッとしか知らなかった(使うことが無かった)MySQL の join
も実際に動かしてみるとフムフム。
mysqldiff を試す
mysqldiff とは
Perl で書かれた MySQL のテーブル定義の差分を解析して ALTER
構文を生成してくれる Perl モジュール。
残念ながらデータの差分までは見てくれないけど試してみたい。
インストール
tarball を wget
で取得して展開。
cd ~/src/ wget http://search.cpan.org/CPAN/authors/id/A/AS/ASPIERS/MySQL-Diff-0.43.tar.gz tar zxvf MySQL-Diff-0.43.tar.gz
MySQL-Diff-0.43/bin/mysqldiff
にライブラリパスを追加。
#!/usr/bin/perl -w use lib '~/src/MySQL-Diff-0.43/lib'; =head1 NAME
CentOS や Amazon Linux の場合には Slurp をインストールする必要があるので yum からインストール。
sudo yum -y install perl-File-Slurp.noarch
動作確認用のテーブルを作成
先ほどの join
動作確認を行った際に利用したデータベースをそのまま利用する。
use d1; create table t2(id int, user_id varchar(20)); use d2; create table t2(id int, user_id varchar(20), name varchar(20));
念の為に確認。
mysql> desc d1.t2; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | user_id | varchar(20) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> desc d2.t2; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | user_id | varchar(20) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
各テーブルの差分を確認
とその前に mysqldiff のヘルプを確認。
Usage: mysqldiff [ options ]Options: -?, --help show this help -A, --apply interactively patch database1 to match database2 -B, --batch-apply non-interactively patch database1 to match database2 -d, --debug[=N] enable debugging [level N, default 1] -o, --only-both only output changes for tables in both databases -k, --keep-old-tables don't output DROP TABLE commands -n, --no-old-defs suppress comments describing old definitions -t, --table-re=REGEXP restrict comparisons to tables matching REGEXP -i, --tolerant ignore DEFAULT, AUTO_INCREMENT, COLLATE, and formatting changes -h, --host=... connect to host -P, --port=... use this port for connection -u, --user=... user for login if not current user -p, --password[=...] password to use when connecting to server -s, --socket=... socket to use when connecting to server for only, where N == 1 or 2, --hostN=... connect to host --portN=... use this port for connection --userN=... user for login if not current user --passwordN[=...] password to use when connecting to server --socketN=... socket to use when connecting to server Databases can be either files or database names. If there is an ambiguity, the file will be preferred; to prevent this prefix the database argument with `db:'.
以下のようにデータベースホストとユーザー、比較するデータベースを指定して mysqldiff
を実行する。
$ ./mysqldiff -h localhost -u root -p d1 d2 ## mysqldiff 0.43 ## ## Run on Thu Feb 5 01:56:51 2015 ## Options: user=root, debug=0, host=localhost ## ## --- db: d1 (host=localhost user=root) ## +++ db: d2 (host=localhost user=root) ALTER TABLE t2 ADD COLUMN name varchar(20) DEFAULT NULL;
上記のように差分が ALTER
構文で出力されている。
d1 と d2 を逆にして実行すると以下のように出力される。
$ ./mysqldiff -h localhost -u root -p d2 d1 ## mysqldiff 0.43 ## ## Run on Thu Feb 5 01:58:12 2015 ## Options: user=root, debug=0, host=localhost ## ## --- db: d2 (host=localhost user=root) ## +++ db: d1 (host=localhost user=root) ALTER TABLE t2 DROP COLUMN name; # was varchar(20) DEFAULT NULL
テーブル差分をマージ
-A
オプションを付与して実行することで差分をマージする。
$ ./mysqldiff -h localhost -u root -p d1 d2 -A ## mysqldiff 0.43 ## ## Run on Thu Feb 5 01:59:14 2015 ## Options: apply, user=root, debug=0, host=localhost ## ## --- db: d1 (host=localhost user=root) ## +++ db: d2 (host=localhost user=root) ALTER TABLE t2 ADD COLUMN name varchar(20) DEFAULT NULL; Apply above changes to d1 [y/N] ?
上記のようにインタラクティブにマージが行われるので思わぬ事故発生は軽減されそう…と思ったら以下のように auth_args
というメソッドの呼び出しでエラー…。
## mysqldiff 0.43 ## ## Run on Fri Feb 6 08:03:27 2015 ## Options: apply, user=root, debug=0, host=localhost ## ## --- db: d1 (host=localhost user=root) ## +++ db: d2 (host=localhost user=root) ALTER TABLE t2 ADD COLUMN name varchar(20) DEFAULT NULL; Apply above changes to d1 [y/N] ? y Applying changes ... Can't locate object method "auth_args" via package "MySQL::Diff::Database" at ./mysqldiff line 200,line 1.
むむ、こちらの修正を施すと状況は進捗するものの未だにテーブルのマージ出来ていない。(なんでや)→後で調べよ…
ということで
今回得た知見としては…
- MySQL の join の動作を確認した
- mysqldiffというテーブル構造の差分を抽出するツールを使ってみたけど自分の手元だとテーブルのマージが動いていない(引続き調査)
お疲れ様でした。
元記事はこちらです。
「インフラエンジニアでも覚えておくと嬉しいかもしれない MySQL の join と mysqldiff を試してみる。」