これは、なにをしたくて書いたもの?
MySQLでは、(可能なものについては)設定をset
で動的に変更することができます。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.9 システム変数の使用
MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.4 サーバーオプション、システム変数およびステータス変数リファレンス
ただ、set
で変更した内容はMySQLサーバーを再起動するともとに戻ってしまいます。
MySQL 8.0では、この状況が変わっているようです。
第94回 SET PERSISTを使ってシステム変数を永続化させる:MySQL道普請便り|gihyo.jp … 技術評論社
日々の覚書: MySQL 8.0.0でSET PERSIST構文が出来て、my.cnfへの反映忘れが防げそう
MySQL 8.0 新機能 Persisting configuration variables - Qiita
set persist/set persist_only
MySQL 8.0からset persist
(set persist_only
)が追加され、設定ファイル(mysqld-auto.cnf
)へ保存できるようになったようです。
以降、このことを「永続化」と呼ぶことにします。
MySQL now supports a SET PERSIST variant of SET statement syntax, for making configuration changes at runtime that also persist across server restarts. Like SET GLOBAL, SET PERSIST is permitted for any global system variable that is dynamic (settable at runtime). The statement changes the runtime variable value, but also writes the variable setting to an option file named mysqld-auto.cnf in the data directory. At startup, the server processes this file after all other option files.
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.0 (2016-09-12, Development Milestone)
MySQL now supports a SET PERSIST_ONLY variant of SET statement syntax, for making configuration changes at runtime that also persist across server restarts. Like SET PERSIST, SET PERSIST_ONLY writes the variable setting to an option file named mysqld-auto.cnf in the data directory. However, unlike PERSIST, PERSIST_ONLY does not modify the runtime global system variable value. This makes PERSIST_ONLY suitable for configuring read-only system variables that only be set can at server startup.
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.2 (2017-07-17, Development Milestone)
set persist
は動的に変更可能な設定を、設定変更とともに設定ファイル(mysqld-auto.cnf
)へ永続化します。
set persist_only
は、動的に変更できない設定を設定ファイル(mysqld-auto.cnf
)へ永続化するもので、次回の再起動時に
反映されるというものです。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.9.3 永続化されるシステム変数
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.6.1 変数代入の SET 構文
対象のスコープは、グローバルなものとなります。
動的に変更できるかどうかは、このあたりを見ると確認できるでしょう。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.4 サーバーオプション、システム変数およびステータス変数リファレンス
MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.5 サーバーシステム変数リファレンス
MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.9.2 動的システム変数
set persist
で永続化した内容を削除するには、reset persist
を使用します。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.8.7 RESET PERSIST ステートメント
また、永続性のない設定もあるようです。log_bin
、hostname
など。たくさんあるので、以下を参照してください。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.9.4 永続的で永続的に制限されないシステム変数
同じページに、読み取り専用であっても永続化できないものも書かれています。
権限
set persist
、set persist_only
、reset persist
の実行に必要な権限を、それぞれ調べてみます。
こちらに書かれていますね。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.9.1 システム変数権限
set persist
SYSTEM_VARIABLES_ADMIN
またはSUPER
set persist_only
SYSTEM_VARIABLES_ADMIN
およびPERSIST_RO_VARIABLES_ADMIN
reset persist
- 動的に変更できる設定の場合 …
SYSTEM_VARIABLES_ADMIN
またはSUPER
- 読み取り専用の設定の場合 …
SYSTEM_VARIABLES_ADMIN
およびPERSIST_RO_VARIABLES_ADMIN
- 動的に変更できる設定の場合 …
永続化の機能を無効にする
永続化した設定ファイルを読み込むかどうかは、persisted_globals_load
で制御できるようです。
あと、重要そうなことがさらっと書かれていますね。
永続化された構成設定をデータディレクトリの mysqld-auto.cnf ファイルからロードするかどうか。 サーバーは通常、このファイルを起動時にほかのすべてのオプションファイルのあとに処理します (セクション4.2.2.2「オプションファイルの使用」 を参照)。
ということは、永続化した設定の優先度が高そうですね?あとで確認してみましょう。
では、情報を見るのはこれくらいにして実際に試してみましょう。
環境
今回の環境は、こちらです。
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.24 | +-----------+ 1 row in set (0.00 sec)
データディレクトリは、/var/lib/mysql
となっています。
また、操作はroot
アカウントで行っているものとします。
set persistを試す
まずは、set persist
から試してみましょう。
innodb_buffer_pool_size
を使いましょう。現在値を確認。
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+ 1 row in set (0.01 sec)
デフォルトの128MBです。
これを、1GBに変更してみましょう。最初は、set global
で永続化なしで変更します。
mysql> set global innodb_buffer_pool_size = 1073741824; Query OK, 0 rows affected (0.00 sec)
変更されました。
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 1073741824 | +-------------------------+------------+ 1 row in set (0.00 sec)
ここで、MySQLサーバーを再起動してもう1度確認してみます。
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+ 1 row in set (0.00 sec)
元に戻りました。では、set persist
で変更してみましょう。
mysql> set persist innodb_buffer_pool_size = 1073741824; Query OK, 0 rows affected (0.00 sec)
今度は、MySQLサーバーを再起動しても元に戻りません。
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 1073741824 | +-------------------------+------------+ 1 row in set (0.00 sec)
もうひとつ、max_connections
も変更してみましょう。
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.01 sec) mysql> set persist max_connections = 300; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 300 | +-----------------+-------+ 1 row in set (0.00 sec)
データディレクトリ内には、mysqld-auto.cnf
というファイルができ、ここまでset persist
で永続化した内容がJSON形式で
保存されています。
/var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "300" , "Metadata" : { "Timestamp" : 1619882895525080 , "User" : "root" , "Host" : "host01" } } , "mysql_server_static_options" : { "innodb_buffer_pool_size" : { "Value" : "1073741824" , "Metadata" : { "Timestamp" : 1619882888860561 , "User" : "root" , "Host" : "host01" } } } } }
続いて、動的に設定できない項目にも試してみましょう。skip_name_resolve
でやってみます。
mysql> set persist skip_name_resolve = on; ERROR 1238 (HY000): Variable 'skip_name_resolve' is a read only variable
失敗しますね。
set persist_only
というわけで、今度はset persist_only
を使います。
まずは、先ほど変更できなかったskip_name_resolve
の値を確認。
mysql> show variables like 'skip_name_resolve'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | skip_name_resolve | OFF | +-------------------+-------+ 1 row in set (0.00 sec)
set persist_only
で変更してみます。
mysql> set persist_only skip_name_resolve = on; Query OK, 0 rows affected (0.00 sec)
今度は、OKになりました。
ですが、現在の値そのものは変わっていません。
mysql> show variables like 'skip_name_resolve'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | skip_name_resolve | OFF | +-------------------+-------+ 1 row in set (0.00 sec)
1度MySQLを再起動すると、反映されます。
mysql> show variables like 'skip_name_resolve'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | skip_name_resolve | ON | +-------------------+-------+ 1 row in set (0.00 sec)
mysqld-auto.cnf
にも、値が反映されましたね。
/var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "300" , "Metadata" : { "Timestamp" : 1619882895525080 , "User" : "root" , "Host" : "host01" } } , "mysql_server_static_options" : { "innodb_buffer_pool_size" : { "Value" : "1073741824" , "Metadata" : { "Timestamp" : 1619882888860561 , "User" : "root" , "Host" : "host01" } } , "skip_name_resolve" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1619882940640701 , "User" : "root" , "Host" : "host01" } } } } }
reset persist
では、設定した内容を削除してみましょう。reset persist
を使います。
reset persist
では、set persist
で永続化したもの、set persist_only
で永続化したものを問わず削除することができます。
先ほど設定したmax_connections
を
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 300 | +-----------------+-------+ 1 row in set (0.01 sec)
リセットしてみます。
mysql> reset persist max_connections; Query OK, 0 rows affected (0.00 sec)
成功しました。
ただ、現在の値は変わっていません。
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 300 | +-----------------+-------+ 1 row in set (0.00 sec)
mysqld-auto.cnf
からは、値がなくなりましたね。
/var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "mysql_server_static_options" : { "innodb_buffer_pool_size" : { "Value" : "1073741824" , "Metadata" : { "Timestamp" : 1619882888860561 , "User" : "root" , "Host" : "host01" } } , "skip_name_resolve" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1619882940640701 , "User" : "root" , "Host" : "host01" } } } } }
なので、MySQLサーバーを再起動すると反映されることになります。
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.00 sec)
もしくはreset persist
したうえで、set global
でデフォルト値に戻すなどですね。動的に変更できるものに限定されますが。
reset persist
は、設定項目名を指定しない場合は、永続化した項目すべてを削除します。
mysql> reset persist; Query OK, 0 rows affected (0.00 sec)
`mysqld-auto.cnf``から設定値がなくなりました。
/var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { } }
もちろん、単一の設定項目を指定した場合と動きは変わらないので、設定を反映したければ再起動なりset global
するなり
行う必要があります。
使い方は、だいたいわかった感じですね。
設定ファイルと永続化の両方を使った場合は?
なんとなく結果はわかっている気がしますが、気になるところではあるので。
設定ファイルと永続化の両方で設定をした場合にどうなるかを確認してみます。
たとえば、MySQLの設定ファイルでinnodb_buffer_pool_size
を1Gに設定してみます。
[mysqld] ... innodb_buffer_pool_size = 2G
MySQLサーバーを再起動して反映された、現在の値です。
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 2147483648 | +-------------------------+------------+ 1 row in set (0.00 sec)
ここで、set persist
で1Gに変更してみます。
mysql> set persist innodb_buffer_pool_size = 1073741824; Query OK, 0 rows affected (0.00 sec)
変更後の値。
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 1073741824 | +-------------------------+------------+ 1 row in set (0.01 sec)
mysqld-auto.cnf
にも反映されました。
/var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "mysql_server_static_options" : { "innodb_buffer_pool_size" : { "Value" : "1073741824" , "Metadata" : { "Timestamp" : 1619884781853180 , "User" : "root" , "Host" : "host01" } } } } }
ここで、MySQLサーバーを再起動してみます。
結果は、こうなりました。
mysql> show variables like 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 1073741824 | +-------------------------+------------+ 1 row in set (0.00 sec)
set persist
の内容になっています。
これは、persisted_globals_load
の説明にもありましたが、mysqld-auto.cnf
は他の設定ファイルを読み込んだ後に
読むことになるからですね。
ファイルにまとめて変更する
こういう感じでset persist
またはset persist_only
で変更できるのなら、ファイルに保存してから実行する方が、管理面などで
便利かもですね。特に、設定ファイルが直接触れない環境の場合など。
set_persit_variables.sql
set persist innodb_buffer_pool_size = 1073741824; set persist max_connections = 300; set persist_only max_allowed_packet = 134217728;
こんな感じで実行。
$ mysql -uroot -p < set_persit_variables.sql
まとめ
MySQL 8.0から使えるようになった、set persist
、set persist_only
、そしてreset persist
を試してみました。
set
で値を変更するだけではなくて、永続化できるようになって便利ですね。これは覚えておきましょう。