MySQLの設定値が戻る原因を調べる｜SET PERSISTとvariables_info

SET GLOBALで変更した値は、そのままでは再起動後に引き継がれません。ただ、戻り先が常にmy.cnfとは限りません。以前にSET PERSISTで保存した値や、起動オプションの値へ戻っていることもあります。

まずいま動いている値・永続化された値・現在の値の由来を並べて見ます。この記事では、その診断SQLと、MySQL 8.4.11で実際に再起動して確認した変化を紹介します。数値は動作を見分けるための例で、接続数の推奨値ではありません。

実行中の値と保存値を、1つのSQLで比べる

次はmax_connectionsを調べる読み取り用SQLです。global_variablesにある実行中の値へ、persisted_variablesの保存値とvariables_infoの由来を結合します。

SELECT
  g.VARIABLE_NAME,
  g.VARIABLE_VALUE AS running_value,
  p.VARIABLE_VALUE AS persisted_value,
  i.VARIABLE_SOURCE,
  i.VARIABLE_PATH,
  i.SET_TIME,
  i.SET_USER,
  i.SET_HOST
FROM performance_schema.global_variables AS g
JOIN performance_schema.variables_info AS i USING (VARIABLE_NAME)
LEFT JOIN performance_schema.persisted_variables AS p USING (VARIABLE_NAME)
WHERE g.VARIABLE_NAME = 'max_connections';

persisted_valueがNULLなら、少なくともこの変数は永続化テーブルにありません。「どこにも設定がない」という意味ではなく、通常の設定ファイルや起動オプションに値がある可能性は残ります。

例えば、実行値が210、保存値が200、由来がDYNAMICなら、いまは210で動いていても、保存されているのは200です。後ほどこの状態を実際に作り、再起動で200へ戻ることを確認します。

接続先も一緒に記録しておくと、別のDBサーバーを見ていたという取り違えを調べられます。

SELECT VERSION(), @@hostname, @@port, @@server_uuid;
SELECT @@GLOBAL.persisted_globals_load;

この診断はグローバル値を対象にしています。セッション値も持つ変数で「アプリから見ると違う」場合は、アプリと同じ接続で@@SESSION.変数名も比較します。接続時の初期設定や既存のコネクションプールを確認する入口です。スコープや基本構文は、MySQLのシステム変数とユーザー定義変数で扱っています。

DYNAMICは「実行中に変更された」という手がかり

VARIABLE_SOURCEは、変数の値がどこから設定されたかを表します。次のように読むと、次に確認する場所を絞れます。

由来	次に見るもの
DYNAMIC	実行中の変更。保存値との比較、SET_TIME・SET_USER・SET_HOST
PERSISTED	mysqld-auto.cnfから読み込まれた値。現在の保存値も別途比較する
COMMAND_LINE	サービスやコンテナの起動引数
GLOBAL・SERVER・EXPLICITなど	VARIABLE_PATHに出ている設定ファイル
COMPILED	組み込みの既定値

ここでのGLOBALは設定ファイルの分類です。SQLのSET GLOBALで変えた、という意味ではありません。

variables_infoは変更履歴の一覧ではありません。実行中に値を変更すると、由来はDYNAMICになり、元の設定ファイルのパスは空になります。「最初はどのファイルで設定されたか」をこの行だけで遡ることはできません。variables_infoの公式説明も、この更新のされ方を示しています。

そのため、DYNAMICだから永続化されていない、とも断定できません。次の実験ではSET PERSISTの直後もDYNAMICになります。保存の有無はpersisted_valueで確認します。

SET PERSISTとPERSIST_ONLYを、再起動して比べる

ここからの変更例は、アプリが接続していない検証用DBで試します。サーバー全体の接続上限を変更し、途中で再起動するためです。今回の検証用MySQLは、起動引数に--max-connections=161を指定しています。

最初の実行値は161、永続化した値はありません。次の順番で1つずつ実行し、各段階で最初の診断SQLを実行しました。

-- 1. いまの実行値だけを変更する
SET GLOBAL max_connections = 180;
-- 検証DBを再起動し、診断SQLで確認する

-- 2. 実行値と永続化した値を変更する
SET PERSIST max_connections = 190;
-- 検証DBを再起動し、診断SQLで確認する

-- 3. 永続化した値だけを変更する
SET PERSIST_ONLY max_connections = 200;
-- 検証DBを再起動し、診断SQLで確認する

確認した時点	実行値	保存値
最初の起動	161	なし
GLOBALで180へ変更	180	なし
その後の再起動	161	なし
PERSISTで190へ変更	190	190
その後の再起動	190	190
PERSIST_ONLYで200を保存	190	200
その後の再起動	200	200

SET PERSISTは、その場の実行値も変更します。一方、SET PERSIST_ONLYの直後は、保存値が200でも実行値は190のままでした。次の起動で使う値を用意している状態です。

