案内編論文編書籍編雑記編

さくらの VPS 設定覚書（５）MySQL

MySQL は、世界でもっとも使われている、オープンソースのリレーショナルデータベース管理システムです。このページでは、MySQL および MySQL サーバをウェブ・ブラウザで管理するためのデータベース接続クライアント・ツール phpMyAdmin のインストールと初期設定の方法、データベースのエクスポート、インポートの方法を解説します。また、完成した LAMP スタックのサーバの性能の計測もしましょう。

1. MySQL

既にインストールが終了している場合、MySQL デーモンを、以下のコマンドで起動しましょう。

service mysqld start

“OK”と出たら、成功。

1.1. セキュリティの強化

最初に、セキュリティ強化の設定を行いましょう。ルートにいることを確認して、以下の一行を入力します。

/usr/bin/mysql_secure_installation

すると、次のような質問が出ます。

Enter current password for root (enter for none):

デフォルトではパスワードが無いので、そのままリターン・キーを押します。

Set root password? [Y/n] Y

“Y”と入力し、リターン・キーを押します。その後、二回パスワードを入力し、出てくる問いにすべて“Y”と答えます。“Success!”と出たら、成功です。

テスト用に設定されている匿名ユーザによるログインを禁止します。

Remove anonymous users? [Y/n] Y

“Y”と入力し、リターン・キーを押します。“Success!”と出たら、成功です（以下同様）。

セキュリティを高めるために、localhost 以外からのアクセスを禁止します。

Disallow root login remotely? [Y/n] Y

テスト用のデータベースを削除します。

Remove test database and access to it? [Y/n] Y

権限のあるテーブルをリロードします

Reload privilege tables now? [Y/n] Y

“All done! If you've completed all of the above steps, your MySQL installation should now be secure.　Thanks for using MySQL!”というメッセージで終了。

1.2. 設定の変更

MySQL の設定ファイルを変更することで、バッファの上限を変更することができます。バッファには、グローバルバッファとスレッドバッファの二種類があります。サーバに十分なメモリがある場合、グローバルバッファの上限をそれに合わせて引き上げたほうがよいのですが、スレッドバッファは、コネクションごとに確保されるものなので、多くのメモリを割り当てることには慎重でなければいけません。

こうした設定変更をする時、一番気を付けなければいけないことは、innodb_log_file_size の変更です。これを変更する場合、ログファイルをいったん削除して、更新しなければならないのですが、テーブルスペースに反映されていないデータを残したまま削除してしまうと、

InnoDB: Error: log file /var/lib/mysql/ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 134217728 bytes!

というエラー・メッセージが出ます。そこで、以下のように、テーブルスペースへデータを反映させ、MySQL をシャットダウンし、ログファイルを削除し、MySQL を再開させます。

mysql>SET GLOBAL innodb_fast_shutdown=0;
service mysqld stop
rm -f /var/lib/mysql/ib_logfile[01...]
service mysqld start

“ib_logfile[01...]”の数字の部分は、ログファイルの数に応じて、書き加えてください。

以上の設定変更に当たっての注意点を踏まえた上で、設定の変更を行いましょう。

設定ファイルにはいくつかのテンプレートが用意されています。以下のコマンドで、その候補を見てみましょう。

ls -al /usr/share/mysql/

リストには、my-huge.cnf, my-innodb-heavy-4G.cnf, my-large.cnf, my-medium.cnf, my-small.cnf という五つの cnf ファイルがあります。自分のサーバの規模にあったファイルを選びましょう。ここでは、“my-innodb-heavy-4G.cnf”を選んで、コピーすることにします。

cp /usr/share/mysql/my-innodb-heavy-4G.cnf /etc/my.cnf

設定ファイルを開きます。

vi /etc/my.cnf

まず、日本語等も扱えるようにするために、デフォルトの文字セットを指定します。[mysqld] の項目に以下の 1行を追記します。

character_set_server = utf8

MySQL 5.5 以上は“utf8mb4”もサポートしています。３バイト対応だった“utf8”とは異なり、“utf8mb4”は４バイト対応であり、BMP（基本多言語面）外の文字を使うことができます。他方で、インデックスされる最大文字数が減るとか、パフォーマンスが落ちるなどのデメリットもあるので、用途に応じて、文字セットを選びましょう。ここでは、以下、“utf8”を採用することにします。

