minimize

事業拡大のため、新しい仲間を募集しています。
→詳しくはこちら

ここでは、MySQLに関する事を書いていきます。
新しく書いたものが上になるように並べてあります。

新しくユーザ(権限)を追加する

MySQLではOracleなどとデータベース・スキーマ・ユーザの考え方が違います。

ユーザ情報は、mysqlデータベースという特別なデータベースにより管理されています。
ここには host, user などいくつかの権限に関するテーブルが用意されていて
ここにレコードを作成したりすることで、各種権限管理を行います。

MySQL4以降では、このテーブルを直接いじることなくユーザ管理を行うコマンドが用意されました。
それが GRANT / REVOKE 構文です。他のデータベースではおなじみですね。

MySQLではユーザを追加するときに、以下の情報を入力する必要があります。

この中で少しやっかいなのがホスト名の設定です。
MySQLではユーザ認証時、ユーザ名だけでなく「どのホストから接続されたユーザか」
ということを認証の判断材料として利用します。
これによって、ユーザ名だけによる認証よりも強固な可能になっています。

とりあえず、まずは全てのホストから接続可能で全てのデータベースにアクセスできる
テスト用のユーザを作成してみましょう。

GRANT ALL PRIVILEGES ON *.* TO username@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

*.* の部分が接続可能なデータベースおよびテーブル名です。*.* なので、全て接続可能という意味です。

username@'%' という部分が、ユーザ名とホスト名の定義です。
ここでは、usernameというのがユーザ名で '%' というのがホスト名です。
ワイルドカード形式なので、全てのホストから接続可能という意味です。

しかし、これだけでは意外な落とし穴があります。
なぜかlocalhostから接続できないのです。
マニュアルによれば、既に localhost に関する認証エントリが作成されているので
そちらの方が優先されるとあります。
…よくわかりませんが、とりあえず次の一文を追加すればOKです。

GRANT ALL PRIVILEGES ON *.* TO username@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;

このように、localhostに特化したGRANT文を発行します。
これで、localhostも含めた全てのホストから接続可能なユーザが作成されました。

あ、当然ですが。外部に公開したマシンでこんなユーザは作らないで下さいね。

INSERT DELAYED構文

通常、クライアントがINSERT文を発行すると
サーバはそれに従ってレコードを挿入し、挿入が完了すると
クライアントに処理を返します。

しかし、他のスレッド(接続)がそのテーブルをロックしている場合には
そのロックが解除されるまで待つ必要があるため
このときクライアントは長時間待つことになります。

これを解消するために、INSERT DELAYED構文があります。
これは、INSERT文を発行したときに
そのレコードを即座に挿入するのでなく
レコード挿入のための「ハンドラ」を生成し
そのハンドラが保持するキューに挿入するレコードの情報を登録して
すぐさまクライアントに処理を返します。
ただし、一つのテーブルに delayed_queue_size 以上のレコードが
キューに登録されるとクライアントはキューに空きが出来るまで
待機します。

ハンドラは、テーブルが他のスレッドに使用されていないときを
見計らってキューに登録されたレコードをテーブルに挿入します。

この構文が使用できるのは、MyISAM / MEMORY / ARCHIVE 型のテーブルに限られます。
しかし実際には、MyISAMテーブルでこの構文を使用しなければならない場面というのは
そう多くありません。
なぜなら、このテーブルではSELECT文とINSERT文の同時実行が可能だからです。
つまり、INSERT文を発行したときに既にSELECT文が発行されていたとしても
MySQLサーバは待つこと無く即座にレコードを挿入できるのです。

MySQL4.0から5.0へのデータ移行について

MySQL4.0から5.0にデータを移行するときの注意点があります。
基本的に、MySQLはテーブルデータ内容をファイルで保持しているので
同じバージョンのMySQL間ならばファイルをそのままコピーしてOKなのですが
バージョンの異なるデータファイルをそのままコピーすると
うまくいかない場合があります。

具体的にはCHAR、VARCHAR型カラムの桁数が「1/3」になってしまいます。
これはおそらく、最近のMySQLがCHAR桁数の解釈を変えたことに
原因があると思います。
MySQL5.0では、CHAR(10)に格納できる文字は「10バイト」ではなく
「10文字(マルチバイト対応)」となっています。
このため、MySQL4.0でCHAR(30)と宣言していたところが
MySQL5.0ではCHAR(10)と解釈されてしまったようです。

ですから、MySQL4.0から5.0にデータを移行するときには
面倒ですがmysqldumpなどを使ってSQL文によるデータ挿入をしましょう。
余談ですが、もしファイルをそのままコピーしてしまった場合でも
既存データ内容が失われることはありません(確認済み)。
ただし、その後INSERTされるデータに関しては桁数を超えた文字が切り捨てられて
挿入されてしまうので、その前にALTER TABLEで桁数を変更しておけばOKです。

MySQL4.1での文字化けについて

MySQL4.1では、文字コードの扱い方が以前までと大幅に異なっているようです。
僕はJDBC経由での文字化けを解消するのにとても苦労しました。

MySQL4.1.13(Linux版)で確認した、文字化けの解消法を記します。
.my.cnf に以下の記述を追加します(EUC-JPエンコーディング指定の場合)。

[mysqld]

default-character-set = ujis

[client]

default-character-set = ujis

以前までは [mysqld] の方にしか default-character-set の記述はしてなかったのですが、
どうもこれだとJDBC経由で文字化けしてしまいます。
そこで、[client] の方にもこの記述を追加することで
文字化けはあっさり解消しました(Connector/J 3.1.10で確認)。

