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

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

タグ:mysql

先日、このようなブログを書きました:
"Image to Excel" を REST API 化(xlsx-js-style の使い方)


xlsx-js-style というライブラリを使って、ネットでプチバズった Image to Excel というツール(を REST API 化したもの)を独自実装した、という紹介エントリでした。

この REST API を作る上で xlsx-js-style というエクセルシートやエクセルブックファイルを Node.js で作成・編集するライブラリを使いました。Image to Excel を作るだけであればセルのサイズや背景色を設定する機能を使えばできるので(セルのテキストを読み書きする必要はなかったので)さほど難しくありませんでした。

というわけで、その勉強の続きという意味も含め、より実践的なセルの読み書きを伴うサービスを作ってみようと思い立ち、苦労しながらブログネタになりそうなものを作ることができたので紹介させていただきます。


【作ったサービスの内容】
一言でいうと「データベースとエクセルの相互変換ツール」です。データベースをエクセルファイルに、エクセルファイルはデータベースに、それぞれ変換して記録(保存)するツール。正確にはツールというよりも REST API として実装しているので、別の外部アプリケーションから使うことを想定しています(Swagger API の UI は標準で用意したので、単体でも動かしてみせることはできます)。

作ったサービスをもう少し詳しく説明します。まず「データベースをエクセルにする」部分から、現時点では PostgreSQL か MySQL だけで動くのですが、こんな感じのデータベース接続文字列を指定して API を実行します:
 (PosgreSQL の場合)postgres://username:password@servername:port/dbname
 (MySQL の場合)  mysql://username:password@servername:port/dbname


PostgreSQL の場合は最初のプロトコル名部分を "postgres" に、MySQL の場合は "mysql" と指定します。"username:password" 部分は接続する際のユーザー名(username)とパスワード(password)を指定します。servername はデータベースサーバー名(または IP アドレス)で、port は接続ポート番号です(PostgreSQL のデフォルト値は 5432 、MySQL のデフォルト値は 3306 です)。そして dbname は接続先のデータベース名を指定します。

例えば localhost の 5432 番ポートで動いている PostgreSQL サーバーの mydb データベースに、ユーザー名 user1 、パスワード pass1 で接続する場合は以下のようなデータベース接続文字列が必要となります:
 postgres://user1:pass1@localhost:5432/mydb


話を戻します。このようなデータベース接続文字列を指定して REST API を実行すると、以下のルールでデータベース内容をまるごと1つのエクセルファイルに変換します:
・データベース内の全テーブルと、各テーブル内のレコードデータを取り出す
・各テーブルが1枚のエクセルシート(タブ)になる テーブル名がシート名になる
・シートの1行目にテーブルの全列が書き出される
・シートの2行目以降にテーブル内の全レコードデータが1行ずつ格納される(ただし BLOB などのバイナリデータは無視する)
・処理が完了すると、(データベース名).xlsx という名前のエクセルファイルでダウンロードできる

という誰得(?)な処理を行います。 もう1つの「エクセルをデータベースにする」は基本的にこの逆の処理を行います:

・実行時のパラメータとして(上述の処理と同様に)データベース文字列を指定する
・併せてエクセルファイルをアップロードする形で REST API を実行する
・エクセルファイル内の全シートを1つのテーブル内容としてデータベースに格納する
 ・テーブル名はシート名
 ・各シートの1行目にテーブルの列名が格納されているとみなす
 ・各シートの2行目以降にテーブルのレコードデータが1行ずつ格納されているとみなし、
  テーブルに1行ずつ追加される(ただし BLOB などのバイナリデータは無視する)
・REST API 実行時のパラメータにより各シートの2行目にある情報はレコード情報ではなく、各列の定義情報とみなすこともできる。その場合、3行目以降にテーブルのレコードデータが格納されているとみなす。
・同様にして REST API 実行時のパラメータによりシート名の既存テーブルを削除(drop table)してから実行するか、新規作成(create table)してから実行するかを選択することができる
・処理が完了するとエクセル内の各シートに定義されていた情報がまるごとデータベース化される


【ソースコード】
作成したサービスのソースコードをこちらで公開しています:
https://github.com/dotnsf/db2xlsx