次に、[mysqld] に記載されているメモリの設定値を確認しましょう。

MyISAM を使う場合は、以下の設定値を見直します。

  • key_buffer_size：MyISAM のインデックスをキャッシュするメモリのサイズ。空きメモリの30% を割り当てます。
  • max_allowed_packet：パケット・メッセージ・バッファの最大値。
  • myisam_sort_buffer_size：REPAIR TABLE 文が MyISAM テーブルのインデックスをソートするときなどに使うメモリのサイズ。

InnoDB を使う場合は、以下の設定値を見直します。

  • default-storage-engine：“innodb”にします。
  • innodb_buffer_pool_size：InnoDB のデータとインデックスをキャッシュするメモリのサイズ。空きメモリの70～80%を割り当てます。
  • innodb_additional_mem_pool_size：InnoDB のデータ・ディクショナリ情報をキャッシュするメモリのサイズ。低めに設定し、エラーログに警告が出力されたら増やすとよいでしょう。
  • innodb_log_file_size：InnoDB の更新ログを記録するディスク上のファイルのサイズ。innodb_buffer_pool_size に合わせて大きくします。
  • innodb_log_buffer_size：InnoDB の更新ログを記録するメモリのサイズ（できるだけ変更しない方がよい）。

[mysqldump] に記載されている max_allowed_packet の値が小さすぎて、バックアップ・ファイルのインポートができないという時は、引き上げましょう。

  • max_allowed_packet = 16M → 64M（例）

編集が終わったら、保存して閉じます。

1.3. 再起動

MySQL を再起動します。

/etc/rc.d/init.d/mysqld restart

　 最後に、chkconfig コマンドで、MySQL が自動起動するように設定します。

chkconfig mysqld on

自動起動設定を確認します。

chkconfig --list mysqld
mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off

2-5 が on であることを確認します。

2. phpMyAdmin

phpMyAdmin は、MySQL サーバをウェブ・ブラウザで管理するためのデータベース接続クライアント・ツールです。既にインストールは終えているものとします。

2.1. 設定の変更

サンプルから設定ファイルをコピーします（フォルダ名が“phpMyAdmin”となっているか、“phpmyadmin”となっているか、事前に調べましょう。

cp /usr/share/phpmyadmin/config.sample.inc.php /usr/share/phpmyadmin/config.inc.php

設定ファイルを開きます。

vi /usr/share/phpmyadmin/config.inc.php

任意の暗号化パスフレーズを英数字で入力します。

$cfg['blowfish_secret'] = ''; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
　　　　　↓
$cfg['blowfish_secret'] = 'abc123'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH!*/

“abc123”は例です。

また、好みに応じて、以下の二行を追加しましょう。数字は任意です。

$cfg['LoginCookieValidity'] = 36000; 
$cfg['MaxRows'] = 100;

1行目は、クッキーの持続時間（単位は秒）の設定で、「1800 秒以上操作をしませんでした。ログインしなおしてください」というエラー・メッセージが頻繁に出て不便なときは、長めに時間を設定しましょう。2行目は、1ページに表示する表の行数を決める設定で、上の例では、デフォルトの 30 行が 100 行に増やされています。

編集を終えたら、保存して、閉じます。

2.2. バーチャルホストの登録

アパッチの設定ファイルを開きます。

vi /etc/httpd/conf/httpd.conf

“G”を入力して、最下端にジャンプし、VirtualHost のリストに、phpMyadmin のためのバーチャルホストを一つ追加します。 

###VirtualHosts of phpmyadmin subdomains

<VirtualHost *:8080>
ServerName phpmyadmin.domain.com
ServerAdmin 電子メールアドレス
DocumentRoot /usr/share/phpmyadmin/
</VirtualHost>

保存して閉じた後、phpMyAdmin の設定ファイルを開きます。 

vi /etc/httpd/conf.d/phpmyadmin.conf

接続元のIPアドレスを追加します。

