MySQL - 設定ファイルのソースを表示

== 概要 ==
MySQLの設定ファイルは、MySQLサーバの動作を制御する重要な設定を記述するファイルである。<br>
Linuxでは <u>my.cnf</u>、Windowsでは <u>my.ini</u> という名前が一般的に使用される。<br>
<br>
設定ファイルには、サーバの起動オプション、メモリ割り当て、接続数制限、ログ設定、文字コード、セキュリティ設定等を記述できる。<br>
これらのパラメータは、データベースのパフォーマンス、安定性、セキュリティに大きな影響を与える。<br>
<br>
MySQLは複数の場所から設定ファイルを読み込み、後から読み込まれた設定が優先される。<br>
設定ファイルは <code>[mysqld]</code>、<code>[client]</code>、<code>[mysql]</code> 等のセクション (グループ) に分けて記述される。<br>
各セクションは、特定のMySQLプログラムに対応する設定を含む。<br>
<br>
設定は、設定ファイルへの記述による静的設定と、<code>SET GLOBAL</code> による動的設定の2つの方法がある。<br>
MySQL 8.0以降では、<code>SET PERSIST</code> により動的設定を永続化することも可能である。<br>
<br>
適切な設定により、システムのワークロード (OLTP、OLAP、混在型) に最適化されたパフォーマンスを実現できる。<br>
<br>
設定の確認には、<code>SHOW VARIABLES</code> 文 や <code>mysqld --verbose --help</code> コマンドを使用する。<br>
<br><br>

== 設定ファイルの場所 ==
MySQLは、複数の場所から設定ファイルを順次読み込む。<br>
後から読み込まれた設定ファイルの値が、先に読み込まれた値を上書きする。<br>
<br>
==== Linuxでの設定ファイルの場所 ====
Linuxでは、以下の順序で設定ファイルが読み込まれる。<br>
<br>
<center>
{| class="wikitable"
|+ Linux設定ファイル読み込み順序
! 順序 !! ファイルパス !! 説明
|-
| 1 || /etc/my.cnf || システム全体の設定
|-
| 2 || /etc/mysql/my.cnf || MySQL専用のシステム設定
|-
| 3 || SYSCONFDIR/my.cnf || コンパイル時指定ディレクトリ
|-
| 4 || $MYSQL_HOME/my.cnf || 環境変数で指定されたディレクトリ
|-
| 5 || defaults-extra-file || --defaults-extra-fileで指定されたファイル
|-
| 6 || ~/.my.cnf || ユーザ個別設定 (ホームディレクトリ)
|-
| 7 || ~/.mylogin.cnf || 暗号化されたログイン情報
|}
</center>
<br>
システム管理者は、通常 <u>/etc/my.cnf</u> または <u>/etc/mysql/my.cnf</u> にサーバ設定を記述する。<br>
ユーザ個別設定は、<u>~/.my.cnf</u> に記述する。<br>
<br>
環境変数 <code>MYSQL_HOME</code> を設定することにより、特定ディレクトリの設定ファイルを優先的に読み込むことができる。<br>
<br>
==== Windowsでの設定ファイルの場所 ====
Windowsでは、以下の順序で設定ファイルが読み込まれる。<br>
<br>
<center>
{| class="wikitable"
|+ Windows設定ファイル読み込み順序
! 順序 !! ファイルパス !! 説明
|-
| 1 || %PROGRAMDATA%\MySQL\MySQL Server X.Y\my.ini || MySQL公式インストーラのデフォルト設定
|-
| 2 || %WINDIR%\my.ini || Windowsディレクトリ (非推奨)
|-
| 3 || C:\my.ini || Cドライブルート (非推奨)
|-
| 4 || BASEDIR\my.ini || MySQLインストールディレクトリ
|-
| 5 || defaults-extra-file || <code>--defaults-extra-file</code> で指定されたファイル
|}
</center>
<br>
Windows環境では、<u>%PROGRAMDATA%\MySQL\MySQL Server 8.0\my.ini</u> が標準的な設定ファイルである。<br>
MySQL公式インストーラは、インストール時にこのファイルを自動生成する。<br>
<br>
<u>%WINDIR%</u> は、<u>C:\Windows</u>、<u>BASEDIR</u> は、<u>C:\Program Files\MySQL\MySQL Server 8.0</u> である。<br>
<br>

==== 設定ファイルの読み込み順序の確認 ====
現在のMySQL環境で実際に読み込まれる設定ファイルの順序を確認する方法を以下に示す。<br>
<br>
 mysqld --verbose --help | grep -A 1 "Default options"
 
 # 出力例
 
 Default options are read from the following files in the given order:
 /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
<br>
実際に有効な設定値を確認するには、以下に示すコマンドを使用する。<br>
このコマンドは、<code>[mysqld]</code> セクションで有効になっている全ての設定を表示する。<br>
 my_print_defaults mysqld
<br>
特定の設定ファイルのみを使用する場合は、<code>--defaults-file</code> オプションを使用する。<br>
 mysqld --defaults-file=/etc/mysql/my-custom.cnf
<br>
デフォルトの設定ファイルを無視する場合は、<code>--no-defaults</code> オプションを使用する。<br>
 mysqld --no-defaults
<br>
追加の設定ファイルを読み込む場合は、<code>--defaults-extra-file</code> オプションを使用する。<br>
 mysqld --defaults-extra-file=/etc/mysql/my-extra.cnf
<br><br>

