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