以前までは、JDBC接続文字列に文字エンコーディングの指定をしていたのですが
MySQL4.1ではサーバ側の(.my.cnfの[client])設定を優先しているようです。
その代わりに、JDBC接続文字列での文字エンコーディング指定は省略できるようになりました。
というか、指定しても無視されます。

MySQLでの日付・時刻カラムの扱い

MySQLはあらゆる面で、よりプログラマの視点に基づいた作りになっています。
例えば、日付型のカラムに「1999-02-00」という値を格納することが許されています。
OracleなどのDBでは、これらを格納しようとするとエラーが発生します。

利便性

「00」という日にちは、現実の世界では有り得ませんが
プログラムの世界では利便性の為に用いられることがあります。
例えば、ある日付の正確な日付がわからないときなどに
「2000-00-00」のように格納します。
これは「2000年であることはわかっているが、正確な月日がわからない」
ということを指します。
※ もちろんこの場合、DATE_SUB などの関数は正常に働きません

MySQLでは、プログラムの一部としてDBを位置付けています。
通常のプログラム言語では、日付の存在チェックなどは必ず行われています。
よって、DB側でこれらのチェックを再度行う必要は無いという事です。
そして、あえてチェックを行わないことで前述したような便利な使い方も可能になります。

多言語・クロスプラットフォーム

この前、Oracleを使ったプロジェクトで思わず問題が発生しました。
旧暦では「2004-02-30」のような日付(←例えば)が正しい日付として存在しているらしいのですが
Oracleの日付型カラムにはこの値を格納することが出来ません。
よって、このとき取った策は「全てのカラム型を文字列型に変更する」事でした。

こんな馬鹿げた話があるでしょうか。
旧暦も扱えないようなカラムを日付型と言っていいのでしょうか。
MySQLは、あらゆる言語・あらゆるOSでの利用を想定しています。
これは、多言語・クロスプラットフォームの現代では当り前の考え方です。

TIME型の場合

MySQLのTIME型は、'-838:59:59' ~ '838:59:59' の範囲を取ることが出来ます。
もちろんこれも、利便性の為です。

複数バージョンのMySQLを同一マシンから動かす

サーバ上のMySQLをバージョンアップするときなど、必要不可欠になるのがこの作業です。
現行のMySQLを動かしたまま、新バージョンのMySQLを動作させることが目標です。

確認に使用したバージョンは MySQL 4.0.18 です。
ちなみに、現在動いているMySQLのバージョンは 3.23.53 でした。
使用OSはRedHat Linux 7.2です。

新バージョンのMySQLをインストールする

今回はソースからコンパイルしました。

./configure --with-tcp-port=13306 --with-unix-socket-path=/tmp/mysql.sock-4.0.18 \
    --prefix /usr/local/mysql-4.0.18

使用するポート番号とソケットパス、それとインストールディレクトリを明記して
configureを実行します。
もしかしたら前の二つは実際には必要ないかもしれません。
結局後で設定ファイルに記述する事になるからです。
しかし今回はとりあえず初めてなので、念には念を入れてやることにします。

後は通常通りインストール作業を行います。

make
make install

ここで一つアドバイスを。
通常、make install の前にはchrootする事が多いと思いますが、今回は予めrootで

mkdir /usr/local/mysql-4.0.18
chmod someuser:someuser /usr/local/mysql-4.0.18

を実行しておき、chrootせずに make install を実行することにしました。
こうすれば、一般ユーザーでインストールする事になるので
/usr/local/bin 等に格納してある現行のMySQLコマンドを誤って消してしまうことが無くなります。
もし間違って消してしまうと、現行のMySQLが動かなくなる危険性もありますから。

設定ファイルを作成する

新バージョンのMySQLを実行するユーザーを新規で作成しましょう。
もしくは既存ユーザーでも構いません。
そのユーザーのホームディレクトリ上に、.my.cnf を作成します。

[mysqld]
port=13306
basedir=/usr/local/mysql-4.0.18
datadir=/usr/local/mysql-4.0.18/var
socket=/tmp/mysql.sock-4.0.18
log=/usr/local/mysql-4.0.18/var/log

設定項目の内容は以下の通りです。各自自分の環境に合わせて置き換えて下さい。

MySQLデータベースを初期化する

ソースを展開したディレクトリ上(MySQLをインストールしたディレクトリでは無い)で、

scripts/mysql_install_db

を実行します。なんかエラーが出るかもしれませんが、
データベース初期ファイルが生成されていればOKです。
datadir で指定したディレクトリに mysql, test という二つのディレクトリが
作成されていることを確認しましょう。

MySQLサーバーを起動する

ここまで来れば、あとはサーバを実行するだけです。
新バージョンのMySQLをインストールしたディレクトリ上で、

./bin/mysqld_safe &

を実行します。
MySQL4から、実行スクリプト名が以前の safe_mysqld から変わっています。内容は同じです。

Starting mysqld daemon with databases from /usr/local/mysql-4.0.18/var

というメッセージだけが出力されれば成功です。
設定ファイルに記述ミスがあった場合などは、
エラーメッセージが出力されてサーバは起動されません。

確認する

無事実行が確認できたら、実際にMySQLに接続しましょう。
現行のMySQLと新バージョンのMySQL、どちらにも接続できればOKです。
そして、新バージョンのMySQLだけをシャットダウンさせてみます。
新バージョンのMySQLをインストールしたディレクトリ上で、

./bin/mysqladmin shutdown

を実行して下さい。

mysqld ended

というメッセージが表示されるはずです。
最後に、この段階でも現行のMySQLに接続できることを確認しておきましょう。