インフラエンジニアでも覚えておくと嬉しいかもしれない MySQL の join と mysqldiff を試してみる。

ども、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 モジュール。

mysqldiff

残念ながらデータの差分までは見てくれないけど試してみたい。

インストール

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 を試してみる。」

インフラエンジニアでも覚えておくと嬉しいかもしれない MySQL の join と mysqldiff を試してみる。

インフラエンジニアに定年は無い

参考

メモ

動作確認準備

d1.t1 のレコード

d2.t1 のレコード

とりあえず left join

とりあえず left join して d1 と d2 の差分を確認

left もあるなら right も

mysqldiff を試す

mysqldiff とは

動作確認用のテーブルを作成

各テーブルの差分を確認

テーブル差分をマージ

ということで

アイレット新卒シリーズ Vol.66 24新卒 Google オフィスツアーレポート

【AWS re:Invent 2024】re:Invent に行った気になれるフォトギャラリー

AWS ベストプラクティスに沿っているか？ Well-Architected IaC Analyzer を魔改造してさらに便利にしてみた

X (旧 Twitter) を複数人で安全に運用する方法

Arduinoを初めて使う時のセットアップと実例集

インフラエンジニアでも覚えておくと嬉しいかもしれない MySQL の join と mysqldiff を試してみる。

インフラエンジニアに定年は無い

参考

メモ

動作確認準備

d1.t1 のレコード

d2.t1 のレコード

とりあえず left join

とりあえず left join して d1 と d2 の差分を確認

left もあるなら right も

mysqldiff を試す

mysqldiff とは

動作確認用のテーブルを作成

各テーブルの差分を確認

テーブル差分をマージ

ということで

関連記事Related Articles

【cloudpack 大阪 BLOG】MSPのシステム化について Hubotでの状態管理(mysql)とGoogleカレンダーとslack連携 その1

RDSのエンドポイントを"Route 53"の"Private DNS"にCNAMEで別名として定義するCloudFormationのテンプレートを作ってみた

RDS のリードレプリカを HAProxy で振り分ける際の死活監視について考えてみた

memsqlファーストインプレッション

RDS の MySQL と Aurora で SSL 接続するメモ

【cloudpack 大阪 BLOG】MSPのシステム化について Hubotでの状態管理(mysql)とGoogleカレンダーとslack連携その1