MySQL の導入~ ラズベリーパイ研究室

MySQL は PHP の現場で頻繁に利用されているデータベースです。世界でもっとも普及しているオープンソース RDBMS(リレーショナル・データベース・マネジメント・システム)とも言われています。

ここでは Raspberry Pi で MySQL を利用するための導入部分をご説明します。

おススメ!記事
Raspberry Pi 用「HAL」で、
カップラーメン・タイマーを作ってみよう!
ラズパイDIYの決定版! ソケットサーバー「HAL」をご紹介します。

MySQL の導入

MySQL は PHP の現場で頻繁に利用されているデータベースです。世界で最も普及している RDBMS(リレーショナル・データベース・マネジメント・システム)とも言われます。

オープンソースであり、一時はサン・マイクロシステムズに買収されて維持管理されていましたが、現在は更にサン・マイクロシステムズ自体がオラクルに買収されたため、オラクルの所有物となりました。

ライセンス形態は GNU GPL と商用ライセンスのどちらかを選べるデュアルライセンスとなっています。

MySQL の特徴

とにかく気楽に使えます。他の RDB ではカラムの型を意識しないとエラーになったりしますが、MySQL では、 MySQL が「よしなに」判断して、多分、ユーザーが望んでいるであろう処理を勝手にやってくれます。そこが良い所でもあり、恐ろしいところでもあります。そんなところが PHP とよく似ているから PHP の現場で頻繁に利用されるのかもしれないと勝手に考えています。

初期の MySQL は文字コードやストレージエンジンの問題でいろいろと不備がありましたが、MySQL 5.5 あたりからはかなりの問題が解消され、とても良い RDB になりました。

しかし、オラクル社に買収されたことは多少なりとも悲劇でした。

オラクル社は、その名を冠する有名な RDB である「Oracle」を別に所有しているため、MySQL が飼い殺しにあうかもしれないという危機感が生まれることになりました。IT の世界ではよくあることです。

そこで、MySQL のオリジナルコードの作者自らが MySQL をフォーク(あるプロダクトから別の分岐を派生させること)し、MariaDB というプロダクトを立ち上げました。ライセンスは GPL v2 です。

この2つの RDB はとても良く似ていて、現在はどちらでも同じコードがほぼそのまま動きますが、将来的には別々の拡張がなされるかもしれません。

MySQL のインストール

さて、では MySQL をインストールしてみましょう。インストールはやはり apt-get です。Raspbian Jessie の apt-get で現在(2016-07-06)インストールされるのは MySQL 5.5.44 のようです。

sudo apt-get install mysql-server

すると、root パスワード入力画面が表示されます。ここは強固なパスワードを指定しましょう。絶対に忘れないようにして下さい。

rootパスワード設定

入力すると、確認用にもう一度パスワードの入力を求められます。パスワードが一致すればインストールが続行されます。

※2016/07/10 追記

インストールが終わったら、mysqlとクライアントのデフォルトの文字コードを設定します。

sudo vi /etc/mysql/my.cnf

下記のように、[mysqld] と [client] の記述の下に、キャラセットを指定します。異なるパラメター名ですので注意して下さい。

[mysqld]
...
character-set-server=utf8mb4

[client]
...
default-character-set=utf8mb4

以上、インストールと設定が終わったら MySQL にログインしてみましょう。

mysql -u root -p

パスワードを求められるので、先ほどのパスワードを入力して下さい。以下のようにプロンプトが mysql> に変わればログイン成功です。

ログイン後

ではちょっと SQL コマンドを入力してみましょう。MySQL には SHOW DATABASES; という命令があります。これは、MySQL に登録されているデータベースのリストを表示する MySQL 専用の SQL です。実行してみましょう。なお、SQL では基本的には大文字と小文字を区別しません。

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

今度はデータベースを作成してみましょう。CREATE DATABASE `pi_test`; です。(※以下の`は当サイトのシステムの都合上多バイト文字の`になっていますが、正しくはシングルバイトのバッククオートです)※追記:デフォルトキャラクタセットの指定を追加しました。

mysql> CREATE SCHEMA `pi_test` DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec)