実際にネット上のどこかでサービスとして公開することも考えたのですが、現実問題としてデータベースがインターネット上に公開されていないと使えないので(普通、公開されていないはずなので)、公開しても使えるものではないと判断しています。 そういうこともあってのソースコードでの公開としました。

サービスとして自分の PC で使ってみたい場合は、Git と Node.js がインストールされた環境で以下の手順を実行してください:

(1)ターミナルやコマンドプロンプトなどの端末を開く

(2)ソースコードをダウンロード
$ git clone https://github.com/dotnsf/db2xlsx


(3)必要なライブラリをインストール
$ cd db2xlsx

$ npm install


(4)実行(終了時は Ctrl+C)
$ npm start


ただ単にサービスとして実行しても対象のデータベースやエクセルファイルが無いと使えないので、より具体的な手順を次のセクションで紹介します。


【Docker と Swagger API で実際に使う手順】
このサービスを実際に使うにはインポートやエクスポートの対象となるデータベースや、エクセルファイルが必要です(そういうサービスです)。エクセルファイルはサンプルを用意できるのですが、データベースはそうはいきません。 実際に使える PostgreSQL や MySQL のデータベースがあればそれをそのまま使っていただいてもいいのですが、そういう人も多くはないと思うので、以下に Docker を使ってデータベースを用意する手順を紹介します。というわけでまずは Docker(個人利用であれば Docker Desktop がお奨めですが、業務利用の場合は有料なので、無料を希望する場合は WSL などを使って Community Edition)をインストールしておいてください。


Docker で PostgreSQL データベースを1つ用意する場合は以下を実行します:
$ docker run -d --name postgres -p 5432:5432 -e POSTGRES_USER=user1 -e POSTGRES_PASSWORD=pass1 -e POSTGRES_DB=db1 postgres

↑このコマンドで作成した場合、ユーザー名 user1 、パスワード pass1 、データベース名 db1、ポート番号 5432 番で postgres という名前の PostgreSQL コンテナが起動します。この例だとデータベース接続文字列は "postgres://user1:pass1@localhost:5432/db1" となります。

データベースを2つ作る必要はないので PostgreSQL で作成した場合はそれを使えばいいのですが、どうしても MySQL を使いたい場合はこちらを実行します:
$ docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=P@ssw0rd -e MYSQL_USER=user1 -e MYSQL_PASSWORD=pass1 -e MYSQL_DATABASE=db1 -p 3306:3306 mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

この場合だとデータベース接続文字列は "mysql://user1:pass1@localhost:3306/db1" となります。ユーザー名部分やパスワードなどは適宜変更して読み替えてください。


この方法を含め、なんらかの方法で接続して読み書き可能なデータベースが用意できているものと仮定して以下の説明を続けます。


データベースが稼働している状態で上述の(4)までを実行して本ツールを起動します(本ツール起動後にデータベース起動でも構いません):
$ npm start

> db2xlsx@0.0.1 start
> node app.js

server starting on 8080 ...

↑の青字のような表示が出力されれば起動に成功しています。ツールは 8080 番ポートで稼働しているので、ウェブブラウザで http://localhost:8080/_doc にアクセスして Swagger API Document を開きます:
2023122201


↑のような画面が表示されます。この画面でも "POST /db2xlsx" と "POST /xlsx2db" の2つの REST API が存在していることが確認できます。名前の通りですが、それぞれ「データベースからエクセル」、「エクセルからデータベース」に変換する API になっています。


これらの API をそれぞれ使ってみましょう。順番としてはデータベースは空の状態であると想定して、
(1)まずエクセルファイルの内容をデータベースにインポート(POST /xlsx2db)して、
(2)そのデータベースの内容をエクセルファイルにエクスポート(POST /db2xlsx)する
という順に実行してみることにします。

まずはデータベースにインポートするエクセルファイルを用意します。独自のものを準備いただいてもいいのですが、すぐ使えるようなサンプルを用意しておきました:
https://dotnsf.blog.jp/items-brands.xlsx


↑このリンクからエクセルファイル items-brands.xlsx をダウンロードしてください。エクセルや互換ツールを使って開ける場合は内容を確認してみてください(自由に変更していただいても構いません):
2023122301

2023122302


