MySQL 5.7 の /etc/my.cnf パラメータの紹介

[クライアント]
ポート = 3306
デフォルトの文字セット=utf8mb4
ソケット=/tmp/mysql.sock

[mysql]
#prompt="\\u@\\h :\\d \\r:\\m:\\s> "
プロンプト="\\u@\\h \\R:\\m:\\s [\\d]> "
#自動再ハッシュなし
自動再ハッシュ
デフォルトの文字セット=utf8mb4
ソケット=/tmp/mysql.sock

[mysqld]
##########################
＃ まとめ
##########################
skip_name_resolve = オン
#許可テーブルをスキップする
#ネットワークをスキップ

バインドアドレス = 0.0.0.0
ポート = 3306
ベースディレクトリ=/usr/local/mysql
データディレクトリ=/data/mysql
ソケット=/tmp/mysql.sock
tmpdir = /tmp
pidファイル=/tmp/mysqld.pid

タイムスタンプの明示的なデフォルト=1
小文字テーブル名=1

テーブルオープンキャッシュ = 8000
transaction_isolation = '繰り返し読み取り'

##########################
＃ タイムアウト
##########################
接続タイムアウト = 20
待機タイムアウト = 300
インタラクティブタイムアウト = 300

##########################
＃繋がり
##########################
最大接続数 = 2000
最大ユーザー接続数 = 1900
最大接続エラー数 = 100000
最大許容パケット = 1G

##########################
# 文字セット
##########################
文字セットサーバー = utf8mb4
照合サーバー = utf8mb4_bin

##########################
# ログビン
##########################
サーバーID = 1
log_bin = mysql-bin
# 行、ステートメント、混合
binlog_format = 行
同期バイナリログ = 1
有効期限切れログ日数 = 7
binlog_cache_size = 128m
最大バイナリログキャッシュサイズ = 512m
最大バイナリログサイズ = 256M
マスター情報リポジトリ=テーブル
ログスレーブ更新=オン
binlog_checksum=なし

#binlog_ignore_db=情報スキーマ
#binlog_ignore_db=mysql
#binlog_ignore_db=パフォーマンススキーマ
#binlog_ignore_db=sys
#binlog_do_do=
replicate_ignore_db=情報スキーマ
レプリケート_ignore_db=mysql
replicate_ignore_db=パフォーマンススキーマ
複製無視db=sys
#レプリケートする_db=

##########################
#gtid
##########################
gtid_mode = オン
強制GTID一貫性 = オン

##########################
# スレーブパラレル
##########################
スレーブ_ネット_タイムアウト=60
スレーブパラレルタイプ=LOGICAL_CLOCK
スレーブ並列ワーカー = 4

##########################
# ログリレー
##########################
リレーログ情報リポジトリ=テーブル
リレーログ = mysql リレー bin
リレーログパージ = オン
リレーログリカバリ = オン
最大リレーログサイズ = 1G

##########################
# ログエラー
##########################
log_error=/data/mysql/mysqld_error.log

##########################
# ログが遅い
##########################
slow_query_log = オン
slow_query_log_file = /data/mysql/mysqld_slow.log
長いクエリ時間 = 2
log_queries_not_using_indexes = オン

##########################
# ログ一般
##########################
general_log = オン
一般ログファイル = /data/mysql/mysqld_gener.log


##########################
# スレッドプール、エンタープライズエディション
##########################
#thread_handling=スレッドプール
#thread_handling=接続ごとに 1 つのスレッド
#スレッドプールオーバーサブスクライブ=8 

##########################
# インデックス
##########################
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=2
innodb_adaptive_flushing=オン
innodb_file_per_table=1
innodb_log_file_size=1024M
innodb_log_buffer_size=64M
最大準備済みステートメント数=150000
innodb_flush_log_at_trx_commit=1
innodb_max_dirty_pages_pct=50

##########################
# パスワードポリシー
# 初期化後 
##########################
#validate_password_policy=0
#validate_password_length=4     
#validate_password_mixed_case_count=0
#パスワードの検証回数=0
#validate_password_special_char_count=0

[クライアント]
ポート = 3306
ソケット = /tmp/mysql.sock