再び SHOW DATABASES; で確認してみましょう。

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| pi_test |
+--------------------+
4 rows in set (0.00 sec)

新しいデータベース pi_test が作成されましたね。

次は新規ユーザーを1人作ってみましょう。データベース名を pi_test にしたので、ユーザー名も pi_test にしてみます。パスワードの ** は適宜変えて下さい。なお、指定したパスワードは忘れないように。

CREATE USER 'pi_test'@'localhost' IDENTIFIED BY '********';

うまくユーザーが作られれば

Query OK, 0 rows affected (0.01 sec)

といったメッセージが返ります。ところで、@'localhost' ですが、MySQL では接続するプロトコル毎にユーザーが作成できます。

例えば 'pi_test'@'192.168.1.10' などとした場合は、TCP/IP 接続でのアドレス 192.168.1.10 に対してユーザーを作成しますが、'pi_test'@'localhost' の場合は UNIX ソケットでのユーザーを作成します。ですから 'pi_test'@'localhost' と 'pi_test'@'127.0.0.1' は、全く違うユーザーアカウントを指す事になります。詳しくは「とみたまさひろ」さんが作成されたスライド MySQLの始め方 を参照して下さい。

そういうわけで、このあと、GUI(グラフィカル・ユーザー・インタフェース)を導入して Raspberry Pi の MySQL を遠隔操作するため、TCP/IP 用のアカウントも同時に作っておきます。IP アドレスの ** はお使いの環境に合わせて下さい。

CREATE USER 'pi_test'@'192.168.1**.**' IDENTIFIED BY '********';

ちなみに「とみたまさひろ」さんとは Postfix や MySQL の本を何冊も書かれ、OSS貢献者賞の受賞歴もある方で、

とみたまさひろさん

こんな顔をした人です。

さて、ユーザーを作ったら、そのユーザーに権限を与えましょう。pi_test.* は、「pi_test データベースの全てのテーブルについて」という意味です。*(アスタリスク)はワイルドカードといって、全ての値を表現する特殊な文字です。(これまでセキュリティのために伏せ字にしてきた ** などとは別の意味ですので注意して下さい)

GRANT ALL PRIVILEGES ON pi_test.* TO 'pi_test'@'localhost';
GRANT ALL PRIVILEGES ON pi_test.* TO 'pi_test'@'192.168.**.**';

これ以降の様々な SQL についてはここで説明すると膨大になり過ぎるので省きます。SQL については別途、検索エンジンなどを使って調べて下さい。

最後に quit を入力して MySQL のシェルから抜けましょう。

mysql> quit
Bye
pi@raspberrypi:~ $

MySQL Workbench の導入

さて、先程はコマンドラインから MySQL を操作しましたが、全てのコマンドをタイプするのはとても面倒です。そこで、GUI(グラフィカル・ユーザーズ・インターフェイス)を導入して MySQL の操作を簡単にしてしまいましょう。

MySQL Workbench

MySQL Workbench サイト

「ダウンロードはこちら >>」を選ぶと、ダウンロードする OS を選択する画面に遷移します。

ダウンロード画面

MySQL Workbench 6.3.7 の Select Platform から お使いの OS を選択してダウンロードして下さい。x64 Windows(64bit OS) の方は 「Windows (x86, 64-bit), MSI Installer 6.3.7 (mysql-workbench-community-6.3.7-winx64.msi)」あたりが良いのではないかと思います。x86(32bit OS)の方は、そちらのパッケージを選んでください。

すると、こんな画面になります。「ログインするか、アカウントを作ってくれ」という画面です。

ダウンロードを開始する

あまりおおっぴらには推奨できないのですが、画面下に小さく「No thanks, just start my download.」というリンクがあります。「いいえ、結構です。ダウンロードを開始します」と書いてありますので、そういう意味です。

さて、ダウンロードが終わったらセットアップを行いましょう。

インストーラースタート

インストールするディレクトリ指定

インストールの仕方指定

確認画面

インストール中

インストール完了

これでインストール完了です。

では立ち上げてみましょう。

MySQL Workbench 起動直後

先ほど作ったデータベースに接続するには、画面上の◯の中に+のアイコンをクリックして下さい。Setup New Connection ダイアログが開かれます。

