English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
大規模システムを経験した人なら分かるように、ログの役割は小視してはならない。プロジェクトの後期に入ると、プロジェクトの最適化やアップグレードに関する決定はほぼすべてログに基づいている。そのため、MySQLを学ぶ際にはログの部分は必ず見逃さないでください。私たちが面接で実際に話している最適化はすべてログから導き出されるものです。MySQLのログを体系的に学ぶことで、問題を正確に特定し、自分の業務レベルを向上させる助けになります。また、後のシリーズのログは特にDBAの運用面から取り組むことに重点を置き、MySQLの各種設定を体系的に理解し、己を知り、敵を知ることで、MySQLを自分が使いこなせるデータベースにする必要があります。
MySQLのログタイプについて
デフォルトでは、すべてのMySQLログはファイルとしてデータベースのルートディレクトリに保存されます：
[root@roverliang data]# pwd /usr/local/webserver/extend_lib/mysql/data [root@roverliang data]# ls auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mytest performance_schema roverliang roverliang.err roverliang.pid test
MySQLのログの種類には以下のようなものがあります：
1． エラーログ（error）：MySQLサービスインスタンスの起動、実行または停止に関する情報。
2． 普通クエリログ（general）：MySQLサービスインスタンスが実行するすべてのSQL文またはMySQLコマンド。
3． バイナリログ（binary）：データベースに対して実行されるすべての更新文、selectおよびshow文を除く。
4． スロークエリログ（slow）：long_query_time設定値を超えるSQL文またはインデックスを使用していないSQL文。
MySQLのログキャッシュ
高速で安定して信頼性の高いシステムでは、その中にキャッシュがあれば、非常に重要な役割を果たします。MySQLのログ処理もキャッシュメカニズムを使用しています。MySQLのログは最初にMySQLサーバーのメモリに保存されます。指定された容量を超えた場合、メモリのログは外部メディアに書き込まれたり（またはフラッシュ）または、データベーステーブルまたはファイルとして永远にハードディスクに保存されます。
MySQLのエラーログ（error log）
MySQLのエラーログは、MySQLサービスインスタンスの起動、停止の詳細情報、およびMySQLインスタンスの実行中に発生する警告やエラーデータを主に記録します。他のログとは異なり、MySQLのエラーログは必ず開始され、停止することができません。
デフォルトでは、エラーログのファイル名は：ホスト名.errです。しかし、エラーログはすべてのエラーデータを記録することはありません。MySQLサービスインスタンスの実行中に発生する重要なエラー（critical）のみが記録されます。
mysql> show variables like 'log_error'\G *************************** 1. row *************************** Variable_name: log_error Value: /usr/local/webserver/extend_lib/mysql/data/roverliang.err 1 row in set (0.02 sec)
MySQLの通常のクエリログ(general log)
MySQLの通常のクエリログは、MySQLサービスインスタンスのすべての操作を記録します。例えば、select、update、insert、deleteなどの操作、操作が成功したかどうかに関係なく記録されます。また、MySQLクライアントとMySQLサービスエンドの接続および切断に関する情報も記録されます。接続が成功したかどうかに関係なくです。MySQLの通常のクエリログに関連するパラメータは3つあります。
[]()general_log mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+ 1 row in set (0.01 sec)
set @@global.general_log = 1 の方法で通常のクエリログをオンにします。
mysql> set @@global.general_log =1; mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | ON | +---------------+-------+
この方法でMySQLの変数を変更することは、現在のMySQLインスタンスの実行中にのみ効果があり、MySQLが再起動すると、デフォルトの状態に戻ります。永続的に効果を得るには、mysqlのmy.cnfファイルを変更します。設定ファイルの後ろに追加します：
general_log = 1
general_log_file
通常のクエリログが有効になると、MySQLサービスインスタンスは自動的に通常のクエリログファイルを作成します。general_log_fileパラメータは通常のクエリログファイルの物理位置を設定します。以下のようになります：
mysql> show variables like 'general_log_file'; +------------------+-----------------------------------------------------------+ | 変数名 | 値 | +------------------+-----------------------------------------------------------+ | general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log | +------------------+-----------------------------------------------------------+
注意：通常のクエリログはMySQLのほぼすべての操作を記録しており、データアクセスが頻繁なデータベースサーバーでは、MySQLの通常のクエリログを有効にすると、データベースのパフォーマンスが大幅に低下するため、通常のクエリログをオフにすることをお勧めします。特殊な時期に、特定のクエリログを追跡する必要がある場合、一時的に通常のクエリログをオンにすることができます。
log_output
log_outputパラメータは、通常のクエリログおよび遅いクエリログの内容をデータベーステーブルに保存する設定です。通常のクエリログおよび遅いクエリログをMySQLシステムデータベースのgeneralテーブルおよびslow_logテーブルに保存するには、set @@global.log_output='table'を使用します。注：この2つのテーブルのストレージエンジンはCSVであり、新しい通常のクエリログの内容を確認する際にはSQLクエリを使用できます；
set @@global.log_output = 'table'; mysql> show variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+
五、MySQLのスローキャラログ（slow log）
スローキャラログに関する問題は、面接の際に面接官が非常に興味を持つトピックです。以前は、MySQLのマスターサレンジアーキテクチャについて高く語り、さまざまな方法でMySQLを最適化することに焦点を当てていましたが、実際にはスローキャラログの有効化方法や関連する設定について深く理解していませんでした。
MySQLのスローキャラログを使用することで、実行時間が長いまたは索引を使用していないクエリ文を効果的に追跡できます。これには、select 文、update 文、delete 文、および insert 文が含まれます。これにより、クエリの最適化に役立ちます。通常のクエリログとは異なるもう一つの違いは、スローキャラログは成功したクエリ文のみを含むことです。MySQLのスローキャラログに関連するパラメータには5個。
1、slow_query_log
slow_query_log は、スローキャラログを有効化するかどうかを設定します。
mysql> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | OFF | +----------------+-------+
2、slow_query_log_file
スローキャラログが有効化されると、MySQLインスタンスは自動的にスローキャラログファイルを作成します。slowquerylog_file で指定されたファイルには、スローキャラログの内容が保存されます。上記の方法と同様に変更できます。my.cnf ファイルに直接編集してください。
3、long_query_time
long_query_time は、スローキャラログの時間閾値を設定します。デフォルトの閾値は10s。
4、log_quries_not_using_indexes
log_quries_not_using_indexes は、索引を使用していないクエリ文をスローキャラログに記録するかどうかを設定します。クエリの速度がどれだけ遅いかにかかわらずです。
mysql> set @@global.log_queries_not_using_indexes=1; mysql> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+
5、log_output
通常のクエリログおよびスローキャラログの出力形式が設定されています。値はfile、tableの2つがあります。
六、MySQLのスローキャラログの確認
log_outputパラメータを使用してスローキャラログの出力形式を設定できます。デフォルトはFILEですが、TABLEに設定することもできます。
mysql> desc mysql.slow_log; +----------------+---------------------+ | Field | Type | +----------------+---------------------+ | start_time | timestamp | | user_host | mediumtext | | query_time | time | | lock_time | time | | rows_sent | int(11) | | rows_examined | int(11) | | db | varchar(512) | | last_insert_id | int(11) | | insert_id | int(11) | | server_id | int(10) unsigned | | sql_text | mediumtext | | thread_id | bigint(21) unsigned | +----------------+---------------------+
その中で、lock_timeはSQLの実行中にロックされた時間を示します。rows_sendはSQLの実行後に返される内容の行数を示します。rows_examinedはSQLの実行中に実際にスキャンされたレコードの数を示します。
ただし、TABLEを使用してスローキャラログを保存することは稀です。ビジネスの量が多い場合、システムのメインサービスに影響を与える可能性があります。FILEの方法を使用してログを保存することができます。MySQLをインストールする際、MySQLのbinディレクトリには既にmysqldumpslow.plツールがデフォルトでインストールされています。このツールを使用するには、Windowsでは設定が必要になるかもしれませんが、これは本文の紹介範囲外です。システムサービスを学ぶ場合は、Linuxに移動してください。Linuxでのコマンドおよびツールは、コマンド自体を使用できます。 + --ヘルプのオプションを使用してヘルプドキュメントを表示します。
-sはどのようにしてソートするかを示します
サブオプション: c、t、l、r
c : SQL実行の回数
t : 実行時間
l : ロック待ち時間
r : データの件数を返します
at、al、arはt、l、rの平均値です。 -t : N件の前の記録を返します。
-g：grepの省略形。含糊検索
一般的な使い方は以下の通りです：
//アクセス回数が多いものを返します20件のSQL文 ./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //return記録数が多いものを返します20件のSQL文 ./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //likeを含むSQL文を返します ./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log
七、バイナリログ（binary）
バイナリログは前述のいくつかのログとは異なり、バイナリログは直接「cat」や「less」テキストビューアで確認することはできません。専門のツールを使用する必要があります。バイナリログはデータベースの変更状況を主に記録しており、マスターとサブの同期に使用されます。内容はデータベースのすべての更新操作、use文、insert文、delete文、update文、create文、alter文、drop文などが含まれています。簡潔に言えば、データ変動に関わるすべての操作はバイナリログに記録されることになります。
バイナリログの使用を開始するには、コマンド「show variables like 'log_bin'\G」を使ってバイナリログが有効かどうかを確認します。
mysql> show variables like 'log_bin'\G
*************************** 1. row ***************************
Variable_name: log_bin
Value: OFF
1 row in set (0.00 sec)
mysql> set @@global.log_bin=1;
ERROR 1238 (HY000): 変数 'log_bin' は読み取り専用の変数です
mysql>
log_binがデフォルトで有効ではなく、読み取り専用の変数であることを確認します。それからmy.cnfに設定してMySQLを再起動する必要があります。service mysql restart MySQLを再起動すると、dataディレクトリに新しいファイルが生成されます。1.000001のファイル。実際にはMySQLが再起動するたびに、そのディレクトリにこのようなファイルが生成され、ファイル名は順番に増えていきます。さらに、MySQLはそのディレクトリにバイナリログのインデックスファイルも作成します。インデックスファイルの場所はコマンド「show variables like 'log_bin_index'\G」で確認でき、それから「cat」コマンドを使って見てみると、バイナリファイルの相対パスが記録されています。
二進位ログを確認するには、MySQLに内蔵されたツールを使用できます。具体的な場所はmysqlのbinディレクトリ下にあります。mysqlbinlogコマンドの一般的なオプション：
-s ログ内容を簡略に表示する
-v ログ内容を詳細に表示する
-d=データ库名 指定データベースのログ内容のみ表示する
-o=n ログ中の前n行のMySQLコマンドを無視する
-r=file 指定内容を指定ファイルに書き込む
--start-datetime
指定時間範囲のログ内容を表示します
--stop-datetime
--start-position
指定位置間隔のログ内容を表示します
--stop-position
現在使用している二進位ログファイルを取得します
mysql> show master status; +----------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------+----------+--------------+------------------+-------------------+ | 1.000002 | 120 | | | | +----------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
バイナリログを使用してデータをリカバリする
文法はシンプルです：
mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p
mysqlbinlogの後には --start-datetime 、--stop-datetime 、start-position 、stop-positionなどのパラメータ。
--start-datetime 、--stop-datetimeこの2つのパラメータは時間ポイントに基づいてデータリカバリができます；
start-position 、stop-positionはデータリカバリの操作ポイントをより詳細に設定できます；
MySQLの二進位ログ関連パラメータ
mysql> show variables like '%binlog%'; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | IGNORE_ERROR | | binlog_format | STATEMENT | | binlog_gtid_simple_recovery | OFF | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlogging_impossible_mode | IGNORE_ERROR | | innodb_api_enable_binlog | OFF | | innodb_locks_unsafe_for_binlog | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | simplified_binlog_gtid_recovery | OFF | | sync_binlog | 0 | +-----------------------------------------+----------------------+
max_binlog_size
maxbinlogsize 単一の二進数ログファイルのサイズ。この値を超えた場合、新しいファイルが生成され、接尾辞名+1；
binlog_cache_size
binlogcachesize メモリに保持する二進数ログのキャッシュのサイズ
sync_binlog
sync_binlog ブイニナログをメモリのキャッシュに何度書き込むか、外部記憶（ハードディスク）に同期してリフレッシュ開始します。
log_slave_updates
logslvaeupdates 用于主从复制
logslvaeupdatesは主從複製用
バイナリログのクリーンアップ
原則的には、準備を整えたログを物理的なバックアップ方法で他のストレージデバイスにバックアップし、永続的に保存する必要があります。その後、以下のリスクの低い2つのクリーンアップ方法を使用することを推奨します：
pruge master logs before '2017-02-16 第1種：
00:00:00';
第2種：
MySQLの設定ファイルmy.cnfにexpire_logs_daysパラメータを設定して、バイナリファイルの有効期限を設定し、有効期限が切れたバイナリファイルは自動的に削除されます。削除前に別のサイクルスケジュールタスクを起動し、定期的にバイナリログのバックアップを行うことを推奨します。数日後にデータが誤差が発生したことが判明しても、バイナリログが自動的に削除されないようにします。90
expire_logs_days=
8、InnoDBトランザクションログ
InnoDBトランザクションログは、前述のログとは異なり、InnoDBトランザクションログはInnoDBストレージエンジンが独自にメンテナンスし、内容はデータベース管理者によって読まれることができません。MySQLは最大限のキャッシュを利用し、データのアクセス効率を向上させます。つまり、高性能なシステムはキャッシュを利用する必要があり、キャッシュはあらゆる層で巨大な役割を果たします。さらに高次元で精炼すると、キャッシュとキューは高性能を実現するための必須の道です。それでは、データベースにとってこれは非常に厄介な問題であり、データの高効率な読み取りと保存を保証するために、キャッシュを利用する必要があります。しかし、データの一致性を保証するには、すべてのデータが正確にデータベースに保存される必要があり、異常が発生してもデータが復旧可能であることを保証する必要があります。私たちはInnoDBがトランザクションセーフなストレージエンジンであり、一貫性はトランザクションACIDの重要な特性であることを知っています。InnoDBストレージエンジンは、InnoDBトランザクションログを通じてデータの一貫性を実現しており、InnoDBトランザクションログにはリドライブ（redo）ログとロールバック（undo）ログが含まれます。
リドライブログ（redo）
リドライブログは、完全に完了したトランザクション、つまりcommitが実行されたログを主に記録しており、デフォルトでは、リドライブログの情報はiblogfile0およびiblogfileに記録されます。1リドライブログ中。
[root@roverliang data]# pwd /usr/local/webserver/mysql/data [root@roverliang data]# ls ib* ibdata1 ib_logfile0 ib_logfile1
ロールバックログ（undo）
ロールバックログは、部分完了してハードディスクに書き込まれた未完了のトランザクションを主に記録しており、デフォルトでは、ロールバックログの情報はテーブルスペースファイル、共有テーブルスペースファイルibdataに記録されます。1またはエクスクルーシブテーブルスペースではibdに記録されていません。
上の図から分かるように、ロールバックログはデフォルトでibdtaに記録されています。1のうち。私のMySQLシステムのバージョンは：5.6.24.
Checkpointメカニズム
MySQLサーバーがクラッシュした後、MySQLサービスを再起動する際に、重做ログ（redo）とロールバックログ（undo）の存在により、InnoDBはロールバックログ（undo）を使ってすべての未完成でハードディスクに一部書き込まれたトランザクションをロールバック操作（rollback）を行います。その後、重做ログ（undo）のトランザクションをすべて再実行することで、すべてのデータをリカバリできます。ただし、データ量が大きい場合、リカバリ時間を短縮するためにInnoDBはCheckpointメカニズムを導入しています。
ダーティーページ（dirty page）
トランザクションが特定のレコードを変更する必要がある場合、InnoDBはまずそのデータが所在するデータブロックを外存から読み込んでハードディスクに保存します。トランザクションがコミットされた後、InnoDBはデータページのレコードを変更し、この時点でキャッシュ中のデータページは外存のデータブロックとは異なります。この時点でキャッシュ中のデータページはダーティーページ（dirty page）と呼ばれ、ダーティーページは外存にフラッシュされ、クリーンページ（clean page）に変換されます。
備考：メモリページのデフォルトは4K、または4Kの倍数です。メモリを一冊の擦り消せる本として想像できます。MySQLがデータを読むたびに、メモリにいくつかのクリーンなページをリクエストし、そこに書き込みます。データがハードディスクに書き込まれた後、これらのデータページはすぐに擦り消され、他のプログラムに使用されます。
ログシーケンス番号（log sequence number）
ログシーケンス番号（LSN）は、ログスペース中の各ログの終了点であり、バイトオフセットで表され、Checkpointとリカバリ時に使用されます。
Checkpointメカニズムの原理 假定、ある時点ですべてのダーティーページ（dirty page）がハードディスクに書き込まれた場合、その時点以前のすべての重做ログ（redo）は再書き込みの必要がありません。システムはその時点の重做ログの終了位置をCheckpointとして設定し、Checkpoint以前の重做ログは再書き込みの必要がなく、安心して削除できます。重做ログ（redo）のスペースをより良い方法で利用するために、InnoDBはループの戦略を使って重做ログスペースを使用します。したがって、InnoDBの重做ログファイルは少なくとも2個。Checkpointメカニズムを通じて、データベースがクラッシュした際に既に完了したが、まだキャッシュ中の変更が外存に完全に書き込まれていないトランザクションを重做ログ（redo）を使って戻し（undo）操作を行うことで、データの一致性を確保し、リカバリ時間を短縮できます。
InnoDB重做ログ(redo)のパラメータ
innodb_log_buffer_size: 重做ログバッファーのサイズを設定します。
innodb_log_files_in_group : ログファイルグループ内のリプレイログ（redo）の数を設定します。
innodb_log_file_size: リプレイログファイルのサイズを設定します。ファイルが大きいほど、リカバリが時間がかかります。
innodb_mirrored_log_groups: リプレイログのミラーリングファイルグループの数を設定します。設定可能なのは以下の通りです：1.
innodb_log_group_home_dir： ログファイルグループの保存先ディレクトリを設定します。デフォルトはデータベースのルートディレクトリです。
InnoDB ロールバックログ（undo）のパラメータ
innodb_undo_directory: ロールバックログを保存するディレクトリを設定します。
innodb_undo_logs : ロールバックログのロールバックセグメントのサイズを設定します。デフォルトは未設定です。128k
innodb_undo_tablespace: ロールバックログが何個のロールバックログファイルで構成されるかを設定します。デフォルトは0です。
Warning 特別注意：MySQLをインストールした後は、my.cnfでロールバックログのパラメータを設定する必要があります。データベースを作成した後にロールバックログのパラメータを設定すると、MySQLがエラーを報告し、ロールバックログが作成された後は変更や追加ができなくなります。
九 、ログファイルのバックアップ
バックアップの際には、flush logsを使用して、現在のすべてのログファイルを閉じ、新しいログファイルを作成することができます。ログファイルを閉じた後は、物理的な方法でバックアップを行うことができます。また、flush logsには具体的なログタイプを追加することもできます：
flush error logs flush general logs flush binary logs flush slow logs
声明：このコンテンツはインターネットから取得され、著作権者に帰属します。インターネットユーザーによって自発的に提供されたコンテンツであり、このサイトは所有権を持ちません。また、人工編集は行われていません。著作権侵害を疑うコンテンツがある場合は、以下のメールアドレスまでお知らせください：notice#oldtoolbag.com（メールを送信する際、#を@に置き換えてください。申し訳ありませんが、関連する証拠を提供し、確認がとれた場合、このサイトは即座に侵害を疑われるコンテンツを削除します。）