== 設定ファイルの構造 ==
MySQLの設定ファイルは、セクション (グループ) 単位で構成される。<br>
各セクションは、<code>[セクション名]</code> で開始され、次のセクションまたはファイル末尾まで続く。<br>
<br>
==== セクション (グループ) ====
下表に、MySQLの設定ファイルで使用される主要なセクションを示す。<br>
<br>
<center>
{| class="wikitable"
|+ 設定ファイルのセクション
! セクション名 !! 適用対象 !! 用途
|-
| [mysqld] || mysqldサーバプロセス || サーバの動作設定 (メモリ、接続数、ログ等)
|-
| [mysql] || mysqlクライアント || クライアントの動作設定 (プロンプト、文字コード等)
|-
| [client] || 全てのMySQLクライアント || 全クライアント共通設定 (接続先、パスワード等)
|-
| [mysqldump] || mysqldumpコマンド || バックアップ時の設定 (オプション等)
|-
| [mysqld_safe] || mysqld_safe起動スクリプト || MySQLサーバの安全起動設定
|-
| [mysqladmin] || mysqladminコマンド || 管理コマンドの設定
|-
| [mysqlbinlog] || mysqlbinlogコマンド || バイナリログ解析の設定
|-
| [mysqlimport] || mysqlimportコマンド || データインポートの設定
|}
</center>
<br>
最も重要なセクションは、<code>[mysqld]</code> である。<br>
このセクションには、<u>MySQLサーバプロセスの動作を制御する全ての設定</u> を記述する。<br>
<br>
<code>[client]</code> セクションは、<u>全てのMySQLクライアントプログラム (mysql、mysqldump、mysqladmin等) に適用</u> される。<br>
<br>
<code>[mysql]</code> セクションは、<u>mysqlコマンドラインクライアントのみに適用</u> される。<br>
<br>
==== 設定の記述方法 ====
設定ファイル内の各設定項目は、以下の形式で記述する。<br>
<br>
基本的な記述形式を以下に示す。<br>
* <u>キー=値</u>
*: パラメータに値を設定
* <u>キー</u>
*: ブール値パラメータを有効化 (<code>ON</code> と同じ)
* <u># コメント</u> または <u>; コメント</u>
*: コメント行
<br>
設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # サーバID設定
 server-id=1
 
 # ポート番号
 port=3306
 
 # バイナリログ有効化
 log-bin=mysql-bin
 
 # スロークエリログ有効化
 slow-query-log
 
 # スロークエリ閾値 (秒)
 long_query_time=2.0
 </syntaxhighlight>
<br>
変数名のハイフン (<code>-</code>) と アンダースコア (<code>_</code>) は互換性がある。<br>
<code>log-bin</code> と <code>log_bin</code> は同じ意味である。<br>
<br>
不明なオプションを無視する場合は、<code>loose-</code> プレフィックスを使用する。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # MySQL 8.0未満では無視される
 loose-innodb_redo_log_capacity=1G
 </syntaxhighlight>
<br>
他の設定ファイルをインクルードする場合は、<code>!include</code> または <code>!includedir</code> ディレクティブを使用する。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 !include /etc/mysql/conf.d/mysql-custom.cnf
 !includedir /etc/mysql/conf.d/
 </syntaxhighlight>
<br>
<code>!includedir</code> は、指定ディレクトリ内の.cnfファイル (Windowsでは、.ini および .cnf) を全て読み込む。<br>
<br>
==== 設定ファイルの優先順位 ====
同じパラメータが複数の設定ファイルに記述されている場合、後から読み込まれた値が優先される。<br>
<br>
例として、以下に示す2つの設定ファイルがある場合を考える。<br>
<br>
 <syntaxhighlight lang="ini">
 # /etc/my.cnfファイル
 
 [mysqld]
 max_connections=100
 </syntaxhighlight>
<br>
 <syntaxhighlight lang="ini">
 # ~/.my.cnfファイル
 
 [mysqld]
 max_connections=500
 </syntaxhighlight>
<br>
この場合、<u>~/.my.cnf</u> が後から読み込まれるため、最終的な <code>max_connections</code> は500になる。<br>
<br>
なお、コマンドライン引数は、設定ファイルより優先される。<br>
<br>
 mysqld --max-connections=1000
<br>
この場合、設定ファイルの値に関わらず、<code>max_connections</code> は1000になる。<br>
<br><br>

