FAQ のバックアップ(No.32)



目次:


全体概要

ライセンスはどうなっていますか?商用利用ではどうすべきですか?

MySQL のライセンスは単純です。 GPL か コマーシャルライセンスか。

GPL では具合が悪い、GPL では条件に合わない、GPL に納得できない場合に、コマーシャルライセンスを購入すればOK。

それは商用利用するかどうかには関係がありません。

GPL については http://www.gnu.org/home.ja.html をご覧ください。

2000年6月に、「GPLかコマーシャルライセンスか」への移行がありましたが、それ以前のライセンス(FPL)は 別物でした。
前のライセンス条項(FPL)は現在は適用されていません(無くなっています)。
古い情報で判断しないほうがお得です。



機能はどういったものがありますか?

MySQLの歴史 を見て。


MySQLインストール

mysqld が最低必要とする物

  • basedir/share/ ディレクトリ以下(shareファイル。errmsg.sys や charsets/)
  • datadir/mysql/ (mysql 権限データベース、テーブル)

権限テーブルや charsets/ がなければ mysqld は起動しない。
errmsg.sys はバージョンによって数が違うので、違うバージョンの errmsg.sys を使用していると mysqld が起動しない。
これらが起きた場合、.err ファイルにエラーが記録されないことが多い。



バイナリファイル

  • mysql-バージョン-ベンダ-OS-CPUアーキテクチャ.tar.gz
    Unix, Unixライク用のバイナリ。
    basedir は /usr/local/mysql/
    datadir は /usr/local/mysql/var/
    としてコンパイルされている
    展開して、/usr/local/mysql/ にする。

  • mysql-バージョン-win-noinstall.zip , mysql-noinstall-バージョン-win32.zip
    ms-win用。
    展開して、C:\mysql\ にする。
    NT系列の場合、サービスに登録したければ、mysqld-nt --install を使う。
    basedir は c:/mysql/
    datadir は c:/mysql/data/
    MySQL は Windows のレジストリには依存せず、コピーするだけで動く。

  • mysql-バージョン-win.zip , mysql-バージョン-win32.zip , mysql-essential-バージョン-win32.msi
    ms-win用。インストーラー付き。
    インストーラーがおかしいことがあるので、こちらはあまり使わない。
    mysql-バージョン-win-noinstall.zip (mysql-noinstall-バージョン-win32.zip) の方を使う。

Windows 用のバイナリ mysqld ファイル

4.1バイナリ名named-pipeBDB
mysqld.exe95系用--
mysqld-max.exemysqld.exe の、フル機能バージョン-O
mysqld-debug.exe--debug オプションを有効にして作ったバイナリOO
mysqld-nt.exeNT/XP 系用。named-pipe が使用できるO-
mysqld-max-nt.exemysqld-nt の、フル機能バージョンOO

MySQL サーバーの起動には、

  • サービスで起動するか
  • コマンドプロンプトで、直接 mysqld.exe を実行するか
  • winmysqladmin.exe で起動するか

の方法がある。

起動しない場合は、

  • c:\mysql\data\mysqld.err ファイルの中身を確認
  • c:\mysql\share\ がちゃんとあるのか確認
  • c:\mysql\data\ がちゃんとあるのか確認

Windows サービス

  • 登録
c:\mysql\bin\mysqld-max-nt.exe --install サービス名

mysqld のオプションを指定したい場合は:

c:\mysql\bin\mysqld-max-nt.exe --install サービス名 --defaults-file=c:/mymy.txt

のように、指定したいオプションをサービス名の後につける。

  • 削除
c:\mysql\bin\mysqld-max-nt.exe --remove サービス名



ダウンロード

ファイル名の規則が分かっている人は、こちらが早い。
http://ftp.lab.kdd.co.jp/database/MySQL/Downloads/
いちいち web のリンクをたどってられない人向き。




MySQL の SQL 文

四捨五入

  • ROUND() は四捨五入ではない場合がある。OS の C ライブラリに依存する。
  • 確実に四捨五入したければ、FLOOR() か TRUNCATE() を使用する。
    • FLOOR(c+0.5) や TRUNCATE(c+0.05, 2)


SELECT, DELETE, UPDATE と ORDER BY と LIMIT と replication

  • ORDER BY 無しで SELECT したら、どういう順序で返ってくるか分かりません。
  • DELETE .... LIMIT 10 で 10 レコードだけ DELETE します。
    が、SELECT と同じで、ORDER BY が無いと、どこが削除されるか、当たるも八卦、当たらぬも八卦になります。
  • UPDATE .... LIMIT も同様。
  • これは replication の時も同様。
    master で ORDER BY 無しの LIMIT を実行したとき、master と slave の返すものは、違うものになります。


auto_increment フィールドに 0 か NULL を入れた場合

  • 自動的に採番した値が入ります。
  • 0 から採番できません。auto_increment フィールドの値は 1以上の値になります。
  • auto_increment は 1 から採番する仕様です。


ALTER TABLE でテーブルを定義後に変更できますか?

  • そんなもん、最初っから。データあってもOK。


TIMESTAMP フィールド

  • 1つのテーブルに 2つ以上の TIMESTAMP フィールドを作った場合、1つめの TIMESTAMP しか動作しません。




MySQL運用

OS選択

2004-12末時点

  • 本格運用には、Unix, Unixライク OS を選ぶ。
    • MS-Win は、実験とか開発とかのレベルにおさえておく
  • Linux
    • glibc 2.3 使用。
    • LinuxThread 使用のこと。新しいスレッドは今は使わないように。
    • 中途半端なバージョンの glibc は使用しない。redhatがよくやるような、バージョン x.y.90 とかは避けよ
  • FreeBSD
    • 今のところ FreeBSD 4 では LinuxThread を使うこと。
      • MySQL AB 提供の LinuxThread 使用のバイナリには、NDBクラスターは入っていない。ネイティブスレッドの方には入っている。
    • FreeBSD 5 だと、ネイティブスレッドを使ってもいいかも。
    • 今のところ、うーん、な状態。


OS による MySQL の違い

