FreeBSD8.0 WEBサーバ構築ノート
MySQLサーバー5.1 のインストールおよび設定方法

MySQLサーバー5.1 のインストールおよび設定方法のご紹介です
ページ応答速度・ページランク取得タグ ⇒ 応答速度 & Google PageRank

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のインストール準備まで完了していると、以下の通りインストールは至って簡単です。
表1 MySQLサーバ5.1 のインストール
# 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 <-- ログの保存期間を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性能およびデータ転送性能が一昔前のハードウェアと比較して格段に改善されたことによる所が大きいでしょう。 しかし、システムの性能を最大限に活用するためには、やはりシステム構成やデーターベースの特徴に応じたチューニングがある程度有効です。 ここでは、以下のようなシステムに応じたチューニングの一例を示します。 このような事象は、表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にて定期的(1週間に1回または2回)に再起動するようにしています。 とりあえず、MySQLも定期的に再起動するように設定し、様子を見ることにしました。
最終更新日:2012年2月14日

* 無断転載禁止。
* どのページでもご自由にリンクして下さい。
* ご意見・ご質問等がございましたら こちらからメールをご送付下さい。

FreeBSD8.0 メニュー
Sponsoredサイト
美容・健康・ダイエットサイト
脳の機能と心と体の健康
健康な心と体の栄養ブログ
占星術・風水・タロット・手相
東洋・西洋占星術
今月の運勢 - 月間無料占い
今週の運勢 - 週間無料占い
今日の運勢 - 無料占い
誕生日占い
相性占い
恋愛占い
ホロスコープ占星術
12星座占い
無料姓名判断で読み解く運命
タロットカードで無料占い
手相占いによる未来診断
飛星派風水で住宅開運
夢の夢占い
男と女の心理学と心理テスト
占星術・風水 & 旅行ブログ
会社情報等
運営会社情報

プライバシー・ポリシー
通販Neo-楽天編
ネットショッピングNeo
通販Neo-Yahoo!ショッピング編
お楽しみサイト
Western-Eastern Astrology

ザ・ホラリー占星術

便利サイト
評価コム・動物病院偏
株式投資講座(改訂版)
OnlineShop-Neo Amazon-USA
OnlineShop-Neo Amazon-UK
通販検索Neo
モバイル通販Neo-楽天
モバイル通販Neo
トラベルNeo
脳の機能と心と体の健康
ネオラクス
株式投資講座(改訂版)
無料SEO対策 無料SEO対策
(192.168.1.100)