MySQL 5.
今回は、
SET PERSISTを利用してみる
SET PERSISTを使った構文はSET GLOBALを使用するときと同じようにSET PERSIST <システム変数名> またはSET @@PERSIST.<システム変数名> と記述します。通常、
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_ONLY
SET PERSIST_
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. |
| SET PERSIST_ | されない | mysqld-auto. |
RESET PERSIST
RESET PERSISTはSET PERSIST、
mysql> RESET PERSIST;
Query OK, 0 rows affected (0.00 sec)
$ cat mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { } }
設定されていたシステム変数が削除されていることが確認できました。
なお、
mysql> RESET PERSIST max_connections; Query OK, 0 rows affected (0.00 sec)
起動時にmysqld-auto.cnfを無視する
SET PERSISTで変更した値はmysqld-auto.
もし、
詳細については5.
SET PERSISTで設定した値の確認
SET PERSIST、
persisted_
> SELECT * FROM performance_schema.persisted_variables WHERE VARIABLE_NAME like '%max_connection%'; +------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------------+----------------+ | max_connections | 205 | | mysqlx_max_connections | 500 | +------------------------+----------------+
特に、
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)
そのため、
> 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、