はじめに

MySQL 8.0のシステム変数の変更手順を紹介します。
システム変数は、MySQLサーバの動作を制御するパラメータの事を指し、Oracle Databaseで例えるとデータベース初期化パラメータに相当します。

本記事は、MySQL 8.0の下記マニュアルを参考に作成しました。
5.1.9 システム変数の使用

動作確認環境

mysql> status
--------------
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          14
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.32 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 26 days 22 hours 19 min 37 sec

Threads: 2  Questions: 41  Slow queries: 0  Opens: 148  Flush tables: 3  Open tables: 67  Queries per second avg: 0.000
--------------

システム変数のスコープ

各システム変数は、下記のいずれかのスコープを持っています。

グローバルのみ
セッションのみ
グローバルおよびセッションの両方

スコープ	説明
グローバル	MySQLサーバ全体の動作に影響する(MySQLサーバ全体で値は1つ)
セッション	各クライアントの動作に影響する(セッション単位に個別の設定が可能)

システム変数の変更方法

MySQLサーバの起動時に各グローバルシステム変数がデフォルト値で初期化されます。
デフォルト値は下記の方法で変更可能です。

MySQLサーバ起動時のコマンドラインオプションで設定する
オプションファイル(構成ファイル)で設定する
SET文で設定する

MySQLサーバ起動時のコマンドラインオプションで設定する

下記のように、MySQLサーバを起動する際にシステム変数を設定します。

mysqld --innodb-log-file-size=16M --max-allowed-packet=1G

ただし、筆者の環境のように、systemdを使用して、MySQLサーバプロセスの起動停止を行っている場合は、上記の方法は使用せず、MYSQLD_OPTS変数を使用して、システム変数を設定します。
MYSQLD_OPTS変数に、local_infileシステム変数を設定する手順を示します。

[root@mdb01 ~]# systemctl set-environment MYSQLD_OPTS="--local-infile=ON"
[root@mdb01 ~]# systemctl show-environment|grep MYSQLD_OPTS
MYSQLD_OPTS=--local-infile=ON
[root@mdb01 ~]# systemctl restart mysqld

MySQLサーバを再起動後、mysqldに指定したコマンドラインオプションが設定されている事を確認します。

[root@mdb01 ~]# pgrep -a mysqld
1606455 /usr/sbin/mysqld --local-infile=ON

mysqlクライアントを使用して、グローバルシステム変数が設定されていることを確認します。

[root@mdb01 ~]# mysql -uroot -p
Enter password:

mysql> SHOW GLOBAL VARIABLES LIKE '%local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> exit
Bye

MYSQLD_OPTS変数の値をリセットし、MySQLサーバのコマンドラインオプションを元に戻します。

[root@mdb01 ~]# systemctl unset-environment MYSQLD_OPTS
[root@mdb01 ~]# systemctl restart mysqld
[root@mdb01 ~]# pgrep -a mysqld
1608732 /usr/sbin/mysqld

オプションファイル(構成ファイル)で設定する

MySQLサーバ起動時にオプションファイルに定義したシステム変数を読み取ることにより、システム変数を設定する事が可能です。
マニュアルによるとUnix系では下記のオプションファイルが使用可能です。

ファイル名	目的
/etc/my.cnf	グローバルオプション
/etc/mysql/my.cnf	グローバルオプション
SYSCONFDIR/my.cnf	グローバルオプション
$MYSQL_HOME/my.cnf	サーバー固有のオプション (サーバーのみ)
defaults-extra-file	--defaults-extra-file で指定されたファイル (存在する場合)
~/.my.cnf	ユーザー固有のオプション
~/.mylogin.cnf	ユーザー固有のログインパスオプション (クライアントのみ)
DATADIR/mysqld-auto.cnf	SET PERSIST または SE PERSIST_ONLY で永続化されるシステム変数 (サーバーのみ)

上記表の先頭に記載したファイルから順番に読み取られて、後で読み取られたファイルが優先される動作となるため、同じシステム変数が複数のファイルに定義されていた場合は、最後に定義されていた値が採用されます。

一例として、/etc/my.cnfファイルの内容を紹介します。

[root@mdb01 ~]# egrep -v ^# /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

補足

筆者のMySQL 8.0.32環境においては--defaults-extra-fileに指定するファイル名が、my.cnf の場合に下記のエラーが発生しました。my.cnfをext.cnfに変更したところ、起動できたため、ファイル名の問題と考えています。

[root@mdb01 ~]# systemctl status mysqld
(省略)
Apr 30 10:44:17 mdb01 mysqld[1796310]: mysqld: [ERROR] Failed to open required defaults file: /ext/my.cnf
(省略)

SET文で設定する

動的に変更可能なシステム変数

動的に変更可能なシステム変数の設定変更は、MySQLサーバを再起動する事なく実施可能です。
グローバルスコープのシステム変数を変更する場合は、SET GLOBAL文で設定します。
SET GLOBAL文による変更は一時的な変更となるため、MySQLサーバを再起動するとデフォルト値に戻ってしまいます。Oracle Databaseに例えると、ALTER SYSTEM SET xxx=yyy SCOPE=MEMORY;に相当します。

SET GLOBAL文を実行するためには、SYSTEM_VARIABLES_ADMIN権限(SUPERは非推奨)が必要になります。作業用アカウントのchibattyに権限を付与します。

[root@mdb01 ~]# mysql -uroot -p
Enter password:

mysql> SHOW GRANTS FOR chibatty@localhost;
+-----------------------------------------------------------------+
| Grants for chibatty@localhost                                   |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `chibatty`@`localhost`                    |
| GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO `chibatty`@`localhost`   |
| GRANT ALL PRIVILEGES ON `menagerie`.* TO `chibatty`@`localhost` |
+-----------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> exit
Bye

