MySQLユーティリティー群

isamchk 

MySQL テーブルをチェック、最適化そして修復する。

make_binary_release 

コンパイルされた MySQL のバイナリリリースを作成する。

msql2mysql 

mSQL プログラムを MySQL に変換するシェルスクリプト。

mysql 

mysql は簡単な SQL シェルだ
以下のように簡単にスクリプトを実行できる：

shell> mysql database < script.sql > output.tab

クライアントのメモリが十分でないことによる問題がある場合は、 --quick スイッチを使用すること。

mysqlaccess 

ホスト、ユーザそしてデータベースの組合せで特権をチェックするためのスクリプト。 

mysqladmin 

管理ユーティリティ。データベースの作成/破棄。reload (新しいユーザの読み込み) と refresh (ディスクへのテーブルのフラッシュ、ログファイルの再オープン)。サーバのバージョンと処理と状態情報も与える。

mysqlbug

MySQL バグレポートスクリプト。このスクリプトは MySQL メーリングリストにバグレポートを出す際、常に使用する。

mysqld

SQL デーモン。これは常に実行される。

mysqldump

MySQL データベースの SQL ステートメントまたはタブで区切られたテキストのファイルにダンプする。

mysqlimport

一つまたは複数のテキストファイルをそれぞれのテーブルに読み込む。LOAD DATA INFILE でサポートされる全ての形式を使用できる。

mysqlshow

データベース、テーブル、項目そしてインデックスについての情報を表示する。

mysql_install_db

MySQL 承認テーブルをデフォルト特権で作成する。これは通常、最初の MySQL リリースを新しいシステム上にインストールする時に一回だけ実行される。

replace

msql2mysql で使用されるバイナリ。ファイル内または標準入力上の文字列を変換するユーティリティプログラム。

safe_mysqld 

mysqld デーモンをいくつかの安全な機能で開始する。エラー時には再起動し、実行時情報をログファイルへ記入する。

mysqladmin:MySQLデータベース管理コマンド

mysqladminはデータベース管理操作を行うユーティリティーだ。主な使い方は以下の通り。

> mysqladmin [OPTIONS] command [command-option] command ...

mysqladmin --help を実行すれば、 mysqladmin がサポートするオプション一覧が得られる。

mysqladmin がサポートするコマンド

全てのコマンドはそのコマンド固有の接頭部分で省略できる。

> mysqladmin proc stat

mysqladmin statusコマンドが返す項目

• Uptime
  MySQL サーバーが起動してからの秒数 
• Threads
  実行中のスレッド (クライアント) 数
• Questions
  mysqld 開始以降のクライアントからの問い合わせ数
• Slow queries
  long_query_time 秒以上にかかったクエリの数
• Opens
  mysqld が過去に開いたテーブルの数
• Flush tables
  flush ..., refresh, reload コマンドの実行回数
• Open tables
  現在開かれているテーブルの数
• Memory in use
  mysqld によるメモリー割り当て (MySQL を --with-debug でコンパイルした場合だけに表示される)
• Max memory used
  mysqld が割り当てたメモリーの最大数 (MySQL を --with-debug でコンパイルした場合だけに表示される) 

mysqldumpでデータベースやテーブルをまるごと出力！

mysqldumpはデータベースのバックアップに使用します。

mysqldump を使った定期バックアップ

mysqldump を使った簡単な定期バックアップ方法を紹介しましょう。まずシェルスクリプトを作成します。

自分のホームディレクトリに移動
# cd

シェルスクリプト用のディレクトリを作成
# mkdir sh

ダンプ用ディレクトリを作成
# mkdir dump

mysqldump 用のファイルを vi で作成します。
# vi mysqldump.sh

mysqldump.sh の内容は以下のとおりです。ユーザ名 myuser、パスワード mypass 、バックアップディレクトリは /home/dump/db.txt とします。

mysqldump.sh のソース

#! /bin/sh

mysqldump --all-databases > /home/dump/db.txt -u myuser --password=mypass --opt

最後に cron に登録。毎日1時に /home/sh ディレクトリにある mysqldump.sh を実行させます。

crontab -e
0 1 * * * * /home/sh/mysqldump.sh

mysqldump の使い方

データベース、あるいは、バックアップ、他のSQLサーバーへのデータを移動を目的としたデータのまとまり、これらをダンプするためのユーティリティ。ダンプは、テーブルの作成のための SQL 文を含みます。

サーバでバックアップを行なう場合、mysqlhotcopy を代わりに使用することを考慮すべきです。

# mysqldump [OPTIONS] database [tables]

もしくは
# mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

もしくは
# mysqldump [OPTIONS] --all-databases [OPTIONS]

もしテーブルを指定せず、--databases や --all-databases を使用しなかったなら、データベースの全てのテーブルがダンプされます。

mysqldump --help によって、オプションの一覧を手にいれることができます。

もし mysqldump を --quick や --opt なしで実行するならば、 mysqldump は結果を表示する前に、結果をメモリに全てロードすることに注意してください。これは大きなデータベースをダンプする際に問題になるでしょう。

mysqldump のオプション

--add-locks