OS による違いはありません。
どの OS 用の MySQL でも動きや機能は同じです。
MS-Winはシンボリックリンクやソケットの実装がないので、それを MS-Win 用にしているぐらい。それ以外は Unix 用と同じです



ストレージエンジン??

MySQL は、データの保存形式を複数持っています。 その保存形式により、機能や特徴、パフォーマンスが変わります。 これをストレージエンジンと呼んでいます。

このモデルの利点は:

  • 用途にあったストレージエンジンを選択することで、特別なチューニングを施すことなく、その目的に合った能力を得ることができる。
  • 古いバージョンのMySQLで作成したファイルは、MySQLをバージョンアップをしてもそのまま使い続けることができる。 MySQL のバージョンアップに際して、データの吸い上げ、入れ直しは発生しない。
  • 新しい手法が登場したとき、簡単にその手法を取り入れ、よりよい環境を提供できる。
  • あるストレージエンジンは他のストレージエンジンに影響を及ぼさない。 このため、バグの影響が少ない

実際の使い方は

CREATE TABLE ..... (.....) TYPE=InnoDB;

のようにするだけ。

ALTER TABLE .... TYPE=MyISAM;

のように、テーブルのストレージエンジンを ALTER TABLE 文で変えることも可能。(TYPEのかわりに ENGINE キーワードでも OK)

色々なストレージエンジン:

MyISAM検索に強い
InnoDBトランザクション, foreign key
NDBクラスター
HEAP(MEMORY)メモリ内で動作するテーブル
BDBberkley db
ARCHIVEINSERT, SELECT のみできる。
CSVデータファイルの中身が CSV 形式という変なエンジン
FEDERATEDリモートの MySQL サーバーにテーブルを集中させることができる。ver.5.0.3から
ISAM古い。機種依存度が高く、ファイルサイズの制限も低い。ver.5.0から外れそう


標準のストレージエンジン

標準のストレージエンジンは MyISAM。
すなわち、TYPE とか ENGINE を指定しなければ、テーブルの型は MyISAM になるということ。


標準のストレージエンジンの変更

標準のストレージエンジンを InnoDB に変更したい場合は

mysqld --default-table-type=innodb

もしくは

mysqld --default-storage-engine=InnoDB

もちろん、my.cnf (my.ini) に default-table-type=InnoDB を書いてもよい。

(4.1.10のms-win用のインストーラー付きバイナリは、インストール時にインストーラーが my.ini ファイルを作るようになっています。
自動で作成された my.ini に default-storage-engine=InnoDB が記述されています)


権限テーブルのテーブルの型

mysql データベース(権限データベース)のテーブルは、MyISAM , ISAM 以外にしてはいけない。



複数のMySQLを同居させるには/MySQLを違うディレクトリで動かすには

mysqld --basedir=/usr/local/mysql-4.0.22 --datadir=/usr/local/mysql-4.0.22/data --socket=/tmp/sock4.0.22 --port=4022 &

もしくは

/usr/local/mysql-4.0.22/my.cnf に

[mysqld]
basedir=/usr/local/mysql-4.0.22
datadir=/usr/local/mysql-4.0.22/data
socket=/tmp/sock4.0.22
port=4022

と書いて

mysqld --defaults-file=/usr/local/mysql-4.0.22/my.cnf &



I/O分散

MyISAMの場合

CREATE TABLE .... (...) DATA DIRECTORY = '/path/to/directory/MYD'  INDEX  DIRECTORY = '/path/to/directory/MYI'

InnoDB の場合1

[mysqld]
innodb_data_home_dir =
innodb_data_file_path=/path/to/file1:10M;/path/to/file2:50M

InnoDBの場合2

[mysqld]
innodb_file_per_table

OSでの対処の場合

  • データベースは単なるディレクトリなので、違うdiskをそのディレクトリにマウント
  • データベースのディレクトリをどこかに移動して、シンボリックリンクを張る



ログとリカバリ

log-bin (バイナリ更新ログ) が肝です。

# 3.22 までは、log-update (更新ログ) を使います。

log-bin 採取開始時のデータに対して、log-bin の内容(テキストのSQL文) を順に全て当てていけば、現在のデータになります。
もちろん、ログの途中まであてれば、その時までの状態になります。
roll forward リカバリですね。

どういう運用ができるかはあなたのアイデア次第。

  • max_binlog_size : 1つのバイナリ更新ログファイルの最大サイズ。これに達すると、自動でローテート
  • log-bin=名前 : バイナリ更新ログファイルの名前の指定。ディレクトリを含めることが出来る。 log-bin=/disk1/logfile とすると、/disk1/logfile.###### ファイルになる。


mysqlbinlog

バージョン 4.1 の mysqlbinlog には、バイナリ更新ログの切り出しに便利なオプションがあります。

  • --start-datetime=
  • --stop-datetime=
  • --start-position=
  • --stop-position=



バックアップ

  • mysqldump
    mysqldump --single-transaction --master-data --skip-opt --create-options --set-charset  --default-character-set=binary
  • バイナリ更新ログ
    • バイナリ更新ログ(log-bin)ファイルと、log-bin採取開始時のデータベースの状態があればいい
  • ファイルの直接コピー
    • tar, rsync, cp, cpio など
  • InnoDB Hot Backup
    • InnoDB のみ。
    • hot back up ツール
    • mysqld 実行中のサーバー上で実行する



テーブルの修復、フラグメンテーションの改善

  • MyISAM の場合
    REPAIR TABLE
    OPTIMIZE TABLE 
  • InnoDBの場合
    • 通常の(いきなりの電源断)の障害からの復旧は、InnoDB の REDO ログファイルから自動で修復
    • フラグメンテーションの改善は、ALTER TABLE か テーブルの作り直しか。



日本語環境の設定

標準のキャラクターセットが日本語になっていないバイナリを使用するときは、設定をしておきます。

[mysqld]
default-character-set = ujis

[mysqldump]
default-character-set = ujis

[mysql]
default-character-set = ujis

なお、language = の指定は、日本語が扱える、扱えないには、全く関係ありません。設定しても無意味。
たんにエラーメッセージが日本語になるだけで、クエリやデータを日本語扱いにするという意味ではありません。
エラーメッセージが日本語になっても邪魔くさい(変な文だし、文字化けするかも)ので、通常は language は指定しません。