== 主要パラメータ ==
==== 接続関連 ====
MySQLサーバへの接続に関連するパラメータを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 接続関連パラメータ
! パラメータ !! デフォルト !! 説明
|-
| port || 3306 || MySQLサーバのリスニングポート
|-
| bind-address || * (全てのIP) || サーバがバインドするIPアドレス
|-
| socket || /var/lib/mysql/mysql.sock || UnixソケットファイルのPATH
|-
| max_connections || 151 || 最大同時接続数
|-
| max_connect_errors || 100 || 接続エラー許容回数 (ホストブロック閾値)
|-
| wait_timeout || 28800 (8時間) || 非インタラクティブ接続のタイムアウト (秒)
|-
| interactive_timeout || 28800 (8時間) || インタラクティブ接続のタイムアウト (秒)
|-
| connect_timeout || 10 || 接続確立時のタイムアウト (秒)
|-
| max_allowed_packet || 64MB (8.0) || 最大パケットサイズ
|-
| skip-name-resolve || OFF || DNS逆引きを無効化 (高速化)
|}
</center>
<br>
接続関連の推奨設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # 接続設定
 port=3306
 bind-address=0.0.0.0
 max_connections=500
 max_connect_errors=1000000
 
 # タイムアウト設定
 wait_timeout=600
 interactive_timeout=600
 connect_timeout=10
 
 # パケットサイズ
 max_allowed_packet=256M
 
 # DNS逆引き無効化 (パフォーマンス向上)
 skip-name-resolve
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ ネットワーク・接続の主要なパラメータ
|-
! パラメータ !! 説明
|-
| <code>bind-address</code> || 特定のIPアドレスからのみ接続を受け付ける場合に設定する。<br><u>0.0.0.0</u> は、全てのIPアドレスからの接続を許可する。<br><u>127.0.0.1</u> は、ローカルホストからのみ接続を許可する。
|-
| <code>max_connections</code> || システムのメモリ量とワークロードに応じて設定する。<br>一般的なWebアプリケーションでは、<u>100〜500</u> が適切である。
|-
| <code>skip-name-resolve</code> || 有効にすると、DNS逆引きが無効化され、接続時のオーバーヘッドが削減される。<br>ただし、ホスト名ベースの権限管理 (<code>GRANT</code> 文でホスト名を使用) は使用できなくなることに注意する。
|}
</center>
<br>
==== InnoDB関連 ====
InnoDBストレージエンジンの動作を制御するパラメータを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ InnoDB関連パラメータ
! パラメータ !! デフォルト !! 説明
|-
| innodb_buffer_pool_size || 128MB || InnoDBバッファプールサイズ
|-
| innodb_buffer_pool_instances || 8 (pool>1GBの場合) || バッファプールのインスタンス数
|-
| innodb_log_file_size || 48MB (8.0.30未満) || REDOログファイルサイズ
|-
| innodb_redo_log_capacity || 100MB (8.0.30以降) || REDOログ容量
|-
| innodb_log_buffer_size || 16MB || ログバッファサイズ
|-
| innodb_flush_log_at_trx_commit || 1 || ログフラッシュ動作 (0/1/2)
|-
| innodb_flush_method || fsync || フラッシュ方式 (O_DIRECT推奨)
|-
| innodb_file_per_table || ON || テーブルごとに個別ファイル使用
|-
| innodb_io_capacity || 200 || I/O容量 (IOPS)
|-
| innodb_io_capacity_max || 2 * innodb_io_capacity || 最大I/O容量
|-
| innodb_read_io_threads || 4 || 読み取りI/Oスレッド数
|-
| innodb_write_io_threads || 4 || 書き込みI/Oスレッド数
|-
| innodb_thread_concurrency || 0 (無制限) || InnoDBスレッド同時実行数
|-
| innodb_autoinc_lock_mode || 2 || AUTO_INCREMENTロックモード
|}
</center>
<br>
InnoDB関連の推奨設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # InnoDBバッファプール (物理メモリの70-80%を推奨)
 innodb_buffer_pool_size=8G
 innodb_buffer_pool_instances=8
 
 # REDOログ設定 (MySQL 8.0.30以降)
 innodb_redo_log_capacity=2G
 
 # REDOログ設定 (MySQL 8.0.30未満)
 # innodb_log_file_size=512M
 
 # ログバッファ
 innodb_log_buffer_size=64M
 
 # フラッシュ設定
 innodb_flush_log_at_trx_commit=1
 innodb_flush_method=O_DIRECT
 
 # I/O設定 (SSD環境)
 innodb_io_capacity=2000
 innodb_io_capacity_max=4000
 innodb_read_io_threads=8
 innodb_write_io_threads=8
 
 # その他
 innodb_file_per_table=ON
 innodb_autoinc_lock_mode=2
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ InnoDBの主要なパラメータ
|-
! パラメータ !! 説明
|-
| <code>innodb_buffer_pool_size</code> || 最も重要なInnoDBパラメータである。<br>専用データベースサーバでは、物理メモリの70〜80[%]を割り当てることを推奨する。<br>例: 16[GB] メモリのサーバでは、<u>12G〜13G</u> が適切である。
|-
| <code>innodb_buffer_pool_instances</code> || バッファプールを複数のインスタンスに分割する。<br>バッファプールサイズが1GB以上の場合、デフォルトで8インスタンスに分割される。<br>高負荷環境では、インスタンス数を増やすことで競合を軽減できる。
|-
| <code>innodb_flush_log_at_trx_commit</code> || ログのディスク書き込み方式を指定する。<br><u>0</u> : ログを1秒ごとにディスクに書き込む (高速だがクラッシュ時にデータ損失の可能性)<br><u>1</u> : トランザクションごとにログをディスクに書き込む (最も安全、デフォルト)<br><u>2</u> : トランザクションごとにOSバッファに書き込み、1秒ごとにディスクに書き込む (折衷案)
|-
| <code>innodb_flush_method</code> || ファイルI/Oの方式を指定する。<br>Linux環境では、<code>O_DIRECT</code> を推奨する。<br><code>O_DIRECT</code> は、OSのページキャッシュをバイパスし、二重バッファリングを回避する。
|-
| <code>innodb_io_capacity</code> || ストレージのIOPS性能に応じて設定する。<br>HDD環境では <u>200</u>、SATA SSDでは <u>2000〜5000</u>、NVMe SSDでは <u>10000</u> 以上が適切である。
|}
</center>
<br>
<u>MySQL 8.0.30以降では、<code>innodb_log_file_size</code> と <code>innodb_log_files_in_group</code> が廃止され、<code>innodb_redo_log_capacity</code> に統合された。</u><br>
<br>
==== クエリキャッシュ関連 (MySQL 5.7以前) ====
クエリキャッシュは、MySQL 8.0で完全に削除された機能である。<br>
MySQL 5.7以前のバージョンでは、以下のパラメータを使用できる。<br>
<br>
* <code>query_cache_type</code>
*: クエリキャッシュの有効化
*: 0: 無効、1: 有効、2: DEMAND
* <code>query_cache_size</code>
*: クエリキャッシュのサイズ (バイト)
* <code>query_cache_limit</code>
*: キャッシュ可能な最大クエリサイズ
<br>
MySQL 5.7以前の設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # MySQL 5.7以前のみ有効
 query_cache_type=1
 query_cache_size=256M
 query_cache_limit=2M
 </syntaxhighlight>