テーブルのダンプの前に LOCK TABLES 文を追加し、テーブルのダンプ後に UNLOCK TABLE 文を追加( あとで MySQL に挿入するときに速くなる)。

--add-drop-table

テーブル作成の前にテーブルを削除する

-A, --all-databases

全てのデータベースをダンプします。

-a, --all

全てを含むオプションを作成します。

--allow-keywords

予約語と同じ名前をもつフィールドの作成を許可します。これは、それぞれのフィールド名の前に、テーブル名を付け足します。

-c, --complete-insert

完全な INSERT 文(フィールド名を書いた文) を使用します。

-C, --compress

サーバーとクライアントの両方が圧縮をサポートしている場合は、その間でやりとりされる通信を圧縮する。

-B, --databases

ダンプするデータベースを指定できます。
例：--databases db1 db2 ...

--delayed

INSERT DELAYED コマンドを使用してレコードを挿入します。

-e, --extended-insert

新しいマルチライン INSERT 構文を使用します。(これはあとで挿入する際、よりコンパクトかつ速くなります。)

-#, --debug[=option_string]

Trace usage of the program (for debugging).

--help

Display a help message and exit.

--fields-terminated-by=...

--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...

これらのオプションは -T とともに使用され、 LOAD DATA INFILE と同様の意味をなします。 「7.18 LOAD DATA INFILE構文」節参照

-F, --flush-logs

ダンプする前に、MySQL のログファイルをフラッシュします。

-f, --force,

ダンプの途中で SQL エラーが発生しても、ダンプを続けます。

-h, --host=..

指定されたホストの MySQL からダンプする。デフォルトのホストは localhost。

-l, --lock-tables.

ダンプを開始する前に全てのテーブルをロックする。The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables.

-n, --no-create-db

'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;' will not be put in the output. The above line will be added otherwise, if --databases or --all-databases option was given.

-t, --no-create-info

テーブルの作成情報( CREATE TABLE 文) を書き出さない。

-d, --no-data

テーブルの構造だけを書き出し、テーブル内のレコードを書き出さない。

--opt

--quick --add-drop-table --add-locks --extended-insert --lock-tables と同じ。MySQL から読み出す時間を可能な限り速くします。

-pyour_pass, --password[=your_pass]

サーバーに接続する際のパスワードを指定。もし 「=your_pass」部分を与えなかった場合は、 mysqldump はパスワードのプロンプトをだします。

-P port_num, --port=port_num

サーバーに接続する際に使用する TCP/IP ポート番号。 (これは localhost 以外のホストへの接続に使用されます。localhost の場合は、Unix ソケットが使用されます。)

-q, --quick

クエリをバッファにため込みません。stdout に直接出します。 mysql_use_result() をこれに使用しています。

-r, --result-file=...

Direct output to a given file. This option should be used in MSDOS, because it prevents new line '\n' from being converted to '\n\r' (new line + carriage return).

-S /path/to/socket, --socket=/path/to/socket

localhost に接続する際のソケットファイルを指定。デフォルトは「/tmp/mysql.sock」。

--tables

Overrides option --databases (-B).

-T, --tab=path-to-some-directory

Creates a table_name.sql file, that contains the SQL CREATE commands, and a table_name.txt file, that contains the data, for each give table. NOTE: This only works if mysqldump is run on the same machine as the mysqld daemon. The format of the .txt file is made according to the --fields-xxx and --lines--xxx options.
与えられたテーブル毎に、 SQL CREATE 文を含む table_name.sql ファイル、データを含む table_name.txt ファイルを作成します。NOTE: これは mysqld デーモンが走っているマシン上で mysqldump を実行する場合にだけ、動作します。 .txt ファイルのフォーマットは、 --fields-xxx と --lines--xxx オプションに従います。

-u user_name, --user=user_name

サーバーに接続する際の MySQL ユーザー名を指定。デフォルトは Unix のログイン名。

-O var=option, --set-variable var=option

変数の値をセット。可能な変数は後述。

-v, --verbose

冗長モード。 プログラムが何を行っているかより表示します。

-V, --version

バージョン表示。

-w, --where='where-condition'

選択されたレコードのみをダンプ（クオートは必須）。
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"

-O net_buffer_length=#, where # < 16M

When creating multi-row-insert statements (as with option --extended-insert or --opt), mysqldump will create rows up to net_buffer_length length. If you increase this variable, you should also ensure that the max_allowed_packet variable in the MySQL server is bigger than the net_buffer_length.

mysqldump の活用

データベースをファイルにバックアップする場合は以下のとおり。

mysqldump --opt データベース名 > ファイル名

バックアップファイルをデータベースに戻す場合は次のとおり。

mysql データベース名 < ファイル名

もしくは、下記の方法でもいい。

mysql -e "source ファイル名" データベース名

他の MySQL にデータベースをコピーするのにも使える。

mysqldump --opt データベース名 | mysql --host=リモートホスト名 -C データベース名

複数のデータベースを指定してダンプします。

mysqldump --databases database1 [database2 database3...] > my_databases.sql

すべてのデータベースをダンプします。

mysqldump --all-databases > all_databases.sql

mysqlimport テキストファイルからデータを読み込む