この items-brands.xlsx ワークブックには items と brands という2つのシート(タブ)があります。それぞれ以下のようなデータが表形式で格納されています:

(items)
id name brand_id price body updated
varchar(20) primary key varchar(100) not null varchar(20) integer text timestamp
item001 商品1 brand001 100 商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1 2023-12-20
item002 商品2 brand002 1200 商品説明2商品説明2商品説明2商品説明2商品説明2商品説明2商品説明2 2023-12-21
item003 商品3 brand003 500 商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3 2023-12-22
item004 商品4 brand002 2980 商品説明4商品説明4商品説明4商品説明4商品説明4商品説明4 2023-12-23


(brands)
id name body url updated
varchar(20) primary key varchar(100) not null text varchar(100) timestamp
brand01 ブランド1 説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1 https://www.brand1.com 2023-12-20
brand02 ブランド2 説明文2説明文2説明文2説明文2説明文2説明文2説明文2説明文2説明文2 https://www.brand2.net 2023-12-21
brand03 ブランド3 説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3 https://www.brand3.jp/ 2023-12-22


エクセルファイルが用意できたらこれをデータベースにインポートしてみましょう。先ほどのブラウザ画面内の "POST /xlsx2db" と書かれた箇所をクリックします:
2023122303


すると下のような隠れていた画面が展開されます。この API を実行する時に指定するパラメータに関する情報が表示されています。内容を確認し、そのまま実行したいので "Try it out" と書かれたボタンをクリックします:
2023122304


するとパラメータ部分が編集可能状態に切り替わります。ここに必要な情報を入力していきます:
2023122305


まず一番上の database_url パラメータは指定が必須になっています。ここにはデータベース接続文字列を入力します。上の方法で PostgreSQL データベースを起動した場合であれば、
 postgres://user1:pass1@localhost:5432/db1
また MySQL データベースを起動した場合であれば、
 mysql://user1:pass1@localhost:3306/db1
を指定してください(別の方法で起動したデータベースの場合はそのデータベースに接続するための接続文字列を指定してください)。


また一番下の「エクセルワークブックファイル」と書かれた file パラメータも必須入力です。ここは「ファイルを選択」ボタンをクリックしてから、先ほど用意したワークブックファイル(ダウンロードしたものを使う場合は items-brands.xlsx )を指定します。

それ以外の部分は必要に応じて指定するものですが、今回指定したワークブックファイルは各シートの2行目にテーブルの列定義が記載されています。このような場合(シートの2行目がレコード情報ではなく、列定義情報の場合)は col_attr パラメータに "1" という値を指定する必要があります(何も指定しない場合は2行目もデータレコードとみなされて insert into table が実行されます。またテーブルの各列は全て text 型として create table が実行されます)。

また今回は初回実行なのでまだデータベース内にテーブルは何も作成されていません。したがって create table を実行してから insert into table を実行する必要があります。その場合は create_table パラメータに "1" を指定してください。今回は指定しませんが drop_table パラメータに "1" をセットして実行すると create table の前に drop table が実行されます。定義の異なる同名のテーブルが作成されている可能性があって、そちらを削除してから実行する場合や、現在登録済みのデータを削除してから再実行したい場合は drop_table の方にも "1" を指定してください。

パラメータの指定が完了したら最後に青い "Execute" ボタンを実行すると xlsx2db 処理が実行されます:
2023122601


実行後、ボタンの下のほうに実行結果が表示されます。またこの REST API を実行した時のコマンド内容も表示されるので、この REST API を外部から利用する機会があれば参考にしてください:
2023122307


とりあえず実行は完了し、成功しているようです。 せっかくなので実際にデータベースにアクセスして成功した結果を確認してみましょう。

ターミナルやコマンドプロンプトなどの端末をもう1つ開いて、CLI からデータベースに接続します。上述のような Docker でデータベースを起動している場合はそのコンテナに直接ログインして調べることができます。

データベースが PostgreSQL の場合は、以下のようなコマンドで確認できます(赤字が入力内容、青字は説明用のコメントです。実際に入力しないでください):
$ docker exec -it postgres bash (データベースが動いているコンテナにログイン)

/# psql "postgres://user1:pass1@localhost:5432/db1" (接続文字列を指定して psql コマンドでログイン)