また、全てのアプリが my.cnf を読むわけではありません。勘違いしないように。
my.cnf を読むのは、原則、MySQL 付属のコマンドだけと思ってください。
それ以外は、アプリ側で読むようにコーディングする必要があります。
(この FAQ の「MySQL連携」を参照。)



my.cnf ファイルの読み込み順番

後から読み込まれた設定が、前の設定を上書きします。

  • Unix系列の場合
    • /etc/my.cnf → データディレクトリ/my.cnf → --defaults-extra-file=で指定されたファイル → ~/.my.cnf
    • 「データディレクトリ/my.cnf」 は、コンパイル時に指定されたデータディレクトリ固定です。
  • MS-Windows 系列の場合
    • %WINDIR%\my.ini → C:\my.cnf → --defaults-extra-file=で指定されたファイル
    • my.ini , my.cnf が同時に存在する時は、両方読まれる。my.ini が先に読まれる(今は)

特別に:

  • 全ての OS において
    • --defaults-file= で設定ファイルが指定された場合、指定されたファイルだけが読まれる。



データベース名、テーブル名、フィールド名

データベース名、テーブル名、フィールド名に日本語は使えますか?

使うな。使えるけど。

case sensitive? case in-sensitive?

フィールド名は case in-sensitive (大文字小文字を区別しない)。
データベース名、テーブル名は、OS依存。
Winのファイルシステムは case in-sensitive なので、DB名、テーブル名は case in-sensitive。
Win の場合は、mysqld --lower_case_table_names=2 にしておくこと。



init-connect

クライアントが接続時に、自動的にサーバー内部で実行される SQL 文を指定できる。
ただし、接続ユーザーに SUPER 権限がある場合は、この SQL 文は無視される。

ex. mysqld --init-connect='SET AUTOCOMMIT=0'



AUTOCOMMIT モード

autocommit をはずす。

SET AUTOCOMMIT=0;

autocommit mode

SET AUTOCOMMIT=1;

標準は、AUTOCOMMIT=1



大きなフィールドを含む場合

mysqld --max_allowed_packet=

を指定します。
version 4.0 より前は、max_allowed_packetに指定できる値の上限は 16M まででした。
4.0 からは 1G まで指定できます。
これにより、1G弱の大きさの値を LONGBLOB フィールドに入れて扱うことができます。

なお、

  • TINYBLOB, TINYTEXT : 255 (2^8-1) バイト
  • BLOB, TEXT : 65535 (2^16-1) バイト
  • MEDIUMBLOB,MEDIUMTEXT : 16777215 (2^24-1) バイト
  • LONGBLOB, LONGTEXT : 4294967295 (2^32-1) バイト



多くの接続がある場合

mysqld --max_connections=500

これは同時接続数を500まで許すということ。
標準では 100。
接続数を増やす場合は、メモリーの使用量に注意。
1接続に対して1スレッドが割り当てられます。

また、これだけではなく、さらに以下も考慮します。

mysqld_safe --open-files-limit=
mysqld --back_log=



メモリーの使用量

単純な計算式:

グローバルでのメモリー割り当て量 + 各スレッドへのメモリー割当量 * 接続数

注意:

  • ハードウェアやOSによって、1つのプロセスに割り当てられるメモリーの上限値に違いがあることに注意
  • メモリーが割り当てられなかったとき、子供のスレッドは立ち上がることができない。


変数(全部は書きません。バージョンによって違うし、常に指定される量を最大限に取るとも限らない)

  • グローバルでの割り当て、代表的な変数:
    • bdb_cache_size
    • bdb_log_buffer_size
    • innodb_additional_mem_pool_size
    • innodb_buffer_pool_awe_mem_mb
    • innodb_buffer_pool_size
    • innodb_log_buffer_size
    • key_buffer_size
    • query_cache_size
  • 各スレッドへの割り当て、代表的な変数:
    • join_buffer_size
    • read_buffer_size
    • read_rnd_buffer_size
    • sort_buffer_size



クエリー、接続の記録

全ての接続情報、実行したクエリーを記録するには

mysqld --log

を使用します。
いつ、どのユーザーが、どのホストから接続してきて、
いつどういうクエリー(SELECTも全部)を実行したか記録される。




4.1以上の壁 (日本語の扱いの違い)

MySQL 4.0 までは、3.X から 4.0 に upgrade しても、そのまんまなんの影響もなく古いデータを使えました。特にダンプしてリストアしなくても良かったし、ALTER TABLE も必要ありませんでした。

ところが、4.1 以上に upgrade するときは、我々日本語キャラクターセットを使う人間は、注意しなくてはならない点が複数存在します。
4.1 は、5.0pre だと思ってもいいぐらいの開きがあります。(4.0 と 4.1 を同じバージョン 4 だとは思わない方がいいという意味で。)



クライアントとサーバーのキャラクターセットの確認

まず、自分の状態を知るための SQL 文

SHOW VARIABLES LIKE 'char%';

を覚えてください。



クライアントのキャラクターセットが binary
サーバーのキャラクターセットが ujis
の場合、以下のように表示される。

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | binary                     |
| character_set_connection | binary                     |
| character_set_database   | ujis                       |
| character_set_results    | binary                     |
| character_set_server     | ujis                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)



サーバーとクライアントのキャラクターセットが同じ場合は、
文字の自動変換は起きず、
文字化けは発生しない。

| character_set_client     | ujis                       |
| character_set_connection | ujis                       |
| character_set_database   | ujis                       |
| character_set_results    | ujis                       |
| character_set_server     | ujis                       |



クライアントが binary の場合は、文字の自動変換は起きない。
文字化けは発生しない。
しかし、クライアントの SQL 文のエスケープに注意すること。

| character_set_client     | binary                     |
| character_set_connection | binary                     |
| character_set_database   | ujis                       |
| character_set_results    | binary                     |
| character_set_server     | ujis                       |



クライアントが latin1 で、
サーバーが日本語の場合は、
確実に文字が壊れる。

| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | ujis                       |
| character_set_results    | latin1                     |
| character_set_server     | ujis                       |