SET PERSIST直後の由来はDYNAMICでしたが、再起動後にはPERSISTEDへ変わり、パスは/var/lib/mysql/mysqld-auto.cnfになりました。このパスは今回の検証環境の値です。

二つの構文の適用範囲や、永続化できない変数については、MySQL公式の永続化の説明も確認してください。ある変数で使えた構文が、すべての変数で使えるわけではありません。

RESET PERSISTは、いまの実行値までは戻さない

先ほどの続きで、保存値200を残したまま、実行値だけを210へ変えます。

SET GLOBAL max_connections = 210;
-- 診断SQL: running_value=210 / persisted_value=200
-- 検証DBを再起動すると、実行値は200へ戻る

この状態で設定ファイルばかり探しても、200の由来を見落とすことがあります。永続化テーブルの200が再び読み込まれているからです。

この変数の永続化を解除するなら、変数名を指定して実行します。

RESET PERSIST max_connections;

SELECT @@GLOBAL.max_connections;
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.persisted_variables
WHERE VARIABLE_NAME = 'max_connections';

検証では、保存行はなくなりましたが、実行値は200のままでした。由来もその場ではPERSISTEDのままです。再起動後、起動引数の161へ戻りました。RESET PERSISTは、現在の値を過去の値へ巻き戻す操作ではありません。

再起動せずに実行値も戻す場合は、事前に記録した値を別途SET GLOBALで指定します。今回の検証で161へ戻すなら、次の一文です。

-- 161はこの検証環境の起動値。実際には記録した復元先を使う
SET GLOBAL max_connections = 161;

SET PERSIST max_connections = DEFAULTは、保存行の削除にはなりません。既定値を保存する操作と、永続化を解除する操作は分けます。また、変数名のないRESET PERSISTは全体の保存設定を消すため、この1項目を戻す目的では使いません。

my.cnfを直しても変わらないときの確認先

まず、診断結果のVARIABLE_PATHを見ます。パスが空なら、起動コマンドや実行中の変更も含めて追います。設定ファイルの候補を調べるには、対象サーバーと同じmysqldのヘルプを使えます。

mysqld --verbose --help

ヘルプの冒頭付近に、探すファイルの順番と読み込むグループが出ます。今回のMySQL 8.4.11のコンテナでは、次のように表示されました。

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysql_cluster mysqld server mysqld-8.4

これは、そのコマンドで探す候補の一覧です。全ファイルが存在する、すべてが現在のプロセスに読み込まれた、という証拠ではありません。サービスの起動ユーザー、--defaults-fileなどの引数、!include・!includedirも確認します。別のMySQLや別ユーザーでヘルプを実行すると、実際の起動条件とずれることがあります。

通常の設定ファイルについては、公式のオプションファイルの説明に読み込み順が掲載されています。ただし、今回の実験のようにmysqld-auto.cnfの保存値が最後の実行値になる場合もあるため、my.cnfだけで結論を出さないことが大切です。

SQLで保存内容を調べるpersisted_variablesと、実行中のglobal_variablesは別の表です。mysqld-auto.cnfを通常の設定ファイルのつもりで手編集せず、保存・解除はSQLで行います。persisted_globals_loadが無効なら、保存してあっても起動時に読み込まれない点にも注意してください。

環境に合わせて、設定を残す場所を決める

原因が分かったら、毎回同じ値へ直す前に、今後どこで設定を管理するかを決めます。

環境	先に確認すること
自分で管理するサーバー	設定ファイルと永続化SQLのどちらを正本にするか。同じ変数を両方で管理していないか
Docker	起動引数・マウントした設定ファイルに加え、MySQLのデータディレクトリを次のコンテナへ引き継いでいるか
管理サービスや共用DB	使える権限と、サービス側の設定変更方法。SQLで変更できない場合の管理窓口

mysqld-auto.cnfはデータディレクトリに保存されます。同じDBを再起動することと、保存先を引き継がず新しいDBを作ることは違います。「コンテナを作り直したら消えた」場合は、値を再投入する前に、どのデータ保存先を使っているかを確認します。

権限エラーが出る場合も、構文の打ち直しだけでは解決しません。MySQL 8.4ではSET PERSISTにSYSTEM_VARIABLES_ADMINなどの権限が必要です。SET PERSIST_ONLYにはSYSTEM_VARIABLES_ADMINとPERSIST_RO_VARIABLES_ADMINが必要になります。今回も、権限を付けていない検証ユーザーではSET PERSISTがエラー1227で拒否されました。必要な操作に合わせて、公式の権限一覧を管理者と確認してください。

最後は「変更SQLが成功した」だけで終えず、診断SQLの結果を変更前・変更後・予定した再起動後に残します。実行値と保存値の差に理由があること、再起動後の由来が想定どおりであることまで確認できれば、同じ設定を何度も直す状況から抜け出しやすくなります。