db1=# \dt (テーブル一覧を表示、brands テーブルと items テーブルが作成されていることを確認)

        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | brands | table | user1
 public | items  | table | user1

db1=# select * from brands; (brands テーブルの内容を確認し、エクセルに入力されていた内容が格納されていることを確認)

   id    |    name    |
           body
   |          url           |       updated
---------+------------+--------------------------------------------------------
-------------------------------------------------------------------------------
---+------------------------+---------------------
 brand01 | ブランド1 | 説明文1説明文1説明文1説明文1説明文1説明文1説明文
説明文1説明文1説明文1説明文1説明文1説明文1
  | https://www.brand1.com | 2023-12-20 00:00:00
 brand02 | ブランド2 | 説明文2説明文2説明文2説明文2説明文2説明文2説明文
説明文2説明文2
  | https://www.brand2.net | 2023-12-21 00:00:00
 brand03 | ブランド3 | 説明文3説明文3説明文3説明文3説明文3説明文3説明文
説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文
 | https://www.brand3.jp/ | 2023-12-22 00:00:00
(3 rows)

db1=# select * from items; (items テーブルも同様に確認する)

(略)

db1=# \q (psql からログアウトする)

/# exit (コンテナからもログアウトする)

データベースが MySQL の場合も、コマンドが少し異なりますが同様に確認できます:
$ docker exec -it mysql bash (データベースが動いているコンテナにログイン)

# mysql -u user1 -ppass1 db1 --default-character-set=utf8mb4" (接続情報を指定して mysql コマンドでログイン)

mysql> show tables; (テーブル一覧を表示、brands テーブルと items テーブルが作成されていることを確認)

+--------------+
| Tables_in_db |
+--------------+
| brands       |
| items        |
+--------------+
2 rows in set (0.01 sec)

mysql> select * from brands; (brands テーブルの内容を確認し、エクセルに入力されていた内容が格納されていることを確認)

(略)

mysql> select * from items; (items テーブルも同様に確認する)

(略)

mysql> quit (mysql からログアウトする)

# exit (コンテナからもログアウトする)

エクセルファイルをアップロードして、データベース化できることが確認できました。


では次に「データベースからエクセル」も試してみましょう。上の作業でエクセルファイルからデータベースを作成した直後だと、アップロードしたものと同じエクセルファイルが作成されるだけ(苦笑)ではあるのですが、データベースの内容がエクセルになる、という部分については体験できると思います。

Swagger API ドキュメントに戻り、今度は "POST /db2xlsx" のボタンをクリックします:
2023122301


API 実行時のパラメータが展開されます。今回は "POST /xlsx2db" の時よりもシンプルなパラメータになっていることがわかります。"Try it out" ボタンをクリックします:
2023122302


こちらの API ではエクセルにエクスポートしたいデータベースを database_url パラメータにデータベース接続文字列の形で指定するだけです。先ほどと同様のデータベース接続文字列を入力して "Execute" ボタンをクリックします:
2023122303


処理が成功すると "Download file" と書かれたリンクが現れます。ここをクリック:
2023122304


すると、(データベース名).xlsx というエクセルファイルがダウンロードされます。ダウンロード後に開いてみてください:
2023122305


ダウンロードしたエクセルファイルを開いてみると、items と brands という2つのワークシート(タブ)が含まれていて、それぞれのワークシート内に items テーブルと brands テーブルの内容がエクスポートされていることが確認できます。日付列は日付情報として取り出しているので少しフォーマットが変わっていますが中身(日付は)正しいはず:
2023122306

2023122307


データベースからエクセル、の REST API も正しく動くことが確認できるはずです。


【あとがき的なもの】
一部でエクセルがデータベースの代わりに使われていることを知っている身として、その状況はなんとかならんかという思いで作ってみました。REST API 化したので簡単なプログラムを書けば一括処理もできるはずです(笑)。で、どうしてもまたエクセル化する必要が生じた場合にも対応できるような逆方向の REST API も用意しているので、これでなんとかしてください。


 

IBM Cloud から提供されている 30 日間無料 Kubernetes サービスIBM Kubernetes Service 、以下 "IKS")環境を使って利用することのできるコンテナイメージを1日に1個ずつ 30 日間連続で紹介していきます。