クライアントとサーバーがマルチバイトキャラクターセットだが、
違うキャラクターセットの場合、
幾つかの文字は破壊されるかもしれない。

| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | ujis                       |
| character_set_results    | utf8                       |
| character_set_server     | ujis                       |



4.1 では何が変わったの?

  • CHAR(10) は 10 バイトから 10 文字に変わった。
  • char() に、全てのバイト列が入らなくなった。例えば、ujis のフィールドには、EUC-JP に定義されるコード空間のバイトしか INSERT できない。全てを入れるパッチは http://www.mysql.gr.jp/frame/modules/bwiki/?Contrib
  • パスワードの保存形式が変わった。(長くなった)
  • 文字コードの自動変換機能が追加された。
  • mysqldump の標準キャラクターセットがどうコンパイルしても utf8 になる
  • データベース、テーブル、フィールド、それぞれ個別にキャラクターセットを指定できるようになった。
  • データベース名、テーブル名は、OS 上では utf8 コードに直されて保存されるように変わった(ディレクトリ名、ファイル名が、utf8のコードで書かれるようになったということ)。4.0 まではバイナリ列がそのままデータベース名(=ディレクトリ名)、テーブル名(=ファイル名)になっていた。
  • timestamp 型の表示フォーマットが変わった。4.0 までは 「20050116214504」 であったが、4.1 では 「2005-01-16 21:45:50」。アプリの作り方に注意。
  • HEAP(MEMORY) テーブルのインデックスに、BTREE が加わった。4.0 までの HEAP テーブルは HASH のみ。(i int, INDEX USING BTREE (i)) TYPE=MEMORY;
  • sub query の追加
  • NDB クラスターの追加
  • GIS追加
  • utf8 キャラクターセットの追加


CHAR の定義の違いによる弊害

これは 3.X, 4.0 から 4.1 に upgrade したときに遭遇する問題です。
3.X, 4.0 で char(10) と定義していたフィールドが、 4.1 に uprade した瞬間に、char(3) もしくは char(5) 扱いになってしまいます。
なぜか?

  • ujis は、最大で 3バイトを使用して 1文字を表現する。sjis は最大で 2バイトを使用して sjis 1文字を表現する。
  • 4.0 で CHAR(10) と定義した。これは 10 バイト。
  • 4.1 に upgrade したら、10バイトを、X文字にしなければならない。
  • そこで、ujis の場合は、10バイト -> 3(ujis)文字となる。


文字コードの自動変換機能による弊害

4.0 までは、キャラクターセットはサーバーだけが設定するものであり、クライアントがサーバーのキャラクターセットに自動的に合せて動作していました。

ところが

4.1 になってから、サーバー、クライアントがそれぞれのキャラクターセットで動作するようになりました。

例えば、

  • クライアントが sjis で動作
  • サーバーが ujis で動作

している場合、サーバーは、クライアントに対しては常に sjis のデータを送るようになります。

サーバーは ujis のデータを sjis に変換して、クライアントに送るのです。

これは一見便利です。

が、落とし穴があります。

  1. 変換できないキャラクターセットの組み合わせの場合
  2. 変換しきれない文字があった場合

こういう場合は、文字が ? になったり、期待外れの文字になったりします。

例えば、

  • クライアントが latin1 で動作
  • サーバーが sjis で動作

の場合、どうやって sjis を latin1 の文字コードに変換できるというのでしょう?
出来るわけがないので、sjis 文字はあえなく全て ? になってしまいます。
(え、「?」にしなけりゃいいのにって?
私もそう思います。変換できなければ「?」にせず、そのままのバイトのままにしておけば クライアントが latin1 でも問題が少なかったかもしれません。が、仕様が「?」に変換なので、なんとも。)

例えば、

UTF を使用してアプリを作っている方は経験されているでしょうが、 UTF の変換マップというのは統一が取れていません。
同じことが MySQL にもおきています。
MySQL が変換するコードと、あなたが使用している環境が考えるコードと文字の形の組み合わせが一致するとは限りません。

では、クライアントが latin1 で、サーバーが ujis,sjis の状況というのは、起きうるものなのでしょうか?

困ったことに、現状では簡単にこの状態が引き起こってしまいます。

あなたが使用している PHP は、自分でコンパイルしましたか?
あなたが使用している MySQL は、ご自身の手でコンパイルしましたか?
ほとんどの方が、他の誰かがコンパイルしたものを使用していると思います。
では、あなたが使用している PHP, MySQL のライブラリ(libmysql.dll, libmysqlclient)の標準キャラクターセットは何かご存じですか?
そう、ここが問題なのです。
日本人以外のデベロッパーが作ったバイナリは、ujis,sjis を標準のキャラクターセットにしているわけがありません。
事実 MySQL AB 配布のバイナリは、latin1 が標準です。
それらのバイナリを使って PHP の MySQL モジュールを動かせば(作成すれば)、クライアント(PHP)は latin1 で動作し、サーバーは ujis,sjis で動くことになるのです。こうして日本語文字は破壊されます。

これらの問題を避けるには、方法は2つ。

  • PHP(Ruby,Perl,C,...)のアプリの変更。サーバーに接続した後にすぐ、"SET NAMES キャラクターセット名" という SQL 文を実行する
  • PHP(Ruby,Perl,C,...)の MySQL モジュールの標準キャラクターセットを、自分が使うキャラクターセットにする。これはアプリの変更はない。しかし、libmysql.dll, libmysqlclient のコンパイルし直しが発生する。



mysqldump の仕様変更の弊害

mysqldump は、3.X, 4.0 までは、コンパイルするときに指定されたキャラクターセットを標準としていました。
4.1 からは、mysqldump はコンパイル時のキャラクターセットを無視して、utf8 を標準とします。(ひどい。普通、mysqldump のキャラクターセットは、mysqlコマンドとかのキャラクターセットと同じだと思って使うよなぁ...)

これは、4.1 の文字コード自動変換機能と組合わさって、最低な状況を起こしてしまいます。

サーバーが ujis, sjis で動いていたとしても、utf8 に変換して dump します。
utf8 はご存じの通り、到底整理されているコードとは言い難く、そのため、dump した内容が壊れる可能性が高いです。