<br>
ただし、クエリキャッシュはスケーラビリティの問題により、高負荷環境ではパフォーマンスを低下させる。<br>
MySQL 5.7以前でも、クエリキャッシュを無効化することを推奨する。<br>
<br>
MySQL 8.0以降では、クエリキャッシュ機能は完全に削除されている。<br>
<br>
==== ログ関連 ====
下表に、MySQLの各種ログ機能を制御するパラメータを示す。<br>
<br>
<center>
{| class="wikitable"
|+ ログ関連パラメータ
! パラメータ !! デフォルト !! 説明
|-
| log-error || 標準エラー出力 || エラーログのファイルPATH
|-
| general_log || OFF || 一般クエリログの有効化
|-
| general_log_file || hostname.log || 一般クエリログのファイルPATH
|-
| slow_query_log || OFF || スロークエリログの有効化
|-
| slow_query_log_file || hostname-slow.log || スロークエリログのファイルPATH
|-
| long_query_time || 10.0 || スロークエリ閾値 (秒)
|-
| log_queries_not_using_indexes || OFF || インデックス未使用クエリをログに記録
|-
| log-bin || OFF || バイナリログの有効化
|-
| binlog_format || ROW || バイナリログ形式 (ROW/STATEMENT/MIXED)
|-
| binlog_expire_logs_seconds || 2592000 (30日) || バイナリログの保持期間 (秒)
|-
| max_binlog_size || 1GB || バイナリログファイルの最大サイズ
|-
| sync_binlog || 1 || バイナリログの同期頻度
|}
</center>
<br>
ログ関連の推奨設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # エラーログ
 log-error=/var/log/mysql/error.log
 
 # スロークエリログ
 slow_query_log=ON
 slow_query_log_file=/var/log/mysql/slow-query.log
 long_query_time=2.0
 log_queries_not_using_indexes=ON
 
 # バイナリログ (レプリケーション・PITR用)
 log-bin=/var/log/mysql/mysql-bin
 binlog_format=ROW
 binlog_expire_logs_seconds=604800
 max_binlog_size=500M
 sync_binlog=1
 
 # 一般クエリログ (デバッグ時のみ有効化、本番環境では無効推奨)
 # general_log=ON
 # general_log_file=/var/log/mysql/general.log
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ MySQLの主要なログ設定
|-
! 設定項目 !! 説明
|-
| <code>log-error</code> || MySQLサーバの起動エラー、警告、重要なメッセージを記録する。<br>必ず有効化し、定期的に確認すること。
|-
| <code>slow_query_log</code> || 実行時間が <code>long_query_time</code> を超えるクエリを記録する。<br>パフォーマンスチューニングの際に必須のログである。
|-
| <code>log_queries_not_using_indexes</code> || 有効にすると、インデックスを使用しないクエリもスロークエリログに記録される。<br>インデックス不足によるパフォーマンス問題を早期に発見できる。
|-
| <code>log-bin</code> || バイナリログを有効にする。<br>レプリケーション、ポイントインタイムリカバリ (PITR) に必須である。
|-
| <code>binlog_format</code> || バイナリログの形式を指定する。<br><code>ROW</code> : 行ベース (デフォルト、最も安全)<br><code>STATEMENT</code> : SQL文ベース (ログサイズ小、非決定的関数で問題)<br><code>MIXED</code> : 自動選択 (状況に応じてROW/STATEMENT切り替え)
|-
| <code>sync_binlog</code> || バイナリログの同期頻度を指定する。<br><code>1</code> はトランザクションごとに同期 (最も安全)、<code>0</code> はOSに任せる。(高速だが安全性低)
|-
| <code>general_log</code> || 全てのクエリを記録するため、大量のディスクI/Oが発生する。<br>デバッグ時のみ有効化し、本番環境では無効にすることを強く推奨する。
|}
</center>
<br>
==== 文字コード関連 ====
下表に、MySQLの文字コードと照合順序を制御するパラメータを示す。<br>
<br>
<center>
{| class="wikitable"
|+ 文字コード・照合順序の主要なパラメータ
|-
! パラメータ !! 説明
|-
| <code>character-set-server</code> || サーバのデフォルト文字コード<br>(デフォルト: <code>utf8mb4</code> in 8.0)
|-
| <code>collation-server</code> || サーバのデフォルト照合順序<br>(デフォルト: <code>utf8mb4_0900_ai_ci</code> in 8.0)
|-
| <code>character-set-client</code> || クライアント接続の文字コード
|-
| <code>character-set-connection</code> || 接続時の文字コード
|-
| <code>character-set-results</code> || 結果セットの文字コード
|-
| <code>init_connect</code> || 接続時に実行されるSQL文
|}
</center>
<br>
文字コード関連の推奨設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # サーバ文字コード (UTF-8 4バイト対応)
 character-set-server=utf8mb4
 collation-server=utf8mb4_general_ci
 
 # クライアント接続時の文字コード設定
 init_connect='SET NAMES utf8mb4'
 
 [client]
 # クライアント側のデフォルト文字コード
 default-character-set=utf8mb4
 </syntaxhighlight>
