概要
業務を行なっていると、開発環境とステージング環境で差分を取りたい等二つの異なるDBでの差分を取りたいという事は発生するかと思います。この時にスキーマの差分を取るツールはMySQLWorkBench等色々とあるのですが、データまで差分を取る簡単な手法が調べた限りでは見つからず、この記事ではデータまで含めた差分の取り方を説明します。
この記事のソース一式は以下に入っておりますので、すぐに利用されたい方やソースを追いたい方は活用ください。
https://github.com/gosagawa/mysqldbcompare-docker
やりたい事
要件としては以下となります。
- 二つのDBのスキーマの差分を取りたい
- データの差分を取るテーブルは指定したい
- トランザクションテーブルは必ず差分が出るので、マスタテーブルのみを比較したいため
- 差分をSQLで出力したい
- それを流せば差分が修正されるというようにしたいため
- DBはMysql5.7
解決までの経緯
mysqldbcompareの利用と、ローカル実行時の問題
「MySQL db差分」等で検索するとスキーマの比較までできるツールはよくあるものの、データ比較ができるツールはあまりなくmysqldbcompareしか見つけられませんでした。MySQL8系でなければ動きそうだったので、こちらのツールを検討することにしました。
しかし、このツールはpythonで書かれた少し古いツールのようで、ローカル環境(mac)で動かすと環境依存の問題が多々発生します。
[~] % mysqldbcompare
Traceback (most recent call last):
File "/usr/local/bin/mysqldbcompare", line 28, in <module>
from mysql.utilities.common.tools import check_python_version
[~] % mysqldbcompare
(中略)
mysql.connector.errors.InternalError: Unread result found
pythonのバージョンを変えてみたり、ライブラリを入れ直したりエラーを一つ一つ潰そうとしたですが、検索してもあまり情報が少なく最終的にローカルで動かす事を断念しました。欲しい環境を作れれば解決する問題なので、Dockerで環境を作ることにしました。
コンテナの作成
以下のように、必要なコンテナを作ります。
FROM centos:centos7
RUN yum -y install https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
RUN yum -y install mysql-server
RUN yum -y install mysql-utilities
RUN yum -y install mysql-connector-python
RUN yum -y install openssh-clients
RUN ln -s /usr/lib/python2.7/site-packages/mysql/utilities /usr/lib64/python2.7/site-packages/mysql/utilities
version: '3'
services:
web:
build: .
ports:
- "8080:8080"
volumes:
- .:/repo
depends_on:
- db
tty: true
privileged: true
db:
image: mysql:5.7
command: mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci
volumes:
- ./mysql/data:/var/lib/mysql
restart: always
environment:
MYSQL_ROOT_PASSWORD: root
コンテナ内で実行
あとはコンテナ内でmysqldbcompareを実行すれば良いのですが、実際に比較を行う際、
作りとして一度mysqldumpして、dbに格納してから差分を取るようにしています。
これは必要なデータのみ差分比較を行うためで、mysqldumpをする際に対象を絞っています。
また実際の業務上はテーブルのカラムに作成日と更新日が含まれてしまっており、これが運用上含まれてしまうので取り除きたいという要件もあったためそれをsedで無理やり取り除くという事をやってます
# カラムに作成日時や更新日時が含まれていたら以下のようにして消す
# cat repo/dump/dev.dump | sed "s/'[^']*','[^']*');$/'2019-01-01 00:00:00','2019-01-01 00:00:00');/" > /tmp/dev.dump
# cat repo/dump/stg.dump | sed "s/'[^']*','[^']*');$/'2019-01-01 00:00:00','2019-01-01 00:00:00');/" > /tmp/stg.dump
Makefileやらshellやらをまとめ、最終的に
% docker-compose up -d
% make dump_all
% make ready
% make compare_dev_stg
という4コマンドで、以下のように出力したいデータ差分を出す事ができました！
% make compare_dev_stg
docker-compose exec web bash repo/compare.sh dev stg
compare success. file -> result/result_dev_stg_20190904_062508.txt
% cat result/result_dev_stg_20190904_062508.txt
# WARNING: Using a password on the command line interface can be insecure.
# server1 on db: ... connected.
# server2 on db: ... connected.
# Checking databases sample_test on server1 and dbcompare_dev on server2
#
# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE xxxxx pass pass -
# - Compare table checksum FAIL
# - Find row differences pass
# TABLE yyyyy FAIL FAIL -
# - Compare table checksum FAIL
# - Find row differences FAIL
#
# Transformation for --changes-for=server1:
#
(ALTER文文)
# Row counts are not the same among `sample_test`.`yyyy` and `dbcompare_dev`.`yyyyy`.
#
# Transformation for --changes-for=server1:
#
# Data differences found among rows:
(INSERT文やUPDATE文)
# Database consistency check failed.
#
# ...done
所感
mysqldumpはコンテナ内でなくローカルで行ってしまっているのでそこは直す余地があります。（実際の業務ではトンネリングされたり、その際に認証を求められたりと自動化できなかったのでこのようになってます。）
あと、そもそもmysqldbcompare以外に普通に使えるツールってないのだろうか？というのが疑問です。同様の独自ツールを見た事がありますし、需要はありそうだと思うのですが。
もしもっと良い方があるのであれば、是非コメントいただければと思います。