これを避けるためには、mysqldump を実行するとき、必ず default-character-set オプションを指定すべきです。

個人的には以下が好みです。

[mysqldump]
default-character-set=binary
skip-opt
create-options
set-charset
hex-blob
single-transaction
master-data

なお、コンパイル時のキャラクターセットを mysqldump が無視しなくなる、 mysqlコマンドと同じキャラクターセットと同じにするパッチは以下にあります。
http://www.mysql.gr.jp/frame/modules/bwiki/?Contrib



パスワード保存形式の変更

4.1 からは、パスワードの長さが長くなりました。

しかし、3.X, 4.0 までのパスワードはそのまま使えます。 3.X, 4.0 までの mysql データベースの内容は、そのまま引き続き使用できます。

Password
4.0まで12de31820cbc0fce
4.1*59170D1E4A5E56267B6ED9C51ED62619FB817E6B

3.X, 4.0 から 4.1 に upgrade して、mysql_fix_privilege_tables を実行しても、4.0 までの時代に設定した短いパスワードは変更されません。上記例では '12de31820cbc0fce' のままです。
4.1 であたらしくパスワードを設定した場合、'*59170D1E4A5E56267B6ED9C51ED62619FB817E6B' の形式になります。

4.1 のクライアントは、長い方のパスワードでも短い方のパスワードでも認証可能です。

4.0 までのクライアント( libmysqlclient , libmysql.dll ) と 4.1 のサーバーは、長い方のパスワードを使用したら、必ず認証失敗になります。
(「Client does not support authentication protocol requested by server;」)

4.0 までのクライアントと 4.1 のサーバー間の認証が成功する条件は、

  • 保存されているパスワードが短い方の形式である場合
  • 4.1 サーバーを4.0までのパスワード形式のみで動作させている場合 に限ります。

4.1 サーバーを4.0までのパスワード形式で動作させるには

[mysqld]
old-passwords

を指定します。
これは 4.1 サーバーが生成するパスワードの形式を、4.0 までの短い方にするオプションです。



データベース名、テーブル名、フィールド名は、utf8 コードに直されて保存されるように変わったことの弊害

MySQL は、データベースをディレクトリ、テーブルを(基本的に)ファイルで表現します。フィールド名は .frm ファイルに記録されています。

4.0 まではバイナリ列がそのままデータベース名(=ディレクトリ名)、テーブル名(=ファイル名)、フィールド名になっていました。

ところが 4.1 からは、ディレクトリ名、ファイル名、フィールド名が、utf8のコードで書かれるようになったのです。
このために、4.0 までの MySQL で作成した日本語データベース名、テーブル名、フィールド名は、サーバーを 4.1 に upgrade したとたん、使えなくなります。

4.0 までに使用していた日本語のデータベース名、日本語のテーブル名、日本語のフィールド名を 4.1 に引き継ぐためには、

  • 4.0 までのデータを一度フルダンプして(ファイルのコピーじゃ駄目)、
  • 4.1 にバージョンアップしたあとに、
  • フルリストアする必要があります。



3.X,4.0 -> 4.1 以上の手順

  1. クライアントの upgrade Ruby, PHP, Perl など、C クライアントを 4.1 (以上)の libmysqlclient にリンクし直してインストール。

    なお、文字の自動変換機能による弊害が発生する可能性があるので、他のFAQを参考のこと。

  2. 念のため、mysqldump でバックアップ
  3. upgrade
    [mysqld]
    default-character-set = ujis
    old-passwords
    
    [mysqldump]
    default-character-set = ujis
    skip-opt
    
    [mysql]
    default-character-set = ujis

    old-passwords オプションは、4.1以上のサーバーの認証方法を、4.0 までの認証方法のままにするということ。

    mysqldump は、標準では全てのデータを utf8 に変換してしまうという極悪な仕様に変わったので、それを避けるために default-character-set を指定。

  4. 権限テーブル upgrade mysql_fix_privilege_tables もしくは mysql -f -uroot mysql < mysql_fix_privilege_tables.sql
  5. 全ての CHAR(), VARCHAR() を ALTER する。 4.1 以上では CHAR(10) は 10文字を意味するようになったため。 http://www.mysql.gr.jp/frame/modules/bwiki/?Contrib 他のFAQも参照



4.1サーバーと4.0クライアント

[mysqld-4.1]
old-passwords

MySQL 4.1 サーバーを 4.0 までの認証で動かす。 こうすれば、3.X, 4.0の libmysqlclient(libmysql.dll) をリンクしているアプリケーションと 4.1 サーバーがきちんと接続できる。



version 4.1 以上の文字コード変換機能とうまくつきあうには?

MySQL version 4.1 から、utf8 が組み込まれたり、文字コードの自動変換が組み込まれたりと、けっこう文字周りがかわりました。 4.0までの MySQL とは感覚が変わるので、一応、こうした方がトラブルは少ないね、という経験上のものを書いておきます。

  • mysqldump には必ず --default-character-set= を指定すること。
  • MySQL サーバーとクライアントは、必ず同じキャラクターセットにしておくこと。
  • 4.1以上対応のアプリケーションには、必ず "SET NAMES キャラクターセット名" という SQL 文を、サーバーに接続した直後に実行すること。
  • データベース名、テーブル名、フィールド名には、マルチバイト文字は使用しないこと。
  • マルチバイトキャラクターセットと、latin1などのシングルバイトキャラクターセットを混在させないようにすること。
  • キャラクターセットはデータベース単位、テーブル単位、フィールド単位で指定できるが、フィールド単位でのキャラクターセットの指定はなるべく避けた方がよいだろう
  • 文字の自動変換機能を抑止する、--default-character-set=binary の使用が便利。
  • 既にコンパイル済みのバイナリを使うときは、そのバイナリが作成されたときに埋め込まれている、標準のキャラクターセットを知っておいた方がよい。
    • !!! MySQL AB のバイナリは latin1 が標準 !!!
  • 自分でコンパイルする場合は、binary キャラクターセットを標準に指定してコンパイルすれば、アプリの変更や設定の変更無しに、MySQL 4.0,3.23 対象のアプリが動く。
  • --init-connect='SET NAMES キャラクタセット名' は使わない。これはクライアントコマンドオプション --default-character-set= を無効にしてしまうから。
  • 4.1 の mysqldump には --hex-blob というオプションが追加されている。これは BLOB のバイナリデータを壊さずに mysqldump 可能になる。