環境のセットアップや制約事項については Day0 のこちらの記事を参照してください。

Day 6 からは Day 5 までのアプリケーション・サーバー系コンテナではなく、データベース系コンテナとその GUI ツールを中心に紹介していく予定です。その初日は最もメジャーなデータベース・サーバーの1つだと思っている MySQL イメージをデプロイする例を紹介します。
mysql_logo



【イメージの概要】
今更改めて説明する必要もないくらい有名で、かつ広く使われているオープンソースのリレーショナル・データベース・サーバーだと思っています。



【イメージのデプロイ】
Day 5 までは(特にパラメータの指定なども不要で動くコンテナばかりだったので)Github 上の YAML ファイルを直接していしてデプロイしていました。が、Day 6 以降では動かすコンテナに向けた設定項目(MySQL でいえば管理者パスワードなど)を自分で指定して動かす方針とします。 というわけで、まずはこちらのファイルを自分の PC にダウンロードしてください:
https://raw.githubusercontent.com/dotnsf/yamls_for_iks/main/mysql.yaml


次にこのファイルをテキストエディタで開いてパラメータを編集します。具体的には "MYSQL_" で始まる4箇所の env.name の value 値を変更してください。それぞれの具体的な意味は以下の通りです(初期値として指定されている値のまま動かすことも可能ですが、安全のためなるべく変更してください):
・MYSQL_ROOT_PASSWORD : 管理者パスワード(初期値 P@ssw0rd)
・MYSQL_DATABASE : デプロイと同時に作成するデータベースの名前(初期値 mydb)
・MYSQL_USER : デプロイと同時に作成するデータベースを利用するユーザー名(初期値 user1)
・MYSQL_PASSWORD : デプロイと同時に作成するデータベースを利用するパスワード(初期値 password1)

ではこのダウンロード&編集した mysql.yaml ファイルを指定してデプロイします。以下のコマンドを実行する前に Day 0 の内容を参照して ibmcloud CLI ツールで IBM Cloud にログインし、クラスタに接続するまでを済ませておいてください。

そして以下のコマンドを実行します:
$ kubectl apply -f mysql.yaml

以下のコマンドで MySQL 関連の Deployment, Service, Pod, Replicaset が1つずつ生成されたことと、サービスが 30306 番ポートで公開されていることを確認します:
$ kubectl get all

NAME                        READY   STATUS    RESTARTS   AGE
pod/mysql-5bd77967b-799vp   1/1     Running   0          9s

NAME                  TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)          AGE
service/kubernetes    ClusterIP   172.21.0.1     <none>        443/TCP          26d
service/mysqlserver   NodePort    172.21.40.65   <none>        3306:30306/TCP   10s

NAME                    READY   UP-TO-DATE   AVAILABLE   AGE
deployment.apps/mysql   1/1     1            1           10s

NAME                              DESIRED   CURRENT   READY   AGE
replicaset.apps/mysql-5bd77967b   1         1         1       10s

この後に実際にサービスを利用するため、以下のコマンドでワーカーノードのパブリック IP アドレスを確認します(以下の例であれば 161.51.204.190):
$ ibmcloud ks worker ls --cluster=mycluster-free
OK
ID                                                       パブリック IP    プライベート IP   フレーバー   状態     状況    ゾーン   バージョン
kube-c3biujbf074rs3rl76t0-myclusterfr-default-000000df   169.51.204.190   10.144.185.144    free         normal   Ready   mil01    1.20.7_1543*

つまりこの時点で(上述の結果であれば)アプリケーションは 169.51.204.190:30306 で稼働している、ということになります。早速確認してみます、と言いたいところなのですが、これまでのようにウェブブラウザで確認できるものではなく、専用の CLI コマンドが使えると確認できるのですが・・・ ここでは動作確認を Day 7 で行うことにして、今回はこのままにしておきます。


【YAML ファイルの解説】
YAML ファイルはこちらを使っています(編集する前の状態です):
apiVersion: v1
kind: Service
metadata:
  name: mysqlserver