[mysqld]
ポート = 3306
ソケット = /tmp/mysql.sock

ベースディレクトリ = /usr/local/mysql
データディレクトリ = /data/mysql
pid ファイル = /data/mysql/mysql.pid
ユーザー = mysql
バインドアドレス = 0.0.0.0
server-id = 1 # は、このマシンのシリアル番号が 1 であることを示します。これは通常、マスター skip-name-resolve を意味します。
# MySQL が外部接続の DNS 解決を実行しないようにします。このオプションを使用すると、MySQL が DNS 解決に費やす時間を削減できます。ただし、このオプションをオンにすると、
# すべてのリモートホスト接続認​​証は IP アドレスモードを使用する必要があります。そうしないと、MySQL は接続要求を正常に処理できません。#skip-networking

バックログ = 600
# MySQL が持つことができる接続の数。これは、メインの MySQL スレッドが短時間に大量の接続要求を受け取ったときに発生します。
# その後、メイン スレッドは接続を確認し、新しいスレッドを開始するのに少し時間がかかります (短い時間ではありますが)。 back_log 値は、MySQL が一時的に新しいリクエストへの応答を停止するまでにスタックできるリクエストの数を示します。
# 短期間に多数の接続が予想される場合は、この値を増やすことをお勧めします。つまり、MySQL 接続データが max_connections に達すると、新しい受信リクエストはスタックに保存されます。
# リソースを解放するために接続を待機しています。スタックの数は back_log です。待機中の接続数が back_log を超えると、接続リソースは付与されません。
# さらに、この値 (back_log) は、着信 TCP/IP 接続に対するオペレーティング システムのリスニング キューのサイズによって制限されます。
# ご使用のオペレーティング システムには、このキューのサイズに対する独自の制限があります (この変数の最大値については、OS のドキュメントを確認してください)。back_log を OS の制限よりも高く設定しても効果はありません。

最大接続数 = 1000
# MySQL の最大接続数。サーバーに多数の同時接続要求がある場合は、この値を増やして並列接続数を増やすことをお勧めします。もちろん、これはマシンのサポート能力に基づいています。接続数が多いと、MySQL は接続ごとに接続バッファーを提供するため、メモリの消費量が多くなります。したがって、この値を適切に調整し、盲目的に値を増やすべきではありません。 「conn%」ワイルドカードを使用して、現在の状態の接続数を表示し、この値のサイズを判断できます。

最大接続エラー数 = 6000
# 同じホストに対して、このパラメータで指定された数よりも多くの中断されたエラー接続がある場合、そのホストは接続を禁止されます。ホストのブロックを解除するには、FLUSH HOST を実行します。

オープンファイル制限 = 65535
# MySQL オープンファイル記述子の制限。デフォルトの最小値は 1024 です。open_files_limit が設定されていない場合は、max_connections*5 と ulimit -n のどちらか大きい方の値を比較します。
# open_file_limitが設定されている場合は、open_files_limitとmax_connections*5の値を比較し、大きい方を使用します。

テーブルオープンキャッシュ = 128
# MySQL がテーブルを開くたびに、一部のデータが table_open_cache キャッシュに読み込まれます。MySQL がこのキャッシュ内に対応する情報を見つけられない場合は、ディスクから読み取ります。デフォルト値: 64
# システムに 200 の同時接続があると仮定すると、このパラメータは 200*N に設定する必要があります (N は各接続に必要なファイル記述子の数)。
# table_open_cache が大きな値に設定されている場合、システムが多数のファイル記述子を処理できない場合、クライアントは接続に失敗します。max_allowed_pa​​cket = 4M
# 受け入れられるパケット サイズ。追加のメモリは必要な場合にのみ割り当てられるため、この変数の値を増やしても安全です。たとえば、MySQLd は、長いクエリを発行した場合、または MySQLd が大きな結果行を返す必要がある場合にのみ、より多くのメモリを割り当てます。
# この変数のデフォルト値が小さいのは、クライアントとサーバー間のエラー パケットをキャッチし、誤って大きなパケットを使用したためにメモリがオーバーフローしないようにするための予防策です。