4.1 にupgradeしたら文字が?になります / 4.1 にしたらクエリの結果がおかしいことがある

4.1 から文字の自動変換機能の追加、char() の仕様変更がありました。
なので、サーバーを 4.1 に upgrade すると、文字が ? になったり、文字が変になったり、クエリーの結果がおかしくなったりすることがあります。
4.1 に upgrade するならば、以下のようにします(アプリの変更をしないための方法)。

  1. まず、ソースから MySQL をビルドし直す。charset は binary にして。(後述、備考参照)

  2. ビルドしてできた libmysqlclient (windows の場合は libmysql.dll) を使用して、PHP, Perl, Ruby, などの MySQL モジュールをコンパイルし直す。

  3. サーバーを 4.1 にする前に、念のためバックアップを取っておく

  4. サーバーを 4.1 に upgrade する。
    /etc/my.cnf には
    [mysqld-4.1]
    old-passwords
     
    [mysqld]
    default-character-set=使用するキャラクターセット名
     
    [mysqldump]
    default-character-set=使用するキャラクターセット名
    skip-opt
    
    [mysql]
    default-character-set=使用するキャラクターセット名 

    を書いて、4.1 の サーバー起動

  5. mysql_fix_privilege_tables を実行して、mysql データベースの権限テーブルを upgrade する。
    Windows の場合は、
    mysql -f -uroot mysql < mysql_fix_privilege_tables.sql
    とする

  6. すべてのテーブルを ALTER する。
    char() の定義がおかしくなっているので、指定し直す。
    Unix の場合は、MyNA の http://www.mysql.gr.jp/frame/modules/bwiki/?Contrib にあるシェルスクリプトを使ってもよい。

  7. また、mysql コマンド、mysqldump コマンド等は、必ず、default-character-set= を指定して使用しましょう。
    character-set はサーバーと同じにしましょう。

  • アプリのソースを変更して対応する方法:
    クライアントプログラムのソースに、'SET NAMES ' 文を追加する方法もあります。この場合は、1,2 の手順は必須ではなくなります。
    MySQL サーバーに接続した直後に、'SET NAMES クライアントのキャラクターセット名' という SQL 文を実行します。



  • 備考. ソースからのビルド:
    Unix なら ./configure --with-charset=binary にする。 Windows なら、
    include/my_config.h , include/config-win.h の
#define MYSQL_DEFAULT_CHARSET_NAME "latin1"

#define MYSQL_DEFAULT_CHARSET_NAME "binary"

に。

#define MYSQL_DEFAULT_COLLATION_NAME "latin1_swedish_ci"

#define MYSQL_DEFAULT_COLLATION_NAME "binary"

に。



mysql コマンドでは大丈夫なのに、PHP,perl,accessなどで文字が ? に化けるのは?

良くある勘違いは、
「mysqlコマンドが動いているから、自分のアプリも正しくコード変換するだろう」
というものです。
これは間違いです。

次の節の概念図をじっくり見てください。

MySQL サーバーとおしゃべりしている libmysqlclient.so (libmysql.dll) に、キャラクターセットが埋め込まれています。

mysql コマンドには default-character-set のオプションがあり、キャラクターセットの指定ができます。
これは libmysqlclient.so (libmysql.dll) の埋込キャラクターセットを上書きします。

しかし、MySQL AB が提供しているコマンド(mysqldump, mysql, mysqlimport等)や MyODBC 以外の、
別の誰かが作ったアプリやコマンドは、
my.cnf を読んだり、default-character-set オプションがあったりするわけではありません。
my.cnf を読んだり、default-character-set オプションが使えたり、SET NAMES 文が実行されたりするのは、
あくまでも、アプリ側の責任、作り手の責任なのです。
上の図で言えば、PHPMyAdmin や PHP 自身の部分に、キャラクターセットの指定をする処理や my.cnf を読む処理を、作り手が意図して書かない限り、実現しないのです。

これは perl だろうが Ruby だろうが、access だろうが、PHP と同じです。
(MyODBC(Connector/ODBC)は設定をすることで、my.cnf の [odbc] グループを読みます。
access で MyODBC を使用しているなら、my.cnf を読むオプションを有効にしておきます。)


コードを書くのが嫌なら(変更することが出来ない状況なら)、libmysqlclient (libmysql,dll) の埋込キャラクターセットを変えるのです。
これは libmysqlclient(libmysql.dll)のコンパイルのし直しを意味します。



いままでの説明の概念図

例えば、PHP のアプリケーションの場合、次のような階層構造になります。
(等幅フォントで見てね)

+---- PHPアプリ ---------+
| 例えば PHPMyAdmin など |
|                        |
|       connect()        |
| SET NAMES sjis;        | <- SET NAMES を実行するのはこの層
+------------------------+
   ↓↑
+------- PHP -------------+
| PHP エンジン            |
|  ↓↑                   |
| PHP の MySQL モジュール |
| mysql.so (UNIX)         |
| mysql.dll (Windows)     |
+-------------------------+
   ↓↑
+----------------------------+
| libmysqlclient.so (UNIX)   |      +---- MySQL サーバー ------+
| libmysql.dll (Windows)     |      |                          |
|標準キャラクターセット埋込  |  ←→ [文字コード変換ルーチン]  | <- こいつが曲者
|  例えば latin1 とする      |      |         ↓↑             |
+----------------------------+      | 内部の処理。結果を返す   |
  SET NAMES が実行されない時        |                          |
  埋込の標準charsetが使用される     | サーバー側のcharsetは    |
    これがとても曲者                | 例えば sjis とすると     |
                                    | 「あ」は 0x82A0          |
                                    +--------------------------+

この例では、SET NAMES 文を実行しない場合、latin1 <-> sjis の組み合わせになる。
latin1 には 0x82A0 のコードがないので、「あ」は「?」に変換されてしまう。


char(10) が char(3) になるトリック