spec:
  selector:
    app: mysql
  ports:
  - port: 3306
    protocol: TCP
    targetPort: 3306
    nodePort: 30306
  type: NodePort
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: mysql
        image: mysql:5.7  # should be 5.7 to avoid password caching error
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: "P@ssw0rd"
        - name: MYSQL_DATABASE
          value: "mydb"
        - name: MYSQL_USER
          value: "user1"
        - name: MYSQL_PASSWORD
          value: "password1"
        ports:
        - containerPort: 3306

Deployment 1つと、Service 1つのごくごくシンプルな YAML ファイルですが、一応解説を加えておきます。アプリケーションそのものは 3306 番ポートで動作するように作られているため、NodePort 30306 番を指定して、外部からは 30306 番ポートでアクセスできるようにしています(NodePort として指定可能な番号の範囲は 30000 ~ 32767 です、指定しない場合は空いている番号がランダムに割り振られます)。また ReplicaSet は1つだけで作りました(データベースなので、別途クラスタ構成の準備をしない限りはこの数値だけを増やしてもあまり意味ないと思います)。


デプロイしたコンテナイメージを削除する場合はデプロイ時に使った YAML ファイルを再度使って、以下のコマンドを実行します。不要であれば削除しておきましょう(ちなみにこの MySQL コンテナは明日の Day 7 でも使う予定なので、削除するのはそのあとの方がいいかもしれません):
$ kubectl delete -f mysql.yaml



【紹介したイメージ】
https://hub.docker.com/_/mysql


【紹介記録】
Dayカテゴリーデプロイ内容
0準備準備作業
1ウェブサーバーhostname
2Apache HTTP
3Nginx
4Tomcat
5Websphere Liberty
6データベースMySQL
7phpMyAdmin
8PostgreSQL
9pgAdmin4
10MongoDB
11Mongo-Express
12Redis
13RedisCommander
14ElasticSearch
15Kibana
16CouchDB
17CouchBase
18HATOYA
19プログラミングNode-RED
20Scratch
21Eclipse Orion
22Swagger Editor
23R Studio
24Jenkins
25アプリケーションFX
262048
27DOS Box
28VNC Server(Lubuntu)
29Drupal
30WordPress

タイトルどおりの内容のエラーが発生したので、その原因と解決策を探った記録をブログにまとめました。

環境としては以下の図のようなものです。1台の Windows 10 PC の中に Docker Desktop を導入・起動し、MySQL サーバーのイメージからコンテナを作って 3306 番ポートで公開起動しました。この環境に WSL(2) の MySQL CLI を使って、localhost:3306 の MySQL サーバーにログインする、というだけの内容なのですが、ログイン時にコネクションエラーが発生してしまう、という症状が出ていました:
20210612


具体的に紹介します。今回使った MySQL のコンテナイメージはこちらです。厳密には mariadb を使っています。同様の現象が発生すると思うので、他の MySQL 系イメージでも構いません:
https://hub.docker.com/r/linuxserver/mariadb


Docker Desktop を使ってこのイメージからコンテナを作成します。WSL を起動するなどして docker コマンドが使える状態で以下のコマンドを実行します(目的は Docker Desktop 内のコンテナとして起動することなので、このコマンド自体は WSL から実行しなくても構いません):
$ docker run -d --name=mariadb -e PUID=1000 -e PGID=1000 -e MYSQL_ROOT_PASSWORD=root -e TZ=Asia/Tokyo -e MYSQL_DATABASE=mydb -e MYSQL_USER=user -e MYSQL_PASSWORD=P@ssw0rd -p 3306:3306 --restart unless-stopped linuxserver/mariadb

こんな感じで、Docker Desktop 内のコンテナとして MySQL が起動します:
2021061301


指定したオプションはコンテナイメージのドキュメントを参考に指定しています。上の例では root のパスワードは root 、利用ユーザーは user 、利用ユーザーのパスワードは P@ssw0rd 、利用データベース名を mydb、コンテナ名は mariadb、などを指定しています(適当に変更いただいても構いません)。ポート番号に -p 3306:3306 を指定しているので、ローカルホストの 3306 番ポートからも(公開されているので)アクセスできるはずです。事前準備はここまで。

ではこの方法で Docker Desktop 内に起動した MySQL(mariadb) に、WSL から MySQL CLI で接続してみます。上述のオプションであれば以下のコマンドで接続できるはずです:
$ mysql -u user -pP@ssw0rd -h localhost mydb