新規接続設定画面

Connection Name には自由な接続名をつけて下さい。

Connection Method には、Standard TCP/IP over SSH を指定します。これで秘密鍵を使った SSH 通信が使えます。

SSH Hostname には SSH接続する時と同じ、Raspberry Pi の IP アドレス : ポート番号を指定します。例えば

192.168.1.10:54321

といった感じです。

SSH Username は変更してなければ pi です。

SSH Password の Store in Vault... で SSH 接続用のフレーズを入力します。

SSH Key File に、SSH 接続用の id_rsa ファイルを指定して下さい。

MySQL Hostname、MySQL Server Port、Username は、今回は SSH 接続先と同じなので特に変更する必要はありません。

MySQL Hostname: 127.0.0.1
MySQL Server Port: 3306
Username: pi_test

Password については、先ほどユーザーを作成した時に設定したパスワードを入力してください。

以上を設定したら、「Test Connection」をクリックして接続テストをします。

パスフレーズ確認画面

次のような画面が出れば成功です。エラーが表示された場合は入力項目を再確認して下さい。特に WiFi 接続している時などは、WiFi に繋がっていない場合があります。

テスト接続成功

接続テストに成功したら OK をクリックして下さい。Workbench の画面に接続が登録されます。

接続登録成功

登録された接続をクリックして、実際に RDB にログインしてみましょう。次の様な画面が開くはずです。画面左上の SCHEMAS を確認して下さい。先ほどコマンドラインで作成した pi_test データベースが確認できるはずです。

データベース操作画面

pi_test の左側にある▲をクリックして情報を開いてみましょう。Tables、Views、Stored Procedures、Functions がありますが、まだ何も登録されていないのでどれも空です。

では、テーブルを作成してみましょう。

Tables を右クリックして Create Table... です。テーブル作成ウィンドウが開くので以下のように適当に入力してみましょう。

テーブル作成画面

id カラム(Column)の PK はプライマリ・キー(primary key)と言って、そこに記録される情報は一意であり、更にヌル(null)を許さないという特別な制約です。このため、ある PK を持つレコードは必ず 1 つであることが保証されます。NN は Not Null のことで、null を許可しないという意味です。

AI は MySQL での連番処理のことで、オート・インクリメント(auto_increment)の略です。これが指定してあるカラムは、レコードを追加する度に連番を1ずつ繰り上げて記録していきます。

一つ注意して欲しいのは Collation の項です。MySQL では様々な文字コードを扱うことができるのでここで、このテーブルのデフォルト文字コードを指定します。

utf8mb4 というのは MySQL でのユニコード(Unicode)の charset のうちの一つで、世界中の言語の文字を利用できる文字コードですが、別に、utf8 という charset も存在します。

utf8 はマルチバイトになっていて、その文字によって何バイトを利用するかが変化します。例えば a ~ z や 0 ~ 9 などは 1 バイトしか使いませんが、一般的な日本語の文字は 3 バイトを必要とします。utf8 自体では最大で 6 バイト(とみたさんから「ユニコードとしての UTF-8 は現在、最大 4 バイトです」というご指摘を頂きました。感謝して訂正します)までが規定されているのですが、MySQL の utf8 は 3 バイトまでの文字しか対応していませんでした。

ところが、例えば携帯電話等で使われる絵文字は 4 バイトを必要とする物があり、これが問題となっていました。

そこで MySQL では utf8mb4 という、4 バイト対応の utf8 を用意したのです。ですから今後は、特別な場合を除いて utf8mb4 を使ったほうが良さそうです。なお、この utf8mb4 は MySQL 5.5.3 以降で導入され利用できるようになりました。

また、MySQL の Collation(照合順序)は様々な言語に対応しているのですが、日本語を扱うにあたっては general_ci を利用することをお勧めします。

unicode_ci という Collation があってこれが頻繁に使われているのですが、この unicode_ci では類似検索(LIKE 検索)する際に、半角数字の 1 と全角数字の1と丸付き数字の①などを同じ文字とみなします。

もっと凄いところでは、パパとハハとババも同じ文字だとみなします。

