KLab株式会社

MySQL5開拓団

MySQL 5.0 概観と実験環境作り

※こちらのページは技術評論社刊「WEB+DB PRESS Vol.32」に掲載された内容になります。

はじめに

連載の目的

連載第1回目ということで、まずはこの連載の目的と今後の予定について書きたいと思います。

とうとつですが、みなさん、MySQLを使っているでしょうか? 使っているのはどのバージョンでしょうか? 私の印象では、かなりの方がまだ4.0を使っているのではないかと思います。4.0はリリースからだいぶ日が経ち、大変安定しているため、安定性を求めた場合の選択肢としてはまだまだ現役なのではないかと思います。

とはいえ、4.0は既に前の前の安定版(Generally Available release)で、深刻なバグの修正以外は変更されないと宣言されています。

こういう状況ですので、私のまわりでもそろそろ5.0を試してみようとか新規案件で本格的に導入してみようという話を耳にするようになりました。

さて、その5.0ですが、ご存じの方が多いと思いますが、MySQLは4.1、5.0でとても多くの機能強化がなされました。また、日本語も含め多くの文字コードを扱うための機能が強化されました。一方で、それに伴い、いわゆる「文字化け」が多発する情報を耳にして不安に思っている方も多いのではないかと思います。

こういった状況をふまえ、この連載では次のものを目的としたいと考えています。

  • 5.0を知ってもらうために、5.0の新機能とその実力を検証します。(注1)
  • 4.0, 4.1のユーザに向けて、これまでと5.0の違いや注意点を紹介します。

この他に、バージョンにあまり依存しない運用面やチューニングのtipsなども、コラムとしてご紹介していきたいと思っています。

注1:
別ないいかたをすると、地雷を踏みまくるのでみなさんは二の舞いにならないようにしてくださいね、ということです。

自己紹介

申し遅れましたが、著者のバックグラウンドを知ってもらった方が何かとよいと思いますので簡単にですが自己紹介をしたいと思います。

私はKLab株式会社(旧、株式会社ケイ・ラボラトリー) という会社に勤めています。弊社の主な事業は携帯電話を軸としたビジネス構築、サービス提供で、その中で私は数名のメンバーと共に、DSASと呼んでいるコンテンツ横断的な共通インフラを手掛けています。DSASの特徴的なところは、OSSを多用している、というかほとんどOSSのプロダクトの組み合わせでできている点で、いわゆる箱ものはスイッチングハブだけで、負荷分散機もPCサーバにネットワークブートなLinuxという構成で実現しています。もちろん、冗長化もぬかりなく、L2レベル(RSTP(注2)やbonding device(注3)など)からサーバレベル(MySQLのレプリケーションやDRBD(注4)を利用したストレージサービスなど)まで各所で多重化しておりSPOF(注5)がない構成となっています。

語り出すと止まらないのでこの辺で切り上げて、冗長性をもったスケーラブルなインフラ、という話題は別の機会にお話しできればと思います。また、おいおい弊社のホームページで技術情報の提供をしていきたいと考えていますので、お手隙の時に立ち寄っていただければと思います。

注2:
ループを論理的に断ち切るためのプロトコルです。STP(Spanning Tree Protocol)より短い時間(Rapid)で安定化することができます。
注3:
複数のネットワークインターフェースを論理的にひとつに束ねて冗長化や負荷分散化できるLinuxのデバイスドライバです。
注4:
Distributed Replicated Block Device。ネットワーク越しに逐次データをミラーするLinuxのブロックデバイスです。おおざっぱにいうと、ネットワーク版のRAID1のようなイメージです。
注5:
Single Point of Failure。日本語では『単一故障点』と訳されることが多い、その部分が故障するとシステム全体が停止してしまう個所という意味の言葉です。いうなればシステムの『急所』といったところでしょうか。

5.0概観

さて、話を戻してMySQLです。