しかし実際には以下のようなエラーが表示されてしまいます:
$ mysql -u user -pP@ssw0rd -h localhost mydb
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

ユーザー名やパスワード、データベース名は間違っておらず、接続先も localhost だから間違えようがないはずです。ポート番号もデフォルトの 3306 のままなので特別なオプションも不要のはず・・・ ではなぜエラーになってしまうのでしょうか?


改めてエラーメッセージをよく見ると "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'" と表示されています。ちなみにこのファイルは存在していません(/var/run/mysqld というディレクトリが存在していません):
$ ls -la /var/run/sqld/mysqld.sock
ls: cannot access '/var/run/sqld/mysqld.sock': No such file or directory

このエラーメッセージでわかる人もいると思うのですが、MySQL コマンドは localhost が対象の場合はデフォルトで「ソケット接続」という方法で接続を試みます。そしてこのソケット接続をする場合の情報を /var/run/sqld/mysqld.sock というファイルで管理しているため、このファイルを読み込もうとしているのでした。

しかし、今回の環境では WSL 内には MySQL サーバーは起動していません。WSL 内からの docker コマンドで起動してはいますが、実体は Windows にインストールされた Docker Desktop のコンテナとして起動しています。要は「WSL と同じマシン上で動いてはいて、ポートも公開されているけど、WSL から直接ソケット接続できる状態で動いていない」のです。これがエラーの原因で、エラーメッセージの理由でもあります。

では、エラーなしに WSL から MySQL に接続するにはどのようなコマンドにすればいいでしょうか? 答はシンプルで「ソケット接続ではなく、TCP 接続するようなオプションを指定」することで解決できます。具体的には以下のように --protocol=TCP オプションを付けて実行します:
$ mysql -u user -pP@ssw0rd --protocol=TCP -h localhost mydb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.4.18-MariaDB-1:10.4.18+maria~bionic-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [mydb]>

今度は無事に接続できました。「localhost の罠」にかかった時の話でした。


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

docker を使って複数の WordPress 環境を立ち上げる手順をスクリプト化してみました。

普通に1つの WordPress 環境を作るだけであれば(特に docker-compose を使えば、yaml ファイルを1つ用意するだけで)簡単に作れます。詳しくはここでは紹介しませんが、"docker WordPress" などでググると多くの紹介ページが見つかります。

ただ今回自分が作りたかった環境はこれらとは少し異なり、1つのホスト内に複数の独立した WordPress 環境を作る、というものでした。具体的には MySQL サーバーは1つだけ用意した上で、ポート番号で分離して1つ目の環境は localhost:8081 で、2つ目の環境は localhost:8082 で、・・・といった具合に、それも簡単に後から WordPress 環境を追加/削除できるよう考慮してスクリプト化して公開しました:
https://github.com/dotnsf/docker-wordpress

2021030700


※ここで方法で作成した WordPress 環境を実際にインターネットに公開する場合は DNS と連動したポートフォワーディングができる環境があれば、全ての WordPress 環境に 80 番ポート(http)や 443 番ポート(https)でアクセスできるようになると思っています。が、そちらについては環境依存になるので本ブログでは触れません。


利用方法については README.md で紹介していますが、一応ここでも説明します:

まず前提条件として docker が導入された環境が必要です(docker-compose は使わないので導入する必要はありません)。また用意したシェルスクリプトは Linux などの bash などで動かすことを想定しています。ただ docker コマンドが使える環境下であれば、(例えば Windows であれば、シェルスクリプトファイルの拡張子を .sh から .bat などに変更するだけで)使えるはずです。 なお、以下の内容については Windows10 の WSL2(Ubuntu 18.04) 環境で動作を確認しています。


docker 導入済みのシステムで docker を起動後、最初に MySQL イメージと WordPress イメージをダウンロードしておきます。実際には後述のシェルスクリプト実行時にダウンロードされていないと判断されれば docker が自動で最新イメージをダウンロードした上で実行してくれるので、この手順は必須ではありません。ただ最初に1回実行しておくことで後述のスクリプトが軽快に動くようになるので特に理由がなければこのタイミングでダウンロードしておくことを推奨します:
$ docker pull mysql

$ docker pull wordpress

