【MySQL】設定値を一覧・検索する

SHOW VARIABLESで設定値を一覧・検索する

MySQLの全設定値を一覧するにはSHOW VARIABLES文を実行する。

SHOW VARIABLES;

設定項目数は600件を超え(バージョンによって異なる)、Variable_nameとValueの2カラムのみが表示される。

個別の設定値だけを確認する場合は、LIKE句に設定名を指定する。

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

LIKEにはワイルドカードを含むパターンも指定できる。
設定名の一部だけが分かっている場合は%で挟んで検索する。

mysql> SHOW VARIABLES LIKE '%timeout%';
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| connect_timeout                   | 10       |
| delayed_insert_timeout            | 300      |
| have_statement_timeout            | YES      |
| innodb_flush_log_at_timeout       | 1        |
| innodb_lock_wait_timeout          | 50       |
| innodb_rollback_on_timeout        | OFF      |
| interactive_timeout               | 28800    |
| lock_wait_timeout                 | 31536000 |
| mysqlx_connect_timeout            | 30       |
| mysqlx_idle_worker_thread_timeout | 60       |
| mysqlx_interactive_timeout        | 28800    |
| mysqlx_port_open_timeout          | 0        |
| mysqlx_read_timeout               | 30       |
| mysqlx_wait_timeout               | 28800    |
| mysqlx_write_timeout              | 60       |
| net_read_timeout                  | 30       |
| net_write_timeout                 | 60       |
・
・
・
+-----------------------------------+----------+

WHERE句を使えばVariable_name以外の条件でも絞り込める。

SHOW VARIABLES WHERE Variable_name LIKE '%timeout%' AND Value > 100;

GLOBALとSESSIONでスコープが異なる

MySQLの設定値には、サーバー全体に効くGLOBALスコープと、接続ごとに効くSESSIONスコープがある。
SHOW VARIABLESは指定を省略するとSESSIONスコープの値を表示する。

SHOW GLOBAL VARIABLES LIKE '設定名';
SHOW SESSION VARIABLES LIKE '設定名';

例えばwait_timeoutはセッションごとにSET SESSIONで変更できる設定である。

mysql> SET SESSION wait_timeout = 1000;
mysql> SHOW SESSION VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 1000  |
+---------------+-------+

SET SESSIONで変更した値は現在の接続にのみ反映され、他の接続やGLOBALの値には影響しない。
別の接続でSHOW GLOBAL VARIABLES LIKE 'wait_timeout';を実行すると、変更前のデフォルト値28800のままである。

GLOBALとSESSIONの両方が存在しない、サーバー全体だけの設定（max_connectionsなど）も多い。
その場合はSHOW GLOBAL VARIABLESとSHOW SESSION VARIABLESのどちらでも同じ値が表示される。

performance_schemaのビューで設定値を確認する

SQLで設定値を取得する場合はperformance_schema.global_variablesビューを参照する。
SHOW GLOBAL VARIABLESの結果は、内部的にはこのビューを参照して生成される。

mysql> SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'max_connections';
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 151            |
+-----------------+----------------+

SHOW VARIABLESと違いWHERE句やORDER BYが自由に使えるため、複雑な検索条件を組み立てやすい。

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME LIKE '%timeout%'
ORDER BY VARIABLE_NAME
LIMIT 5;

+------------------------------+----------------+
| VARIABLE_NAME                | VARIABLE_VALUE |
+------------------------------+----------------+
| connect_timeout              | 10             |
| delayed_insert_timeout       | 300            |
| have_statement_timeout       | YES            |
| innodb_flush_log_at_timeout  | 1              |
| innodb_lock_wait_timeout     | 50             |
+------------------------------+----------------+

セッションスコープの値を取得する場合はperformance_schema.session_variablesビューを使う。

SELECT * FROM performance_schema.session_variables WHERE VARIABLE_NAME = 'wait_timeout';

global_variablesとsession_variablesはどちらもVARIABLE_NAMEとVARIABLE_VALUEの2カラムのみを持ち、SHOW VARIABLESと同様に値の型や説明は含まれない。
参考: 【PostgreSQL】SHOW ALLやpg_settingsで設定値を一覧・検索する