FAT47の底辺インフラ議事録

学んだことのメモ帳です

MySQL Serverの運用を行う上で役に立つ設定

MySQL Serverには運用を行う上で便利なコマンドや設定が存在します。
今回はそれらの紹介をしていきたいと思います。

mysql_secure_installation
mysql_secure_installationは、MySQLの初回起動時に実行するとよいコマンドです。
MySQL Serverのインストール後の初期設定はセキュリティの甘い設定がなされています。
このコマンドを利用することで、以下のような設定を簡単に行うことができます。
・rootパスワードの変更
・rootのリモートホストからのログイン禁止
・匿名ユーザの削除
・testデータベースの削除

mysql_secure_installation
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

このようなmysql.sockが見つからない旨のエラーメッセージが表示された場合、以下のようにシンボリックリンクを張って対応するとよいでしょう。

ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

起動に成功するといかのように対話式で設定が進んでいきますので、基本的にすべてyで承諾してください。

Change the root password? [Y/n] y #rootパスワードを変更よいか
New password:   #rootパスワード入力
Re-enter new password:  #もう一度入力
Password updated successfully!
Reloading privilege tables..
 ... Success!
 
 Remove anonymous users? [Y/n] y #匿名ユーザを削除して良いか
 ... Success!
 
 Disallow root login remotely? [Y/n] y #リモートからのrootログインを禁止してよいか
 ... Success!
 
 Remove test database and access to it? [Y/n] y #testデータベースを削除してよいか
 - Dropping test database...
  ... Success!
  
Reload privilege tables now? [Y/n] y #リロードしてよいか
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

TCP/IP接続の禁止
MySQLサーバとアプリケーションが同じホストに同居しているような小規模な構成の場合、TCP/IP接続を無効にするという手がつかえます。
無効にした場合MySQL ServerはTCP/IP接続を行わずに、UNIXドメインソケットを通じた接続のみを行うようになります。
MySQL ServerへログインするためにはOSホストへログインしなければいけないようになるので、セキュリティリスクを下げる効果が期待できます。
（もちろんOS上のセキュリティの確保は必須です）
無効化するには以下のオプションを設定します。

vi /etc/my.cnf
[mysqld]
skip-networking

バイナリログの利用
バイナリログとはデータベースの中身を変更する捜査を行った際の操作履歴を記録したもので、バックアップから最新状態へロールフォワードリカバリに使用したりできます。
有効化するには以下のオプションを指定します。

vi /etc/my.cnf
[mysqld]
log_bin=mysql-bin

有効にするとdataディレクトリにmysql-bin.indexやmysql-bin.000001といったファイルができます。
mysql-bin.indexはバイナリログインデックスというもので、バイナリログファイルの一覧が格納されています。
バイナリログには連番の名前がつけられていて、ローテーションのたびに数字が増えていきます。
バイナリログへの書き込みはデフォルトの設定ではディスクと同期されていません。
例えば更新処理中にクラッシュしてしまった場合、ディスクへの更新が済んでいない更新に関してはバイナリログに記録されていないという状態になる可能性があります。
これは、テーブルには最新データが格納されているが、バイナリログには最新データの更新は反映されていないという状態です。
この状態を許容できないようなシステム（基幹系やお金を扱うシステム）では、sync_binlog=1オプションを利用します。
このオプションは指定した数字の回数に更新ごとにディスクへの更新を行うというものです。上記の設定では、バイナリログの更新を行うごとにディスクへの同期が行われる設定になります。
もちろんこの設定は更新処理の性能低下につながるので、システム要件として絶対にバイナリログのデータを失わせたくない場合に使用しましょう。

バイナリログはMySQL Serverを利用しているとどんどん増えていきます。
いずれディスク容量を圧迫することになりかねないので、古いログファイルを自動的に削除するオプションを利用しましょう。

expire_logs_days = 14

このように指定すると14日以上前にローテーションされたバイナリログファイルが自動的に削除されます。

メモリのサイズ設定
MySQLサーバの設定で大きくパフォーマンスを左右するのが各種リソースに割り当てるメモリ設定です。
バッファに割り当てるメモリが少なすぎるとリソースの有効活用ができないため、処理の高速化は期待できません。
逆にバッファにメモリを割り当てすぎるとスワップが発生してしまいます。かなりレスポンスが悪くなるためスワップは起こさないように注意しましょう。
MySQL Serverのメモリの使用量は以下のようになります。

全体バッファ(※innodb_buffer_pool_size等） +  ( mac_connections * (セッション毎のバッファの平均値 + スタック)

セッション毎のバッファには以下のようなものが存在します。

sort_buffer_size ファイルソートで割り当て
read_buffer_size テーブルスキャン時に割り当て
read_rnd_buffer_size インデックスを用いたソート時に割り当て
join_buffer_size インデックスを用いないJOIN時に割り当て
tmp_table_size テンポラリテーブルが必要なクエリ実行時に割り当て

セッション毎のバッファ平均値は、アプリケーションのクエリのパターンによるので確実な計算はできません。
初期の見積もりとしては各セッション毎のバッファの値を合計したものの3割〜5割程度+テンポラリテーブルの1割程度の値を利用するとよいでしょう。
上記のセッション毎のバッファーサイズがtmp_table_sizeが16M、他が全て1Mに設定されていた場合、以下のような計算式になります。

(1+1+1+1) * 0.5 + 16 / 10 = 3.6MB

1つのセッションで4MB弱が利用される計算になります。この数字を最初の式にいれていきます。
max_connectionsはMySQLに接続する最大のコネクション数です。今回はこの数値が1000に設定されていたとします。

1000 * 4M = 4GB

8GBのマシンを利用していた場合、OSが利用するメモリが1GB、上記のセッション処理で必要な最大数4GBをひいて、3GBが全体バッファとして利用できるメモリになります。

この計算はあくまで見積もり初期値なので、負荷試験を行い実際にどれだけメモリを使用しているかを計り調整を行いましょう。