<Directory "/usr/share/phpmyadmin">
Order Deny,Allow
Deny from all
Allow from 127.0.0.1
Allow from IP アドレス
</Directory>

アパッチを再起動させて、設定変更を反映させます。

service httpd reload

次に、NGINX のバーチャル・ホスト・ファイルを開きます。

vi /etc/nginx/conf.d/virtual.conf

以下のように、phpmyadmin サブドメインを登録します。 

server {
 listen IPアドレス:80;
 server_name phpmyadmin.domain.com;
 access_log /var/log/nginx/phpmyadmin.domain-access.log;
 root /var/www/phpmyadmin.domain.com/htdocs;
 location / {
 proxy_redirect off;
 proxy_set_header Host $host;
 proxy_set_header X-Real-IP $remote_addr;
 proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
 proxy_set_header X-Server-Address $server_addr;
 proxy_pass_header Set-Cookie;
 proxy_pass http://localhost:6081;
	}
}

保存して閉じ、NGINX を再起動します。

/etc/init.d/nginx restart

“OK”と出れば、成功。

2.3. ユーザの追加

以下の URL で、phpMyAdmin にブラウザでアクセスします。

http://phpmyadmin.domain.com/

言語で“utf-8”を選び、ユーザ名“root”とそのパスワードでログインします。

「特権」をクリックします。すると、以下のような「ユーザ概略」が示されます。

	root 	127.0.0.1 	はい 	ALL PRIVILEGES 	はい 	特権を編集
	root 	::1       はい 	ALL PRIVILEGES 	はい 	特権を編集
	root 	localhost 	はい 	ALL PRIVILEGES 	はい 	特権を編集

「新しいユーザを追加する」をクリックします。

ユーザ名、ホスト名（localhost）、パスワードを入力し、「グローバル特権」で「すべてチェックする」を選びます。

「実行する」をクリックして、「ユーザ概略」に新しいユーザが追加されていることを確認します。

この他、“www英数字.sakura.ne.jp”、“127.0.0.1”をホスト名とするユーザを登録しておきましょう。

3. データベースの構築

データベースの構築は、コマンドでもできるが、ここでは、phpMyAdmin を使った方法を優先的に採用します。

3.1. データベースの作成

ユーザ名でログインし、phpMyAdmin のホーム（index.php）に移動します。

「新規データベースを作成する」のフォームにデータベース名を入力し、照合順序のプルダウンから以下のように文字セットを選び、「作成」をクリックします。

照合順序のプルダウンから“utf8-general-ci”を選ぶ
図1 照合順序のプルダウンから“utf8-general-ci”を選ぶ

「データベース×××を作成しました」というメッセージが出たら、成功。

3.2. データベースのエクスポート

データベースをバックアップするには、phpMyAdmin のエクスポート機能を使います。

Drop Table オプションを選んでエクスポートする
図2 Drop Table オプションを選んでエクスポートする

まず、保存するデータベースを左のコラムから選び、それから「エクスポート」タブをクリックします。

  • 「全選択｣を選ぶ。
  • 「DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT を追加」をチェックする。
  • 「ファイルに保存する」にチェックを入れる。
  • 「ファイル名のテンプレート」を“__DB__”にして、「テンプレートを記憶させる」にチェックを入れておけば、自動的にデータベース名でファイルが保存される。

以上を確認して｢実行する｣をクリックし、SQL ファイルを保存します。圧縮は必ずしも必要ではありません。

3.3. データベースのインポート

データベースのサイズが小さいなら、phpMyAdmin のインポート機能を用いることで、簡単にデータベースの復元ができますが、大きい場合は、SSH を使わなければいけません。

まず、ダウンロードしたSQLファイルを SFTP クライアントソフトを用いて、リモートサーバーのホームディレクトリにアップロードします（あぷろーどする場所はどこでもかまわないのですが、ここではユーザの権限が及ぶディレクトリを選ぶことにします）。

その後、Putty を使って、SSH にログインし、以下のようなコマンドを出します。

cd /home/ユーザ名/
mysql -h phpmyadmin.ドメイン名.com -u ユーザ名 -pパスワード データベース名 <インポートするファイル名.sql

