MySQL道普請便り

第94回 SET PERSISTを使ってシステム変数を永続化させる

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

MySQL 5.7以前まではDynamic(動的)なシステム変数をSET構文で変更するときに,変更内容を永続化する場合,再起動しても反映されるようにするために,my.cnfに別途手動で記述する必要がありました。MySQL 8.0からはSET構文にPERSISTが追加され,SET PERSISTで追加したシステム変数は,再起動後も反映されるようになりました。

今回は,そんなSET PERSISTについて確認していきたいと思います。なお,MySQLのバージョンはMySQL 8.0.15,OSはCentOS 7を利用しています。

SET PERSISTを利用してみる

SET PERSISTを使った構文はSET GLOBALを使用するときと同じようにSET PERSIST <システム変数名> またはSET @@PERSIST.<システム変数名> と記述します。通常,SET GLOBALを利用すると,サーバーが終了した場合には変更したシステム変数は元の設定に戻ってしまいますが,SET PERSISTを利用するとリセットされず,変更された状態を保持します。変更した値は,データディレクトリの中のmysqld-auto.cnfというファイルにJSON形式で保持されます。

max_connectionsを変更した例

mysql> SET PERSIST max_connections=200;
Query OK, 0 rows affected (0.00 sec)


$ cat mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "200" , "Metadata" : { "Timestamp" : 1554207960111930 , "User" : "root" , "Host" : "localhost" } } } })

もし,すでにSET PERSISTで修正した値を再度設定した場合は,JSONファイルに追記されるのではなく,すでに記述されているシステム変数の値部分を修正します。

SET PERSIST_ONLY

SET PERSIST_ONLYはSET PERSISTと同様にmysqld-auto.cnfに書き出しますが,MySQLサーバーへの即座の反映は行われません。そのため,これを利用して変更したシステム変数は,次にMySQLを再起動したときに反映されます。

mysql>SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

mysql>SET PERSIST_ONLY max_connections=200;
Query OK, 0 rows affected (0.00 sec)

mysql>SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

$ cat mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "200" , "Metadata" : { "Timestamp" : 1554238282431564 , "User" : "root" , "Host" : "localhost" } } } }
ステートメント即時反映再起動後
SET GLOBALされるもとに戻る
SET PERSISTされるmysqld-auto.cnfから読み込み
SET PERSIST_ONLYされないmysqld-auto.cnfから読み込み

RESET PERSIST

RESET PERSISTはSET PERSIST,PERSIST_ONLYによって記述されたmysqld-auto.cnf内のシステム変数を削除します。これにより,mysqld-auto.cnfに記述されていたすべてのシステム変数は,MySQLを再起動時には反映されなくなります。先ほど設定したmax_connections=200が削除されているか確認してみましょう。

mysql> RESET PERSIST;
Query OK, 0 rows affected (0.00 sec)

$ cat mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : {  } }

設定されていたシステム変数が削除されていることが確認できました。

なお,RESET PERSISTは起動中のMySQLに設定されていたシステム変数には影響を受けず,あくまでmysqld-auto.cnfの中身を書き換えます。また,特定のシステム変数のみmysqld-auto.cnfから削除したい場合は,RESET PERSIST <システム変数名>を実行します。

mysql> RESET PERSIST max_connections;
Query OK, 0 rows affected (0.00 sec)

起動時にmysqld-auto.cnfを無視する

SET PERSISTで変更した値はmysqld-auto.cnfに記述され,MySQLを再起動時にはmy.cnfやdefaults-extra-fileを読み込んだあとにmysqld-auto.cnfを読み込みます。第31回 MySQLのオプションファイル my.cnfの豆知識[その1]「デフォルトでオプションファイルが読み込まれる順番」にも記述している通り,⁠後から読み込まれたファイル」が優先されるため,起動時はmysqld-auto.cnfで設定しているシステム変数が反映されます。

もし,mysqld-auto.cnfが正常に読み込めない場合やmysqld-auto.cnfに設定している値をすべて無視したい場合は,起動時に--persisted-globals-load=OFFをつけて起動することで,mysqld-auto.cnfを読まずに起動することができます。

詳細については5.1.8 Server System Variablesのpersisted_globals_loadをご確認ください。

SET PERSISTで設定した値の確認

SET PERSIST,SET PERSIST_ONLYで記述した値はmysqld-auto.cnfファイルで確認することができますが,performance_schemaのvariable_infoとpersisted_variablesでも確認することができます。

persisted_variablesには現在mysqld-auto.cnfが表示され,variables_infoは現在設定されているシステム変数がmy.cnfまたはmysqld-auto.cnfから読み込んだのか,またはSET GLOBALやSET PERSISTで設定されたのかを確認することができます。

