MySQL - 設定ファイル
概要
MySQLの設定ファイルは、MySQLサーバの動作を制御する重要な設定を記述するファイルである。
Linuxでは my.cnf、Windowsでは my.ini という名前が一般的に使用される。
設定ファイルには、サーバの起動オプション、メモリ割り当て、接続数制限、ログ設定、文字コード、セキュリティ設定等を記述できる。
これらのパラメータは、データベースのパフォーマンス、安定性、セキュリティに大きな影響を与える。
MySQLは複数の場所から設定ファイルを読み込み、後から読み込まれた設定が優先される。
設定ファイルは [mysqld]、[client]、[mysql] 等のセクション (グループ) に分けて記述される。
各セクションは、特定のMySQLプログラムに対応する設定を含む。
設定は、設定ファイルへの記述による静的設定と、SET GLOBAL による動的設定の2つの方法がある。
MySQL 8.0以降では、SET PERSIST により動的設定を永続化することも可能である。
適切な設定により、システムのワークロード (OLTP、OLAP、混在型) に最適化されたパフォーマンスを実現できる。
設定の確認には、SHOW VARIABLES 文 や mysqld --verbose --help コマンドを使用する。
設定ファイルの場所
MySQLは、複数の場所から設定ファイルを順次読み込む。
後から読み込まれた設定ファイルの値が、先に読み込まれた値を上書きする。
Linuxでの設定ファイルの場所
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 | 暗号化されたログイン情報 |
システム管理者は、通常 /etc/my.cnf または /etc/mysql/my.cnf にサーバ設定を記述する。
ユーザ個別設定は、~/.my.cnf に記述する。
環境変数 MYSQL_HOME を設定することにより、特定ディレクトリの設定ファイルを優先的に読み込むことができる。
Windowsでの設定ファイルの場所
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 | --defaults-extra-file で指定されたファイル
|
Windows環境では、%PROGRAMDATA%\MySQL\MySQL Server 8.0\my.ini が標準的な設定ファイルである。
MySQL公式インストーラは、インストール時にこのファイルを自動生成する。
%WINDIR% は、C:\Windows、BASEDIR は、C:\Program Files\MySQL\MySQL Server 8.0 である。
設定ファイルの読み込み順序の確認
現在のMySQL環境で実際に読み込まれる設定ファイルの順序を確認する方法を以下に示す。
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
実際に有効な設定値を確認するには、以下に示すコマンドを使用する。
このコマンドは、[mysqld] セクションで有効になっている全ての設定を表示する。
my_print_defaults mysqld
特定の設定ファイルのみを使用する場合は、--defaults-file オプションを使用する。
mysqld --defaults-file=/etc/mysql/my-custom.cnf
デフォルトの設定ファイルを無視する場合は、--no-defaults オプションを使用する。
mysqld --no-defaults
追加の設定ファイルを読み込む場合は、--defaults-extra-file オプションを使用する。
mysqld --defaults-extra-file=/etc/mysql/my-extra.cnf
設定ファイルの構造
MySQLの設定ファイルは、セクション (グループ) 単位で構成される。
各セクションは、[セクション名] で開始され、次のセクションまたはファイル末尾まで続く。
セクション (グループ)
下表に、MySQLの設定ファイルで使用される主要なセクションを示す。
| セクション名 | 適用対象 | 用途 |
|---|---|---|
| [mysqld] | mysqldサーバプロセス | サーバの動作設定 (メモリ、接続数、ログ等) |
| [mysql] | mysqlクライアント | クライアントの動作設定 (プロンプト、文字コード等) |
| [client] | 全てのMySQLクライアント | 全クライアント共通設定 (接続先、パスワード等) |
| [mysqldump] | mysqldumpコマンド | バックアップ時の設定 (オプション等) |
| [mysqld_safe] | mysqld_safe起動スクリプト | MySQLサーバの安全起動設定 |
| [mysqladmin] | mysqladminコマンド | 管理コマンドの設定 |
| [mysqlbinlog] | mysqlbinlogコマンド | バイナリログ解析の設定 |
| [mysqlimport] | mysqlimportコマンド | データインポートの設定 |
最も重要なセクションは、[mysqld] である。
このセクションには、MySQLサーバプロセスの動作を制御する全ての設定 を記述する。
[client] セクションは、全てのMySQLクライアントプログラム (mysql、mysqldump、mysqladmin等) に適用 される。
[mysql] セクションは、mysqlコマンドラインクライアントのみに適用 される。
設定の記述方法
設定ファイル内の各設定項目は、以下の形式で記述する。
基本的な記述形式を以下に示す。
- キー=値
- パラメータに値を設定
- キー
- ブール値パラメータを有効化 (
ONと同じ)
- ブール値パラメータを有効化 (
- # コメント または ; コメント
- コメント行
設定例を以下に示す。
[mysqld]
# サーバID設定
server-id=1
# ポート番号
port=3306
# バイナリログ有効化
log-bin=mysql-bin
# スロークエリログ有効化
slow-query-log
# スロークエリ閾値 (秒)
long_query_time=2.0
変数名のハイフン (-) と アンダースコア (_) は互換性がある。
log-bin と log_bin は同じ意味である。
不明なオプションを無視する場合は、loose- プレフィックスを使用する。
[mysqld]
# MySQL 8.0未満では無視される
loose-innodb_redo_log_capacity=1G
他の設定ファイルをインクルードする場合は、!include または !includedir ディレクティブを使用する。
[mysqld]
!include /etc/mysql/conf.d/mysql-custom.cnf
!includedir /etc/mysql/conf.d/
!includedir は、指定ディレクトリ内の.cnfファイル (Windowsでは、.ini および .cnf) を全て読み込む。
設定ファイルの優先順位
同じパラメータが複数の設定ファイルに記述されている場合、後から読み込まれた値が優先される。
例として、以下に示す2つの設定ファイルがある場合を考える。
# /etc/my.cnfファイル
[mysqld]
max_connections=100
# ~/.my.cnfファイル
[mysqld]
max_connections=500
この場合、~/.my.cnf が後から読み込まれるため、最終的な max_connections は500になる。
なお、コマンドライン引数は、設定ファイルより優先される。
mysqld --max-connections=1000
この場合、設定ファイルの値に関わらず、max_connections は1000になる。
主要パラメータ
接続関連
MySQLサーバへの接続に関連するパラメータを以下に示す。
| パラメータ | デフォルト | 説明 |
|---|---|---|
| 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逆引きを無効化 (高速化) |
接続関連の推奨設定例を以下に示す。
[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
| パラメータ | 説明 |
|---|---|
bind-address |
特定のIPアドレスからのみ接続を受け付ける場合に設定する。 0.0.0.0 は、全てのIPアドレスからの接続を許可する。 127.0.0.1 は、ローカルホストからのみ接続を許可する。 |
max_connections |
システムのメモリ量とワークロードに応じて設定する。 一般的なWebアプリケーションでは、100〜500 が適切である。 |
skip-name-resolve |
有効にすると、DNS逆引きが無効化され、接続時のオーバーヘッドが削減される。 ただし、ホスト名ベースの権限管理 ( GRANT 文でホスト名を使用) は使用できなくなることに注意する。
|
InnoDB関連
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ロックモード |
InnoDB関連の推奨設定例を以下に示す。
[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
| パラメータ | 説明 |
|---|---|
innodb_buffer_pool_size |
最も重要なInnoDBパラメータである。 専用データベースサーバでは、物理メモリの70〜80[%]を割り当てることを推奨する。 例: 16[GB] メモリのサーバでは、12G〜13G が適切である。 |
innodb_buffer_pool_instances |
バッファプールを複数のインスタンスに分割する。 バッファプールサイズが1GB以上の場合、デフォルトで8インスタンスに分割される。 高負荷環境では、インスタンス数を増やすことで競合を軽減できる。 |
innodb_flush_log_at_trx_commit |
ログのディスク書き込み方式を指定する。 0 : ログを1秒ごとにディスクに書き込む (高速だがクラッシュ時にデータ損失の可能性) 1 : トランザクションごとにログをディスクに書き込む (最も安全、デフォルト) 2 : トランザクションごとにOSバッファに書き込み、1秒ごとにディスクに書き込む (折衷案) |
innodb_flush_method |
ファイルI/Oの方式を指定する。 Linux環境では、 O_DIRECT を推奨する。O_DIRECT は、OSのページキャッシュをバイパスし、二重バッファリングを回避する。
|
innodb_io_capacity |
ストレージのIOPS性能に応じて設定する。 HDD環境では 200、SATA SSDでは 2000〜5000、NVMe SSDでは 10000 以上が適切である。 |
MySQL 8.0.30以降では、innodb_log_file_size と innodb_log_files_in_group が廃止され、innodb_redo_log_capacity に統合された。
クエリキャッシュ関連 (MySQL 5.7以前)
クエリキャッシュは、MySQL 8.0で完全に削除された機能である。
MySQL 5.7以前のバージョンでは、以下のパラメータを使用できる。
query_cache_type- クエリキャッシュの有効化
- 0: 無効、1: 有効、2: DEMAND
query_cache_size- クエリキャッシュのサイズ (バイト)
query_cache_limit- キャッシュ可能な最大クエリサイズ
MySQL 5.7以前の設定例を以下に示す。
[mysqld]
# MySQL 5.7以前のみ有効
query_cache_type=1
query_cache_size=256M
query_cache_limit=2M
ただし、クエリキャッシュはスケーラビリティの問題により、高負荷環境ではパフォーマンスを低下させる。
MySQL 5.7以前でも、クエリキャッシュを無効化することを推奨する。
MySQL 8.0以降では、クエリキャッシュ機能は完全に削除されている。
ログ関連
下表に、MySQLの各種ログ機能を制御するパラメータを示す。
| パラメータ | デフォルト | 説明 |
|---|---|---|
| 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 | バイナリログの同期頻度 |
ログ関連の推奨設定例を以下に示す。
[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
| 設定項目 | 説明 |
|---|---|
log-error |
MySQLサーバの起動エラー、警告、重要なメッセージを記録する。 必ず有効化し、定期的に確認すること。 |
slow_query_log |
実行時間が long_query_time を超えるクエリを記録する。パフォーマンスチューニングの際に必須のログである。 |
log_queries_not_using_indexes |
有効にすると、インデックスを使用しないクエリもスロークエリログに記録される。 インデックス不足によるパフォーマンス問題を早期に発見できる。 |
log-bin |
バイナリログを有効にする。 レプリケーション、ポイントインタイムリカバリ (PITR) に必須である。 |
binlog_format |
バイナリログの形式を指定する。ROW : 行ベース (デフォルト、最も安全)STATEMENT : SQL文ベース (ログサイズ小、非決定的関数で問題)MIXED : 自動選択 (状況に応じてROW/STATEMENT切り替え)
|
sync_binlog |
バイナリログの同期頻度を指定する。1 はトランザクションごとに同期 (最も安全)、0 はOSに任せる。(高速だが安全性低)
|
general_log |
全てのクエリを記録するため、大量のディスクI/Oが発生する。 デバッグ時のみ有効化し、本番環境では無効にすることを強く推奨する。 |
文字コード関連
下表に、MySQLの文字コードと照合順序を制御するパラメータを示す。
| パラメータ | 説明 |
|---|---|
character-set-server |
サーバのデフォルト文字コード (デフォルト: utf8mb4 in 8.0)
|
collation-server |
サーバのデフォルト照合順序 (デフォルト: utf8mb4_0900_ai_ci in 8.0)
|
character-set-client |
クライアント接続の文字コード |
character-set-connection |
接続時の文字コード |
character-set-results |
結果セットの文字コード |
init_connect |
接続時に実行されるSQL文 |
文字コード関連の推奨設定例を以下に示す。
[mysqld]
# サーバ文字コード (UTF-8 4バイト対応)
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
# クライアント接続時の文字コード設定
init_connect='SET NAMES utf8mb4'
[client]
# クライアント側のデフォルト文字コード
default-character-set=utf8mb4
utf8mb4 は、4バイトUTF-8文字 (絵文字等) をサポートする文字コードである。
MySQL 8.0以降では、utf8mb4 がデフォルトである。
utf8 (エイリアス: utf8mb3) は、最大3バイトまでのUTF-8文字のみをサポートする。
絵文字や一部の漢字が格納できないため、utf8mb4 の使用を推奨する。
照合順序は、文字列の比較・ソート方法を定義する。
主要な照合順序を以下に示す。
| 照合順序 | 説明 |
|---|---|
utf8mb4_general_ci |
高速、大文字小文字を区別しない |
utf8mb4_unicode_ci |
より正確な言語ルール、やや低速 |
utf8mb4_0900_ai_ci |
MySQL 8.0デフォルト、高速かつ正確 |
utf8mb4_bin |
バイナリ比較、大文字小文字を区別 |
文字コード設定の詳細については、MySQL - 文字コードと照合順序のページを参照すること。
セキュリティ関連
MySQLのセキュリティを強化するパラメータを以下に示す。
| パラメータ | デフォルト | 説明 |
|---|---|---|
| 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ソケット接続を強制 |
セキュリティ関連の推奨設定例を以下に示す。
[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
| パラメータ | 説明 |
|---|---|
local_infile |
クライアント側のファイルをサーバに読み込む機能である。 セキュリティリスクがあるため、MySQL 8.0以降ではデフォルトで無効化されている。 |
secure_file_priv |
LOAD DATA INFILE、SELECT ... INTO OUTFILE が使用できるディレクトリを制限する。空文字列を設定すると制限なし、 NULL を設定すると完全無効化される。
|
sql_mode |
SQLの厳格性を制御する。STRICT_TRANS_TABLES を含めることにより、不正なデータの挿入を防ぐことができる。
|
require_secure_transport |
有効にすると、SSL/TLSまたはUNIXソケット経由の接続のみを許可する。 パブリックネットワーク経由の接続を受け付ける場合は、SSL/TLS設定と併用することを推奨する。 |
メモリ関連
MySQLのメモリ使用量を制御するパラメータを以下に示す。
| パラメータ | デフォルト | スコープ | 説明 |
|---|---|---|---|
| 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テーブルの最大サイズ |
メモリパラメータは、グローバルスコープとセッションスコープに分類される。
グローバルスコープのパラメータは、サーバ全体で共有される。
セッションスコープのパラメータは、接続ごとに割り当てられる。
総メモリ使用量の概算式を以下に示す。
総メモリ使用量 ≈ innodb_buffer_pool_size
+ key_buffer_size
+ max_connections * (
sort_buffer_size
+ read_buffer_size
+ read_rnd_buffer_size
+ join_buffer_size
+ thread_stack
)
+ その他グローバルバッファ
例として、以下に示す設定の場合のメモリ使用量を計算する。
innodb_buffer_pool_size=8Gmax_connections=500sort_buffer_size=2Mread_buffer_size=2Mread_rnd_buffer_size=2Mjoin_buffer_size=2Mthread_stack=256K
計算式を以下に示す。
総メモリ = 8GB + 500 * (2MB + 2MB + 2MB + 2MB + 0.25MB)
= 8GB + 500 * 8.25MB
= 8GB + 4.125GB
≈ 12.1GB
セッションバッファは、実際には必要な時にのみ割り当てられるため、最大値を大きくしすぎないよう注意する。
パフォーマンスチューニング
MySQLのパフォーマンスは、設定ファイルのパラメータ調整により大きく改善できる。
チューニングの基本方針
パフォーマンスチューニングの基本方針を以下に示す。
- 測定なしにチューニングしない
SHOW STATUS、SHOW VARIABLES、MySQL - EXPLAIN で現状を把握する
- 1つずつ変更して効果を測定
- 複数のパラメータを同時に変更すると、効果の測定が困難になる
- ワークロードに応じた設定
- OLTP、OLAP、混在型でチューニング方針は異なる
- ハードウェアリソースを考慮
- メモリ、CPU、ストレージ性能に応じた設定が必要
- スロークエリログを活用
- パフォーマンス問題の大半はクエリに起因する
パフォーマンスモニタリングのクエリ例を以下に示す。
-- 接続状態の確認
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%';
OLTP向け推奨設定
OLTP (Online Transaction Processing) ワークロード向けの推奨設定を以下に示す。
OLTPでは、多数の小規模トランザクションを高速に処理することが求められる。
[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
OLTP環境では、innodb_flush_log_at_trx_commit=1 を維持してデータ整合性を確保する。
ただし、パフォーマンスが重要でデータ損失許容可能な場合は、2 に設定することで高速化できる。
innodb_autoinc_lock_mode=2 は、AUTO_INCREMENT値の高速割り当てを有効にする。
レプリケーション環境でSTATEMENTベースのバイナリログを使用する場合は、1 に設定する必要がある。
大規模データ処理向け推奨設定
大規模データ処理 (バッチ処理、データウェアハウス) 向けの推奨設定を以下に示す。
[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
大規模データ処理では、innodb_flush_log_at_trx_commit=2 に設定して書き込み性能を向上させる。
処理完了後にデータの整合性を確認することで、データ損失リスクを軽減できる。
セッションバッファ (sort_buffer_size、join_buffer_size 等) を増やすことにより、大規模ソート・JOIN処理が高速化される。
ただし、これらは接続ごとに割り当てられるため、設定値を大きくしすぎるとメモリ不足になる可能性がある。
バルクインサート時は、innodb_autoinc_lock_mode=2 と bulk_insert_buffer_size を増やすことで高速化できる。
レプリケーションが不要な場合は、バイナリログを無効化することでI/Oオーバーヘッドを削減できる。
設定の確認・変更方法
MySQLの設定は、起動時の設定ファイル読み込みによる静的設定 と 実行時の動的設定 の2種類がある。
SHOW VARIABLESでの確認
現在有効な設定値を確認するには、SHOW VARIABLES 文を使用する。
-- 全ての設定変数を表示
SHOW VARIABLES;
-- 特定のパターンにマッチする変数を表示
SHOW VARIABLES LIKE 'innodb_buffer%';
-- 文字コード関連の変数を表示
SHOW VARIABLES LIKE 'character%';
-- タイムアウト関連の変数を表示
SHOW VARIABLES LIKE '%timeout%';
-- 特定の変数を表示
SHOW VARIABLES WHERE Variable_name = 'max_connections';
グローバル変数のみを表示する場合は、SHOW GLOBAL VARIABLES を使用する。
セッション変数のみを表示する場合は、SHOW SESSION VARIABLES を使用する。
-- グローバル変数を表示
SHOW GLOBAL VARIABLES LIKE 'max_connections';
-- セッション変数を表示
SHOW SESSION VARIABLES LIKE 'sql_mode';
performance_schemaを使用して、変数情報を取得することもできる。
SELECT * FROM performance_schema.global_variables
WHERE VARIABLE_NAME LIKE 'innodb_buffer%';
SET GLOBALでの動的変更
MySQLの多くの設定は、サーバの再起動なしで動的に変更できる。
SET GLOBAL 文を使用して、グローバル変数を変更する。
-- 最大接続数を変更
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;
セッション変数を変更する場合は、SET SESSION または単に SET を使用する。
-- 現在のセッションのSQLモードを変更
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
-- 現在のセッションのソートバッファを変更
SET sort_buffer_size = 4 * 1024 * 1024;
ただし、SET GLOBAL で変更した値は、MySQLサーバを再起動すると元に戻る。
永続化するには、設定ファイルに記述するか、SET PERSIST を使用する。
また、一部の変数は動的変更できない。
例として、innodb_log_file_size (MySQL 8.0.30未満) は、サーバの再起動が必要である。
動的変更可能かどうかは、SHOW VARIABLES の出力または公式ドキュメントで確認できる。
SET PERSISTでの永続化 (MySQL 8.0)
MySQL 8.0以降では、SET PERSIST 文により設定変更を永続化できる。
-- 設定を変更し、mysqld-auto.cnfに永続化
SET PERSIST max_connections = 1000;
-- 次回起動時のみ有効 (現在のセッションには影響しない)
SET PERSIST_ONLY innodb_buffer_pool_size = 16 * 1024 * 1024 * 1024;
SET PERSIST で設定した値は、データディレクトリの mysqld-auto.cnfファイル に記録される。
このファイルは、設定ファイルより優先して読み込まれる。
SET PERSIST と SET PERSIST_ONLY の違いを以下に示す。
| 構文 | 説明 |
|---|---|
SET PERSIST |
即座に設定を適用し、mysqld-auto.cnfファイル に永続化 |
SET PERSIST_ONLY |
mysqld-auto.cnfファイル に永続化するが、現在のセッションには適用しない (次回起動時に有効) |
永続化された設定を削除するには、RESET PERSIST を使用する。
-- 特定の設定を削除
RESET PERSIST max_connections;
-- 全ての永続化設定を削除
RESET PERSIST;
永続化された設定一覧を確認するには、performance_schema.persisted_variables テーブルを参照する。
SELECT * FROM performance_schema.persisted_variables;
現在の設定ファイルの確認
MySQLサーバが実際に使用している設定ファイルを確認する方法を以下に示す。
# 設定ファイルの読み込み順序を確認
mysqld --verbose --help | grep -A 1 "Default options"
# 実際に有効な設定を表示
my_print_defaults mysqld
# 特定の設定ファイルを指定して起動
mysqld --defaults-file=/etc/mysql/my-custom.cnf
# 設定ファイルを無視して起動
mysqld --no-defaults
MySQLサーバが起動済みの場合、エラーログに読み込まれた設定ファイルが記録される。
# エラーログを確認
tail -100 /var/log/mysql/error.log
推奨設定例
開発環境向け設定
開発環境 (小規模、デバッグ重視) 向けの設定例を以下に示す。
[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
開発環境では、スロークエリログ と 一般クエリログ を有効にして、クエリのデバッグを容易にする。
long_query_time=0.5 により、0.5秒以上かかるクエリを全て記録する。
innodb_flush_log_at_trx_commit=2 により、書き込み性能を向上させている。
開発環境ではデータ損失リスクは許容できるため、この設定は問題ない。
本番環境向け設定
本番環境 (大規模、高可用性、セキュリティ重視) 向けの設定例を以下に示す。
[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
本番環境では、データ整合性を最優先するため、innodb_flush_log_at_trx_commit=1 と sync_binlog=1 を設定する。
バイナリログは、レプリケーションとポイントインタイムリカバリ (PITR) のために必須である。
binlog_expire_logs_seconds=604800 により、7日間分のバイナリログを保持する。
skip-name-resolve により、DNS逆引きを無効化してパフォーマンスを向上させている。
セキュリティ設定として、local_infile=OFF、secure_file_priv、強力なパスワードポリシーを設定している。
SSL/TLS設定は、パブリックネットワーク経由の接続を受け付ける場合に有効化する。