動的変更可能なautocommitシステム変数をグローバルスコープで変更します。
現在の値は、autocommitが有効であることを示す1が設定されています。

[root@mdb01 ~]# mysql -uchibatty -p
Enter password:

mysql> SELECT @@GLOBAL.autocommit, @@SESSION.autocommit;
+---------------------+----------------------+
| @@GLOBAL.autocommit | @@SESSION.autocommit |
+---------------------+----------------------+
|                   1 |                    1 |
+---------------------+----------------------+
1 row in set (0.00 sec)

autocommitシステム変数を0に変更します。

mysql> SET GLOBAL autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.autocommit, @@SESSION.autocommit;
+---------------------+----------------------+
| @@GLOBAL.autocommit | @@SESSION.autocommit |
+---------------------+----------------------+
|                   0 |                    1 |
+---------------------+----------------------+
1 row in set (0.00 sec)

上記の通りグローバルスコープのautocommitシステム変数は0に設定されましたが、セッションスコープのautocommitシステム変数は1のままであるため、当セッションでは、autocommit=1が採用されます。以降の新規セッションはグローバルスコープの値が、セッションスコープに引き継がれて、autocommit=0になります。
今回は、自身のセッションスコープのシステム変数をSET SESSION文を使用して、変更します。

mysql> SET SESSION autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.autocommit, @@SESSION.autocommit;
+---------------------+----------------------+
| @@GLOBAL.autocommit | @@SESSION.autocommit |
+---------------------+----------------------+
|                   0 |                    0 |
+---------------------+----------------------+
1 row in set (0.00 sec)

MySQLサーバ再起動後も変更を維持したい場合は、SET PERSIST文を使用します。Oracle Databaseにて例えるとALTER SYSTEM SET xxx=yyy SCOPE=BOTH;に相当します。
autocommitシステム変数を0で永続化します。

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

SET PERSIST文を使用して変更した場合は、mysqld-auto.cnfに設定されます。mysqld-auto.cnfは、データベースディレクトリに格納されるため、rpmパッケージからインストールした場合のデフォルトは、/var/lib/mysqlとなります。

[root@mdb01 ~]# cat /var/lib/mysql/mysqld-auto.cnf
{"Version": 2, "mysql_dynamic_variables": {"autocommit": {"Value": "OFF", "Metadata": {"Host": "localhost", "User": "chibatty", "Timestamp": 1682820286166006}}}}

また、performance_schema.persisted_variablesテーブルを参照することでも確認可能です。

mysql> SELECT variable_name, variable_value FROM performance_schema.persisted_variables;
+---------------+----------------+
| variable_name | variable_value |
+---------------+----------------+
| autocommit    | OFF            |
+---------------+----------------+
1 row in set (0.00 sec)

SET PERSIST文は、現在のシステム変数を動的に変更するとともに、mysqld-auto.cnfに記録し、設定値を永続化します。一方で、動的に変更できないシステム変数や、次回MySQLサーバの起動後からシステム変数の値を変更したい場合は、SET PERSIST_ONLY文を使用して、mysqld-auto.cnfにのみ記録する事が可能です。Oracle Databaseで例えると、ALTER SYSTEM SET xxx=yyy SCOPE=spfile;に相当します。
下記は、autocommit設定が、mysqld-auto.cnfとグローバルシステム変数ともに無効化(0はOFFと同値)されている状態を示しています。

mysql> SELECT variable_name, variable_value, @@GLOBAL.autocommit FROM performance_schema.persisted_variables WHERE variable_name =
'autocommit';
+---------------+----------------+---------------------+
| variable_name | variable_value | @@GLOBAL.autocommit |
+---------------+----------------+---------------------+
| autocommit    | OFF            |                   0 |
+---------------+----------------+---------------------+
1 row in set (0.00 sec)

SET PERSIST_ONLY文を使用するためには、PERSIST_RO_VARIABLES_ADMIN権限が必要になるため、chibattyアカウントに付与します。

[root@mdb01 ~]# mysql -uroot -p
Enter password:

mysql> GRANT PERSIST_RO_VARIABLES_ADMIN ON *.* TO chibatty@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR chibatty@localhost;
+------------------------------------------------------------------------------------------+
| Grants for chibatty@localhost                                                            |
+------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `chibatty`@`localhost`                                             |
| GRANT PERSIST_RO_VARIABLES_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `chibatty`@`localhost` |
| GRANT ALL PRIVILEGES ON `menagerie`.* TO `chibatty`@`localhost`                          |
+------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

chibattyアカウントを使用してMySQLサーバに接続し、SET PERSIST_ONLY文を使用して、autocommitシステム変数の値を変更します。

[root@mdb01 ~]# mysql -uchibatty -p
Enter password:

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

実行後の値を確認すると想定通り、グローバルスコープの値は変更されず、mysqld-auto.cnfのみ変更されていることが確認できます。

mysql> SELECT variable_name, variable_value, @@GLOBAL.autocommit FROM performance_schema.persisted_variables WHERE variable_name = 'autocommit';
+---------------+----------------+---------------------+
| variable_name | variable_value | @@GLOBAL.autocommit |
+---------------+----------------+---------------------+
| autocommit    | ON             |                   0 |
+---------------+----------------+---------------------+
1 row in set (0.00 sec)

mysqld-auto.cnfに記録されたシステム変数を削除する場合は、RESET PERSIST文を使用します。
autocommitシステム変数を削除します。

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

削除されたことを確認します。

mysql> SELECT * FROM performance_schema.persisted_variables WHERE variable_name = 'autocommit';
Empty set (0.00 sec)

システム変数名を指定せず、RESET PERSIST; と実行するとすべて削除されます。

[MySQL] システム変数の変更