CLOVER🍀

That was when it all began.

MySQL Server 8.0の設定を、set persist(またはset persist_only)で変更する

これは、なにをしたくて書いたもの?

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で制御できるようです。

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は他の設定ファイルを読み込んだ後に
読むことになるからですね。

persisted_globals_load

ファイルにまとめて変更する

こういう感じで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で値を変更するだけではなくて、永続化できるようになって便利ですね。これは覚えておきましょう。