日本語の部分を自分の情報に置き換えてください。なお、“-p”とパスワードとの間にスペースを入れてはいけません。

Enter を押して、成功しても無反応なので、phpMyAdmin の該当テーブルをチェックし、データベースがインポートされていることを確認しましょう。

4. サーバの性能の計測

サーバの性能を測るベンチマークには、いろいろありますが、ここでは、代表的なものを取り上げます。

4.1. UnixBench

UnixBench は、総合的なサーバ性能を計測するツールで、12項目のパフォーマンスを測定し、個別の結果とそれらを総合的に評価した指標を出力します。

まず、必要なパッケージをインストールします。

yum --enablerepo=remi,epel,rpmforge install perl-Time-HiRes

リンク先で最新バージョンを確認のうえ、以下のコマンドで、UnixBench をダウンロードします。

wget http://byte-unixbench.googlecode.com/files/UnixBench5.1.3.tgz

解凍して、コンパイルします。

tar zxvf UnixBench5.1.3.tgz 
cd UnixBench/
make

そして、計測を開始します。

./Run

さくらインターネットのVPS 512 プランの Index Score は 1300 程度、同 1.5G は 1500 程度、同 4G は2000 程度です。

4.2. ApacheBench

アパッチ・ウェブサーバの性能を計測するベンチマークです。アパッチに標準で付属しているので、何もインストールする必要はありません。

接続数 100、最大同時接続 5で計測する時は、以下のコマンドで行います。

ab -n 100 -c 5 http://www.systemicswiki.com/index.html

すると次のような結果が出てきます。

Server Software:        cloudflare-nginx
Server Hostname:        www.systemicswiki.com
Server Port:80
Document Path:          /index.html
Document Length:        2769 bytes
Concurrency Level:      5
Time taken for tests:   1.279284 seconds
Complete requests:      100
Failed requests:        0
Write errors:           0
Total transferred:      328689 bytes
HTML transferred:       276900 bytes
Requests per second:    78.17 [#/sec] (mean)
Time per request:       63.964 [ms] (mean)
Time per request:       12.793 [ms] (mean, across all concurrent requests)
Transfer rate:          250.14 [Kbytes/sec] received
Connection Times (ms)
  min  mean[+/-sd] median   max
Connect:        8   10   2.0     10      20
Processing:    22   27   5.8     25      54
Waiting:       21   26   5.7     25      53
Total:         31   37   6.6     35      65
Percentage of the requests served within a certain time (ms)
  50%     35
  66%     36
  75%     39
  80%     40
  90%     47
  95%     57
  98%     60
  99%     65
 100%     65 (longest request)

このデータの中で最も重視されるのが、1秒間に処理されたリクエスト数を示す“Requests per second”で、この数値を大きくすることが、ウェブ・サーバ改善の目標となります。

4.3. ページ・ロード計測サイト

これ以外にも、ページの読み込み時間が測定できるサービスが、ネット上にはたくさんあります。

  • Page Speed Online：Google によるサイト診断ツール。改善すべき点を、優先の度合い別に列挙しれくれます。
  • YSlow Yahoo! がページ・ロード時間の計測とサイト高速化のアドバイスをしてくれます。
  • Web Page Test AOL が開発したロード時間計測サイト。どこに時間がかかっているのか、詳細に分析してくれます。
  • DynaTrace AJAX Edition ページ・ロード時間のみならず、JavaScript 実行時間とそのボトルネックを分析します。
  • Blaze Mobitest　iPhone をはじめとするモバイルでのページ・ロード計測。
  • Loads.in 様々な国やブラウザでのアクセス時間を計測。
  • Dr. Watson 接続時間計測を含めた総合的な SEO 対策ツール。
参考文献
［投稿者：永井俊哉］
ブログ内移動
← さくらの VPS 設定覚書（４）PHP｜総目次｜
カテゴリー内移動
← さくらの VPS 設定覚書（４）PHP｜コンピュータ関係の備忘録
お知らせ

このページでのコメントの受付は終了しました。本ページに関して、御意見、御質問、御批判をお持ちの方で、私に回答を求める方は、システム論フォーラムに投稿してください。

Sponsored Links
Books