まずは4.1、5.0で何が変わったのかをざっと眺めてみましょう。

VARCHAR型、CHAR型

4.0以前はVARCHAR型とCHAR型の「長さ」の単位はバイトだったのですが、4.1以降で単位が「文字」になりました。

また、今までVARCHAR型の長さの上限は255だったのですが、5.0.3以降で大幅に増えて65535になりました。

このふたつのことから、5.0.3以降はVARCHAR型の場合は最大で65535文字格納できそうですが、実はもうひとつ、65532バイト以下、という制限があります。つまり、utf8やujis、eucjpmsなどの場合は1文字=最大3バイト(注6) 、sjisやcp932の場合は1文字=最大2バイトなので、前者の場合は65532÷3でVARCHAR(21844)、同じく後者はVARCHAR(32766)がVARCHAR型の「長さ」として指定できる上限となります。これより大きい値を「長さ」として与えた場合、暗黙的にTEXT型となり、型が変わってしまうので注意してください。

注6:
EUC-JPはSS3(シングルシフト3)が呼び出される場合は3バイトになります。

サブクエリ

4.1以降でサブクエリがサポートされました。今までテンポラリテーブルや言語側で処理していた4.0ユーザにはうれしい機能強化でしょう。

OpenGIS

4.1で空間情報を扱うための機能が追加されました。当初、MyISAMテーブルのみでのサポートだったのですが、5.0.16からInnoDBなどでも使えるようになりました。

ビュー

5.0.1からビューが使えるようになりました。いくつかの条件を満たしていれば、更新可能なビューを作ることもできます。

ストアドプロシージャ、ファンクション

5.0からストアドプロシージャとストアドファンクションが使えるようになりました。ストアドプロシージャとは、いくつかのSQLをひとまとめにしてサーバ側に保存(ストア)したもので、クライアントはプロシージャを実行するだけでサーバ側で一連のSQLを実行することができます。メリットとしては、クライアント側のアプリケーションや言語が異なっても同じ処理を実行できるため、クライアント側の様々なコストを減らすことができます。また、クライアントとやり取りすることなく一連のSQLを実行できるので、パフォーマンスの向上も期待できます。ただし、他のRDBMSの実装のようにプリコンパイルはされないので、そういった側面でのパフォーマンスの向上は望めません。

プロシージャとファンクションの違いは、ファンクションはSQL関数のように値を返しますが、プロシージャは返さない点です。

トリガ

5.0.2からトリガが使えるようになりました。トリガとは、INSERT、UPDATE、DELETEの前後のタイミングで指定した一連の処理をサーバサイドで行うためのものです。

文字コード

4.0からの変更点の中で、日本語を扱うわれわれにとっていちばん大きいものかもしれません。

まず、扱える文字コードが増えました。主なところでは、4.1でutf8、ucs2、cp932が、5.0でeucjpmsがサポートされました。

また、クライアントとサーバの文字コードの設定が異なる場合に文字コードの自動変換が行われるようになりました。一見、便利になったように思えますが、これが原因で様々な問題が発生する可能性がありますので、自動変換に頼らずクライアントとサーバの文字コードは揃えておくべきです。

ストレージエンジン

おなじみのMyISAM、InnoDBに加え、表1のストレージエンジンが仲間入りしました。

<表1:新しいストレージエンジン>
名称 概要
FEDERATED 他のサーバのテーブルをあたかもローカルのテーブルのように参照できます。
ARCHIVE 大量のデータを小さい容量に格納できます。
CSV CSV形式のテキストファイルにデータを格納します。
BLACKHOLE INSERTされたデータをテーブルに格納しないで捨てます。

XA トランザクション

XA トランザクション 5.0.3からXAトランザクションがサポートされました。XAとは、X/Openによって定義された、分散トランザクション処理におけるトランザクションマネージャとリソースマネージャの通信方法を定めたものです。

情報スキーマ (Information Schema)

