0

 mac brew MySQL5.6を導入しながら、MySQLの運用当初に考えることを整理してみる。

MySQL Serverには運用を行う上で便利なコマンドや設定が存在します。
そこで今回はmacにmysql5.6を導入するケースで下記にそれらの紹介をしていきたいと思います。

brew install mysql後、brew infoで確認してみる。

$ brew info mysql
mysql: stable 5.6.24 (bottled)
https://dev.mysql.com/doc/refman/5.6/en/
Conflicts with: mariadb, mysql-cluster, mysql-connector-c, percona-server
/usr/local/Cellar/mysql/5.6.24 (9720 files, 340M) *
  Poured from bottle
From: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/mysql.rb
==> Dependencies
Build: cmake ✘
Required: openssl ✔
==> Options
--universal
	Build a universal binary
--with-archive-storage-engine
	Compile with the ARCHIVE storage engine enabled
--with-blackhole-storage-engine
	Compile with the BLACKHOLE storage engine enabled
--with-debug
	Build with debug support
--with-embedded
	Build the embedded server
--with-local-infile
	Build with local infile loading support
--with-memcached
	Enable innodb-memcached support
--with-tests
	Build with unit tests
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

To connect:
    mysql -uroot

To reload mysql after an upgrade:
    launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
    launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
Or, if you don't want/need launchctl, you can just run:
    mysql.server start

■mysql_secure_installationで簡単設定
– – –
*mysqlへの接続できないとエラーがでた場合

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

下記シンボリックを張ってください。

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

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

$ mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y   ←#rootパスワードを変更よいか?
New password:                  ←#rootパスワード入力
Re-enter new password:      ←もう一度入力
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y   ←#匿名ユーザを削除して良いか?
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y  ←#リモートからのrootログインを禁止してよいか?
 ... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y  ←#testデータベースを削除してよいか?
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y   ←#リロードしてよいか?
 ... Success!




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

Thanks for using MySQL!


Cleaning up...

■サービス(mysqld)自動起動も設定しておく

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

■多言語対応
homebrew からインストールした mysqlの言語周りの設定がどうなっているか

$ mysql -uほげ -pほげ -e"show variables like '%char%'";
Warning: Using a password on the command line interface can be insecure.
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | utf8                                                 |
| character_set_connection | utf8                                                 |
| character_set_database   | utf8                                                 |
| character_set_filesystem | binary                                               |
| character_set_results    | utf8                                                 |
| character_set_server     | utf8                                                 |
| character_set_system     | utf8                                                 |
| character_sets_dir       | /usr/local/Cellar/mysql/5.6.24/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+

初めから utf8 になっているようなので、設定を加えなくても日本語通りそうです。
もし、character_set_server が latin1 になっていた場合は(CentとかDebianで入れたらlatin1だった気がする…) 設定ファイル(my.cnf 等に)

$ mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

$ vi /usr/local/etc/my.cnf
・・・ 略 ・・・
[mysqld] <-- mysqld の箇所を探して
character-set-server = utf8 <-- この設定を加える
・・・ 略 ・・・

■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を利用しているとどんどん増えていきます。

いずれディスク容量を圧迫することになりかねないので、古いログファイルを自動的に削除するオプションを利用しましょう。
▼14日以上前にローテーションされたバイナリログファイルが自動的に削除

expire_logs_days = 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が全体バッファとして利用できるメモリになります。

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

以上です。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です