mysqlimport は、LOAD DATA INFILE SQL 構文を、コマンドラインインターフェースで提供します。 mysqlimport のオプションのほとんどが、 LOAD DATA INFILE への同じオプションに対応します。

mysqlimport の実行は以下のようにします:

shell> mysqlimport [options] database textfile1 [textfile2....]

mysqlimport は、コマンドラインの引数に与えられたファイル名の拡張子を取り、拡張子を取った後の名前を、ファイルの内容を取り込むテーブルの名前とします。例えば、`patient.txt', `patient.text', `patient' というファイルは全て、patient とという名前のテーブルに取り込まれます。

mysqlimport は以下のオプションをサポートします

-c, --columns=...

This option takes a comma-separated list of field names as an argument. The field list is used to create a proper LOAD DATA INFILE command, which is then passed to MySQL.

-C, --compress

クライアントとサーバーの両方が圧縮をサポートしているなら、クライアント・サーバー間でやり取りされる全ての情報を圧縮します。

-#, --debug[=option_string]

プログラムのトレース(デバッグ)

-d, --delete

テキストファイルを取り込む前にテーブルを空にします。

--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...

これらのオプションは、LOAD DATA INFILE の対応する文節と同じ意味になります。

-f, --force

エラーを無視します。例えば、テキストファイルを取り込むテーブルがない場合、残りのファイルの処理に移ります。 --force がなければ、テーブルがなければ mysqlimport は終了します。

--help

ヘルプを表示して終了。

-h host_name, --host=host_name

名前を指定された MySQL サーバーにデータを取り入れます。デフォルトでは localhost.

-i, --ignore

--replace オプションの説明を見てください。

-l, --lock-tables

それぞれのテキストファイルを処理する前に、書き込まれる 全てのテーブルをロックします。これは確実に、すべてのテーブルをサーバ上で同期させます。

-L, --local

クライアントからの入力ファイルを読みます。デフォルトでは、localhost に接続した場合、テキストファイルはサーバー上にあると仮定されます。(localhost はデフォルト値)

-pyour_pass, --password[=your_pass]