情報スキーマとは、テーブルやユーザといった各種オブジェクトの情報(メタデータ)を参照するためのデータベースとテーブルです。他のRDBMSでいうところの「データディクショナリ」や「システムカタログ」と同じようなものです。

これまでMySQLではSHOWコマンドでメタデータを参照できましたが、今後は情報スキーマを使うように推奨されています。なぜなら、情報スキーマはSQL:2003という規格で標準化されており、他のRDBMSとの互換性や移植の容易さが期待できるからです。

help機能

4.1から密かに(?)追加された機能です。mysqlコマンドの中からリファレンスマニュアルを引くことができます。

例えばSELECT文の構文を表示するには図1のようにします。表示が長くなるので、pagerを有効にした方がよいでしょう。(無効にするにはnopagerと入力します)

<図1:mysqlコマンドのhelp機能>
mysql> pager           ← pagerを有効にします
mysql> help select
Name: 'SELECT'
Description:
Syntax:
SELECT                 ← SELECT文の構文が表示されます
	[ALL | DISTINCT | DISTINCTROW ]
	:    :    :    :
mysql> nopager         ← pagerを無効にします

全てのhelpは、help contentsから辿ることができます。

環境準備

ここまででMySQL 5.0の新機能などをみてきました。次回以降で詳細に説明・検証していきたいと思います。そのために、連載第1回の最後として実行環境を整えておきたいと思います。

OSはDebian/GNU Linuxの安定板(sarge)を使い、そこにMySQL ABのバイナリ版をインストールします。MySQLのバージョンは執筆時点の5.0系の最新版である5.0.19を使います。なお、冒頭で述べた通り5.0の先端を追いかけるのが目的なので、連載の途中で新しいバージョンが出た場合はそれに乗り換える予定です。また、rpm版ではなくtgz版を使い、複数バージョンを切り替えて使えるようなディレクトリ構成にしたいと思います。

Debian/GNU Linuxのインストール

ここでは細かい手順は示しません。Debianのドキュメントなどを参照してインストールしてください。OSのインストールが終わったら、図2のようにしてMySQL関連のパッケージがインストールされていないか確認します。もし、インストールされているものがあったら、図3のようにしてアンインストールします。

<図2:インストールされているパッケージの確認>
$ COLUMNS=100 dpkg -l | grep -i mysql

ii  libdbd-mysql-perl  2.9006-1         A Perl5 database interface to the MySQL database
ii  libmysqlclient12   4.0.24-10sarge1  mysql database client library
ii  mysql-client       4.0.24-10sarge1  mysql database client binaries
ii  mysql-common       4.0.24-10sarge1  mysql database common files (e.g. /etc/mysql/my.cnf)
ii  mysql-server       4.0.24-10sarge1  mysql database server binaries
<図3:パッケージの削除>
# aptitude purge libdbd-mysql-perl libmysqlclient12 mysql-client mysql-common mysql-server

mySQL 5.0のインストール

MySQL 5.0のダウンロードページにアクセスし、「Linux (non RPM package) downloads」の中から「Linux (x86, glibc-2.2, "standard" is static, gcc)」の「Standard」をダウンロードします(図4)。今回はバージョン5.0.19なので、ダウンロードしたファイルの名前はmysql-standard-5.0.19-linux-i686.tar.gzとなります。 次にインストールなのですが、今回はrpm版ではなくてtgz版で、複数バージョンの切り替えが簡単にできるようにするため、表2のようなディレクトリレイアウトにします。別バージョンのMySQLをインストールした際には、/usr/local/app/mysqlのシンボリックリンクを切り替えるだけで付属コマンドなども新しいバージョンのものに切り替えられることをねらった構成です。