> SELECT * FROM performance_schema.persisted_variables WHERE VARIABLE_NAME like '%max_connection%';
+------------------------+----------------+
| VARIABLE_NAME          | VARIABLE_VALUE |
+------------------------+----------------+
| max_connections        | 205            |
| mysqlx_max_connections | 500            |
+------------------------+----------------+

特に,variables_infoには現在のシステム変数のパスが設定されているため,MySQL起動時はmysqld-auto.cnfの設定を読み込んだ場合でも,SET GLOBALやSET PERSISTで値を変更した場合には,VARIABLE_SOURCEがPERSISTEDからDYNAMICに変更されます。

mysql> SELECT * FROM performance_schema.variables_info WHERE VARIABLE_NAME like '%max_connection%';
+------------------------+-----------------+----------------------------+-----------+-----------+----------------------------+----------+-----------+
| VARIABLE_NAME          | VARIABLE_SOURCE | VARIABLE_PATH              | MIN_VALUE | MAX_VALUE | SET_TIME                   | SET_USER | SET_HOST  |
+------------------------+-----------------+----------------------------+-----------+-----------+----------------------------+----------+-----------+
| max_connections        | PERSISTED       | /var/lib/mysql/mysqld-auto.cnf | 1         | 100000    | 2019-04-03 06:59:05.825847 | root     | localhost |
| mysqlx_max_connections | PERSISTED       | /var/lib/mysql/mysqld-auto.cnf | 1         | 65535     | 2019-04-03 07:16:30.156151 | root     | localhost |
+------------------------+-----------------+----------------------------+-----------+-----------+----------------------------+----------+-----------+
2 rows in set (0.01 sec)

mysql> SET PERSIST max_connections=100;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM performance_schema.variables_info WHERE VARIABLE_NAME like '%max_connection%';
+------------------------+-----------------+----------------------------+-----------+-----------+----------------------------+----------+-----------+
| VARIABLE_NAME          | VARIABLE_SOURCE | VARIABLE_PATH              | MIN_VALUE | MAX_VALUE | SET_TIME                   | SET_USER | SET_HOST  |
+------------------------+-----------------+----------------------------+-----------+-----------+----------------------------+----------+-----------+
| max_connections        | DYNAMIC         |                            | 1         | 100000    | 2019-04-03 07:32:06.764444 | root     | localhost |
| mysqlx_max_connections | PERSISTED       | /var/lib/mysql/mysqld-auto.cnf | 1         | 65535     | 2019-04-03 07:16:30.156151 | root     | localhost |
+------------------------+-----------------+----------------------------+-----------+-----------+----------------------------+----------+-----------+
2 rows in set (0.00 sec)

そのため,mysqld-auto.cnfを中心として運用する場合に,もしSET GLOBALで実行されたことによってmysqld-auto.cnfに記述がない場合は,下記のようなクエリで検索することができます。

> SELECT VARIABLE_NAME, VARIABLE_SOURCE, performance_schema.persisted_variables.VARIABLE_VALUE as p_value, performance_schema.global_variables.VARIABLE_VALUE as g_value FROM performance_schema.variables_info INNER JOIN performance_schema.persisted_variables USING(VARIABLE_NAME) INNER JOIN performance_schema.global_variables USING(VARIABLE_NAME) WHERE VARIABLE_SOURCE = 'DYNAMIC' AND performance_schema.persisted_variables.VARIABLE_VALUE != performance_schema.global_variables.VARIABLE_VALUE;
+-----------------+-----------------+---------+---------+
| VARIABLE_NAME   | VARIABLE_SOURCE | p_value | g_value |
+-----------------+-----------------+---------+---------+
| max_connections | DYNAMIC         | 100     | 300     |
+-----------------+-----------------+---------+---------+
1 row in set (0.01 sec)

まとめ

今回はSET PERSISTについて紹介しました。SET PERSIST,SET PERSIST_ONLYを利用することで,my.cnfを修正することなくmysqlクライアントで完結することができますが,my.cnfとmysqld-auto.cnfにシステム変数が記述されることになるため,記述場所が分散したり,my.cnfに設定した値が反映されない等が起こるかもしれないので注意が必要です。

著者プロフィール

深町日出海(ふかまちひでみ)

GMOメディア株式会社のデータベースエンジニア。主にOracleとMySQLを担当。得意なプログラム言語はJava。MySQLの好きなところはTABLEやINDEXの識別子に64byteまで使えるところ。

Twitter:@lhfukamachi