<br>
<code>utf8mb4</code> は、4バイトUTF-8文字 (絵文字等) をサポートする文字コードである。<br>
MySQL 8.0以降では、<code>utf8mb4</code> がデフォルトである。<br>
<br>
<code>utf8</code> (エイリアス: <code>utf8mb3</code>) は、最大3バイトまでのUTF-8文字のみをサポートする。<br>
絵文字や一部の漢字が格納できないため、<code>utf8mb4</code> の使用を推奨する。<br>
<br>
照合順序は、文字列の比較・ソート方法を定義する。<br>
主要な照合順序を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 主要な照合順序
|-
! 照合順序 !! 説明
|-
| <code>utf8mb4_general_ci</code> || 高速、大文字小文字を区別しない
|-
| <code>utf8mb4_unicode_ci</code> || より正確な言語ルール、やや低速
|-
| <code>utf8mb4_0900_ai_ci</code> || MySQL 8.0デフォルト、高速かつ正確
|-
| <code>utf8mb4_bin</code> || バイナリ比較、大文字小文字を区別
|}
</center>
<br>
文字コード設定の詳細については、[[MySQL - 文字コードと照合順序]]のページを参照すること。<br>
<br>
==== セキュリティ関連 ====
MySQLのセキュリティを強化するパラメータを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ セキュリティ関連パラメータ
! パラメータ !! デフォルト !! 説明
|-
| skip-networking || OFF || TCP/IP接続を無効化 (Unixソケットのみ)
|-
| local_infile || OFF (8.0以降) || LOAD DATA LOCAL INFILEの許可
|-
| secure_file_priv || platform dependent || ファイル入出力の許可ディレクトリ
|-
| sql_mode || STRICT系有効 || SQLモード (厳格性)
|-
| validate_password.policy || MEDIUM (8.0) || パスワードポリシー
|-
| default_password_lifetime || 0 (無期限) || パスワード有効期限 (日)
|-
| ssl_ca || NULL || SSL認証局証明書
|-
| ssl_cert || NULL || SSLサーバ証明書
|-
| ssl_key || NULL || SSLサーバ秘密鍵
|-
| require_secure_transport || OFF || SSL/Unixソケット接続を強制
|}
</center>
<br>
セキュリティ関連の推奨設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # ファイル操作の制限
 local_infile=OFF
 secure_file_priv=/var/lib/mysql-files/
 
 # 厳格なSQLモード
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
 
 # パスワードポリシー
 validate_password.policy=MEDIUM
 validate_password.length=8
 validate_password.mixed_case_count=1
 validate_password.number_count=1
 validate_password.special_char_count=1
 
 # SSL/TLS設定
 ssl_ca=/etc/mysql/ssl/ca-cert.pem
 ssl_cert=/etc/mysql/ssl/server-cert.pem
 ssl_key=/etc/mysql/ssl/server-key.pem
 require_secure_transport=ON
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ セキュリティ関連の主要なパラメータ
|-
! パラメータ !! 説明
|-
| <code>local_infile</code> || クライアント側のファイルをサーバに読み込む機能である。<br>セキュリティリスクがあるため、MySQL 8.0以降ではデフォルトで無効化されている。
|-
| <code>secure_file_priv</code> || <code>LOAD DATA INFILE</code>、<code>SELECT ... INTO OUTFILE</code> が使用できるディレクトリを制限する。<br>空文字列を設定すると制限なし、<code>NULL</code> を設定すると完全無効化される。
|-
| <code>sql_mode</code> || SQLの厳格性を制御する。<br><code>STRICT_TRANS_TABLES</code> を含めることにより、不正なデータの挿入を防ぐことができる。
|-
| <code>require_secure_transport</code> || 有効にすると、SSL/TLSまたはUNIXソケット経由の接続のみを許可する。<br>パブリックネットワーク経由の接続を受け付ける場合は、SSL/TLS設定と併用することを推奨する。
|}
</center>
<br>
==== メモリ関連 ====
MySQLのメモリ使用量を制御するパラメータを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ メモリ関連パラメータ
! パラメータ !! デフォルト !! スコープ !! 説明
|-
| innodb_buffer_pool_size || 128MB || グローバル || InnoDBバッファプールサイズ (最重要)
|-
| max_connections || 151 || グローバル || 最大接続数
|-
| table_open_cache || 4000 || グローバル || オープンテーブルキャッシュ数
|-
| table_definition_cache || 2000 || グローバル || テーブル定義キャッシュ数
|-
| thread_cache_size || 自動 (8+max_connections/100) || グローバル || スレッドキャッシュサイズ
|-
| sort_buffer_size || 256KB || セッション || ソート用バッファ
|-
| join_buffer_size || 256KB || セッション || JOIN用バッファ
|-
| read_buffer_size || 128KB || セッション || シーケンシャルスキャン用バッファ
|-
| read_rnd_buffer_size || 256KB || セッション || ランダムスキャン用バッファ
|-
| tmp_table_size || 16MB || セッション || 内部一時テーブルの最大サイズ
|-
| max_heap_table_size || 16MB || セッション || MEMORYテーブルの最大サイズ
|}
</center>
<br>
メモリパラメータは、グローバルスコープとセッションスコープに分類される。<br>
グローバルスコープのパラメータは、サーバ全体で共有される。<br>
セッションスコープのパラメータは、接続ごとに割り当てられる。<br>
<br>
総メモリ使用量の概算式を以下に示す。<br>
<br>
 総メモリ使用量 ≈ innodb_buffer_pool_size
                + key_buffer_size
                + max_connections * (
                     sort_buffer_size
                   + read_buffer_size
                   + read_rnd_buffer_size
                   + join_buffer_size
                   + thread_stack
                  )
                + その他グローバルバッファ
<br>
例として、以下に示す設定の場合のメモリ使用量を計算する。<br>
* <code>innodb_buffer_pool_size=8G</code>
* <code>max_connections=500</code>
* <code>sort_buffer_size=2M</code>
* <code>read_buffer_size=2M</code>
* <code>read_rnd_buffer_size=2M</code>
* <code>join_buffer_size=2M</code>
* <code>thread_stack=256K</code>
<br>
計算式を以下に示す。<br>
<br>
 総メモリ = 8GB + 500 * (2MB + 2MB + 2MB + 2MB + 0.25MB)
         = 8GB + 500 * 8.25MB
         = 8GB + 4.125GB
         ≈ 12.1GB