MySQLバイナリパッケージのダウンロード
<図4:MySQLバイナリパッケージのダウンロード>
<表2:ディレクトリレイアウト>
ディレクトリ、ファイル 説明
/usr/local/app/mysql-standard-5.0.19-linux-i686/ tgz版を展開したもの。
/usr/local/app/mysql ../app/mysql/bin/∗ へのシンボリックリンク。
/usr/local/bin/∗ データファイルなどを置くディレクトリ。mysqlユーザのみ読み書き可能。
/var/lib/mysql/ ソケットファイルなどを置くディレクトリ。だれでも読み出し可能。
/var/run/mysql/ pidファイルを置くディレクトリ。だれでも読み出し可能。
/etc/my.cnf システムワイドな設定ファイル。だれでも読み出し可能。

それでは、まずは必要なディレクトリを作ってダウンロードしたファイルを展開し、ディレクトリレイアウトの通りシンボリックリンクを張ります。(図5)

<図5:アーカイブの展開>
# mkdir /usr/local/app
# cd /usr/local/app
# tar zxf ~/mysql-standard-5.0.19-linux-i686.tar.gz
# ln -snf mysql-standard-5.0.19-linux-i686 mysql
# cd /usr/local/bin/
# ln -sf ../app/mysql/bin/∗ .

次にMySQLを実行するためのユーザを作ります。uid、gidやホームディレクトリはお好みに応じて変更してもらって構いません。(図6)

<図6:ユーザの作成>
# groupadd -g 7010 mysql
# useradd  -u 7010 -g mysql -s /bin/false -d /home/mysql mysql

そして残りの必要なディレクトリを作ります。オーナやパーミッションを一緒に設定するためにinstallコマンドを使っていますが、mkdir、chown、chgrp、chmodでも構いません。(図7)

<図7:ディレクトリの作成>
# install -d -o root  -g root  -m 0555 /usr/local/mysql
# install -d -o mysql -g mysql -m 2750 /usr/local/mysql/data
# install -d -o mysql -g mysql -m 2755 /var/lib/mysql
# install -d -o mysql -g mysql -m 2755 /var/run/mysql

続いて起動に必要な最低限のデータベースを用意します。(図8)

<図8:初期データベースの作成>
# cd /usr/local/app/mysql/
# ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
Installing all prepared tables
Fill help tables
    :    :    :

データベースの初期化が成功したら、システムワイドな設定ファイル(/etc/my.cnf)をリスト1の内容で作ります。

<リスト1:/etc/my.cnf>
[mysqld]
basedir   = /usr/local/app/mysql
datadir   = /usr/local/mysql/data
port      = 3306
socket    = /var/lib/mysql/mysql.sock
pid-file  = /var/run/mysql/mysql.pid
log-error = /var/lib/mysql/mysql.err

[client]
port      = 3306
socket    = /var/lib/mysql/mysql.sock

これで起動に必要な準備ができましたので試しに起動してみましょう。(図9)

<図9:はじめてのMySQLの起動>
# cd /usr/local/app/mysql
# ./bin/mysqld_safe --user=mysql --skip-networking
Starting mysqld daemon with databases from /usr/local/mysql/data

この状態ではMySQLの世界の特権ユーザ(root)にパスワードが設定されておらず、だれでもrootになれてしまうので早速パスワードを設定しましょう。先の手順でMySQLは起動しているはずなので、別のターミナルから次のように実行します。パスワード(ここでは「pa55wd」)は適宜お好きなものにしてください。(図10)

<図10:パスワードの設定>
$ mysqladmin -uroot password 'pa55wd'

設定したパスワードでアクセスできるか確認してみましょう。「mysql>」というプロンプトが表示されればログイン成功です。(図11)

<図11:パスワードの確認>
$ mysql -uroot -p
Enter password:   ← 設定したパスワードを入力します。
mysql>

ここまできたらあともう少しです。最後にOS起動時に自動的にMySQLが起動するようにしましょう。

まずは起動しているMySQLを停止します。

# kill $(cat /var/run/mysql/mysql.pid)

