久しぶりの MySQL ネタです。
MySQL のプライベート DBaaS 的なものを作ろうとしています。要件としてはこんな感じ:
- 1回の操作で MySQL のデータベースと、そのデータベースを操作するユーザー(ID とパスワード)を新規作成する
- 1回の操作で上記データベースとユーザーを削除する
要は「データベースを使いたい」と思ったときに何か(スクリプトや API )を1度実行するとデータベースを作り、そのデータベースにアクセスできるユーザーIDとパスワードを生成する、というのが最初の要件です。2つ目の要件は同様にしてそのデータベースやユーザーを1回の操作でまとめて削除する、というものです。これができると稼働中の MySQL サーバーに対してリクエストイベントベースでデータベースやユーザーを作成/削除できるようになり、MySQL のプライベート DBaaS を安価に構築できるかな、と思っています。
で、それを実現するシェルスクリプトを作ってみたので公開します:
https://github.com/dotnsf/scripts_for_mysql
【準備】
利用するには git clone などでスクリプトをダウンロードし、拡張子 .sh のファイルに実行権限をつけておきます:
README.md にも記載していますが、(特に MySQL 5.7 以降の場合は)事前要件としてパスワードポリシーを LOW に設定しておく必要があります。今回作成したシェルスクリプトではデータベースとアクセスユーザーを同時に作成するのですが、データベース名とユーザー名は同じランダムな文字列を、パスワードは独自に生成する別のランダムな文字列を使って生成します。この組み合わせはランダムに生成されるのですが、場合によってはパスワード文字列が MySQL の(5.7 以降で厳しくなった)パスワードポリシーに合わないことが原因によるエラーが発生する可能性があります。このエラーを回避するため、標準状態のパスワードポリシー(MEDIUM)を LOW に変更しておく必要があるのでした。その手順は以下になります(MySQL の再起動は不要です):
ここまでの準備ができていれば後述のシェルスクリプトを使ってデータベース/ユーザーの作成および削除がそれぞれ1回ずつのスクリプト実行で実現できます。
【実行方法】
データベースおよびユーザーを作成する場合は create_db.sh スクリプトを実行します。実行時に環境変数 MYSQL_ROOT_PASSWORD に MySQL の root ユーザーのパスワードを設定する必要があります。以下は同パスワードが P@ssw0rd である場合の設定例ですが、方法は以下3つのいずれでも:
(1)直接環境変数に指定してからスクリプトを実行する
(2)スクリプト実行時に指定する
(3)スクリプト内の変数に指定してから実行する
※プライベートな環境で使う場合は(3)でもいいと思います。
実行が成功すると以下のような結果が表示されます。上が作成されたデータベース名およびユーザー名、下がユーザーログイン時のパスワードです。パスワードはここで表示されたものを再度確認する手段がないので必ずメモしておきましょう。
この例であればデータベース名 f2fd22b01cefbe51 、ユーザー名 f2fd22b01cefbe51 、パスワード dY2fBxTmvMaTfuoM が作成されているので、この情報を使ってアクセスできることを確認します:
作成したデータベースおよびユーザーを削除する場合はその名称(上記であれば f2fd22b01cefbe51)を指定して drop_db.sh を実行します:
これでデータベースもユーザーも削除できました。
【応用】
基本的にはこれだけで最低限の機能は実装できていると思いますが、より便利に使うための応用方法をいくつか紹介しておきます。
まず現状はシェルスクリプトとしての実装なので外部システムから実行することはできません。REST API などにしておくと外部からも実行できて、その実行結果を HTTP レスポンスの形で返すこともできるのでより便利に使えるようになります。セキュリティ要件等も考慮した上で、問題ない場合はこちらのブログエントリを参考に REST API 化も検討ください。
また現在のスクリプトではローカルシステムからのみログイン可能なユーザーが作成されます。一方、DBaaS としての利用であれば外部システムからもログイン可能なユーザーを作成したいものです。外部からもログイン可能なユーザーを作成する場合は create_db.sh を以下のように変更してください:
※ create_db.sh 内の 'localhost' と書かれていた青字部分2箇所を '%' に変更して保存してください
これで作成したユーザー情報を使うと、外部システムからでもデータベースへのログインが可能になります。こちらもセキュリティ要件と相談の上で変更してください。
MySQL はしばらくメインで使っていない間にセキュリティ要件が少し面倒になった気がしました。このブログエントリ内でも書いたパスワードポリシー、これを恒久的に無効にする方法って、今はないんですかね。。
MySQL のプライベート DBaaS 的なものを作ろうとしています。要件としてはこんな感じ:
- 1回の操作で MySQL のデータベースと、そのデータベースを操作するユーザー(ID とパスワード)を新規作成する
- 1回の操作で上記データベースとユーザーを削除する
要は「データベースを使いたい」と思ったときに何か(スクリプトや API )を1度実行するとデータベースを作り、そのデータベースにアクセスできるユーザーIDとパスワードを生成する、というのが最初の要件です。2つ目の要件は同様にしてそのデータベースやユーザーを1回の操作でまとめて削除する、というものです。これができると稼働中の MySQL サーバーに対してリクエストイベントベースでデータベースやユーザーを作成/削除できるようになり、MySQL のプライベート DBaaS を安価に構築できるかな、と思っています。
で、それを実現するシェルスクリプトを作ってみたので公開します:
https://github.com/dotnsf/scripts_for_mysql
【準備】
利用するには 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 はしばらくメインで使っていない間にセキュリティ要件が少し面倒になった気がしました。このブログエントリ内でも書いたパスワードポリシー、これを恒久的に無効にする方法って、今はないんですかね。。