<br>
セッションバッファは、実際には必要な時にのみ割り当てられるため、最大値を大きくしすぎないよう注意する。<br>
<br><br>

== パフォーマンスチューニング ==
MySQLのパフォーマンスは、設定ファイルのパラメータ調整により大きく改善できる。<br>
<br>
==== チューニングの基本方針 ====
パフォーマンスチューニングの基本方針を以下に示す。<br>
<br>
* 測定なしにチューニングしない
*: <code>SHOW STATUS</code>、<code>SHOW VARIABLES</code>、[[MySQL - EXPLAIN]] で現状を把握する
* 1つずつ変更して効果を測定
*: 複数のパラメータを同時に変更すると、効果の測定が困難になる
* ワークロードに応じた設定
*: OLTP、OLAP、混在型でチューニング方針は異なる
* ハードウェアリソースを考慮
*: メモリ、CPU、ストレージ性能に応じた設定が必要
* スロークエリログを活用
*: パフォーマンス問題の大半はクエリに起因する
<br>
パフォーマンスモニタリングのクエリ例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 接続状態の確認
 SHOW STATUS LIKE 'Threads%';
 SHOW STATUS LIKE 'Max_used_connections';
 
 -- バッファプール使用状況
 SHOW STATUS LIKE 'Innodb_buffer_pool%';
 
 -- テーブルキャッシュ効率
 SHOW STATUS LIKE 'Open%';
 
 -- スレッドキャッシュ効率
 SHOW STATUS LIKE 'Threads_created';
 SHOW STATUS LIKE 'Connections';
 
 -- 一時テーブル使用状況
 SHOW STATUS LIKE 'Created_tmp%';
 </syntaxhighlight>
<br>
==== OLTP向け推奨設定 ====
OLTP (Online Transaction Processing) ワークロード向けの推奨設定を以下に示す。<br>
OLTPでは、多数の小規模トランザクションを高速に処理することが求められる。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # メモリ設定 (16GBメモリサーバを想定)
 innodb_buffer_pool_size=12G
 innodb_buffer_pool_instances=12
 innodb_log_buffer_size=64M
 
 # 接続設定
 max_connections=500
 thread_cache_size=100
 table_open_cache=4000
 table_definition_cache=2000
 
 # InnoDB設定
 innodb_flush_log_at_trx_commit=1
 innodb_flush_method=O_DIRECT
 innodb_file_per_table=ON
 innodb_autoinc_lock_mode=2
 
 # I/O設定 (SSD)
 innodb_io_capacity=2000
 innodb_io_capacity_max=4000
 innodb_read_io_threads=8
 innodb_write_io_threads=8
 
 # ログ設定 (MySQL 8.0.30以降)
 innodb_redo_log_capacity=2G
 
 # バイナリログ
 log-bin=mysql-bin
 binlog_format=ROW
 sync_binlog=1
 
 # スロークエリログ
 slow_query_log=ON
 long_query_time=1.0
 log_queries_not_using_indexes=ON
 </syntaxhighlight>
<br>
OLTP環境では、<code>innodb_flush_log_at_trx_commit=1</code> を維持してデータ整合性を確保する。<br>
ただし、パフォーマンスが重要でデータ損失許容可能な場合は、<u>2</u> に設定することで高速化できる。<br>
<br>
<code>innodb_autoinc_lock_mode=2</code> は、AUTO_INCREMENT値の高速割り当てを有効にする。<br>
レプリケーション環境でSTATEMENTベースのバイナリログを使用する場合は、<u>1</u> に設定する必要がある。<br>
<br>
==== 大規模データ処理向け推奨設定 ====
大規模データ処理 (バッチ処理、データウェアハウス) 向けの推奨設定を以下に示す。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # メモリ設定 (32GBメモリサーバを想定)
 innodb_buffer_pool_size=24G
 innodb_buffer_pool_instances=16
 innodb_log_buffer_size=128M
 
 # セッションバッファ (大規模ソート・JOIN用)
 sort_buffer_size=4M
 join_buffer_size=4M
 read_buffer_size=2M
 read_rnd_buffer_size=4M
 
 # 一時テーブル
 tmp_table_size=256M
 max_heap_table_size=256M
 
 # InnoDB設定
 innodb_flush_log_at_trx_commit=2
 innodb_flush_method=O_DIRECT
 
 # I/O設定 (高速SSD)
 innodb_io_capacity=5000
 innodb_io_capacity_max=10000
 innodb_read_io_threads=16
 innodb_write_io_threads=16
 
 # ログ設定 (MySQL 8.0.30以降)
 innodb_redo_log_capacity=4G
 
 # バルクインサート最適化
 innodb_autoinc_lock_mode=2
 bulk_insert_buffer_size=64M
 
 # バイナリログ (不要な場合は無効化)
 # log-bin=mysql-bin
 # binlog_format=ROW
 </syntaxhighlight>
<br>
大規模データ処理では、<code>innodb_flush_log_at_trx_commit=2</code> に設定して書き込み性能を向上させる。<br>
処理完了後にデータの整合性を確認することで、データ損失リスクを軽減できる。<br>
<br>
セッションバッファ (<code>sort_buffer_size</code>、<code>join_buffer_size</code> 等) を増やすことにより、大規模ソート・JOIN処理が高速化される。<br>
ただし、これらは接続ごとに割り当てられるため、設定値を大きくしすぎるとメモリ不足になる可能性がある。<br>
<br>
バルクインサート時は、<code>innodb_autoinc_lock_mode=2</code> と <code>bulk_insert_buffer_size</code> を増やすことで高速化できる。<br>
<br>
レプリケーションが不要な場合は、バイナリログを無効化することでI/Oオーバーヘッドを削減できる。<br>
<br><br>