サーバーに接続するときに使用するパスワード。もし `=your_pass' のところにパスワードを書かなければ、 mysqlimport はパスワードのためのプロンプトをだします。

-P port_num, --port=port_num

ホストに接続するための TCP/IP ポート番号。 (これは localhost を除くホストへの接続に使用します。 localhost へは、UNIX ソケットを使用します。)

-r, --replace

--replace と --ignore オプションは、入力されているレコードのユニークキーの値が、既に存在しているレコードのユニークキーの値と同じ場合、その入力されているデータの取り扱いを決定します。もし --replace が指定されているなら、既にあるレコードは新しく読まれたレコードに置き換えられます。もし --ignore が指定されているなら、入力された物は無視されます。どちらのオプションも指定していない場合、キーの値が重なっているとエラーを発し、テキストファイルの残りの部分は無視されます。

-s, --silent

静粛モード。エラーだけ出力します。

-S /path/to/socket, --socket=/path/to/socket

localhost への接続時に使用するソケットファイルを指定します。

-u user_name, --user=user_name

サーバーの接続に使用する MySQL ユーザー名の指定。デフォルトは、Unix のログイン名。

-v, --verbose

冗長モード。プログラムがしている事について多く出力します。

-V, --version

バージョンを表示して終了。

mysqlhotcopy, Copying MySQL Databases and Tables

mysqlhotcopy は、データベースのバックアップを迅速に作るために LOCK TABLES 、 FLUSH TABLES 、および、 cp 、または、 scp を使う perl スクリプトです。1 つのテーブルのデータベースのバックアップを作るのが最も速い方法です。ただし、データベースディレクトリがある同じマシン上でしか実行できません。 

mysqlhotcopy db_name [/path/to/new_directory]
mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory
mysqlhotcopy db_name./regex/

mysqlhotcopy のオプション

-?, --help

ヘルプを表示

-u, --user=#

ユーザ名を指定してデータベースにログインする

-p, --password=#

パスワードを指定してデータベースにログインする

-P, --port=#

ポートを指定してデータベースにログインする

-S, --socket=#

ソケットを指定してデータベースにログインする

--allowold

Don't abort if target already exists (rename it _old)

--keepold

Don't delete previous (now renamed) target when done

--noindices

Don't include full index files in copy to make the backup smaller and faster The indexes can later be reconstructed with myisamchk -rq..

--method=#

Method for copy (cp or scp).

-q, --quiet

エラーメッセージを表示しない

--debug

デバックをオンにする

-n, --dryrun

Report actions without doing them

--regexp=#

Copy all databases with names matching regexp

--suffix=#

Suffix for names of copied databases

--checkpoint=#

Insert checkpoint entry into specified db.table

--flushlog

Flush logs once all tables are locked.

--tmpdir=#

Temporary directory (instead of /tmp).

 

You can use perldoc mysqlhotcopy to get a more complete documentation for mysqlhotcopy.

mysqlhotcopy reads the groups [client] and [mysqlhotcopy] from the option files.

To be able to execute mysqlhotcopy you need write access to the backup directory, SELECT privilege to the tables you are about to copy and the MySQL Reload privilege (to be able to execute FLUSH TABLES).
 

ロギング

MySQLは、発行した全てのSQL文のログをとることが可能だ。これは、 「mysqld --log」 あるいは 「mysqld --log -update=ファイル名」というオプション指定で記録できる。
　「mysqld --log」は、全ての操作やエラーも合わせて記録する。「mysqld --log -update=ファイル名」はトランザクション・ログで、SQL文のみが記録される。

　「--log -update=ファイル名」オプションは､以下のコマンドでファイル名､番号というファイルに､これまでのログが移行される。

# mysqladmin refresh

または

# mysqladmin flush-logs

あるいは、以下のSQL文

mysql> FLUSH LOGS;

接続の承認

MySQLサーバーに接続すると、まずサーバー接続の許可判定が行われる。もし身元が一致しない場合、接続を拒否し、接続許可した場合、サーバーはリクエストを待つ。

身元は接続しようとしているホスト、MySQL ユーザー名に基づいて確認される

身元の確認は、 user テーブルのスコープフィールド(Host, User, Password) を使用して行われる。

userテーブルのスコープフィールドの登録は以下のとおり

• Host の値はホスト名か IP アドレスか 「localhost」。
• Host にはワイルドカード文字のパーセント(%) とアンダーバー(_) が使用可能。
• Host に % を設定すると、全てのホストにマッチする。 Host を空にすると、% と同じになる。これらの値は、どんなホストもサーバーに接続できるということになる。
• ワイルドカード文字は User 項目に設定できないが、 User 項目を空(ブランク)にすることはでる。空文字は全ての名前にマッチする。これはユーザー名がない状態で接続してきたものに適用され、クライアントがユーザー名を明記しない限り、匿名ユーザー(名前がブランク)として扱われる。全てのアクセスのチェックに空文字のユーザー名が使用される事を意味する。
• Password 項目は空にできる。その場合、パスワードなして接続できることになる。

非ブランクの Password 値はパスワードを暗号化したものだ。 MySQL はだれもが見れるようにパスワードをプレーンテキストでは保存しない。接続を試みようとしているユーザーのパスワードも(PASSWORD() 関数で) 暗号化され、user テーブルに保存されている暗号化パスワードと比較される。

以下の表は、接続要求に対して与える、 user テーブルの Host と User の設定例：

Host に IP のワイルドカード(例えば '144.155.166.%' はサブネットの全てのホストにマッチ) を使用することができる。が、この場合、 144.155.166.somewhere.com というホスト名でだれかが接続しようとしてくるかもしれない。このような攻撃に対し、MySQL は数字やドットで始まるホスト名を拒否している。もし 1.2.foo.com のような名前のホストを持っている場合、許可テーブルの Host には絶対にマッチしない。 IPアドレスのみ、IP のワイルドカードにマッチする事になる。

サーバーに来る接続は、user テーブル内の登録に１つ以上マッチするかもしれない。例えば, rfs.jp の sv からの接続は、上に示された登録のうちのいくつかにマッチする。サーバーは、複数の登録にマッチした場合、どのようにしてその中から使用する登録を選ぶのでしょう？サーバーは起動後に user テーブルをソートし、並び換えられた順に登録を検索することにより、この問題を解決する。最初にマッチした登録が使用される。

user テーブルが以下のようにソートされていた場合：

+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | staff | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-

サーバーがこのテーブルを読むと、Host に値が最も確実に特定できるホストを指定しているエントリを、最初に参照する。

Host の値が同じエントリがあった場合、もっとも明確に User の値がユーザーを指定しているエントリを最初に参照する。 この結果、user テーブルは以下のようにソートされる。

+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| localhost | root | ...
| localhost | | ...
| % | staff | ...
| % | root | ...
+-----------+----------+-

接続が試みられた場合、サーバーは並び換えられた登録を探し、最初に見つけたものを使用する。 "localhost" の jeffrey からの接続は、まず最初に Host に localhost を設定しているエントリにマッチする。ユーザー名が空のエントリは、ホスト名とユーザー名の両方を指定した接続にもマッチする( '%'/'staff' エントリもマッチするが、最初にはマッチしない)。

よくある過ちは、ユーザー名を与えた場合、サーバーが接続にマッチするものを探す際に、そのユーザーが登録されている全てのルールが、最初に使用されるだろうと考えることだ。これは正しくない。"rfs.jp" の "staff" からの接続が最初にマッチするのは、 User フィールドの値が "staff" になっているエントリではなく、ユーザー名なし(＝だれでも) のエントリの方が先にマッチする。

もしサーバーへの接続がうまく行かない場合、 user テーブルを表示し、マニュアルでソートしてみて、どのエントリに最初にマッチするか試すべきだ。

要求の承認

一度接続か確立されると、サーバーはこの接続から来るそれぞれの要求が許可されているかどうかをチェックする。チェックは実行しようとしている操作のタイプにより行う。その操作が許可テーブルのどの特権フィールドに当てはまるかを見る。

• 特権テーブルは「MySQL権限システム」を参照
• 許可テーブルの許可は 「GRANT と REVOKE 構文」を参照

user テーブルは全てに対して基本となる特権をユーザーに割り当てる。たとえカレントのデータベースが許可を与えていなくても、user テーブルの設定が優先される。例えば、user テーブルで delete を許可した場合、サーバーにあるどんなデータベースの行も削除できる。この特権はスーパーユーザー(サーバーやデーターベース管理者)のみに与えておく事が賢明だ。ユーザーは、user テーブルの特権の設定を 'N' のままにしておくべきだし、また、db テーブルと host テーブルを利用して、データベースを指定した上でユーザーに特権を許可すべきだ。

db テーブルと host テーブルは特定のデータベースに対する特権許可を行う。

• ワイルドカード文字 `%' と `_' は Db テーブルと Host フィールドだけに使用できます。
• '%' Host 値は ``あらゆるホスト'' を意味します。 db テーブルの Host に空を設定すると、``さらに host テーブルに許可情報を探しにいく'' となります。
• '%' か 空値を Host テーブルに設定すると、それは ``あらゆるホスト'' となります。
• '%' か 空値を host テーブルの Db フィールドに設定すると、それは ``あらゆるデータベース'' となります。
• User を空値にすると、匿名ユーザーにマッチします。

サーバー起動時に、db テーブルと host テーブルはサーバーに読み込まれる。 (user テーブルもこの時に同時に読まれる) db テーブルは Host, Db, User のフィールドでソートされ、 host テーブルは Host, Db フィールドでソートされる。 user テーブルは、一番特定できるエントリを最初に、一番特定できないものを最後にソートする。サーバーはソートされたものの中から、最初にマッチしたものを使用する。

tables_priv と columns_priv テーブルは、特定のテーブルと項目に対する特権を許可する。スコープフィールドの値は、以下にそって記述される：

• ワイルドカード文字 `%' と `_' はどちらかのテーブルの Host 項目に使用できる。
• どちらかのテーブルの Host 値を '%' かブランクにすると、 "any host." を意味する。
• Db, Table_name, Column_name 項目はどのテーブルにもワイルドカードやブランクは使用できない。

tables_priv と columns_priv テーブルは Host, Db, User 項目で並び換えらる。これは db テーブルのソートに似ているが、 Host 項目だけがワイルドカードを含むので、ソートはより単純なものになる。

この要求の承認は次のようにして行う。もしアクセス承認を決定する部分のソースコードを理解できるなら、ちょっと変わったアルゴリズムで承認の決定を行っている事に気づくだろう。

管理者の要求(shutdown, reload, etc.)については、サーバーは user テーブルだけを参照する。(user テーブルだけが管理者特権の項目を持つ)。エントリに許可登録されている操作は受け入れられ、それ以外は拒否される。例えば、mysqladmin shutdown を実行しようとしても、user テーブルの shutdown 特権が許されていなければ実行できない。この時、db と host テーブルはチェックされない。(これらのテーブルには Shutdown_priv カラムが無いからだ)

データベースへの要求 (insert, update, etc.) において、サーバーはまず最初に、ユーザーのグローバルな特権(スーパーユーザー)を user の中から探しだす。もし許可が与えられていれば、アクセスは成功する。

user テーブルのグローバルな特権の設定が不十分であるなら、サーバーはユーザーのデータベースに対する特権を db テーブルと host テーブルから決定する：

• サーバーは db テーブルの Host,Db,Userフィールドを参照します。 Host と User はユーザーの接続時のホスト名と MySQL ユーザー名にマッチします。 Db フィールドはユーザーがアクセスしたいデータベース名にマッチします。 Host と User にマッチするものが無かった場合、アクセスは拒否されます。
• db テーブル内の Host フィールドが空でないエントリにマッチした場合、ユーザーの指定されているデータベースに対する特権が定義されます。
• Host フィールドが空値の db テーブルのエントリにマッチした場合、どのホストがそのデータベースへアクセスできるかを host テーブルから探し出します。この場合、host テーブル の Host, Db フィールドとマッチするものを探し出します。 host テーブルにエントリがなかった場合、アクセスは拒否されます。もしマッチすると、ユーザーの特定データベースに対する特権は、 host テーブルと db テーブル両方にまたがった特権から割り出されます。いうならば両方とも 'Y' である特権。 (この方法を使用すると、まず db テーブルのエントリに大まかな特権を設定しておき、それから host テーブルのエントリを使用して、ホスト情報もとに特権を限定していくという事ができます)

特定データベースに対する特権が db テーブルと host テーブルのエントリから決定された後、サーバーはその割り出された特権に対し、user テーブルて設定されている特権を加える。この結果から得られた特権にマッチした要求は受け入れらる。そうでなければ、サーバーはユーザーのテーブル、カラムに対する許可を、 tables_priv と columns_priv 内に探す。アクセスはこの結果により、許可、拒否される。

前のユーザの特権を計算する方法に関する記述は、 boolean で表すとするなら、以下のように表せる：

global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges

もし最初にグローバルな user エントリ特権がオペレーション要求に対して不十分である事がわかった場合に、サーバはあとから、それらの特権をデータベース特定、テーブル特定、コラム特定 特権に加えるのか、これはちょっとわからないかもしれない。

その理由は、ある要求が 1 つ以上のタイプの特権を必要とするかもしれないからだ。例えば、INSERT ... SELECT を実行するばあい、 insert , select 特権が必要だ。もしあなたの特権が、一つだけ user テーブルで許可されており、もう片方が db テーブルで許可されているする。この場合、そのリクエストを実行できる特権を持っているが、 サーバー自身がどちらのテーブルからか見分けることが出来ない。両方のエントリで与えられる特権は結合される。

host テーブルは"安全な" ホストのリストを維持するために使用できる。

逆に host table で安全ではないホストを指定することもできる。 　public.your.domain というマシンが安全ではない、公開されている場所にあるとする。その場合以下のようにして、その公開マシン以外のネットワーク上のホストに対して、アクセスを許可することができる：

+--------------------+----+-
| Host | Db | ...
+--------------------+----+-
| public.your.domain | % | ... (all privileges set to 'N')
| %.your.domain | % | ... (all privileges set to 'Y')
+--------------------+----+-

特権のテーブル設定は、あなたの思い通りに許可が得られるのか、常に(mysqlaccess等を使用して)チェックすべきだ。

特権の変更が反映されるタイミング

mysqld の起動時、全ての許可テーブルはメモリーに読み込まれ、この時点で有効になる。

GRANT, REVOKE, SET PASSWORD を使用して許可テーブルを変更した場合、直にサーバに通知される。

もし手動で許可テーブルを変更した場合(INSERT, UPDATE などで)、 FLUSH PRIVILEGES 構文か mysqladmin flush-privileges コマンドを実行して、サーバーに許可テーブルの読み込みを指示しなければならない。そうしなければ、サーバーを再起動させるまで、変更は反映されない。

サーバーが許可テーブルの変更を通知した場合、既に接続しているクライアントは、以下のような影響を受ける：

• テーブルと項目の許可の変更は、次のクライアントの要求から反映される。
• データベースに対する許可の変更は次の USE db_name コマンド以降から有効になる。

グローバル特権とパスワードの変更は、次のクライアントの接続時から反映される。

ここでは、MySQLの権限システムを詳しく説明しています。設定の仕方は「接続の承認」を参照ください。

MySQL特権システム概要

MySQLには、データベース、ホスト、ユーザ、テーブル、フィールド毎に、データベースを操作（閲覧、挿入、更新、削除など）するための様々な権限を設定することができます。

MySQLでは、データベースにアクセスできるユーザ/パスワード/ホスト名をmysqlというデータベースに登録しているので、そのデータベースを設定していくことになります。
mysqlデータベースに用意されている管理用テーブルは、db、host、user、tables_priv、columns_privの5つで、これにより、どのユーザがどのホストからの何のデータベースに対してアクセスできるか指定できます。

mysqlデータベース内のテーブル一覧

管理テーブルは MySQL インストール時に自動的に作られる。5つのテーブルはmysqlデータベースにあるから、以下のようにして確認してみましょう。

mysqlデータベースを選択

use mysql

dbテーブルの表示

show fields from db;
select * from db;

hostテーブルの表示

show fields from host;
select * from host;

userテーブルの表示

show fields from user;
select * from user;

MySQLが提供する権限

MySQLの権限には、テーブルへのデータ挿入・削除や、更新などがある。権限の設定はmysqlデータベースの user、db、host、tables_priv、columns_privで行う。下記のテーブルを参照してください。

• select, insert, update, deleteの権限は、存在しているデータベースのテーブルに対して許可されています。
• select権限はテーブルから行を取り出すことができます。
• index権限はインデックスの作成と破棄(削除)を許可します。
• alter権限は ALTER TABLE の実行を許可します。
• createとdrop(※1)権限は、新しいデータベースやテーブルの作成、あるいは既に存在するデータベース、テーブルの破棄(削除)を許可します。
• grant権限は、他のユーザーに対して自分の権限を持たせる事を許可します。
• fileの権限を与えると、LOAD DATA INFILE と SELECT ... INTO OUTFILE構文を使用して、サーバーのファイルを読み書きする事ができます。

※mysql データベースに登録されているユーザーにdrop権限を与えると、そのユーザーは MySQL のアクセス権限が格納されているデータベースを破棄できます。

管理者操作に関する許可

残りの権限は管理者操作に関する許可です。この権限により、mysqladminコマンドの実行権が設定されます。mysqladminコマンドのどれが、どの権限に対応しているかは次の表の通りです。

各コマンドの詳細

• reloadはサーバーに権限の設定を再読込させるように伝えます。
• refreshコマンドは全てのテーブルをフラッシュし、ログファイルを開き直します。
• flush-privilegesは reload と同義です。
• その他のflush-*コマンドはrefreshの動作とよく似ているが、適用範囲を絞っており、ちょっとした場合に有効だ。例えば、ログファイルだけをフラッシュしたい場合、refreshを行うよりもflush-logsが効果的な方法です。
• shutdownコマンドは、。
• processlistコマンドはサーバーが実行しているスレッドの情報を表示します。killコマンドはサーバーのスレッドをkillしあす。自分のスレッドは常に表示、killできるますが、他人のスレッドをそうするにはprocess権限が必要です。

管理テーブルの項目

全ての管理テーブルに共通することですが、テーブルの各項目を分類すると、適用範囲を指定するスコープフィールドと、許可を定義する権限フィールドがの2種類があります。

スコープフィールド

スコープフィールドは、権限テーブルの登録ごとに、その適用範囲を決めます。
例えば、userテーブルのHostとUserに "domain.co.jp"と"staff"が登録されている場合、サーバーへの接続はホストdomain.co.jpからアクセスしてきたstaffを許可します。同様に、dbテーブルのHost、User、Dbに"domain.co.jp", "staff", "report"が登録されていると、ホスト domain.co.jp から来たstaffに対し report データベースへの接続が許可されます。

権限フィールド

権限フィールドは有効になった許可を示します。

user/db/host テーブルでは、全ての権限フィールドは ENUM('N','Y') で定義されます。この値は 'N' か 'Y' のどちらかで、デフォルト値は 'N' です。tables_privとcolumns_privテーブルは、テーブルか、テーブルとカラムを対にしたスコープフィールドを含みます。
tables_privとcolumns_privテーブルでは、権限フィールドはSETフィールドとして定義されます。

権限の詳細は下段の「MySQL が提供する権限」を参照してください。

管理テーブルの詳細

user/db/hostテーブル

サーバーは mysql データベースの user, db, host３つの管理テーブルから、アクセス制限を決定します。テーブルのフィールドは以下の通りです。

userテーブルのスコープフィールドは、接続要求を受け入れるか拒否するかを決定します。そして、管理テーブルの中でuserの権限許可が最優先されます。たとえば、userテーブルの権限許可が'Y'であれば、DbやHostで'N'となっていても、userで設定した'Y'が優先されます。userテーブルには最低限の許可を与えるようにし、DbやHostごとに、それぞれの権限許可を定義する方が無難です。

tables_priv/columns_privテーブル

tables_privテーブルとcolumns_privテーブルは、基本的にはdbテーブルが提供している機能の集約です。リクエスト承認のために、サーバーはこれらuser/db/hostの 3 つのテーブルによって決められた許可を基本としますが、もしテーブルに対する要求であるならば、tables_privとcolumns_privテーブルをさらに調べます。これらのテーブルのフィールドは以下のようになっています。

権限の定義に関するノウハウ

権限を許可する際の注意事項

ある権限を欲しがるユーザーだけにその権限を許可するのはよい考えですが、権限を与えるときには、特定の事項を熟知している必要があります。

• grant権限を許可されたユーザーは、他のユーザーの権限を変える事ができます。
• alter権限は、テーブル名の変更を行うことにより権限システムを破るために使用されるかもしれません。
• file権限は、サーバー上にある全ての読み込み可能なファイルをデータベースに取り込むことができ、これはSELECT文でアクセスできます。
• shutdown権限は、他のユーザーに対するサービスを、サーバーを停止することによって、拒否するようにできます。
• process権限は実行されているクエリーをプレーンテキストで見ることに使えます。パスワードの設定、変更のクエリーも含みます。
• mysqlデータベースに対しての権限は、パスワードの変更と他の権限の設定を変更ができます。パスワードは暗号化されて登録されており、読むことはできませんが、パスワードを違うものに変えることができます。

以下は MySQL の権限システムで行うものではありません。

• アクセスを拒否するユーザーを特定して設定することはできません。
• データベース内のテーブルの作成、破棄の権限を持ちますが、データベースそのものを作成、破棄でるようなユーザーを設定できません。

管理者権限の設定ノウハウ

reloadやshutdownなどの管理者権限は、userテーブルにだけ定義したほうが良いです。なぜなら、管理者操作はデータベースではなくサーバーへの操作なので、hostやdbテーブルで定義する必要はありません。また、こうしておくと、管理者操作の許可は、userテーブルの定義だけを見ればわかるようになります。
file操作の権限はuserテーブルにだけ定義しましょう。これは管理者操作ではありませんが、アクセスしているデータベースにかかわらず、サーバー内のファイルを読み書きできます。

便利なツールとしてmysqlaccessスクリプトがMySQLの配布に含まれています。 mysqlaccess を --help オプションで起動するとヘルプが表示されます。mysqlaccess は user,db and hostテーブルだけしか検査しません。テーブルレベルの権限、カラムレベルの権限は調べません。

アクセスコントロールの段階

MySQLは接続の際、ホスト名とユーザー名の両方をあわせてチェックします。違うホスト間でユーザ名が重複していても､ユーザは別のものとして扱われます。

アクセスコントロール

• サーバーは接続許可があるかどうかをチェック
• 接続許可後、サーバーはそれぞれのリクエストをチェック

新しいユーザ権限をMySQLへ追加

GRANTでユーザー登録

SQLでデータベース用のアカウントを作成するには、GRANT構文を使います。GRANT構文は、アカウントにテーブルやビューに関する特定の権限を設定します。

MySQLにログイン

mysql -u root -p
Enter password: ****

spadminユーザの作成

GRANT ALL PRIVILEGES ON *.* TO spadmin@localhost
IDENTIFIED BY 'パスワード' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO spadmin@"%"
IDENTIFIED BY 'パスワード' WITH GRANT OPTION;

guestユーザの作成

GRANT USAGE ON *.* TO guest@localhost;

上記GRANT構文では3つの新しいユーザを作成しています。

spadmin

spadminは、どのドメインからでも接続できる完全なスーパーユーザとして設定しています。

spadmin@localhostと、全てのドメイン名にマッチするspadmin@"%"の両方にGRANT構文を発行します。

guest

mysqlクライアントプログラムを利用できますが、データベースへの変更などの処理を許可していません。

localhostからのみ接続できるユーザーです。パスワードは必要ありません。

GRANT構文でユーザーの権限を定義

GRANT構文で任意のドメインに対してアカウントの権限を定義することができます。アカウント名の後に@をつけて、その後に設定したいドメイン名を指定します。次の例では、customユーザーに対して、db1データーベースにはlocalhostからの接続のみを許可し、db2データベースにはexsample.jpからのみ接続が許可、db3データベースには全てのホストからの接続を許可しています。

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON db1.* TO custom@localhost IDENTIFIED BY 'パスワード';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON db2.* TO custom@exsample.jp IDENTIFIED BY 'パスワード';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON db3.* TO custom@'%' IDENTIFIED BY 'パスワード';

パスワードの設定法

INSERTかUPDATEで空ではないパスワードを設定する場合、暗号化するためにPASSWORD()関数を使用します。これはuserテーブルはプレーンテキストでなく、暗号化されたパスワードであることを要求しているからです。

SET PASSWORD構文を使用する

SET PASSWORD FOR ユーザ名@"%" = PASSWORD('パスワード');

GRANT ... IDENTIFIED BY構文やmysqladmin passwordコマンドでパスワードを設定した場合は、PASSWORD() 関数は必要ありません。両方とも、自動的にパスワードを暗号化します。

MySQLへの接続

MySQLサーバーに接続

mysqlクライアントプログラムには、接続したいホスト名、接続ユーザー名、パスワードといった引数を渡すことができます。 mysqlコマンドを使ってMySQLに接続する際、引数がなければデフォルト値が使われます。デフォルトのホスト名はlocalhost、ユーザー名はUNIXのログイン名です。

mysql [-h ホスト名] [-u ユーザ名] [-p パスワード]

-pコマンドラインでパスワードを入力する際は、オプションの直後にパスワードを入力します。たとえば、rootアカウントでnew_passというパスワードを入力する際は、次のようになります。

mysql -u root -pnew_pass

パスワードを指定する-pオプションに値を渡さなかった場合は、次のようにコマンドラインからパスワードの入力を促されます。

mysql -u ユーザ名 -p
Enter password: ********

セキュリティ的にコマンドラインでパスワードを入力するのは望ましくないので、上記のように-pオプションに引数を渡さずに、Enter passwordと入力を促されてから入力するようにしたほうがよいでしょう。

Unix系OSのログインユーザーがrootの場合、以下のコマンドはどれも同じ意味になります。

mysql -h localhost -u root
mysql -h localhost
mysql -u root
mysql

管理者パスワードの設定

rootのパスワード設定

MySQLをインストールすると、自動的にrootという特殊なユーザが作成されます。rootは、UNIX系OSなどで管理ユーザのアカウント名として使われていますが、MySQLのユーザアカウントはOSのユーザアカウントとは別のものです。ただし、MySQLへのログイン時にユーザ名が指定されていないときは、現在のログインしているユーザ名をMySQLのユーザー名としてログインを試みてくれます。

初期状態のrootはパスワードが設定されていないので、まずはmysqlにログインし、rootのパスワードを設定します。

rootでmysqlにログイン

アカウントrootでmysqlにログインするには、-uオプションでアカウント名を指定します。

mysql -u root

パスワードを設定

ログインが済んだら、SET PASSWORD構文を使ってrootのパスワードを設定しましょう。

SET PASSWORD FOR root=PASSWORD('新しいパスワード');

mysqladminでrootのパスワード設定

コマンドラインでも、mysqladminコマンドでパスワードを設定することができます。

mysqladmin -u root password 新しいパスワード
mysqladmin reload

mysqladminを使ってパスワードを設定した後は、mysqladminのreloadコマンドを実行します。これで、新しいパスワードが有効になります。

パスワード設定後のMySQLへの接続

.my.conf設定ファイルにパスワードを設定

設定ファイルにパスワードを書いておくこともできます。そうすることにより、毎回毎回コマンドラインに引数を与えなくてすむようになります。まず、自分のホームディレクトリに.my.cnfを作り、そのファイルの中の [client] セクションに接続用のパラメターを記述します。

[client]
host=host_name
user=user_name
password=your_pass

.my.cnfファイルはグループやその他のユーザーが読み書きできないようにしましょう。ファイルのパーミッションは 400 か 600 に設定します。

知っておくと便利なキー操作

mysqlのコマンドプロンプトのキー操作一覧です。