まだプログラマーですが何か?

プログラマーネタ中心。たまに作成したウェブサービス関連の話も https://twitter.com/dotnsf

タグ:database

久しぶりの MySQL ネタです。

MySQL のプライベート DBaaS 的なものを作ろうとしています。要件としてはこんな感じ:
- 1回の操作で MySQL のデータベースと、そのデータベースを操作するユーザー(ID とパスワード)を新規作成する
- 1回の操作で上記データベースとユーザーを削除する

要は「データベースを使いたい」と思ったときに何か(スクリプトや API )を1度実行するとデータベースを作り、そのデータベースにアクセスできるユーザーIDとパスワードを生成する、というのが最初の要件です。2つ目の要件は同様にしてそのデータベースやユーザーを1回の操作でまとめて削除する、というものです。これができると稼働中の MySQL サーバーに対してリクエストイベントベースでデータベースやユーザーを作成/削除できるようになり、MySQL のプライベート DBaaS を安価に構築できるかな、と思っています。

で、それを実現するシェルスクリプトを作ってみたので公開します:
https://github.com/dotnsf/scripts_for_mysql

2021051101



【準備】
利用するには git clone などでスクリプトをダウンロードし、拡張子 .sh のファイルに実行権限をつけておきます:
$ git clone https://github.com/dotnsf/scripts_for_mysql

$ cd scripts_for_mysql

$ chmod +x *.sh

README.md にも記載していますが、(特に MySQL 5.7 以降の場合は)事前要件としてパスワードポリシーを LOW に設定しておく必要があります。今回作成したシェルスクリプトではデータベースとアクセスユーザーを同時に作成するのですが、データベース名とユーザー名は同じランダムな文字列を、パスワードは独自に生成する別のランダムな文字列を使って生成します。この組み合わせはランダムに生成されるのですが、場合によってはパスワード文字列が MySQL の(5.7 以降で厳しくなった)パスワードポリシーに合わないことが原因によるエラーが発生する可能性があります。このエラーを回避するため、標準状態のパスワードポリシー(MEDIUM)を LOW に変更しておく必要があるのでした。その手順は以下になります(MySQL の再起動は不要です):
$ mysql -u root -p

> show variables like 'validate_password%';

> set global validate_password.policy=LOW;

> quit

ここまでの準備ができていれば後述のシェルスクリプトを使ってデータベース/ユーザーの作成および削除がそれぞれ1回ずつのスクリプト実行で実現できます。


【実行方法】
データベースおよびユーザーを作成する場合は create_db.sh スクリプトを実行します。実行時に環境変数 MYSQL_ROOT_PASSWORD に MySQL の root ユーザーのパスワードを設定する必要があります。以下は同パスワードが P@ssw0rd である場合の設定例ですが、方法は以下3つのいずれでも:

(1)直接環境変数に指定してからスクリプトを実行する
$ export MYSQL_ROOT_PASSWORD=P@ssw0rd

$ ./create_db.sh

(2)スクリプト実行時に指定する
$ MYSQL_ROOT_PASSWORD=P@ssw0rd ./create_db.sh

(3)スクリプト内の変数に指定してから実行する
$ vi create_db.sh

