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

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

タグ:mysql

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 に続いて指定されている文字列

MySQL テーブル内にバイナリデータを格納する場合、blob 型の列を定義することになります。具体的には blob 型にも複数あり、想定される最大サイズに応じて使うことになります:
最大サイズ(バイト)格納するデータの例
tinyblob255(あまり使われない?)
blob65,535(あまり使われない?)
mediumblob16,777,215画像など
largeblob4,294,967,295動画、音声など


格納するデータの内容によって型を決める必要がありますが、例えば画像、音声、動画といったメディア系のファイルを格納しようとすると tinyblob や blob 型では足りないと思われるので、ほぼ mediumblob か largeblob を使うことになると思われます。1データの最大サイズが 16MB を超える想定があるかどうかで使い分けることになります。ただし largeblob でも 4GB が最大となります。

MySQL で mediumblob 型を使う場合のテーブル定義の例としてはこのようになります:
create table images( id int primary key, img mediumblob ); 

上記例では images テーブル内に mediumblob 型の img 列を定義しました。このテーブルにデータを読み書きする Java のサンプルは後述のようになります。なお後述のサンプル共通で getConnection() という関数を使っていますが、これは java.sql.Connection クラスのインスタンスを返す関数で、具体的には以下のような内容となります:
public Connection getConnection(){
  Connection conn = null;
  try{
    Class.forName( "com.mysql.jdbc.Driver" );
    conn = DriverManager.getConnection( "jdbc:mysql://servername/dbname", "user", "pass" );
  }catch( Exception e ){
    e.printStackTrace();
  }

  return conn;
}


まずはデータの書き込み(insert)です。格納したいバイナリデータが byte[] 型の変数 data に格納されている場合、以下のようなコードで上述の images テーブルに insert することができます:
import java.sql.*;
   : 
 
 
try{
  Connection conn = getConnection();
  String sql = "insert into images( id, img ) values ( ?, ? )";
  PreparedStatement stmt = conn.prepareStatement( sql );
 
 
  stmt.setInt( 1, id ); 
  stmt.setBytes( 2, data ); //. data は byte[] 型 
  int r = stmt.executeUpdate(); 

  stmt.close(); 
  conn.close(); 
}catch( Exception e ){ 
  e.printStackTrace(); 
}

一方、読み出し(select)は以下のようになります。ResultSet からバイナリストリームを取得して ByteArrayOutputStream に書き出し、最後に byte[] 型の変数に変換して取り出しています:
import java.sql.*; 
 : 


byte[] r = null; 

try{ 
  Connection conn = getConnection(); 
  String sql = "select img from images where id = " + id;
  Statement stmt = conn.createStatement(); 

  ResultSet rs = stmt.executeQuery( sql );
  rs.first(); 
  InputStream is = rs.getBinaryStream( 1 );
  ByteArrayOutputStream baos = new ByteArrayOutputStream();
  byte[] bs = new byte[1024];
  int size = 0;
  while( ( size = is.read( bs ) ) != -1 ){
    baos.write( bs, 0, size );
  } 

  r = baos.toByteArray();  //. byte[] 型に変換してデータを取得

  stmt.close();
  conn.close(); 
}catch( Exception e ){
  e.printStackTrace();
  r = null; 
}

バイナリデータをサービスシステム内のどこに格納するべきか、という設計の問題を考慮した上で使うべきだと思いますが、この方法で MySQL に格納しておけば、MySQL のバックアップを取ればバイナリデータもまとめてバックアップすることができ、同様に MySQL をリストアすればバイナリデータごとリストアできる、というメリットがあります。


このページのトップヘ