psコマンドで停止したか確認します。何も表示されなければMySQLは起動していないことになります。

$ ps axw | grep 'mysql[d]'

配布物に含まれている起動スクリプトを所定の場所にコピーして、MySQLの起動と停止ができるか確認します。(図12)

<図12:起動スクリプト>
# cp /usr/local/app/mysql/support-files/mysql.server /etc/init.d/mysql
# /etc/init.d/mysql start
Starting MySQL SUCCESS!
$ ps axw | grep 'mysql[d]'  ← mysqld_safeとmysqldが表示されるか確認します。
 3132 pts/3    S      0:00 /bin/sh /usr/local/app/mysql/bin/mysqld_safe
                      --datadir=/usr/local/mysql/data
                      --pid-file=/var/run/mysql/mysql.pid
 3170 pts/3    S      0:00 /usr/local/app/mysql/bin/mysqld
                      --basedir=/usr/local/app/mysql
                      --datadir=/usr/local/mysql/data
                      --user=mysql
                      --pid-file=/var/run/mysql/mysql.pid
                      --skip-locking
                      --port=3306
                      --socket=/var/lib/mysql/mysql.sock
    :    :    :
# /etc/init.d/mysql stop
Shutting down MySQL... SUCCESS!
$ ps axw | grep 'mysql[d]'  ← 何も表示されないことを確認します。

確認できたらOS起動時に自動的に実行されるように設定します。Debian/GNU Linuxの場合はupdate-rc.dコマンドを使います。実行したらrebootしてちゃんと起動するか確認しておきましょう。(図13)

<図13:自動起動の設定>
# update-rc.d mysql defaults 95 05
 Adding system startup for /etc/init.d/mysql ...
   /etc/rc0.d/K05mysql -> ../init.d/mysql
   /etc/rc1.d/K05mysql -> ../init.d/mysql
   /etc/rc6.d/K05mysql -> ../init.d/mysql
   /etc/rc2.d/S95mysql -> ../init.d/mysql
   /etc/rc3.d/S95mysql -> ../init.d/mysql
   /etc/rc4.d/S95mysql -> ../init.d/mysql
   /etc/rc5.d/S95mysql -> ../init.d/mysql

おわりに

連載第1回は、イントロダクションということで、

  • MySQL 4.1、5.0 で何が変わったのか
  • MySQL 5.0 の環境の準備

という内容をお届けしました。

いよいよ次回からは、エンジン全開でMySQLの新機能について突っ込んだ内容の記事をお届けしていきたいと思いますのでご期待くださいませ!

コラム:MySQL 5.0でmytopを使う

mytop(http://jeremy.zawodny.com/mysql/mytop/)とは、topコマンドのように様々な状態情報とMySQLの処理スレッドの情報を表示するツールです。定期的に情報を更新して表示できるので、高負荷時にサーバの状態をモニタするときなどに重宝します。また、スレッドをkillしたりもできる多機能で便利なツールです。

さて、MySQL 5.0に対してmytopを使う場合、少し変更が必要です。それは、5.0.2からSHOW STATUS文が変更され、GLOBALもしくはSESSIONというオプションが指定できるようになったのですが、省略時はSESSIONが指定されたものと同じになるためです。つまり、4.0や4.1ではSHOW STATUSでグローバルな情報を取得できたのですが、5.0ではセッションローカルな情報しか取れなくなってしまったわけです。具体的なmytopへの影響は、クエリの割合表示が全て0になってしまうなどがあります。

とりあえずの対応方法は、mytopの中に3個所あるSHOW STATUS文をSHOW GLOBAL STATUSに変更すれば期待した情報を表示できるようになります。ただしこの場合、4.0や4.1に対しては対応していない文法のために使えなくなってしまうので、変更したファイルの名前はmytop5などにして使い分けた方がよいでしょう。

著者名:ひろせまさあき/HIROSE Masaaki