== 設定の確認・変更方法 ==
MySQLの設定は、<u>起動時の設定ファイル読み込みによる静的設定</u> と <u>実行時の動的設定</u> の2種類がある。<br>
<br>
==== SHOW VARIABLESでの確認 ====
現在有効な設定値を確認するには、<code>SHOW VARIABLES</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 全ての設定変数を表示
 SHOW VARIABLES;
 
 -- 特定のパターンにマッチする変数を表示
 SHOW VARIABLES LIKE 'innodb_buffer%';
 
 -- 文字コード関連の変数を表示
 SHOW VARIABLES LIKE 'character%';
 
 -- タイムアウト関連の変数を表示
 SHOW VARIABLES LIKE '%timeout%';
 
 -- 特定の変数を表示
 SHOW VARIABLES WHERE Variable_name = 'max_connections';
 </syntaxhighlight>
<br>
グローバル変数のみを表示する場合は、<code>SHOW GLOBAL VARIABLES</code> を使用する。<br>
セッション変数のみを表示する場合は、<code>SHOW SESSION VARIABLES</code> を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- グローバル変数を表示
 SHOW GLOBAL VARIABLES LIKE 'max_connections';
 
 -- セッション変数を表示
 SHOW SESSION VARIABLES LIKE 'sql_mode';
 </syntaxhighlight>
<br>
performance_schemaを使用して、変数情報を取得することもできる。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT * FROM performance_schema.global_variables
 WHERE VARIABLE_NAME LIKE 'innodb_buffer%';
 </syntaxhighlight>
<br>
==== SET GLOBALでの動的変更 ====
MySQLの多くの設定は、サーバの再起動なしで動的に変更できる。<br>
<code>SET GLOBAL</code> 文を使用して、グローバル変数を変更する。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 最大接続数を変更
 SET GLOBAL max_connections = 1000;
 
 -- スロークエリログを有効化
 SET GLOBAL slow_query_log = ON;
 
 -- スロークエリ閾値を変更
 SET GLOBAL long_query_time = 1.0;
 
 -- InnoDBバッファプールサイズを変更 (動的変更可能)
 SET GLOBAL innodb_buffer_pool_size = 16 * 1024 * 1024 * 1024;
 </syntaxhighlight>
<br>
セッション変数を変更する場合は、<code>SET SESSION</code> または単に <code>SET</code> を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 現在のセッションのSQLモードを変更
 SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
 
 -- 現在のセッションのソートバッファを変更
 SET sort_buffer_size = 4 * 1024 * 1024;
 </syntaxhighlight>
<br>
ただし、<code>SET GLOBAL</code> で変更した値は、MySQLサーバを再起動すると元に戻る。<br>
永続化するには、設定ファイルに記述するか、<code>SET PERSIST</code> を使用する。<br>
<br>
また、一部の変数は動的変更できない。<br>
例として、<code>innodb_log_file_size</code> (MySQL 8.0.30未満) は、サーバの再起動が必要である。<br>
<br>
動的変更可能かどうかは、<code>SHOW VARIABLES</code> の出力または公式ドキュメントで確認できる。<br>
<br>
==== SET PERSISTでの永続化 (MySQL 8.0) ====
MySQL 8.0以降では、<code>SET PERSIST</code> 文により設定変更を永続化できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 設定を変更し、mysqld-auto.cnfに永続化
 SET PERSIST max_connections = 1000;
 
 -- 次回起動時のみ有効 (現在のセッションには影響しない)
 SET PERSIST_ONLY innodb_buffer_pool_size = 16 * 1024 * 1024 * 1024;
 </syntaxhighlight>
<br>
<code>SET PERSIST</code> で設定した値は、データディレクトリの <u>mysqld-auto.cnfファイル</u> に記録される。<br>
このファイルは、設定ファイルより優先して読み込まれる。<br>
<br>
<code>SET PERSIST</code> と <code>SET PERSIST_ONLY</code> の違いを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ SET PERSIST と SET PERSIST_ONLYの違い
|-
! 構文 !! 説明
|-
| <code>SET PERSIST</code> || 即座に設定を適用し、<u>mysqld-auto.cnfファイル</u> に永続化
|-
| <code>SET PERSIST_ONLY</code> || <u>mysqld-auto.cnfファイル</u> に永続化するが、現在のセッションには適用しない (次回起動時に有効)
|}
</center>
<br>
永続化された設定を削除するには、<code>RESET PERSIST</code> を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 -- 特定の設定を削除
 RESET PERSIST max_connections;
 
 -- 全ての永続化設定を削除
 RESET PERSIST;
 </syntaxhighlight>
<br>
永続化された設定一覧を確認するには、<code>performance_schema.persisted_variables</code> テーブルを参照する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT * FROM performance_schema.persisted_variables;
 </syntaxhighlight>
<br>

==== 現在の設定ファイルの確認 ====
MySQLサーバが実際に使用している設定ファイルを確認する方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="bash">
 # 設定ファイルの読み込み順序を確認
 mysqld --verbose --help | grep -A 1 "Default options"
 
 # 実際に有効な設定を表示
 my_print_defaults mysqld
 
 # 特定の設定ファイルを指定して起動
 mysqld --defaults-file=/etc/mysql/my-custom.cnf
 
 # 設定ファイルを無視して起動
 mysqld --no-defaults
 </syntaxhighlight>
<br>
MySQLサーバが起動済みの場合、エラーログに読み込まれた設定ファイルが記録される。<br>
<br>
 <syntaxhighlight lang="bash">
 # エラーログを確認
 tail -100 /var/log/mysql/error.log
 </syntaxhighlight>
<br><br>

