第17回　その他のちょっとしたテクニック - MySQL講座

4. データのダンプ

• そういえば、先輩。一番最初にぼくのためにデータを用意してくれましたよね。ダンプ、とか言っていましたっけ？ あれは難しいんですか？
• いや。基本的にはコマンドひとつ叩くだけだから、そんなに難しくないよ。説明しようか。
• お願いします！
• mysqldump というコマンドを使うんだ。--help でいろいろ説明が出てくるよ。
• はい。やってみます。

C:\usr\mysql\bin>mysqldump --help
(略)

• わぁ。いっぱい出てきました。読めません(笑)。
• はは。「とりあえず」使うレベルだと、mysqldump -uユーザ名 -p データベース名 で使えるね。もうちょっとオプションつけたほうがいいけど。-pはパスワードを指定する場合ね（普通は指定する）。
• ええっと・・・・こうかな。。。

C:\usr\mysql\bin>mysqldump -uroot phppro2

• うわぁぁぁ。画面がすごいいきおいで流れていって、止まらないです！
• 人の話は最後まで聞こうね(笑)。
• はい・・・・
• とりあえず ctrl-C で止めていいから。

さて、知っておいたほうがいいオプションを２つだけ紹介しておこう。他のものは --help で目を通しておいてね。

ひとつめが、--no-autocommit だ。
InnoDBという形式(ストレージエンジン)を内部で使っているときには、これをつけないとリストア、つまりデータを入れるときにものすごい時間がかかってしまう。まず普段からつけるもんだと思っておいて良いオプションだろう。

もうひとつが、--add-drop-table。
これはある意味危険なオプションでもあるので会社やチームに方針が決まっているならそれに従って欲しいのだけど、テスト環境のデータの入れ替えを行う際などに便利なオプションだ。ダンプされた結果というのはテーブル定義－つまり CREATE TABLE 文－と、データ群が含まれている。

このCREATE TABLE は、すでに同じ名前のテーブルが存在していたらエラーになるので、CREATEの前にDROPで既存のデータ諸共テーブルを消してしまおうというオプションなわけだ。　危険という意味がわかっただろう？でも便利なんだよ。

• 間違って使われたときが怖いですね。
• まぁそもそもダンプデータを元にデータを突っ込む作業自体が、本番環境では普段そうそうやることではないから、都度気をつければいいという考えもあるけどね。ぼくはダンプしたファイルのファイル名で分かるようにしておくこともあるかな、、、あまり気をつかっていないけど(笑)。
• ではやってみて、、、、いいですか？
• おっと、もうひとつだけ。さっきイキナリ実行して画面にざーーっと流れたけど、ダンプ結果を画面で見ても仕方ないのでファイルに落とすようにしようか。「> ファイル名」でリダイレクト指定してね。
• はい。では、やってみます！

mysqldump -uroot --no-autocommit
 --add-drop-table phppro2 > c:\myppro2_2009xxyy.dump

※ 上述では、表示幅や文字数の問題で2行に表示していますが、実際には1行で入力するものです。

• できました！
• 今回詳しく説明しないけど、テーブル単位でもダンプできるんだ。先ほど phppro2 という「データベース名」を指定したけど、その後ろに テーブル名を並べたらいいよ。 items テーブルと prefecture テーブルならこんな感じだね。

mysqldump -uroot --no-autocommit --add-drop-table phppro2 items prefecture

5. いろんな情報を見る

• そのほか何か、知っていると格好良いコマンドってありますか？
• 太郎君は格好つけたくてデータベースを勉強しているのか。まぁそれで学習のモチベーションが上がるのならば悪いことじゃないけどね(笑)。
• えへへ。
• そうだな。MySQLサーバの現在の設定とか、現在の状態を見るコマンドを5,6個教えようか。内容について説明するとかなり時間が必要なので、今日は見方だけね。

