MySQL道普請便り

第31回 MySQLのオプションファイル my.cnfの豆知識[その1]

この記事を読むのに必要な時間:およそ 4 分

MySQLのオプションファイル

オプションファイルとはその名の通りMySQL関連プログラムに渡すオプションを列挙したもので,Linuxプラットフォームでは/etc/my.cnfが有名です。Windowsプラットフォームではmy.iniというファイル名も利用されますが,今回はCentOS 6.6のyum版MySQL 5.6の構成を例に説明します("yum版"の意図するところについては,第10回 yum, rpmインストールにおけるMySQL 5.6とMySQL 5.7の違いを参照ください)。

デフォルトでオプションファイルが読み込まれる順番

リファレンスマニュアルにも記載がありますが,デフォルトで読み込まれるオプションファイルは複数存在し,「読み込まれる順番」があります。 同じオプションを複数のオプションファイルで指定した場合は「後から読み込まれたファイル」の設定が優先されます(番号の順番にオプションファイルが読み込まれるため,番号が大きいほどオプションの優先順位が高くなります)。

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. SYSCONFDIR/my.cnf
  4. $MYSQL_HOME/my.cnf
  5. --defaults-extra-file=path
  6. ~/.my.cnf
  7. ~/.mylogin.cnf

SYSCONFDIRはパッケージのコンパイル時に指定されるオプションです。mysql-community-server-5.6.34-2.el6.x86_64では/usr/etcがSYSCONFDIRに指定されています。$MYSQL_HOMEは環境変数$MYSQL_HOMEによって決まり,ユーザが明示的に$MYSQL_HOMEを指定しなかった場合,/usr/bin/mysqld_safeが自動的に/usr$MYSQL_HOMEに設定します。--defaults-extra-fileオプションドキュメントによって追加のコンフィグファイルを指定している場合は,この後に読み込まれます。

オプションファイルが読み込まれる順番とパラメーターの関係

yum版では作成されませんが,MySQL 5.6のrpm版やLinux Genericバイナリ版はmysql_install_dbが$MYSQL_HOME/my.cnfにあたる/usr/my.cnfを自動で作成します。

$MYSQL_HOME/my.cnfは,最もよく利用されるオプションファイルである/etc/my.cnfよりも後から読み込まれます。これにより何が起こるかというと,たとえば以下のようにそれぞれのオプションファイルで同じオプションに異なる値を設定した場合,

$ vim /etc/my.cnf
..
[mysqld]
sql_mode= NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,PIPES_AS_CONCAT
..

$ vim /usr/my.cnf
[mysqld]
sql_mode= NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

採用されるオプションの値は後から読み込まれたオプションファイルのものになります。

mysql> SELECT @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

なお,同じオプションファイルの中に同じオプションに異なる値を設定した場合でも,

$ vim /usr/my.cnf
[mysqld]
sql_mode= NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode= ANSI_QUOTES
sql_mode= NO_ZERO_DATE

$ sudo service mysqld restart

mysql> SELECT @@sql_mode;
+--------------+
| @@sql_mode   |
+--------------+
| NO_ZERO_DATE |
+--------------+
1 row in set (0.00 sec)

有効になるのは最後に読み込まれたオプションです。

ただし,ignore-db-dirreplicate-do-tableなどのように,複数回指定することで全てが有効になるオプションもあります。

オプションファイルのインクルード

さらに,オプションファイルの中では!includeディレクティブや!includedirディレクティブを使用することで,デフォルトで読み込まれる以外のパスにあるオプションファイルをインクルードすることができます。この場合でも有効になるのは最後に読み込まれたオプションですが,さてそれでは以下のような/etc/my.cnf/etc/included_my.cnfを設定した場合/usr/my.cnfについては削除してあります),最終的に読み込まれるsql_modeは何になるでしょうか?

$ vim /etc/my.cnf
..
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
!include /etc/included_my.cnf
sql_mode= ANSI_QUOTES
..

$ vim /etc/included_my.cnf
[mysqld]
sql_mode= STRICT_ALL_TABLES

正解はANSI_QUOTESです。sql_mode= NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES/etc/my.cnfから読み取ったあと,!includeディレクティブにより/etc/included_my.cnfsql_mode=STRICT_ALL_TABLESを読み取ってから!includeディレクティブに戻り,その次のsql_mode= ANSI_QUOTESを読むからです。