次に今回の作業用に用意したシェルスクリプトをダウンロードして、実行可能な状態に設定します:
$ git clone https://github.com/dotnsf/docker-wordpress

$ cd docker-wordpress

(UNIX 環境の場合)
$ chmod 755 *.sh

(Windows 環境の場合 以下、拡張子を .sh から .bat に変更して実行)
> ren *.sh *.bat

まず docker 環境でデータベースである MySQL サーバーを起動します(以下のコマンドで 3306 番ポートで MySQL サーバーが起動します):
$ ./docker_run_mysql.sh

なお、この MySQL コンテナに接続してコンテナの中身を確認する場合は、以下のコマンドでターミナルにアタッチ可能です(exit で元のホストに戻ります):
$ docker exec -it mysql /bin/bash

そして docker 環境内に WordPress サーバーを起動します。この際に「何番目の WordPress 環境か」を意味するインデックス番号をパラメータとして指定します(以下の例では 1 を指定しています):
$ ./docker_run_wordpress.sh 1

このコマンドが成功すると、8081 番ポートで wordpress1 という名前の docker コンテナが起動します。ウェブブラウザで http://localhost:8081/ にアクセスすると、WordPress の初期設定画面に遷移して、サイト名やログイン設定などを指定して利用を開始できます:

2021030701


2021030702


2021030703


2021030704


2021030705


2021030706


とりあえず1つ目の WordPress 環境はこれだけで作れました。次の環境を作ることもできますが、この1つ目の WordPress 環境に関する操作を一通り説明しておきます。

この WordPress コンテナに接続してコンテナの中身を確認する場合は、以下のコマンドでターミナルにアタッチ可能です(exit で元のホストに戻ります):
$ docker exec -it wordpress1 /bin/bash

コンテナを停止する場合は以下のコマンドを実行します:
$ docker stop wordpress1

停止したコンテナを再起動する場合は以下のコマンドを実行します:
$ docker start wordpress1

停止したコンテナを削除する場合は以下のコマンドを実行します(コンテナを削除し、MySQL データベースも drop してデータごと削除します):
$ ./docker_rm_wordpress.sh 1


では1つ目の WordPress 環境を起動したまま、2つ目の WordPress を追加で起動してみます。以下のコマンドを実行します:
$ ./docker_run_wordpress.sh 2

このコマンドが成功すると、8082 番ポートで wordpress2 という名前の新しい docker コンテナが起動します。ウェブブラウザで http://localhost:8082/ にアクセスすると、新しい WordPress の初期設定画面に遷移し、同様にサイト名やログイン設定などを指定して利用を開始できます(それぞれが独立した環境なので起動済みの WordPress1 側には変化や影響はありません):

2021030707


2021030708


2021030709


wordpress2 のコンテナについても wordpress1 環境同様に docker コマンドで操作可能です。

後は必要なだけこの操作を繰り返すことで、WordPress 環境をコマンド1回ずつ追加していくことができます。ポート番号が利用中でなければ、おそらく好きなだけ起動できるはず(ポート番号が 8081 から始まるルールを変更したい場合は docker_run_wordpress.sh スクリプト内で適当な値に変更してください)。


そこそこのスペックを持った docker 導入済みのサーバーが1台インターネット上にあれば、DNS やポートフォワーディングなどと組み合わせることで複数の WordPress 環境を好きなタイミングで好きなだけ簡単に構築することができるようになると思っています。docker 環境なのでコンテナごと消してしまえば元のホスト環境を汚すこともなく元に戻せます(開発環境だと大事!)。

なお Github 上に公開したシェルスクリプトはそのまま利用することができますが、特にインターネットに公開する WordPress の場合、セキュリティの観点からパスワード類は変更してから利用することを推奨します。その場合は以下の部分を(すべて同じ文字列に)変更してください:
(docker_run_mysql.sh ファイル内)
- docker コマンド実行時の環境変数 MYSQL_ROOT_PASSWORD の値

(docker_run_wordpress.sh ファイル内)
- docker コマンド実行時の環境変数 WORDPRESS_DB_PASSWORD の値

(docker_rm_wordpress.sh ファイル内)
- docker コマンド実行時のパラメータ -p に続いて指定されている文字列

このページのトップヘ