4.0 まで  : 1 2 3 4 5 6 7 8 9 10
内部バイト: ■■■■■■■■■■
4.1(ujis) : +----+-----+-----+
               1     2     3



4.1以上の日本語に関する問題点まとめ

  • 配布されているバイナリがlatin1であり、文字破壊が起きてしまうこと。
  • コードのマッピングの問題
  • euc<->sjis の変換の時、一度utf8を通ること
  • 自動変換を止めるオプションがないこと
    • 自動変換を止めるにはクライアントをbinaryにするか、サーバーと同じcharsetにするしかない。
      それだけでは全ての場合に対応できない。
      昔のアプリやlatin1のバイナリを使っていても文字破壊や誤変換を防ぐ方法がないと、
      導入や移行の障害となる
  • 文字破壊の原因解明が大変
    • PHPMyAdmin でたとえるなら、PHPMyAdminの層、PHPの層、MySQLクライアントライブラリの層、MySQLサーバーの層、というように、階層が多すぎて、原因の特定に時間がかかる。



結局、どうしろと?

  • MySQL-4.0, 3.X を現在運用しているひとは:
    原則、今のままキープですね。
    サーバーを上げるといくつか問題に遭遇します。
    充分な検証の結果、解決の目処がたってから、サーバーをバージョンアップしましょう。

  • これから新しく導入する人は:
    はじめから 4.1 以上でも OK。
    • ただし、4.0までのアプリの資産を流用したい場合は、old-passwords や default-character-set 、SET NAMES 文に注意しましょう。
    • これから作るアプリには必ず 「SET NAMES キャラクターセット名」 を接続直後に実行しましょう。
    • サーバーとクライアントのキャラクターセットは同じにする。



  • 4.1以上にバージョンアップするときに
    • アプリの変更をしたくない人は:
      • MySQL サーバー、クライアント、libmysqlclient (libmysql.dll) を使用する全ての クライアントをコンパイルし直す。
    • アプリの変更をしてもかまわない人は:
      • アプリのソースに、「SET NAMES クライアントのキャラクターセット名」 SQL 文を追加する。追加する位置は、MySQL サーバーに接続直後にする。
    • old-passwords を指定しておく




MySQL連携

エスケープ

クエリに与える文字列はエスケープしますが、基本的に MySQL 側が用意しているエスケープ関数を使うべきです。
キャラクターセットに従ってエスケープしてくれるからです。
SQLインジェクション対策のためにもエスケープは必要です。



C

エスケープ

  • mysql_escape_string()
  • mysql_real_escape_string()

を使う

my.cnf を読む

mysql_options(&mysql, MYSQL_READ_DEFAULT_FILE, "/etc/my.cnf");
mysql_options(&mysql, MYSQL_READ_DEFAULT_GROUP, "groupname");



PHP

エスケープ

  • mysql_escape_string()
  • mysql_real_escape_string()

を使う

my.cnf を読む

PHP version 4 の場合
読めない。[client] に書いても無駄。
http://www.mysql.gr.jp/frame/modules/bwiki/?Contrib のパッチを当てる。

PHP version 5の場合
mysqli モジュールに、my.cnf を読む関数が用意されている。

mysqli_options(connection, MYSQLI_READ_DEFAULT_FILE, "/etc/my.cnf");
mysqli_options(connection, MYSQLI_READ_DEFAULT_GROUP, "php");

NULL の扱い

  • DBのデータが NULL の場合、PHP は "" を受け取る。NULL と空文字の区別が出来ない。



Ruby

エスケープ

escape_string() メソッドを使う

my.cnf を読む

my.options(Mysql::READ_DEFAULT_FILE, "/etc/my.cnf")
my.options(Mysql::READ_DEFAULT_GROUP, "ruby")

NULL の扱い

  • DBのデータが NULL の場合、Ruby は nil を受け取る。空文字ではない



Perl

エスケープ

DBI の quote() メソッドを使う。

my.cnf を読む

$dsn = "DBI:mysql:test;mysql_read_default_group=perl;mysql_read_default_file=/etc/my.cnf";




MySQLインターナル

SMP, 64bit対応ですか?

最初っから。

SMP は CPU を増やすだけで OK。特別な設定や作り直しは不要です。

追記型ですか?

いいえ

vacuumありますか?

いいえ。

かわりに

MyISAM の場合は、OPTIMIZE TABLE 文か、REPAIR TABLE 文をします。

InnoDB の場合は、ALTER TABLE () TYPE=InnoDB を実行します。

たまにやればいいでしょう

トランザクション分離レベル

InnoDBBDBNDB
READ UNCOMMITTED--
READ COMMITTED
REPEATABLE READ--
SERIALIZABLE--

InnoDB の標準は REPEATABLE READ。

InnoDBの REPEATABLE READは、phantom read は起きない実装。

ロック

InnoDB行レベルロック
NDB行レベルロック
BDBページレベルロック
MyISAMテーブルレベルのロック
  • InnoDB、NDB の場合
    • 必ず テーブルに primary key を作ります。
    • LOCK TABLES は使わないように。
    • 明示的にロックしたい場合は、InnoDB では、SELECT .... FOR UPDATE、 SELECT ..... LOCK IN SHARE MODE を使う。
    • NDB の SELECT .... LOCK IN SHARE MODE、SELECT .... FOR UPDATE は将来対応予定



マルチバージョニング

InnoDB は マルチバージョニング を実装しています



レプリケーション

テーブル型(ストレージエンジン)

  • どのストレージエンジンでもレプリケーション可能
  • master と slave のテーブルの型が違っても動く



埋込MySQLサーバー

MySQLサーバーはライブラリ化されています。

./configure --with-embedded-server

libmysqld.a (libmysqld.dll) がそれです。
このライブラリに埋め込まれているのは

  • SQL エンジン
  • 各種ストレージエンジン

です。

埋め込まれていないのは

  • 認証
  • ネットワークからの接続管理



最大のフィールド数、レコード長、等

