MySQL5.7は、各種設定のデフォルト値がマイナーバージョンで異なることがよくあります。例えば、MySQL5.7.4から5.7.10までは、パスワードの有効期限のデフォルト値が360日でしたが、MySQL5.7.11以降は無期限に変更されました。そこで今回は、現時点（2017年2月15日）の最新版 MySQL5.7.17 の各種設定をメモしておきました。
MySQL 5.7 のインストール
サーバOSは、CentOS7.3 (1611) です。MySQLは公式のyumリポジトリからインストールします。
MySQL公式のyumリポジトリの追加
MySQL5.7のインストール（2017年2月15日時点のバージョンは 5.7.17）
yum -y install mysql-server
MySQL 5.7 の設定
ログの出力先を作成
chown -R mysql:mysql /var/log/mysql
念のためオリジナルの設定ファイルをバックアップしておきます。
設定ファイルを作成します。
vim /etc/my.cnf
# データディレクトリ
datadir=/var/lib/mysql
# テンポラリディレクトリ
tmpdir=/tmp
# ポート番号
port=3306
# UNIXドメインソケット
socket=/var/lib/mysql/mysql.sock
# シンボリックリンクを使わない
symbolic-links=0
# timestamp型カラムに暗黙的なデフォルト値を設定しない
explicit_defaults_for_timestamp=TRUE
# SQLモード
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# サーバ側が使用するデフォルトの文字コード
character-set-server=utf8
# クライアント側の文字コードの設定を無視する
skip-character-set-client-handshake
# 最大同時接続数
max_connections=300
# デフォルトのストレージエンジンの指定
default_storage_engine=InnoDB
# 保存するファイル名の変換モードの設定 InnoDBを使用する場合は1を設定
lower_case_table_names=1
# バッファプールサイズの指定
innodb_buffer_pool_size=128MB
# ファイルシステムキャッシュへのバッファリングを防ぐ
innodb_flush_method=O_DIRECT
# バッファプールをダンプするページの割合の指定（単位：%）
innodb_buffer_pool_dump_pct=25
# NUMAシステムで問題となる、メモリ割り当ての偏りを解消するための設定
innodb_numa_interleave=ON
# 各MySQLサーバを識別するためのIDの設定
server-id=1
# バイナリログの有効化
log_bin=mysql-bin
# バイナリログのフォーマットの指定
binlog-format=ROW
# バイナリログを保存しておく日数
expire_logs_days=14
# スロークエリログの有効化
slow_query_log=1
# 何秒以上かかったクエリをスロークエリログへ記録するかを指定（単位：秒）
long_query_time=1.0
# この値より少ない行数を読み込んだクエリはスロークエリログへ記録しない
min_examined_row_limit=0
# スロークエリログファイルの出力先の指定
slow_query_log_file=/var/log/mysql/slow_query.log
# エラーログの出力先の指定
log-error=/var/log/mysql/mysqld.log
# エラーログのタイムゾーンの指定
log_timestamps=SYSTEM
# プロセスIDファイルの指定
pid-file=/var/run/mysqld/mysqld.pid
各種設定の解説
datadir=/var/lib/mysql
MySQLの各種データを保存するディレクトリを指定します。
tmpdir=/tmp
一時ファイルおよび一時テーブル用に使用するディレクトリを指定します。
port=3306
MySQLが使用する待受ポート番号を指定します。デフォルト値は「3306」です。
socket=/var/lib/mysql/mysql.sock
UNIXドメインソケットのパスを指定します。MySQLをyumなどRPMパッケージでインストールしている場合、デフォルト値は「/var/lib/mysql/mysql.sock」です。
symbolic-links=0
シンボリックリンクを使う場合は「1（YES）」使わない場合は「0（DISABLED）」を指定します。デフォルト値は「1（YES）」です。ただ、シンボリックリンクは、MyISAMテーブル以外では、未知の問題が発生することがあるため、なにか特別な事情がない限りは「0（DISABLED）」を指定しておくのがよいでしょう。
また、symbolic-links のシステム変数名は symbolic-links ではなく「SHOW VARIABLES LIKE 'have_symlink'」で確認できます。
explicit_defaults_for_timestamp=TRUE
timestamp型カラムに暗黙的なデフォルト値を設定しない場合は「TRUE」、暗黙的なデフォルト値を設定する場合は「FALSE」を指定します。デフォルト値は「FALSE」ですが、将来的にtimestamp型カラムの暗黙的なデフォルト値は廃止される予定とのことなので、今のうちから「TRUE」を指定するのが良さそうです。ただし「TRUE」を設定する場合は、アプリケーション側のエンジニアとも相談した方がよいかと思います。
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
SQLモードの設定です。マイナーバージョンアップでころころデフォルト値が変わるので(^^;)なにかしら設定しておくことをオススメします。
character-set-server=utf8
サーバ側が使用するデフォルトの文字コードを指定します。デフォルト値は「latin1」です。日本語を使うのであれば「utf8」を指定しておきます。
skip-character-set-client-handshake
クライアント側の文字コードの設定を無視します。引数は必要ありません。この設定には賛否がありますが、これを指定しておいた方が文字コード関連のトラブルが少ないように思えます。
max_connections=300
MySQLサーバへの最大同時接続数を指定します。デフォルト値は「151」で、1から100000までの値を指定できます。ただし、この値はサーバで利用できるファイルディスクリプタ数によって削減されることがあります。実際に設定されている値を「SHOW VARIABLES LIKE 'max_connections'」で確認するのがよいでしょう。
default_storage_engine=InnoDB
デフォルトのストレージエンジンを指定します。デフォルト値は「InnoDB」です。
lower_case_table_names=1
テーブルやデータベースをファイルに保存するときに、ファイル名を小文字に変換するかどうかを設定します。デフォルト値はOSによって異なりCentOS7.3のデフォルト値は「0」です。「InnoDB」ストレージエンジンを使う場合は、「1」（小文字に変換）を指定する必要があります。
innodb_buffer_pool_size=128MB
InnoDBのバッファプールサイズを指定します。MySQL専用サーバの場合は物理メモリの70%を目安に、WEBサーバなどと相乗りしている場合は物理メモリの10%を目安に設定して様子をみるのが良いそうです。
また、innodb_buffer_pool_chunk_size と innodb_buffer_pool_instances がデフォルト値である場合、innodb_buffer_pool_size は、1GBまでは128MBの倍数、1GB以上は1GBの倍数に自動で設定されます。例えば「innodb_buffer_pool_size=300MB」と指定しても、「SHOW VARIABLES LIKE 'innodb_buffer_pool_size'」で確認すると、384MB(402653184バイト)=128MB×3に設定されます。（近い倍数の値ではなく切上げされるようです）
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 402653184 | +-------------------------+-----------+ 1 row in set (0.00 sec)
innodb_flush_method=O_DIRECT
InnoDBのバッファープールとOSのファイルシステムキャッシュに２重にバッファリングされることを防ぎます。
innodb_numa_interleave=ON
NUMAシステムで問題となる、メモリ割り当ての偏りを解消するための設定です。デフォルト値は「OFF」です。複数のCPUを搭載したサーバを使っている場合は「ON」にしておくのが良いでしょう。
innodb_buffer_pool_dump_pct=25
MySQL5.7.7からバッファプールの自動暖機運転（innodb_buffer_pool_load_at_startup と innodb_buffer_pool_dump_at_shutdown）のデフォルト値が「ON」になりました。innodb_buffer_pool_dump_pct でバッファプールをダンプするページの割合をパーセント単位で指定できます。デフォルト値は「25%」です。ちょうどいい割合を見つけたいですね。
server-id=1
レプリケーション構成時に、各MySQLサーバを識別するためのIDを指定します。今回レプリケーションは設定しませんが、バイナリログを有効にするためには、server-id を指定しておくことが必須のため設定しています。
log_bin=mysql-bin
バイナリログを有効にします。いわゆるトランザクションログですので、有効にしておくことをオススメします。
binlog-format=ROW
バイナリログのフォーマットを指定します。デフォルト値は「ROW」です。このパラメータもデフォルト値がよく変わるので、なにかしら設定しておくことをオススメします。
expire_logs_days=14
バイナリログを保存する日数を指定します。デフォルト値は「0（削除しない）」ですので、運用ポリシーに合わせて設定しておくのが良いかと思います。設定できる値は0〜99(日)です。
slow_query_log=ON
スロークエリログを有効にします。デフォルト値は「OFF」です。
long_query_time=1.0
何秒以上かかったクエリをスロークエリログへ記録するかを指定します。単位は秒ですがマイクロ秒の精度まで指定できます。デフォルト値は「10秒」です。
min_examined_row_limit=0
この値より少ない行数を読み込んだクエリは、たとえ long_query_time で指定した秒数以上の時間がかかっても、スロークエリログに記録されません。デフォルト値も「0」ですが、すべてのクエリをスロークエリ検査の対象としたいので、明示的に設定しています。
slow_query_log_file=/var/log/mysql/slow_query.log
スロークエリログファイルの出力先を指定します。指定しない場合は「datadir」で指定したディレクトリ以下に「<ホスト名>-slow.log」というファイル名で出力されます。
log-error=/var/log/mysql/mysqld.log
MySQL本体のエラーログの出力先を指定します。root ユーザの初期パスワードもこのファイルに出力されます。
log_timestamps=SYSTEM
エラーログに出力される時間のタイムゾーンを指定します。デフォルト値はなんと「UTC（協定世界時）」です(^^;)「SYSTEM」を指定することで、サーバに指定されているタイムゾーンを設定することができます。
pid-file=/var/run/mysqld/mysqld.pid
プロセスIDファイルのパスを指定します。
起動と自動起動設定
起動
自動起動設定
セキュリティ設定
・root の初期パスワードの確認
grep password /var/log/mysql/mysqld.log
mysql_secure_installation
対話式で設定が始まります。（不要なユーザやDBを削除してくれます）
Enter password for user root: ＜初期パスワード＞
The existing password for the user account root has expired. Please set a new password.
New password: ＜パスワード＞
Re-enter new password: ＜パスワード＞
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : ＜空エンター＞
（略）
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
（略）
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
（略）
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
（略）
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
おわりに
この記事の各種設定の解説はかなりざっくりしたものです(^^;) しっかりとMySQL5.7の新機能や変更点を理解したい方には、書籍「詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)
コメント