binlog_cache_size = 1M
# トランザクションがコミットされていない場合、生成されたログはキャッシュに記録されます。トランザクションをコミットする必要がある場合、ログはディスクに保持されます。デフォルトのbinlog_cache_sizeは32Kです

最大ヒープテーブルサイズ = 8M
# ユーザーが作成できるメモリ テーブルのサイズを定義します。この値は、メモリ テーブルの最大行値を計算するために使用されます。この変数はtmp_table_size = 16Mの動的な変更をサポートします。
# MySQL ヒープ テーブル バッファ サイズ。すべての結合は 1 つの DML 命令で実行され、ほとんどの結合は一時テーブルなしでも実行できます。
# ほとんどの一時テーブルはメモリベース (HEAP) テーブルです。レコード長 (すべての列の長さの合計) が大きい一時テーブル、または BLOB 列を含むテーブルはディスクに保存されます。
# 内部ヒープ テーブルのサイズが tmp_table_size を超える場合、MySQL は必要に応じてメモリ内ヒープ テーブルをハード ディスク ベースの MyISAM テーブルに自動的に変更できます。 tmp_table_size オプションを設定することで、一時テーブルのサイズを増やすこともできます。つまり、この値を増やすと、MySQL はヒープ テーブルのサイズも増やし、結合クエリの速度を向上させることができます。read_buffer_size = 2M
# MySQL 読み取りバッファ サイズ。テーブルの順次スキャンの要求により読み取りバッファが割り当てられ、MySQL はそれにメモリ バッファを割り当てます。 read_buffer_size 変数はこのバッファのサイズを制御します。
# テーブルに対するシーケンシャルスキャン要求が非常に頻繁に発生し、頻繁なスキャンが遅すぎると思われる場合は、この変数の値とメモリバッファサイズを増やすことでパフォーマンスを向上させることができます。read_rnd_buffer_size = 8M
# MySQL ランダム読み取りバッファ サイズ。行が任意の順序 (たとえば、ソートされた順序) で読み取られる場合、ランダム読み取りバッファーが割り当てられます。ソートクエリを実行する場合、
# MySQL はディスク検索を回避し、クエリ速度を向上させるために、最初にバッファをスキャンします。大量のデータをソートする必要がある場合は、この値を適切に増やすことができます。ただし、MySQLはクライアント接続ごとにこのバッファスペースを割り当てるため、過度のメモリオーバーヘッドを避けるためにこの値を適切に設定する必要があります。sort_buffer_size = 8M
# MySQL がソートを実行するために使用するバッファのサイズ。 ORDER BY を高速化したい場合は、まず、MySQL で追加のソートフェーズではなくインデックスを使用できるかどうかを確認します。
# そうでない場合は、sort_buffer_size変数のサイズを増やしてみてください。join_buffer_size = 8M
# 共同クエリ操作に使用できるバッファサイズ。sort_buffer_size と同様に、このパラメータに対応する割り当てメモリも各接続に排他的です。thread_cache_size = 8
# この値 (デフォルト 8) は、キャッシュ内で再利用できるスレッドの数を示します。切断時にキャッシュ内にまだスペースがある場合は、クライアントのスレッドがキャッシュに格納されます。
# スレッドが再度要求された場合、要求はキャッシュから読み込まれます。キャッシュが空の場合、または新しい要求の場合は、スレッドが再作成されます。新しいスレッドが多数ある場合は、
# この値を増やすと、システム パフォーマンスが向上します。Connections と Threads_created 状態の変数を比較すると、この変数の役割がわかります。 （–>は調整する値を示します）
# 物理メモリの設定に応じて、ルールは次のようになります。
# 1G —> 8
# 2G —> 16
# 3G —> 32
# 3G より大きい —> 64