MyISAMInnoDB
最大のフィールド数4096(でも実際は2500から2700前後で頭打ち)1000
実際はフィールドの長さ制限に左右されて少なくなる
最大のレコード長BLOB,TEXTを除いて65535バイトBLOB,TEXTを除いて8KB。
BLOB,TEXTを含んだレコードは4GBまで
最大のキーの長さ1000バイト1024バイト
4.1.2より前は500バイト(InnoDB内部は3500だが、MySQL上位層の制限による)



SELECT COUNT(*)

1テーブルの WHERE 無しの SELECT COUNT(*) は、

  • MyISAM の場合はテーブル情報(レコード数が書かれている)からレコード数を直接読み込みます。
  • InnoDB の場合はインデックスツリーをその都度調べます。

なので、SELECT COUNT(*) の計測結果を取って両者を比較するというのは、
前提条件の違う物を比較しているので、
その結果からの考察を全くうのみにすることはできません。



InnoDB

InnoDB テーブルには、primary key を必ず作ってください。

クラスタードインデックスとレコードについての誤った見解

「新しいレコードをインサートするたびに、全てのレコードをキー順になるように、全てのレコードを並び換えてディスク上に保存している。」
これは誤りです。
キー順にレコードが追えればいいわけで、 それは別に物理的な(ディスク上の)順序がキー順というわけではなく、 レコードを指している物をキー順に追えればいい実装になっています。
レコードへのポインターを操作しているにすぎません。


auto_increment

InnoDB の場合、AUTO_INCREMENT の取るべき値は、メモリー上にあります。
mysqld 起動時に、最大の値がチェックされて、メモリーに保存されます。
(MyISAM の場合は、ファイルに書かれています。)


途中で障害等でセッションが切れたときは?

ROLLBACK


誰かがロックを持っていた場合、ほかの人がそのロックを取ろうとしたら?

ずーーーとロックが外れるまで待つわけではなく、
待ち時間が 50"(innodb_lock_wait_timeoutの値) に達したら、デッドロックエラーにします。

Oracle は標準では待つ。


デッドロックのエラーが発生したとき?

デッドロックエラーが発生したとき、自動で ROLLBACK します。
また、ROLLBACK以外のクエリーも実行できます。

Oracle は、デッドロックエラーが発生しても、そのまま ROLLBACK も何もせず、そのままトランザクションが続いている。
postgresql は ROLLBACK 文しか受け付けない状態に陥る


別人が同じ部分のロックを得ようとしたとき、どちらがエラーになる?

コストベースで判断。
後からとか先に実行したとか以外に、どちらが変更が多いかを見ている。


REPEATABLE READ, SERIALIZABLE

InnoDB の REPEATABLE READ は、ファントムリードを起こさない実装になっています。
InnoDB の SERIALIZABLE は、(明示していない)全ての SELECT が、SELECT ... LOCK IN SHARE MODE 扱いになる実装です。




MySQL開発

aclocal 手順

aclocal
autoheader
libtoolize --automake --force
automake
autoconf

cd innobase
aclocal
autoheader
libtoolize --automake --force
automake
autoconf

libtoolize --force を実行しないと libmysqlclient.so.14 じゃなくて libmysqlclient.14 になってしまう環境がある。

bitkeeper で取る

初めて取得するとき

bk clone bk://mysql.bkbits.net/mysql-5.0 mysql-5.0

更新するとき

bk pull

コンパイラー

  • 使用禁止
    gcc-2.96, egcs-1, gcc-2.8




MySQL ドキュメント

種類

manual.texiリファレンスマニュアル
include.texiリファレンスマニュアルと internals.texi が include。定数定義
reservedwords.texiリファレンスマニュアルが include。SQL予約語一覧
is.texiINFORMATION_SCHEMA マニュアル
internals.texi内部構造マニュアル
manual.cluster.texiクラスター

bitkeeper

初めて取得するとき

bk clone bk://mysql.bkbits.net/mysqldoc mysqldoc

更新するとき

bk pull

texi の加工

  • texi ファイル -[texi2html]-> HTML ファイル
  • texi ファイル -[texi2dvi]-> dvi ファイル -[dvi2ps]-> PS ファイル -[ps2pdf]-> PDF ファイル
  • texi ファイル -[texi2db]-> DocBook形式ファイル

  • manual.texi -> HTML への変換の場合
    • manual.html ファイルができる。
    • manual.texi , include.texi , reservedwords.texi を同じディレクトリに置く。
    • texi ファイルのあるディレクトリで、
      texi2html -Verbose -nomenu manual.texi
      とすると、manual.html が出来る。
      これは 1ファイル。
      もし、章ごとにファイルを分割したい場合は
      texi2html -nomenu -split=chapter manual.texi
  • texi2html はバージョンによってオプションが違うので注意。


  • manual.texi -> dvi への変換の場合
    • manual.dvi ファイルが出来る
    • manual.texi と、texinfo.tex ファイル (TeX用の、スタイル定義ファイル) を同じディレクトリに置く。
      • texinfo.tex ファイルは、ネット上を探す
    • texi ファイルのあるディレクトリで、
      texi2dvi manual.texi
      とすると manual.dvi ができる



  • texi2html は perl スクリプト。日本の書き方やマルチバイトの事を考えていないので、頑張って修正しましょう。
  • texinfo.tex も日本語を考慮していないので、がんばって修正する。




その他

バグ報告、質問をする場合

心得の条

  • 以下の情報を 必ず 含めよ
    • mysqlbug の出力内容
      • MS-Win の場合は、mysqlbug が実行できないので、mysqld --version の結果、OS のバージョンを含める
    • my.cnf の内容
    • テーブルの構造
    • 問題となるデータ, SQL 文
    • 得られる結果、エラーメッセージ、.err ファイルの出力
      • .err ファイルは、MS-Win の場合は、data の下の mysql.err ファイルもしくは、「コンピュータ名」.errになっていることがある
    • なにを、どうした時に、なにが起きるか、を書く
  • エラーメッセージをそのままコピーせよ
  • 質問する前に、エラーメッセージを読め!

そのほか、
こころえ
を参考に。



読み方

MySQLまいえすきゅーえる、みーえすきゅーえる(^^;;;
Myみー (ムーミンのミー)
InnoDBいのでーびー
ABあーべー
Oyおい
GmbHげーえむべーはー

トップ   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS