コマンドによるMySQL管理テクニック：快速MySQLでデータベースアプリ！（3）（2/2 ページ）
コマンド操作の応用
外部ファイルによるバッチ処理
多くのフィールド定義を持つテーブルを作成する場合や、バッチ処理のように大量のINSERT文を発行したい場合には、mysqlプロンプトでは少々面倒です。1つ間違えば、もう1度データベースをDROPし、あらためてCREATEした後、もう1度テーブルの定義文を打ち直すことになります。
こんなときは、ファイルに保存しておいたものをリダイレクトできないかと考えます。例えば、テーブルの定義文を下記のように“temp.sql”というファイル名で保存してあったとします。
CREATE TABLE list ( id int(10), name char(20), memo char(240) );
これを
$ mysql データベース名 < temp.sql
とできたら、どれだけ操作が楽になるでしょう。この操作はもちろんできます。試しに上記のファイルを用意し、
$ mysql test < temp.sql
としてみてください。
# mysql [オプション] [データベース]
と確認できると思います。もちろんテーブルの定義にかかわらず、mysqlプロンプト内でタイプできるものは、外部にファイルとして書き込み、リダイレクトすることが可能です。
結果をファイルへ出力
SQL文をファイルから読み込む方法を紹介しましたが、その逆も可能です。つまり、結果をファイルに書き出すことです。下記のように実行します。
$ mysql test > out.txt （mysqlプロンプトは返ってきません） select * from test_table; quit
out.txtファイルを見てみると、ちゃんと結果が出力されているはずです。
$ more out.txt id memo 1 1st 2 2nd
重ね技もできます。
$ mysql データベース名 < 入力ファイル > 出力ファイル
漢字などのマルチバイト文字を挿入したい場合もしばしば利用します。その場合、MySQLサーバの漢字コード（たいていはEUC）とファイルの漢字コードを同一にしておきましょう。
ユーザー参照権について
私もMySQLを業務に取り入れるようになって3年ほどたちますが、いまだにユーザーの参照権には悩まされます。しかし、それを解決する便利なコマンドが用意されています。
例えば、先ほど作成したTEST2テーブルをあるユーザーがパスワードの確認付きで、“select insert update delete”といった作業ができるようにするには、下記のようにします
$ mysql -u root mysql> grant delete,insert,references,select,update on TEST2.* to ユーザー名 identified by 'パスワード'; mysql> quit;
これだけでは完全ではありません。
$ mysqladmin -u root reload
とし、特権テーブルを再読み込みします。
これで、指定したユーザー名とパスワードでなら、TEST2に作られたどんなテーブルにもdelete、insert、references、select、updateの操作が許されるようになりました。
$ mysql -u ユーザー名 -p TEST2
とし、“Enter password:”で、設定したパスワードを入力します。今度はちゃんとmysqlプロンプトがエラーなく返ってくると思います。
では、これらの特権参照情報はどこに蓄えられているのでしょう？ 第1回目の最後に「mysqlはMySQLデーモンが使用する設定テーブルです。MySQLはデータベースに接続するユーザーやホストの管理といった特権情報も自身のデータベースを用いています」と書いたのを覚えていますか？ ちなみに
$ mysql -u root mysql mysql> show tables; +-----------------+ | Tables_in_mysql | +-----------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +-----------------+ 6 rows in set (0.05 sec)
でどんなテーブルを持っているか分かります。
userテーブル
mysqlデータベースの中の1つ“userテーブル”を見てみましょう。
mysql> select * from user; % ユーザー名 パスワードを暗号化したもの N N N N N N N N N N N N N N
上のような行を見つけることができると思います。何となくユーザーの確認に使用されているのは分かるかと思います。しかし、どの権限にも“N”のフラグが立っており、たとえユーザー名とパスワードが合っていたとしても、何もすることができないように思えます。
dbテーブル
もう1つ“dbテーブル”をのぞいてみましょう。
mysql> select * from db; % TEST2 ユーザー名 Y Y Y Y N N N Y N N
という行を見つけることができたと思います。今度はちゃんと、delete、insert、references、select、updateそれぞれのフィールドに“Y”が立っており、操作が可能なようになっています。
MySQLでは、mysqlデータベース中にある複数のテーブルにまたがり、特権情報を書き込みます。TEST2データベースに対し、deleteの権限を剥奪したい場合は、mysqlデータベースのdbテーブルを操作します。
$ mysql -u root mysql> update db set Delete_priv = 'N' where User = 'ユーザー名'; mysql> quit; $ mysqladmin -u root reload
以上で操作完了です。同様にパスワードを変更したい場合は、userテーブルに対し
mysql> update user set Password = password('新しいパスワード') where User = 'ユーザー名';
mysql> quit;
$ mysqladmin -u root reload
とします。
これで、一通りの操作と管理がお分かりいただけたと思います。ちなみに、第1回で紹介させていただいた、『MySQL徹底入門』（翔泳社）でも詳しく紹介していますので、書店で見かけた際にはぜひ手に取ってみてください（私もMySQLユーザ会の一員として執筆に携わっております）。
次回予告
これで、ようやく次回からアプリケーションを主体にしたお話ができます。まずはCGIの代名詞でもある“Perl”に触れてみましょう。次回まで時間がありますので、皆さんの方で下記のファイルがインストールされているか確認しておきましょう。
必要なファイル
- perl 5.00503 (ほとんどのLinuxディストリビューションでは標準だと思います)
- DBD/DBIモジュール
http://www.perl.com/CPAN-local/modules/by-module/DBI/ - MySQLモジュール
http://www.mysql.com/Downloads/Contrib/ - Data-ShowTable
http://www.perl.com/CPAN-local/modules/by-module/Data/
それぞれのインストールについては次回触れますが、ほとんどが
# perl Makefile.PL # make # make test # make install
でインストールできると思います。
また、使っていくうちにバックアップの心配や、速度低下の危惧なども出てくるところです。下記のような事柄については、最終回に新たに場を設けてお話しさせていただきたいと思います。
- バックアップと復元
- テーブルの検査・修復・最適化
- チューンアップ
では、次回もご期待ください。
Copyright © ITmedia, Inc. All Rights Reserved.
注目のテーマ
ITmediaはアイティメディア株式会社の登録商標です。