== 推奨設定例 ==
==== 開発環境向け設定 ====
開発環境 (小規模、デバッグ重視) 向けの設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # 基本設定
 user=mysql
 pid-file=/var/run/mysqld/mysqld.pid
 socket=/var/lib/mysql/mysql.sock
 port=3306
 datadir=/var/lib/mysql
 
 # 文字コード
 character-set-server=utf8mb4
 collation-server=utf8mb4_general_ci
 
 # エラーログ
 log-error=/var/log/mysql/error.log
 
 # スロークエリログ (全クエリを記録)
 slow_query_log=ON
 slow_query_log_file=/var/log/mysql/slow-query.log
 long_query_time=0.5
 log_queries_not_using_indexes=ON
 
 # 一般クエリログ (開発環境のみ)
 general_log=ON
 general_log_file=/var/log/mysql/general.log
 
 # 接続設定
 max_connections=100
 skip-name-resolve
 
 # メモリ設定 (4[GB]メモリを想定)
 innodb_buffer_pool_size=2G
 innodb_log_buffer_size=16M
 
 # InnoDB設定
 innodb_flush_log_at_trx_commit=2
 innodb_flush_method=O_DIRECT
 innodb_file_per_table=ON
 
 # セキュリティ (開発環境では緩い設定)
 local_infile=OFF
 
 # SQLモード (厳格モード)
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
 
 [client]
 port=3306
 socket=/var/lib/mysql/mysql.sock
 default-character-set=utf8mb4
 </syntaxhighlight>
<br>
開発環境では、<u>スロークエリログ</u> と <u>一般クエリログ</u> を有効にして、クエリのデバッグを容易にする。<br>
<code>long_query_time=0.5</code> により、0.5秒以上かかるクエリを全て記録する。<br>
<br>
<code>innodb_flush_log_at_trx_commit=2</code> により、書き込み性能を向上させている。<br>
開発環境ではデータ損失リスクは許容できるため、この設定は問題ない。<br>
<br>
==== 本番環境向け設定 ====
本番環境 (大規模、高可用性、セキュリティ重視) 向けの設定例を以下に示す。<br>
<br>
 <syntaxhighlight lang="ini">
 [mysqld]
 # 基本設定
 user=mysql
 pid-file=/var/run/mysqld/mysqld.pid
 socket=/var/lib/mysql/mysql.sock
 port=3306
 datadir=/var/lib/mysql
 
 # サーバID (レプリケーション環境では必須)
 server-id=1
 
 # 文字コード
 character-set-server=utf8mb4
 collation-server=utf8mb4_0900_ai_ci
 
 # エラーログ
 log-error=/var/log/mysql/error.log
 
 # スロークエリログ
 slow_query_log=ON
 slow_query_log_file=/var/log/mysql/slow-query.log
 long_query_time=2.0
 log_queries_not_using_indexes=ON
 
 # バイナリログ (レプリケーション・PITR用)
 log-bin=/var/log/mysql/mysql-bin
 binlog_format=ROW
 binlog_expire_logs_seconds=604800
 max_binlog_size=500M
 sync_binlog=1
 
 # 接続設定
 port=3306
 bind-address=0.0.0.0
 max_connections=500
 max_connect_errors=1000000
 wait_timeout=600
 interactive_timeout=600
 connect_timeout=10
 max_allowed_packet=256M
 skip-name-resolve
 
 # メモリ設定 (32GBメモリを想定)
 innodb_buffer_pool_size=24G
 innodb_buffer_pool_instances=16
 innodb_log_buffer_size=64M
 table_open_cache=4000
 table_definition_cache=2000
 thread_cache_size=100
 
 # InnoDB設定
 innodb_flush_log_at_trx_commit=1
 innodb_flush_method=O_DIRECT
 innodb_file_per_table=ON
 innodb_autoinc_lock_mode=2
 
 # REDOログ設定 (MySQL 8.0.30以降)
 innodb_redo_log_capacity=2G
 
 # I/O設定 (SSD環境)
 innodb_io_capacity=2000
 innodb_io_capacity_max=4000
 innodb_read_io_threads=8
 innodb_write_io_threads=8
 
 # セキュリティ設定
 local_infile=OFF
 secure_file_priv=/var/lib/mysql-files/
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
 
 # パスワードポリシー
 validate_password.policy=STRONG
 validate_password.length=10
 validate_password.mixed_case_count=1
 validate_password.number_count=1
 validate_password.special_char_count=1
 
 # SSL/TLS設定 (証明書PATH)
 # ssl_ca=/etc/mysql/ssl/ca-cert.pem
 # ssl_cert=/etc/mysql/ssl/server-cert.pem
 # ssl_key=/etc/mysql/ssl/server-key.pem
 # require_secure_transport=ON
 
 # パフォーマンススキーマ
 performance_schema=ON
 
 [client]
 port=3306
 socket=/var/lib/mysql/mysql.sock
 default-character-set=utf8mb4
 
 [mysql]
 # プロンプトカスタマイズ
 prompt="\\u@\\h [\\d]> "
 
 [mysqldump]
 quick
 max_allowed_packet=256M
 </syntaxhighlight>
<br>
本番環境では、データ整合性を最優先するため、<code>innodb_flush_log_at_trx_commit=1</code> と <code>sync_binlog=1</code> を設定する。<br>
<br>
バイナリログは、レプリケーションとポイントインタイムリカバリ (PITR) のために必須である。<br>
<code>binlog_expire_logs_seconds=604800</code> により、7日間分のバイナリログを保持する。<br>
<br>
<code>skip-name-resolve</code> により、DNS逆引きを無効化してパフォーマンスを向上させている。<br>
<br>
セキュリティ設定として、<code>local_infile=OFF</code>、<code>secure_file_priv</code>、強力なパスワードポリシーを設定している。<br>
<br>
SSL/TLS設定は、パブリックネットワーク経由の接続を受け付ける場合に有効化する。<br>
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]