仕組みを知ってしまえば大したことではありませんので,想像を絶するようなインクルードだらけのオプションファイルに出会った時も,丁寧に読み解いてあげてください。

オプションファイルの読み込みに関する3つのオプション

MySQLの標準の動作は「複数のオプションファイルを順番に読み込む」ですが,この動作を変更するためのオプションも用意されています。

オプション名効果
--no-defaultsオプションファイルを一切読み込まない
--defaults-file指定したオプションファイルのみを読み込む
--defaults-extra-fileデフォルトで読み込むオプションファイルに加え指定したオプションファイルを読み込む

これらのオプションは「オプションファイルの読み込みに関する動作を変更(オーバーライド)する」ためのものですので,オプションファイルの中に直接書き込むことはできません[ERROR] unknown variableとなります)。mysqld_safemysqldを直接呼び出している箇所に引数として与えることで効果を発揮します。これら3つのオプションは1つめの引数として与えなければなりません。オプションファイルを読み込まずポート番号だけ13306にしたいような場合,mysqld --port=13306 --no-defaultsは誤りで,mysqld --no-defaults --port=13306としなければなりません。

MySQL 8.0での新機能

2016年10月現在のDMR(DMRについては第8回 MySQLのバージョン体系を知るを参照)であるMySQL 8.0.0ではperformance_schema.variables_infoテーブルが追加されており,パフォーマンススキーマを用いたクエリーでそのパラメーターがどのオプションファイルから読み取られたものかを確認することができるようになっています。

mysql80> SELECT * FROM performance_schema.variables_info WHERE variable_source <> 'COMPILED';
+--------------------------+-----------------+-------------------------+-----------+----------------------+
| VARIABLE_NAME            | VARIABLE_SOURCE | VARIABLE_PATH           | MIN_VALUE | MAX_VALUE            |
+--------------------------+-----------------+-------------------------+-----------+----------------------+
| basedir                  | COMMAND_LINE    |                         | 0         | 0                    |
| binlog_checksum          | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| datadir                  | COMMAND_LINE    |                         | 0         | 0                    |
| foreign_key_checks       | DYNAMIC         |                         | 0         | 0                    |
| general_log_file         | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| innodb_fast_shutdown     | SERVER          | /usr/mysql/8.0.0/my.cnf | 0         | 2                    |
| innodb_file_per_table    | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| innodb_lock_wait_timeout | GLOBAL          | /etc/my.cnf             | 1         | 1073741824           |
| innodb_log_file_size     | GLOBAL          | /etc/my.cnf             | 4194304   | 18446744073709551615 |
| log_error                | COMMAND_LINE    |                         | 0         | 0                    |
| log_output               | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| log_slave_updates        | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| long_query_time          | GLOBAL          | /etc/my.cnf             | 0         | 4719230590769954816  |
| lower_case_table_names   | GLOBAL          | /etc/my.cnf             | 0         | 2                    |
| performance_schema       | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| pid_file                 | COMMAND_LINE    |                         | 0         | 0                    |
| plugin_dir               | COMMAND_LINE    |                         | 0         | 0                    |
| port                     | COMMAND_LINE    |                         | 0         | 65535                |
| query_cache_size         | GLOBAL          | /etc/my.cnf             | 0         | 18446744073709551615 |
| query_cache_type         | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| relay_log                | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| relay_log_recovery       | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| server_id                | COMMAND_LINE    |                         | 0         | 4294967295           |
| skip_name_resolve        | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| slow_query_log           | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| slow_query_log_file      | GLOBAL          | /etc/my.cnf             | 0         | 0                    |
| socket                   | COMMAND_LINE    |                         | 0         | 0                    |
+--------------------------+-----------------+-------------------------+-----------+----------------------+
27 rows in set (0.00 sec)

この機能は現在!includeディレクティブには対応していませんが!includeディレクティブを記述したオプションファイルが表示されてしまいます),今後が楽しみな機能のひとつです。

著者プロフィール

yoku0825

GMOメディア株式会社のDBA。日本MySQLユーザ会員。主に地雷担当。主食はMySQLだがPercona Server,MariaDB,InfiniDBにMroongaストレージエンジンなど,mysqldでありさえすれば雑食。

Twitter:@yoku0825

コメント

コメントの記入