:
:
# MySQL root Password
MYSQL_ROOT_PASSWORD=P@ssw0rd (行頭のコメント記号 # を削除してパスワードを指定)
:
:
$ ./create_db.sh

※プライベートな環境で使う場合は(3)でもいいと思います。

実行が成功すると以下のような結果が表示されます。上が作成されたデータベース名およびユーザー名、下がユーザーログイン時のパスワードです。パスワードはここで表示されたものを再度確認する手段がないので必ずメモしておきましょう。
$ MYSQL_ROOT_PASSWORD=P@ssw0rd ./create_db.sh

mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
USERNAME = DBNAME = 'f2fd22b01cefbe51'
PASSWORD = 'dY2fBxTmvMaTfuoM'

この例であればデータベース名 f2fd22b01cefbe51 、ユーザー名 f2fd22b01cefbe51 、パスワード dY2fBxTmvMaTfuoM が作成されているので、この情報を使ってアクセスできることを確認します:
$ mysql -u f2fd22b01cefbe51 -pdY2fBxTmvMaTfuoM f2fd22b01cefbe51

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.24 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

作成したデータベースおよびユーザーを削除する場合はその名称(上記であれば f2fd22b01cefbe51)を指定して drop_db.sh を実行します:
$ MYSQL_ROOT_PASSWORD=P@ssw0rd ./drop_db.sh f2fd22b01cefbe51

mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
DB & USER: 'f2fd22b01cefbe51' DROPPED.

これでデータベースもユーザーも削除できました。


【応用】
基本的にはこれだけで最低限の機能は実装できていると思いますが、より便利に使うための応用方法をいくつか紹介しておきます。

まず現状はシェルスクリプトとしての実装なので外部システムから実行することはできません。REST API などにしておくと外部からも実行できて、その実行結果を HTTP レスポンスの形で返すこともできるのでより便利に使えるようになります。セキュリティ要件等も考慮した上で、問題ない場合はこちらのブログエントリを参考に REST API 化も検討ください。

また現在のスクリプトではローカルシステムからのみログイン可能なユーザーが作成されます。一方、DBaaS としての利用であれば外部システムからもログイン可能なユーザーを作成したいものです。外部からもログイン可能なユーザーを作成する場合は create_db.sh を以下のように変更してください:
   :
   :
# create database and user
mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "CREATE DATABASE ${USER} default character set utf8"
mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "CREATE USER ${USER}@'%' IDENTIFIED BY '${PASSWD}'"
mysql -uroot -p${MYSQL_ROOT_PASSWORD} -e "GRANT ALL PRIVILEGES ON ${USER}.* TO '${USER}'@'%'"
   :
   :

※ create_db.sh 内の 'localhost' と書かれていた青字部分2箇所を '%' に変更して保存してください

これで作成したユーザー情報を使うと、外部システムからでもデータベースへのログインが可能になります。こちらもセキュリティ要件と相談の上で変更してください。



MySQL はしばらくメインで使っていない間にセキュリティ要件が少し面倒になった気がしました。このブログエントリ内でも書いたパスワードポリシー、これを恒久的に無効にする方法って、今はないんですかね。。

最近、業務でブロックチェーンを使う機会が増えてきました。というか、提案段階のものも含めて大半の案件にキーワードとして出て来るようになっています(イノベーティブなアプリケーション開発に関わる部門に所属していることも関係しているとは思います)。ブロックチェーンを API 経由で使うだけでなく、その API を作ったり、データのモデリングをしたりする機会も出てきました。

まだまだ勉強段階ではありますが、実際にお客様と会話している中でも作る前から「これはブロックチェーンに向いてるなあ/向かないなあ・・・」と感じることができるようになってきました。今日のエントリはそういう話です。

ブロックチェーンは革新的な技術で注目されている一方で、まだわかりにくい部分もあり、誤解を受けている部分もあります。典型的なパターンが「現在動いている○○システムのデータベース部分をブロックチェーンに置き換えて信頼性を向上させる」というものです。まさにブロックチェーンの強みをいかしたシステム改変のようにも聞こえますが、実現に向けては落とし穴も見受けられます。


ブロックチェーンは「分散台帳」と呼ばれる改竄の難しい仕組みでデータを格納します。その意味ではブロックチェーンはデータベースであるとみなすことはできますし、その中に格納されている情報の信頼性は非常に高いと言えます。ここがブロックチェーンの従来のデータベースに対する強みであり、こういった目的で使うにはブロックチェーンは向いていると考えます。

ただ一方で、従来のデータベース(ここではリレーショナルデータベースとしましょう)にもブロックチェーンと比較した時のメリットや得意分野があります。例えば SQL のような標準化されたクエリー言語による検索はリレーショナルデータベースの特徴であり、ブロックチェーンがこの機能を持っているわけではありません(例えばブロックチェーンの実装の1つである Hyperledger Fabric の場合、SQL ライクなクエリーを定義することはできますが、SQL とは異なるものです)。またトランザクション処理のパフォーマンスはまだまだリレーショナルデータベースの方が上と言わざるを得ません。そのような状況であるにも関わらず、興味以上の理由で「今使っているシステムのデータベースをブロックチェーンに単純に置き換える」ことは必ずしも正しくならない可能性があります。


・・・と、ここまでは「ブロックチェーン」を「NoSQL データベース」に置き換えても同じ話なので、NoSQL データベースが出現した頃にも同様の議論があり、特別目新しいことではないかもしれません。ただブロックチェーンの場合はブロックチェーン特有の事情を考慮する必要もあります。

リレーショナルデータベースと NoSQL データベース(やらメモリキャッシュやら検索エンジンやら・・)を比較する場合は、それぞれのデータベースの得意・不得意を見極めた上で適材適所に配置することが理想回答になると思います。例えば商品のコマースサイトであればこんな感じでしょうか?
2017083101

↑商品マスターと、その商品を分類するためのカテゴリーマスターが存在しているものとします。商品マスターと比べてカテゴリーマスターの情報は変更頻度が少ないため、アプリケーション実行時にメモリキャッシュに一括してロードし、実行時は高速なメモリアクセスだけで参照できるような設計にします。また商品情報は柔軟かつ高速な検索ができるよう、検索サーバーを用意します(定期的に情報を更新します)。またこのアプリケーションを使って発生した取引の情報も管理するものとします。

このシステムを一般的なデータベースを使って作る場合は↓このようになります。マスターをデータベースで持ち、商品情報の一部は検索サーバーに同期します。また取引が発生した際のログもデータベースに保存します。比較的一般的で、特別に珍しい点はないと思っています:
2017083102


さて、上記の構成を持ったシステムが現在動いているものとします。この現状のシステムに対して、例えば「商品情報や取引記録の正当性を保証する目的でブロックチェーン対応する」ことを考えてみます。上述のようにブロックチェーンには向き・不向きがあるのですが、例えば現状データベースで管理している「商品情報をブロックチェーン化する」ケースを考えてみます:
2017083103


↑単純に考えるとこんな感じです。データベースで管理していた商品マスターの情報をブロックチェーンに格納するというケースです。もちろんアプリケーションの書き換えは必要ですが、システム構成自体には問題なさそうに見え、商品マスターの情報がよりセキュアに守られるように見えます。

しかし実は問題点をはらんでいます。上述のように、今回のシステムでは商品情報は検索目的で検索サーバーと同期しています。端的にいうと「商品情報は2重管理されている」想定になります。商品マスターをブロックチェーン化して改竄を困難にしたつもりが、検索サーバーが管理している商品情報については何の対策もしていないことになるので、ブロックチェーンで管理しているケースと比較すると、検索サーバー側に弱点(というか盲点)が存在していることになってしまいます:
2017090100


一方でこのシステムを使って生成される取引情報をブロックチェーン管理にする、という部分はこの取引情報の改竄は非常に困難になるため、上述のような情報の正当性を保証する目的においては(トランザクション量などの考慮を除けば)「ブロックチェーン向き」と言える改良といえます:
2017083104


ただこれについても上述のように情報のクエリーなどデータベースが得意とする機能が重要視されるケースもあるでしょう。したがってブロックチェーンがフィットするかどうかという意味では単純にデータベースをブロックチェーンに置き換える、という考え方は危険で、「何を目的として、何をブロックチェーンで管理するのか」、「データベースの方が適している要素を無視していないか」という観点でシステムを設計する必要があります。


IBM Bluemix では IoT デバイスからのセンサー情報を使ったデータフローを定義する Node-RED エディタを提供しています。

特にセンサー情報はそのデバイス機器によって内容やデータフォーマットが異なるため、その内容をデータベースに保存しようと考えた場合はいわゆる NoSQL データベースを使ってテーブルを定義することなく JSON 丸ごと保存、なんてことをやったりします。これはこれで便利ですよね。

ただ保存するまではいいのですが、保存したデータを再利用する際には NoSQL だとクエリーが使えないため、その取り出しに不便に感じることもあります。その解決策の1つとしていったん Claudant に格納したデータを dashDB に複製する、という方法もあります:
Claudant => dashDB の単方向レプリケーション

そのような方法もありますが、もうデータフォーマットがわかりきっていて、NoSQL に格納する理由が特になければ最初からセンサーデータを SQL 型のデータベースに格納してしまってもいいわけです。本エントリではそんな方法のサンプルとして、Node-RED 内で SQL Database(DB2) にセンサーデータを格納する方法を紹介します。


まず Node-RED 側の用意をします。今回はすごくシンプルな形にして、IoT デバイスからのデータを一度整形して、そのままデバッグ出力するだけ、というものです:
2015051901


この中の整形部分(上記の "f" と書かれたノード)の中身は以下のようにしています。メッセージID とペイロードの名前、そして温度情報をそれぞれ ID, MYNAME, TEMP という名前で取り出しています。DB2 側で列名は大文字であることを前提とするため、JSON 側でもここを大文字にすることが肝です:
2015051902


この状態で一度デプロイして動かしてみます。ちゃんと動きますね:
2015051903


デバッグタブの出力内容は(期待通り)このようになっています。この内容を SQL Database のテーブルに格納していきます:
2015051904


では次に Bluemix 側の(SQL Database 側の)準備をします。Bluemix 上に作成した Node-RED プロジェクトランタイムで「サービスまたは API の追加」をクリックします:
2015051905


サービスの一覧から "SQL Database" を選んで追加します:
2015051906


追加が成功するとプロジェクトランタイムの中に SQL Database サービスが表示されます。このアイコンをクリックします:
2015051907


SQL Database の説明画面が表示されます。この中の "LAUNCH" ボタンをクリックしてウェブコンソールに移動します:
2015051908


ウェブコンソールが開いたら、Node-RED からのデータを格納するテーブルを定義する必要があります。そこで "Work with Tables" をクリックします:
2015051909


テーブル作業画面の左側にあるプラスマークをクリックしてテーブルを追加します:
2015051910


画面右のテキストエリアにテーブル作成の DDL を記述します:
2015051911


今回はこのような内容にしました。テーブル名は MYTABLE です:
CREATE TABLE MYTABLE
(
  ID VARCHAR(15),
  MYNAME VARCHAR(20),
  TEMP INT
);


最後に画面下部の "Run DDL" をクリックして、MYTABLE テーブルを作成します。成功すると MYTABLE テーブルが作成され、その定義内容を確認することができます:
2015051912


改めて Node-RED 画面で SQL Database の設定を追加します。ノード一覧の中から左側だけに丸のついた "sqldb"(左右に付いているのは更新用、左側だけのが追加用)を探して、キャンバスにドラッグして追加します:
2015051913


追加した "sqldb" ノードをダブルクリックし、そのサービスとテーブル名を定義します。テーブル名は先ほど作成した "MYTABLE" を指定します:
2015051914


最後にこのようにノードをつなぎます。これでデプロイ!:
2015051915


デプロイが成功するとデバッグタブにセンサー情報が表示されます。同時に SQL Database にも同じデータが格納されているはずです:
2015051916


SQL Database のウェブコンソールに戻り、MYTABLE テーブルの "Browse Data" タブを開くと追加(INSERT)されたレコードが表示されます。センサーデータが直接 DB2 に格納できたことが確認できました:
2015051917


あとは SQL を使って適当にご自由に。










 

このページのトップヘ