これは、ユニコードに規定されている照合レベルのうちの一部だけを MySQL が実装しているためなのですが、あまり好ましい事ではありませんよね?

ですから、これらをきちんと区別してくれるよう、general_ci を使いましょう、ということです。

ところで、携帯絵文字の 4 バイトに対応した utf8mb4 ですが、それでもまだ問題がありました。

MySQL と寿司ビール問題

大元の発信者はとみたまさひろさんです。とみたさんは本当に面白い方です。このスライドが参考になるでしょう。

🍣=🍺 ← ※一応、外部サイトへのリンクです。こんなタイトルなので…

そこで、もっと厳密に区別したい場合は bin という、完全なバイトの一致の検査を行う Collation もあります。ただ、これは厳密すぎて逆に不便なので、必要なところだけそのようにするのがお奨めです。MySQL は データベース、テーブル、カラムのそれぞれに独立した Collation が指定できるので心配ありません。

さて、このように設定を行ったら Apply をクリックしてみましょう。

確認画面

確認画面が表示されます。実際に発行される SQL が表示されているのがわかりますか? 最初のコマンドラインでこれと同じ SQL 文を書いて実行してもテーブルは作成されますが、このように GUI で操作したほうが簡単ですよね?

さて、確認が終わったら Apply をクリックして SQL を適用しましょう。

最終確認画面

さらに確認画面が表示されます。ここで Finish をクリックするとテーブルが作成されます。Navigator で確認してみてください。

作成されたテーブル

さて、テーブルが作成されたら今度はレコードを追加してみましょう。

今度は SQL を書いてみましょうか。

File > New Query Tab

で、クエリ記述画面を開きます。ここに、次のように記述して下さい。

use pi_test;
INSERT INTO test_table1(name) VALUES('テスト タロウ');
INSERT INTO test_table1(name) VALUES('テスト 二郎');
INSERT INTO test_table1(name) VALUES('テスト saburou');

記述したら、クエリ記述画面の上にある実行ボタン(Execute)をクリックします。

クエリ実行

正常に実行されると、画面下の Output にクエリの実行状況が表示されます。

Output

そうしたら、先ほど作成したテーブル test_table1 を Navigator で右クリックして Select Rows - Limit 1000 を選びましょう。INSERT したレコードが登録されているのを確認して下さい。id についても、何も入力していなくても連番が自動でふられているのを確認して下さい。

レコード確認

ところで、name は何文字まで格納できると思いますか?

答えは、テーブル作成の時に

VARCHAR(45)

と指定してあったので 45 文字です。VARCHAR は文字数可変のデータ型で、( ) で指定した文字数までのテキストを格納することができます。

ここでいう 1 文字とは、utf8(及び他の文字コード全て)における 1 文字なので、1 バイト文字の英数字だろうが、3 バイト文字の漢字だろうが 1 文字です。

なお、MySQL の場合、デフォルトでは、指定されている文字数を超えた分については何も言わずにばっさりと切り捨てられます。注意しましょう。

後はあなたのお好きなようにいじってみてください。GUI なのでとても簡単です。(英語を調べる必要は多少ありますが)

まとめ

MySQL の導入から利用までを簡単に纏めてみました。

ソケットサーバー「HAL」では、MySQL、PostgreSQL、SQLite を簡単に扱うためのライブラリである DataMapper Lite を導入する事が決定しました。

DataMapper Lite を利用すれば、簡単な記述で SQL が発行できるようになります。近日中にリリースいたしますし、既に ソケットサーバー「HAL」をご購入されている方には無償で追加ファイルをご提供いたします。

また、さらに簡単に SQL が発行できる製品 DataMapper(有償)も少し遅れてリリースされます。

ぜひ、DataMapper Lite をご利用いただき、データベースというものが如何に簡単に扱える便利なものであるのかをご実感ください。


とみたさんの書籍(共著)と SQL のバイブルを最後に紹介して締めさせて頂きます。

MySQL徹底入門 第3版 5.5新機能対応(翔泳社)

【改訂第3版】 SQLポケットリファレンス (POCKET REFERENCE) (技術評論社)

この記事へのコメント

※現在コメントはMarkdown記法が強制です。>>Markdown の書き方


この記事に返信

このコメントに返信