よく使うMySQLの内部情報を見るコマンドにはこんなものがある。実行のために権限が必要なものもあるので、どういう権限設定になっているかはサーバ管理者に確認してもらいたい。

STATUS             : サーババージョンやキャラセット設定などを表示。mysqlクライアントのコマンド  
SHOW STATUS        : サーバの設定値などを表示                                                   
SHOW VARIABLES     : 現在のサーバ変数を表示                                                     
SHOW TABLE STATUS  : 現在のデフォルトデータベース(useで使っているもの)の各テーブル詳細情報を表示
SHOW PROCESSLIST   : 現在接続中のクライアント情報を一覧表示                                     
SHOW INNODB STATUS : InnoDBストレージエンジンの状態を表示                                       

• この中で STATUS コマンドだけは mysql クライアントのコマンドだ。つまりサーバに処理を依頼するのではなく、(サーバに問い合わせはしつつも)mysqlクライアントプログラム内で表示を組み立てているってことだ。

mysqlクライアントのコマンドか サーバに処理を依頼する命令かは、後ろに ; が必要か否かという違いもある。現在使っているバージョンやポート、モジコードなどを確認するのに便利なので覚えておくといいね。

mysql> STATUS
--------------
mysql  Ver 14.12 Distrib 5.0.67, for Win32 (ia32)

Connection id:          14316
Current database:       phppro2
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.67-community-nt MySQL Community Edition (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Insert id:              700
Server characterset:    cp932
Db     characterset:    cp932
Client characterset:    cp932
Conn.  characterset:    cp932
TCP port:               3306
Uptime:                 3 hours 2 min 14 sec

Threads: 143  Questions: 144345  Slow queries: 3  Opens: 114  Flush tables: 4  Open tables: 3  Queries per second avg: 3.704

• いろいろ表示されるんですね。
• SHOW STATUS と SHOW VARIABLES はそれぞれ250件くらいという長大な結果が返される。LIKE 句を覚えておけば、名前にアタリがついている場合に便利だね。こんな感じで実行する。

mysql> SHOW STATUS;
+-----------------------------------+-----------+
| Variable_name                     | Value     |
+-----------------------------------+-----------+
| Aborted_clients                   | 0         |
| Aborted_connects                  | 0         |
:                                   :           :
| Uptime                            | 11053     |
| Uptime_since_flush_status         | 11053     |
+-----------------------------------+-----------+
249 rows in set (0.02 sec)

mysql> SHOW STATUS LIKE "Slow%";
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 0     |
+---------------------+-------+
2 rows in set (0.00 sec)


mysql> SHOW VARIABLES;
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| auto_increment_increment        | 1                                 |
| auto_increment_offset           | 1                                 |
:                                 :
| version_compile_os              | Win32                             |
| wait_timeout                    | 28800                             |
+---------------------------------+-----------------------------------+
232 rows in set, 1 warning (0.00 sec)

mysql> SHOW VARIABLES LIKE "%buffer%";
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| bulk_insert_buffer_size       | 8388608   |
| innodb_buffer_pool_awe_mem_mb | 0         |
| innodb_buffer_pool_size       | 134217728 |
| innodb_log_buffer_size        | 8388608   |
| join_buffer_size              | 131072    |
| key_buffer_size               | 8384512   |
| myisam_sort_buffer_size       | 8388608   |
| net_buffer_length             | 16384     |
| preload_buffer_size           | 32768     |
| read_buffer_size              | 131072    |
| read_rnd_buffer_size          | 262144    |
| sort_buffer_size              | 16777216  |
+-------------------------------+-----------+
12 rows in set (0.00 sec)

• わー。だめだ。目がまわりそうです。
• 内容はまだ分からなくていいよ。とりあえず見方だけ知っておくとちょっとは「格好良い」だろう？(笑)　各パラメタや変数の内容についてはマニュアルとか　MySQL辞典(翔泳社)などの書籍を参考にして、必要なものからひとつひとつ自分のものにしていったらいいと思うよ。