MySQLサーバー5.1 のインストールおよび設定方法
FreeBSD8.0において、MySQLサーバー5.1 のインストールおよび設定方法についてご紹介します。
MySQLとは
MySQLは、世界で最も有名なオープンソース・リレーショナルデータベース管理システム（RDBMS）であり、PostgreSQLなどの他のRDBMSに比較して圧倒的に高いシェアを誇ります。
Yahoo!、Facebook、Twitter、ウィキペディア等の巨大なウェブサイトでの応用例も多く、
ウェブサイトの構築に用いるソフトウェア環境である LAMP（Linux, Apache, MySQL, PHP）の一角として普及してきています。
ストレージエンジンには、トランザクションが使用できるInnoDBや、高速なMyISAMなどがあり、用途に応じてテーブルごとに使い分けることができます。
また、MySQLの管理ツールとしては phpMyAdmin が比較的良く知られています。
phpMyAdmin と併用すれば、ウェブブラウザを使用してMySQLを管理することもができます。
MySQLサーバ5.1 のインストール（mysql-server-5.1.45）
他モジュールとの整合性を保つため、portsからインストールします。
portsのインストール準備まで完了していると、以下の通りインストールは至って簡単です。
# cd /usr/ports/databases/mysql51-server
# make ← Makefile にしたがってコンパイルします。
# make install clean ← インストール & 不要モジュールの消去
シンボリックリンクによるデータベースディレクトリの移動
MySQL のデフォルト設定では、データベースは /var/db/mysql 配下に作成されます。
しかし、FreeBSD8.0 のデフォルトのパーティション設定では、 /var は0.4Gしか割り当てられません。
このため大容量のDBを作成するには、/var の容量を増やすか、あるいはデータベースを /usr 配下に移動するか、のどちらかを実施する必要があります。
ここでは、表1.1に示す通り、シンボリックリンクを利用して /var/db/mysql を /usr/local/mysql に移動します。
表1.1 シンボリックリンクによるデータベースディレクトリの移動
# /usr/local/etc/rc.d/mysql-server stop ← MySQLが稼動している場合は停止する。
# cp -r /var/db/mysql /usr/local ← 階層構造ごとデータベースをコピーする。
# chown -R mysql:mysql /usr/local/mysql ← /usr/local/mysql 配下の所有者・グループ名をmysqlに変更する。
# mv /var/db/mysql /var/db/mysql.bck ← 元のディレクトリの名称変更する（あるいは削除しても可）。
# ln -s /usr/local/mysql /var/db/mysql ← シンボリックリンクを作成する。
# /usr/local/etc/rc.d/mysql-server start ← MySQLを開始する。
なお、/var/db/mysql 配下に作成されるデータベース名ディレクトリのモード（アクセス権限）は'700'です。
これが'600'（実行権が無い）場合は、データベースが使用できなくなり、phpMyAdmin で見ると、全てのテーブルが「使用中」となり、「修復」もできなくなります。
誤って設定しない限りデータベース名ディレクトリのモードが変わることはありませんが、もしこのような現象に出くわした場合はディレクトリのモードを確認してみて下さい。
MySQLサーバ5.1 の設定
起動時にMySQLも起動する
ファイル /etc/rc.conf に次の一行を挿入しサーバを再起動すればOKです。
表2 サーバ起動時にMySQLサーバ5.1 も起動する方法
mysql_enable="YES"
MySQLの動作環境定義ファイル /usr/local/etc/my.cnf の作成および設定
システムのメモリ容量に応じて、下表に示す通りテンプレートをコピーして使用します。
表3 /usr/local/etc/my.cnf の作成
MySQLに1GB～2GBのメモリを割り当てられる大規模システム
# cp /usr/local/share/mysql/my-huge.cnf /usr/local/etc/my.cnf
MySQLに512MB程度のメモリを割り当てられる少し大きめのシステム
# cp /usr/local/share/mysql/my-large.cnf /usr/local/etc/my.cnf
MySQLに128MB程度のメモリを割り当てられる中規模システム
# cp /usr/local/share/mysql/my-medium.cnf /usr/local/etc/my.cnf
MySQLに64MB以下のメモリしか割り当てらない小規模システム
# cp /usr/local/share/mysql/my-small.cnf /usr/local/etc/my.cnf
テンプレートは読み取り専用ですので、作成した /usr/local/etc/my.cnf の編集を可能にします。
表4 /usr/local/etc/my.cnf の編集を可能にする
# chmod 644 /usr/local/etc/my.cnf
表5 /usr/local/etc/my.cnf の編集：MySQLの文字コード設定
[client] ←クライアントライブラリを使った標準設定（アプリ側で読み込みが必要）
default-character-set = utf8
・・・
[mysqld] ←サーバの設定
character-set-server = utf8
・・・
[mysqldump] ←mysqldumpコマンドの設定
default-character-set = utf8
・・・
[mysql] ←mysqlコマンドの設定
default-character-set = utf8
・・・
なお、MySQLの高速化のためのチューニングは、my.cnfを修正して行います。
詳細は、
MySQLのチューニングによる高速化の項を参照してください。
MySQLのユーザ登録およびパスワードの設定
ユーザ登録するためには、MySQLにrootでログインする必要がありますので、最初にMySQLのrootのパスワードを設定します
（MySQLインストール直後に各コマンドを有効にするためにはサーバーをリブートする必要があります）。
表6 MySQLのrootのパスワードの設定
# mysqladmin -u root -h localhost password 'パスワード'
ユーザ情報は、rootユーザのmysqlというデータベースのuserというテーブル暗号化して保存されますので、下表の要領でユーザを追加します。
表7 MySQLユーザの登録方法
# mysql -p
Enter password:パスワードを入力（-pの後に続けてパスワードの設定でもログイン可）
mysql> use mysql;
Database changed
mysql> insert into user set user="user名", password=password("パスワード"), host="localhost";
mysql> flush privileges; （権限テーブルを再読み込み）
mysql> exit
この設定は、次節に示すMySQLデーモンの再起動で有効になります。
MySQLサーバの起動と停止
MySQLサーバの設定変更やレプリケーションなど、サーバの起動や停止が必要になることがあります。
表8に示すMySQLデーモンの起動・停止スクリプトでサーバの起動・停止が可能です。
表8 MySQLサーバー5.1 の起動・停止スクリプト
# /usr/local/etc/rc.d/mysql-server start ←MySQLデーモンを起動します
# /usr/local/etc/rc.d/mysql-server restart ←MySQLデーモンを再起動します
# /usr/local/etc/rc.d/mysql-server stop ←MySQLデーモンを停止します
バイナリーログの削除
指定日時以前のバイナリーログを削除する方法
MySQLでは更新系のSQLクエリがバイナリーログとして記録され、データベースの再生やレプリケーション（後述）などに利用されます。
マスターデータベースのデフォルト指定では、バイナリーログは消去されませんので、表9に示す通りバイナリーログが溜まり、ディスク領域が圧迫されます。
表9 1GB程度のバイナリーログが多数溜まる
# cd /usr/local/mysql
# ls -l
total 54127120
・・・
-rw-r----- 1 mysql mysql 125 May 9 08:21 mysql-bin.000001
-rw-r----- 1 mysql mysql 7400 May 9 08:21 mysql-bin.000002
・・・
-rw-rw---- 1 mysql mysql 1073741920 Jun 15 05:14 mysql-bin.000044
-rw-rw---- 1 mysql mysql 1073742083 Jun 16 20:37 mysql-bin.000045
・・・
-rw-rw---- 1 mysql mysql 1073742363 Jul 24 15:35 mysql-bin.000066
-rw-rw---- 1 mysql mysql 73870035 Jul 24 19:08 mysql-bin.000067
-rw-r----- 1 mysql mysql 1273 Jul 24 15:35 mysql-bin.index
また、mysqlにログインし、表10に示すコマンドでもバイナリーログが表示されます。
表10 mysqlでバイナリーログを確認する方法
mysql> show binary logs;
そして、例えば2010年7月20日より前のバイナリーログを消去するには、表11に示すコマンドを入力します。
表11 mysqlで2010年7月20日より前のバイナリーログを消去する方法
mysql> PURGE MASTER LOGS BEFORE '2010-07-20 00:00:00';
指定期間以前のバイナリーログを自動的に削除する方法
指定期間以前のバイナリーログを自動的に削除するには、表12に示す通り /usr/local/etc/my.cnf にログの有効期限を設定し、MySQLを再起動します。
表12 /usr/local/etc/my.cnf にログの有効期限を設定
[mysqld]
・・・
server-id=1
set-variable = expire_logs_days=7 <-- ログの保存期間を７日に設定
・・・
ログの有効期限が正常に設定されたかどうか、表13に示す通りMySQLのコマンドで確認することができます。
表13 MySQLのログの有効期限を確認する方法
mysql> show global variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 7 |
+------------------+-------+
MySQLのチューニングによる高速化
最近のシステムでは、表3に示したように、システムの規模に応じた my.cnf を選択してやれば、かなり良い性能が出ます。
これは、CPU性能およびデータ転送性能が一昔前のハードウェアと比較して格段に改善されたことによる所が大きいでしょう。
しかし、システムの性能を最大限に活用するためには、やはりシステム構成やデーターベースの特徴に応じたチューニングがある程度有効です。
ここでは、以下のようなシステムに応じたチューニングの一例を示します。
- CPU:XEON X3450（2.66GHz, 8MBキャッシュ）、メモリ4GB。
- DBサイズは数百万～1千万レコード、1個のテーブルサイズは数十万～100万レコード。
- MyISAMの参照系を高速化したい（更新はバックグラウンドでゆっくりでOK）。
- 15,000rpmのHDDを備えたシステムに比較して、7,200rpmのHDDを備えたシステムの方がクエリが溜まりやすく、最悪1クエリ数十秒～2分程度かかる事象が発生。
このような事象が発生した時には、CPUの稼働率が数%数十%に落ち、HDDアクセスのランプが常時ONとなる。
このような事象は、表14に示すように
sort_buffer_size, read_rnd_buffer_size を大きく取ることによりある程度改善されました。
これらの変数は、各スレッド単位の設定ですので、安全のため max_connections（最大同時接続数：デフォルト151）を低めに設定しています。
(8MB + 2MB + 8MB ) * 64 = 1.15GB のメモリを占めることになりますので、闇雲には大きくできません。
thread_cache_size を max_connections と同じ数にすることにより、新たな接続が発生した時に、新たなスレッドを生成するのではなく、
既にあるスレッドが再利用されるようになり、負荷減少に貢献します。
また、query_cache_size の設定により、クエリの結果がメモリに保存され、同じクエリが発生した場合にはメモリから呼び出されます。
さらに、low_priority_updates の設定では、更新系のクエリの優先度を参照系のクエリよりも下げます。
デフォルトでは、更新系のクエリはより重要と考えられるため、参照系のクエリより優先度が高くなっていますが、ここでは参照系のクエリの優先度を高くして高速な応答を優先させます。
変数の詳しいい意味は、例えば、
MySQL 5.1 リファレンスマニュアル 4.5. MySQL サーバーの最適化
を参照してください。
「15,000rpmのHDDを備えたシステムに比較して、7,200rpmのHDDを備えたシステムの方がクエリが溜まりやすい」という現象は、
HDDの性能に起因しているため、複数のクエリが同時に多数発生した場合には、避けられません。
複数のマシンで負荷分散を行うか、複数のHDDに振り分けるか、あるいは高速なHDDあるいはSSDに変えるか、を検討するのが手っ取り早いと思います。
ただ、複雑なクエリを使用している場合は、クエリやデータベースのインデックスを見直すことにより、劇的にアクセス速度を改善できる場合もありますので、見直す価値はあるでしょう。
表14 /usr/local/etc/my.cnf のチューニング（大規模システム用ファイル my-huge.cnf からの変更点）
[mysqld]
・・・
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
・・・
query_cache_size = 32M
・・・
thread_cache_size = 64
max_connections = 64
low_priority_updates = 1
・・・
HDD分散による高速化
近年のCPUおよび転送速度の向上により、MySQLの性能の上限はHDDの性能に起因する可能性が高いようです。
ここでは、複数のHDDにデータベースあるいはテーブルを割り当てることにより高速化する方法を示します。
基本的なテクニックは、表1.1のようにシンボリックリンクを利用し、データベース単位、あるいはテーブル単位に分散したHDDに配置します。
表15は、 RakutenArticlesX という名称のテーブルを、 /usr2/mysql/neoluxr 配下から /usr3/mysql/neoluxr 配下へ移動する例です。
この操作は、 MySQL を停止して行なっても良いのですが、MySQL が既に稼働中であり、停止できない環境でこの操作を行ないたいというニーズが強いことから、
ここでは、一時的に更新系のクエリを禁止して行ないます。
表15 シンボリックリンクを利用したテーブルのHDD分散
mysql> FLUSH TABLES WITH READ LOCK; ← 更新系のクエリを禁止します。
同じSSHウィンドで mysql を終了すると、更新系クエリの禁止が解除されますので、以下の処理は別ウィンドウで行います。
# cd /usr3/mysql/neoluxr
# cp /usr2/mysql/neoluxr/RakutenArticlesX.* . ← RakutenArticlesXに関連するデータをコピーします。
# chown mysql:mysql RakutenArticlesX.* ← コピーしたファイルのオーナーとグループをmysqlに変更します。
コピー元のディレクトリのファイル名称変更します（または削除でも可能です）。
# mv /usr2/mysql/neoluxr/RakutenArticlesX.MYD /usr2/mysql/neoluxr/RakutenArticlesX.MYD.bck
# mv /usr2/mysql/neoluxr/RakutenArticlesX.MYI /usr2/mysql/neoluxr/RakutenArticlesX.MYI.bck
# mv /usr2/mysql/neoluxr/RakutenArticlesX.frm /usr2/mysql/neoluxr/RakutenArticlesX.frm.bck
シンボリックリンクを作成します。
# cd /usr2/mysql/neoluxr
# ln -s /usr3/mysql/neoluxr/RakutenArticlesX.MYD RakutenArticlesX.MYD
# ln -s /usr3/mysql/neoluxr/RakutenArticlesX.MYI RakutenArticlesX.MYI
# ln -s /usr3/mysql/neoluxr/RakutenArticlesX.frm RakutenArticlesX.frm
更新系クエリを許可します（更新系のクエリを禁止した最初のウィンドウで行なう）。
mysql> UNLOCK TABLES;
シンボリックリンクを自動的に認識してくれる場合もありますが、エラーになる場合もありますのでMySQLを再起動します。
# /usr/local/etc/rc.d/mysql-server restart
クラッシュしたテーブルの修復方法
原因は不明ですが、テーブルを移動した場合にテーブルがクラッシュして読めないことがあります。
例えば、phpMyAdminでテーブルを表示しようとすると次のようなメッセージが出ます。
#144 - Table './neolux/AmazonUKArticlesM' is marked as crashed and last (automatic?) repair failed
このような場合には、MySQLにログインし、次の要領で修復できることがありますので、あきらめず試してみましょう。
mysql> use データベース名
Database changed
mysql> show tables;
+--------------------------+
| Tables_in_neolux |
+--------------------------+
| AmazonUKArticlesA |
| AmazonUKArticlesB |
| AmazonUKArticlesD |
・・・・
mysql> REPAIR TABLE テーブル名;
+--------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| データベース名.テーブル名 | repair | status | OK |
+--------------------------+--------+----------+----------+
1 row in set (9.22 sec)
MySQLの停止事故
3台のWEBサーバー上で稼動するMySQLの運用実績から、FreeBSD上でMySQLは非常に安定して稼動します。
しかし、去る2012年2月12日に、最もMySQLの稼働率の高いサーバーのMySQLに接続できないというエラーが発生しました。
サーバーが稼動してから約3年経過後のことです。
MySQLとApachをリスタートした所、PHPによる接続（リモート接続を含む）は可能になりました。
しかし、phpMyAdminを使用したリモート接続において、「#2002 MySQL サーバにログインできません」というエラーが出るようになりました。
"/usr/local/etc/my.cnf"では、[client]および[mysqld]の項目において"socket = /tmp/mysql.sock"と定義されています。
しかし、問題の起こったサーバーでは/tmp/mysql.sockが無くなっていました。
そこで、サーバー自体を"shutdown -p now"コマンドで再起動したところ、/tmp/mysql.sockが新たに作成され、
phpMyAdminを使用したリモート接続において、ログインが可能となりました。
問題発生時に"df"コマンドで確認しましたが、ディスクが一杯ということでもないようでした。
MySQLが突然停止した真の原因はつかめていませんが、MySQLリスタート時に、停止できないMySQLのプロセスがあったので"kill -9"コマンドで強制終了しました。
Apacheは稼動を継続すると、ログファイルを掴んで離さずディスクが一杯になる事象が発生するため、
/etc/crontabにて定期的（１週間に１回または２回）に再起動するようにしています。
とりあえず、MySQLも定期的に再起動するように設定し、様子を見ることにしました。
最終更新日：2012年2月14日