クエリキャッシュサイズ = 8M
#MySQL クエリ バッファ サイズ (MySQL は 4.0.1 以降でクエリ バッファ メカニズムを提供しています) クエリ バッファを使用して、MySQL は SELECT ステートメントとクエリ結果をバッファに格納します。
# 今後は、同じ SELECT ステートメント (大文字と小文字が区別されます) の結果はバッファーから直接読み取られるようになります。 MySQL ユーザーマニュアルによると、クエリバッファを使用すると最大 238% の効率を達成できます。
# ステータス値「Qcache_%」を確認することで、query_cache_size 設定が適切かどうかを知ることができます。Qcache_lowmem_prunes の値が非常に大きい場合は、バッファリングが不十分になることが頻繁にあることを示しています。
# Qcache_hits の値も非常に大きい場合は、クエリ バッファが非常に頻繁に使用されていることを意味し、バッファ サイズを増やす必要があります。Qcache_hits の値が大きくない場合は、クエリの繰り返し率が非常に低いことを意味します。
# この場合、クエリ バッファを使用すると効率に影響するため、クエリ バッファを使用しないことを検討できます。さらに、SELECT ステートメントに SQL_NO_CACHE を追加すると、クエリ バッファー query_cache_limit = 2M が使用されていないことを明示的に示すことができます。
# 単一のクエリで使用できるバッファサイズを指定します。デフォルトは 1M です。

キーバッファサイズ = 4M
#インデックス作成に使用するバッファ サイズを指定します。このサイズを可能な限り大きくすると、インデックス作成の処理 (すべての読み取りと複数の書き込み) が改善されます。大きすぎると、
# システムはページングを開始し、速度が大幅に低下します。メモリが約 4GB のサーバーの場合、このパラメータは 384M または 512M に設定できます。ステータス値Key_read_requestsとKey_readsをチェックすることで、
# key_buffer_size の設定が適切かどうかを知ることができます。 key_reads/key_read_requests の比率は可能な限り低くする必要があります。
# 少なくとも 1:100、1:1000 が望ましいです (上記のステータス値は、SHOW STATUS LIKE 'key_read%' を使用して取得できます)。注意: このパラメータ値が高すぎると、サーバーの全体的な効率が低下します。ft_min_word_len = 4
# 単語分割語彙の最小長、デフォルトは 4

transaction_isolation = 繰り返し読み取り
#MySQL は 4 つのトランザクション分離レベルをサポートしています。
# READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。
# 指定しない場合、MySQL はデフォルトで REPEATABLE-READ になり、ORACLE はデフォルトで READ-COMMITTED になります。

log_bin = mysql-bin
binlog_format = 混合
expire_logs_days = 30 #30日以上経過したバイナリログを削除 log_error = /data/mysql/mysql-error.log #エラーログのパス slow_query_log = 1
long_query_time = 1 #スロークエリ時間が1秒を超える場合はスロークエリです slow_query_log_file = /data/mysql/mysql-slow.log

パフォーマンススキーマ = 0
タイムスタンプの明示的なデフォルト

#lower_case_table_names = 1 #大文字と小文字を区別しない skip-external-locking #外部ロックを回避するための MySQL オプション。このオプションはデフォルトで有効になっています default-storage-engine = InnoDB #デフォルトのストレージエンジン innodb_file_per_table = 1
# InnoDB は独立テーブルスペース モードです。各データベースの各テーブルはデータ スペースを生成します。# 独立テーブルスペースの利点:
＃1。各テーブルには独自のテーブルスペースがあります。
＃2。各テーブルのデータとインデックスは、独自のテーブルスペースに保存されます。
＃3。単一のテーブルを異なるデータベース間で移動できます。
＃4。スペースは再利用できます (テーブルスペース自体を再利用できないテーブル削除操作を除く)
# 欠点:
# 単一テーブルのサイズが100Gを超えるなど大きすぎる
＃ 結論は：
# 共有テーブルスペースには、挿入操作での利点がほとんどありません。他のものはどれも独立した表領域ほどパフォーマンスが良くありません。独立した表領域を有効にする場合は、innodb_open_files を適切に調整してください。

innodb_open_files = 500
# Innodb が開くことができるテーブルのデータを制限します。データベースにテーブルが多数ある場合は、この値を増やしてください。デフォルト値は300です

innodb_buffer_pool_size = 64M
# InnoDB は、MyISAM とは異なり、バッファー プールを使用してインデックスと生データを格納します。
# 設定値を大きくするほど、テーブル内のデータにアクセスするために必要なディスク I/O が少なくなります。
# スタンドアロン データベース サーバーでは、この変数をサーバーの物理メモリ サイズの 80% に設定できます。
# あまり大きく設定しないでください。大きく設定すると、物理メモリの競合により、オペレーティング システムでページング ジッターが発生する可能性があります。
# 32ビットシステムでは、プロセスごとに2〜3.5Gのユーザーレベルメモリに制限される可能性があることに注意してください。
# あまり高く設定しないでください。

innodb_write_io_threads = 4
innodb_read_io_threads = 4
# innodb はバックグラウンド スレッドを使用して、データ ページの読み取りおよび書き込み I/O (入力および出力) 要求を処理します。CPU コアの数に応じて変更します。デフォルトは 4 です。
# 注意: これら 2 つのパラメータは動的な変更をサポートしていません。パラメータを my.cnf に追加し、変更後に MySQL サービスを再起動する必要があります。許可される値の範囲は 1 ～ 64 です。

innodb_thread_concurrency = 0
# デフォルト設定は 0 で、同時接続数に制限がないことを意味します。CPU のマルチコア処理能力をより有効に活用し、同時接続数を増やすには、0 に設定することをお勧めします innodb_purge_threads = 1
# InnoDB のクリーンアップ操作は、不要なデータを定期的にリサイクルするタイプの操作です。以前のバージョンでは、パージ操作はメイン スレッドの一部であったため、実行中に他のデータベース操作がブロックされる可能性がありました。
# MySQL 5.5.X 以降では、この操作は独立したスレッドで実行され、より多くの同時実行性がサポートされます。ユーザーは、innodb_purge_threads 構成パラメータを設定することで、パージ操作に別のスレッドを使用するかどうかを選択できます。デフォルトでは、パラメータは 0 (別のスレッドを使用しない) に設定されています。1 に設定すると、別のパージ スレッドを使用することを意味します。推奨値: 1

innodb_flush_log_at_trx_commit = 2
# 0: innodb_flush_log_at_trx_commit の値が 0 の場合、ログ バッファーは 1 秒ごとにディスクにフラッシュされ、トランザクションがコミットされたときに操作は実行されません (実行は MySQL マスター スレッドによって行われます)。
# メイン スレッドは、REDO ログ バッファをディスク上の REDO ログ ファイル (REDO LOG) に 1 秒ごとに書き込みます。トランザクションがコミットされたかどうかに関係なく、デフォルトのログファイルはib_logfile0、ib_logfile1です。
# 1: デフォルト値の 1 に設定すると、トランザクションがコミットされるたびにログ バッファーがログにフラッシュされます。
# 2: 2 に設定すると、トランザクションがコミットされるたびにログが書き込まれますが、フラッシュ操作は実行されません。ログ ファイルは 1 秒ごとに更新されます。データが毎秒 100% ディスクにフラッシュされるという保証はなく、これはプロセスのスケジュールに依存することに注意してください。
# トランザクションがコミットされるたびに、データがトランザクション ログに書き込まれます。ここでの書き込み操作はファイル システムの書き込み操作を呼び出すだけであり、ファイル システムにはキャッシュがあるため、この書き込みではデータが物理ディスクに書き込まれたことを保証できません。# デフォルト値の 1 は完全な ACID を保証するためのものです。もちろん、この設定項目を 1 以外の値に設定してパフォーマンスを向上させることもできますが、システムがクラッシュすると 1 秒分のデータが失われます。
# 0 に設定すると、mysqld プロセスがクラッシュしたときにトランザクションの最後の 1 秒が失われます。 2 に設定すると、オペレーティング システムがクラッシュした場合、または停電が発生した場合にのみ、最後の 1 秒のデータが失われます。 InnoDB はリカバリ時にこの値を無視します。
# 概要# 1 に設定するのはもちろん最も安全ですが、パフォーマンスは最悪になります (他の 2 つのパラメーターと比較すると、許容できないほどではありません)。データの一貫性と整合性が必要ない場合は、2 に設定できます。高同時実行ログ サーバーなど、パフォーマンスのみが必要な場合は、0 に設定してパフォーマンスを高めます。innodb_log_buffer_size = 2M
# このパラメータは、これらのログ ファイルで使用されるメモリ サイズを M 単位で決定します。バッファを大きくするとパフォーマンスは向上しますが、予期しない障害が発生するとデータが失われます。 MySQL開発者は1〜8 MBに設定することを推奨しています innodb_log_file_size = 32 MB
# このパラメータはデータ ログ ファイルのサイズを決定します。設定値を大きくするとパフォーマンスが向上しますが、障害が発生したデータベースの回復にかかる時間も長くなります。innodb_log_files_in_group = 3
# パフォーマンスを向上させるために、MySQL はログ ファイルを複数のファイルに循環的に書き込むことができます。推奨設定: 3

innodb_max_dirty_pages_pct = 90
# InnoDBメインスレッドはキャッシュプール内のデータを更新して、ダーティデータ比率を90%未満にします

innodb_lock_wait_timeout = 120 
#InnoDB トランザクションがロールバックされる前にロックを待機できる秒単位のタイムアウト。 InnoDB は、独自のロック テーブルでトランザクションのデッドロックを自動的に検出し、トランザクションをロールバックします。 InnoDB は、LOCK TABLES ステートメントでロック設定を認識します。デフォルト値は50秒です。bulk_insert_buffer_size = 8M
# バッチ挿入キャッシュ サイズ。このパラメータは MyISAM ストレージ エンジン用です。一度に 100 ～ 1000 件以上のレコードを挿入する際の効率を向上するのに適しています。デフォルト値は 8M です。データ量に応じて倍増可能です。

myisam_sort_buffer_size = 8M
# MyISAM は、テーブルを復元するときに使用するバッファのサイズを設定します。REPAIR TABLE 中に MyISAM インデックスをソートするとき、または CREATE INDEX または ALTER TABLE でインデックスを作成するときに割り当てられるバッファは、myisam_max_sort_file_size = 10G です。
# 一時ファイルがインデックスよりも大きくなる場合は、クイック ソート インデックス メソッドを使用してインデックスを作成しないでください。注意: このパラメータはバイト単位で指定します。myisam_repair_threads = 1
# 値が 1 より大きい場合は、ソートによる修復中に MyISAM テーブル インデックスを並列に作成します (各インデックスは独自のスレッドで作成されます) 

インタラクティブタイムアウト = 28800
# 対話型接続を閉じる前にサーバーがアクティビティを待機する秒数。対話型クライアントは、mysql_real_connect() の CLIENT_INTERACTIVE オプションを使用するクライアントとして定義されます。デフォルト値: 28800秒 (8時間)

待機タイムアウト = 28800
# 非対話型接続を閉じる前にサーバーがアクティビティを待機する秒数。スレッドが開始されると、セッションの wait_timeout 値は、グローバル wait_timeout 値またはグローバル interactive_timeout 値に従って初期化されます。
# クライアント タイプによって異なります (mysql_real_connect() の CLIENT_INTERACTIVE 接続オプションによって定義されます)。デフォルト値: 28800秒 (8時間)
# MySQL サーバーがサポートする接続の最大数には上限があります。各接続はメモリを消費するため、クライアントが MySQL サーバーに接続して対応する操作を完了することを期待します。
# 切断して占有されているメモリを解放する必要があります。 MySQLサーバーにアイドル接続が多数ある場合、メモリを無駄に消費するだけでなく、接続が切断されずに蓄積され続けると、
# 最終的には MySQL サーバーの接続制限に達し、「接続数が多すぎます」というエラーが報告されます。 wait_timeout の値は、システムの動作条件に基づいて設定する必要があります。
# システムをしばらく実行した後、show processlist コマンドを使用して現在のシステム接続ステータスを表示できます。スリープ状態の接続プロセスが多数ある場合は、パラメータの設定が大きすぎることを意味します。
# 適宜小さく調整できます。これを有効にするには、interactive_timeout と wait_timeout の両方を同時に設定する必要があります。

[mysqlダンプ]
素早い
max_allowed_pa​​cket = 16M #サーバーが送受信するパケットの最大長 [myisamchk]
キーバッファサイズ = 8M
ソートバッファサイズ = 8M
読み取りバッファ = 4M
書き込みバッファ = 4M