MySQLログシステムの詳細情報共有

大規模なシステムに取り組んだことがある人なら誰でも、ログの役割を過小評価してはならないことを知っています。プロジェクトの後半の段階では、プロジェクトの最適化やアップグレードに関する決定がログに基づいて行われることがよくあります。したがって、MySQL を学習するときは、ログ部分を見逃してはなりません。インタビュー中に実際のアプリケーションで議論した最適化はすべてログから取得されました。 MySQL ログを体系的に研究することで、問題を正確に特定し、作業レベルを向上させることができます。さらに、次の一連のログでは、DBA の運用と保守に焦点を当て、MySQL のさまざまな側面の構成を体系的に理解し、自分と敵を知り、MySQL を身近なデータ ウェアハウスにします。

1. MySQL ログの種類

デフォルトでは、すべての MySQL ログはデータベース ルート ディレクトリにファイルとして保存されます。

[root@roverliang データ]# pwd
/usr/local/webserver/extend_lib/mysql/データ
[root@roverliang データ]# ls
auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mytest performance_schema roverliang roverliang.err roverliang.pid テスト

MySQL ログの種類は次のとおりです。

1. エラー ログ (エラー)、MySQL サービス インスタンスの開始、実行、停止に関連する情報。
2. 一般クエリ ログ (一般)、MySQL サービス インスタンスによって実行されるすべての SQL ステートメントまたは MySQL コマンド。
3. バイナリ ログ (バイナリ)、データベースで実行されたすべての更新ステートメント (select および show ステートメントを除く)。
4. スロークエリログ（遅い）、実行時間が long_query_time 設定値を超える SQL 文、またはインデックスを使用しない SQL 文。

MySQL ログ キャッシュ

高速で安定した信頼性の高いシステムでは、キャッシュが重要な役割を果たします。 MySQL ログ処理でもキャッシュ メカニズムが使用されます。 MySQL ログは、最初は MySQL サーバーのメモリに保存されます。指定されたストレージ容量を超えると、メモリ内のログは外部メモリに書き込まれ (またはフラッシュされ)、データベース テーブルまたはファイルの形式でハードディスクに永続的に保存されます。

3. MySQLエラーログ

MySQL エラー ログには、主に MySQL サービス インスタンスの起動と停止に関する詳細情報や、MySQL インスタンスの操作中に生成された警告やエラー メッセージが記録されます。他のログとは異なり、MySQL エラー ログは有効にする必要があり、無効にすることはできません。

デフォルトでは、エラー ログ ファイル名は hostname.err です。 ただし、エラー ログにはすべてのエラー メッセージが記録されるわけではありません。MySQL サービス インスタンスの動作中に発生した重大なエラーのみが記録されます。

mysql> 'log_error' のような変数を表示\G
************************** 1. 行 ****************************
変数名: log_error
値: /usr/local/webserver/extend_lib/mysql/data/roverliang.err
セット内の1行（0.02秒）

4. MySQL一般ログ

MySQL 一般クエリ ログには、操作が正常に実行されたかどうかに関係なく、選択、更新、挿入、削除など、MySQL サービス インスタンスのすべての操作が記録されます。また、MySQL クライアントが MySQL サーバーに接続したり切断したりしたかどうか、また接続が成功したか失敗したかに関する情報もあります。 MySQL 一般クエリ ログに関連するパラメーターは 3 つあります。

[]()一般ログ

mysql> 'general_log' のような変数を表示します。
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| 一般ログ | オフ |
+---------------+-------+
セット内の1行（0.01秒）

@@global.general_log = 1 を設定すると、一般的なクエリ ログを有効にできます。

mysql> @@global.general_log を 1 に設定します。
mysql> 'general_log' のような変数を表示します。
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| general_log | オン |
+---------------+-------+

ただし、この方法で MySQL 変数を変更した場合、変更が有効になるのは現在の MySQL インスタンスが実行中の場合のみです。MySQL を再起動すると、デフォルトの状態に復元されます。これを永続的にするには、mysql の my.cnf ファイルを変更します。設定ファイルの後に追加します:

general_log = 1
general_log_file

一般クエリ ログを有効にすると、MySQL サービス インスタンスは一般クエリ ログ ファイルを自動的に作成します。general_log_file パラメータは、一般クエリ ログ ファイルの物理的な場所を設定します。次のように：

mysql> 'general_log_file' のような変数を表示します。
+------------------+---------------------------------------------------------+
| 変数名 | 値 |
+------------------+---------------------------------------------------------+
| 一般的なログファイル | /usr/local/webserver/extend_lib/mysql/data/roverliang.log |
+------------------+---------------------------------------------------------+

注意: 一般クエリ ログにはほぼすべての MySQL 操作が記録されるため、データ アクセスが頻繁に行われるデータベース サーバーの場合、MySQL の一般クエリ ログをオンにすると、データベースのパフォーマンスが大幅に低下します。したがって、一般クエリ ログをオフにすることをお勧めします。特定の特別なクエリ ログを追跡する必要がある場合など、特別な期間にのみ、通常のクエリ ログを一時的に開くことができます。

ログ出力

log_output パラメータは、データベース テーブルに保存される通常のクエリ ログとスロー クエリ ログの内容を設定します。 set @@global.log_output='table' を使用すると、一般クエリ ログとスロー クエリ ログを、mysql システム データベースの general テーブルと slow_log テーブルに保存できます。これら 2 つのテーブルのストレージ エンジンは CSV であることに注意してください。その後、新しい共通クエリ ログの内容を表示するときに SQL ステートメントを使用できます。

@@global.log_output = 'table' を設定します。
mysql> 'log_output' のような変数を表示します。
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| ログ出力 | テーブル |
+---------------+-------+

5. MySQL スロークエリログ（スローログ）

遅いクエリ ログに関する質問。面接中、面接官はこれらの問題について話すのが大好きです。以前は、MySQL のマスタースレーブ アーキテクチャについて説明したり、さまざまな側面から MySQL を最適化することしかできませんでしたが、スロー クエリを有効にする方法や関連する構成をあまり理解していませんでした。

MySQL スロー クエリ ログを使用すると、実行に時間がかかりすぎるクエリ ステートメントやインデックスを使用しないクエリ ステートメントを効果的に追跡できます。これには、クエリの最適化に役立つ SELECT ステートメント、UPDATE ステートメント、DELETE ステートメント、および INSERT ステートメントが含まれます。通常のクエリ ログとのもう 1 つの違いは、スロー クエリ ログには正常に実行されたクエリ ステートメントのみが含まれることです。 MySQL スロー クエリ ログに関連するパラメーターは 5 つあります。

1. スロークエリログ

slow_query_log は、スロー クエリ ログを有効にするかどうかを設定します。

mysql> 'slow_query_log' のような変数を表示します。
+----------------+-------+
| 変数名 | 値 |
+----------------+-------+
| slow_query_log | オフ |
+----------------+-------+

2. スロークエリログファイル

スロー クエリ ログを有効にすると、MySQL インスタンスは自動的にスロー クエリ ログ ファイルを作成します。 slowquerylog_file で指定されたファイルには、スロークエリ ログの内容が保存されます。 変更方法は上記と同じです。編集するには、my.cnf ファイルに直接移動します。

3. 長いクエリ時間

long_query_time 、低速クエリの時間しきい値を設定します。デフォルトのしきい値は 10 秒です。

4. log_quries_not_using_indexes

log_quries_not_using_indexes クエリの速度に関係なく、インデックスを使用しないクエリを低速クエリ ログに記録するかどうか。

mysql> @@global.log_queries_not_using_indexes=1 を設定します。

mysql> 'log_queries_not_using_indexes' のような変数を表示します。
+---------------------------------+-------+
| 変数名 | 値 |
+---------------------------------+-------+
| log_queries_not_using_indexes | オン |
+---------------------------------+-------+

5. ログ出力

通常のクエリ ログとスロー クエリ ログの出力形式を、ファイルとテーブルの 2 つの値で設定します。

6. MySQLのスロークエリログの表示

log_output パラメータは、スロー クエリ ログの出力形式を設定できます。デフォルトは FILE ですが、TABLE に設定することもできます。

mysql> desc mysql.slow_log;
+----------------+---------------------+
| フィールド | タイプ |
+----------------+---------------------+
| 開始時刻 | タイムスタンプ |
| ユーザーホスト | 中テキスト |
| クエリ時間 | 時間 |
| ロック時間 | 時間 |
| 送信行数 | int(11) |
| 検査された行 | int(11) |
| db | varchar(512) |
| 最後の挿入ID | int(11) |
| 挿入ID | int(11) |
| server_id | int(10) 符号なし |
| sql_text | 中テキスト |
| スレッドID | bigint(21) 符号なし |
+----------------+---------------------+

ここで、lock_time は、実行時に SQL がロックによってブロックされている時間を示します。 rows_send は、SQL の実行後に返される行数を示します。 rows_examined は、SQL の実行時に実際にスキャンされたレコードの数を示します。

しかし、スロークエリログの保存に TABLE を使用することは一般的ではありません。業務量が多い場合、システムのメインサービスに影響を及ぼします。ログを保存するために FILE を使用できます。 MySQL をインストールすると、スロー クエリ ログを分析するための mysqldumpslow.pl ツールがデフォルトで MySQL bin ディレクトリにインストールされます。このツールを Windows で使用するには、いくつかの設定が必要になる場合がありますが、これはこの記事の範囲外です。システム サービスについて学習するには、Linux に移行することをお勧めします。 Linux のコマンドとツールの場合、コマンド自体と --help オプションを使用してヘルプ ドキュメントを表示できます。

-sはソート方法を示します

サブオプション: c、t、l、r

c : SQL文が実行された回数
t : 実行時間
l : ロック待機時間
r: データ項目の数を返す
at、al、arは対応するtlrの平均値です。 -t: 最初の N 個のレコードを返すことを意味します。

-g: grep の略語。あいまい一致を含む

一般的に使用される方法は次のとおりです。

// アクセス数が最も多い 20 個の SQL ステートメントを返します。/mysqldumpslow -sc -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

// 返されるレコード数が最も多い 20 個の SQL ステートメントを返します。/mysqldumpslow -sr -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 

7. バイナリログ

バイナリ ログは、前述のログとは異なります。バイナリ ログは、cat や less テキスト ビューアを使用して直接表示することはできません。専門的なツールを使用する必要があります。バイナリ ログは主にデータベースの変更を記録するため、マスター ライブラリとスレーブ ライブラリ間の同期に使用できます。内容には主に、すべてのデータベース更新操作、use ステートメント、insert ステートメント、delete ステートメント、update ステートメント、create ステートメント、alter ステートメント、および drop ステートメントが含まれます。より簡潔でわかりやすい方法でまとめると、データの変更を伴うすべての操作はバイナリ ログに記録される必要があります。

バイナリ ログを開始します。バイナリ ログが有効になっているかどうかを確認するには、「log_bin」\G などの show variables を使用します。

mysql> 'log_bin' のような変数を表示\G
************************** 1. 行 ****************************
変数名: log_bin
    値: オフ
セット内の 1 行 (0.00 秒)

mysql> @@global.log_bin=1 を設定します。
エラー 1238 (HY000): 変数 'log_bin' は読み取り専用変数です
マイSQL> 

log_bin はデフォルトでは有効になっておらず、読み取り専用変数であることがわかります。my.cnf でこれを設定してから、MySQL を再起動する必要があります。 service mysql restart で MySQL を再起動すると、データ ディレクトリに 1.000001 ファイルが生成されます。実際、MySQL を再起動するたびに、ディレクトリ内にこのようなファイルが生成され、ファイル名が順に増加していきます。さらに、MySQL はこのディレクトリにバイナリ ログ インデックス ファイルを作成します。コマンド show variables like 'log_bin_index'\G を実行してインデックス ファイルの場所を表示し、cat コマンドを使用して表示することができます。バイナリ ファイルの相対パスが記録されていることがわかります。

MySQL に付属するツールを使用してバイナリ ログを表示できます。具体的な場所は、mysql の bin ディレクトリ内です。 mysqlbinlog コマンドの一般的なオプション:

-s ログの内容を簡潔に表示する
-v ログの内容を詳細モードで表示する
-d=データベース名は指定されたデータベースのログ内容のみを表示します
-o=n ログ内のMySQLコマンドの最初のn行を無視する
-r=file 指定されたコンテンツを指定されたファイルに書き込みます

--開始日時
指定した時間範囲内のログの内容を表示する
--stop-datetime

--開始位置
指定された位置間隔内のログ内容を表示する
--停止位置

現在のバイナリログファイルを取得する

mysql> マスターステータスを表示します。
+----------+----------+--------------+------------------+-------------------+
| ファイル | 位置 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------+----------+--------------+------------------+-------------------+
| 1.000002 | 120 | | | |
+----------+----------+--------------+------------------+-------------------+
セット内の 1 行 (0.00 秒)

バイナリログを使用したデータの復元

構文は簡単です:

mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p

mysqlbinlog の後には、--start-datetime、--stop-datetime、start-position、stop-position などのパラメータを続けることができます。

2 つのパラメータ --start-datetime と --stop-datetime を使用すると、時点に基づいてデータを復元できます。

開始位置と停止位置を使用すると、より詳細な操作ポイントでデータを回復できます。

MySQLバイナリログ関連のパラメータ

mysql> '%binlog%' のような変数を表示します。
+-----------------------------------------+----------------------+
| 変数名 | 値 |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | オフ |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | ステートメント |
| binlog_gtid_simple_recovery | オフ |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | オン |
| binlog_row_image | フル |
| binlog_rows_query_log_events | オフ |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| innodb_api_enable_binlog | オフ |
| innodb_locks_unsafe_for_binlog | オフ |
| 最大バイナリログキャッシュサイズ | 18446744073709547520 |
| 最大バイナリログサイズ | 1073741824 |
| 最大 binlog_stmt キャッシュ サイズ | 18446744073709547520 |
| 簡素化されたbinlog_gtid_recovery | オフ |
| 同期バイナリログ | 0 |
+-----------------------------------------+----------------------+

max_binlog_size

maxbinlogsize 単一のバイナリ ログ ファイルのサイズ。値を超えると、サフィックス +1 が付いた新しいファイルが生成されます。

binlog_cache_size

binlogcachesize メモリにバイナリログを保存するために使用するキャッシュのサイズ

sync_binlog

sync_binlog はバイナリ ログをキャッシュに複数回書き込み、外部メモリ (ハード ディスク) に同期的に更新を開始します。

log_slave_updates

マスタースレーブレプリケーションのlogslvaeupdates

バイナリログのクリーニング

原則として、クリーンアップするログは、永続的に保存するために他のストレージ デバイスに物理的にバックアップする必要があります。 そこで、リスクの少ない次の 2 つのクリーニング方法をお勧めします。

最初のもの:

purge master logs before '2017-02-16 00:00:00';

2番目のタイプ:

バイナリ ファイルの有効期限を設定するには、MySQL 構成ファイル my.cnf で expire_logs_days パラメータを直接設定します。期限切れのバイナリ ファイルは自動的に削除されます。バイナリ タスクを定期的にバックアップするには、削除する前に別の定期的なスケジュールされたタスクを開始することをお勧めします。これは、数日後に一部のデータが間違っていることが判明し、バイナリ ログが自動的に削除されるのを防ぐためです。

expire_logs_days=90

8. InnoDB トランザクション ログ

MySQL はキャッシュを最大限に活用してデータ アクセス効率を向上させます。つまり、高性能システムでは必ずキャッシュを使用する必要があり、キャッシュはあらゆるレベルで大きな役割を果たします。より高いレベルからまとめると、キャッシュとキューは高パフォーマンスを実現する唯一の方法です。これはデータベースにとって非常に厄介な問題です。データの読み取りと保存をより効率的に行うには、キャッシュを使用する必要があります。ただし、データの一貫性を確保するには、すべてのデータがデータベースに正確に保存され、事故が発生した場合でもデータが回復可能であることを確認する必要があります。 InnoDB はトランザクションセーフなストレージエンジンであり、一貫性はトランザクション ACID の重要な機能であることがわかっています。 InnoDB ストレージ エンジンは、主に InnoDB トランザクション ログを通じてデータの一貫性を実現します。InnoDB トランザクション ログには、REDO ログとロールバック ログが含まれます。

InnoDB トランザクション ログは、上記のログとは異なります。InnoDB トランザクション ログは、InnoDB ストレージ エンジン自体によって管理され、その内容はデータベース管理者が読み取ることはできません。

やり直しログ（やり直し）

REDO ログには主に、完了したすべてのトランザクション、つまりコミットされたログが記録されます。デフォルトでは、REDO ログの値は iblogfile0 と iblogfile1 の REDO ログに記録されます。

[root@roverliang データ]# pwd
/usr/local/webserver/mysql/データ
[root@roverliang データ]# ls ib*
ibdata1 ib_logfile0 ib_logfile1

ロールバックログ（元に戻す）

ロールバック ログには、主に部分的に完了してハードディスクに書き込まれた未完了のトランザクションが記録されます。デフォルトでは、ロールバック ログ情報は、テーブルスペース ファイル、共有テーブルスペース ファイル ibdata1、または排他テーブルスペース ファイル ibd に記録されます。

上の図から、ロールバック ログはデフォルトで ibdta1 に記録されていることがわかります。私の MySQL システムのバージョンは 5.6.24 です。

チェックポイントメカニズム

MySQL サーバーがクラッシュした後、MySQL サービスが再起動されると、再実行ログ (redo) とロールバック ログ (undo) が存在するため、InnoDB は部分的に完了してハードディスクに書き込まれたすべての未完了トランザクションをロールバック ログ (undo) を通じてロールバックします。次に、REDO ログ内のすべてのトランザクションを再実行して、すべてのデータを回復します。しかし、データ量が大きすぎるため、InnoDB ではリカバリ時間を短縮するためにチェックポイント メカニズムが導入されました。

汚いページ

トランザクションでレコードを変更する必要がある場合、InnoDB はまず、データが配置されているデータ ブロックを外部メモリからハードディスクに読み取ります。トランザクションがコミットされた後、InnoDB はデータ ページ内のレコードを変更します。この時点で、キャッシュされたデータ ページは、外部メモリ内のデータ ブロックと同じではなくなります。キャッシュ内のデータ ページはダーティ ページと呼ばれます。ダーティ ページは外部メモリにリフレッシュされ、クリーン ページになります。

注: メモリ ページのデフォルトは 4K、または 4K の倍数です。メモリは、完全に消去できる本と考えることができます。MySQL はデータを読み取るたびに、メモリからいくつかのクリーンなページを要求し、そこに書き込みます。データがハードディスクに更新されると、データ ページは直ちに消去され、他のプログラムで使用できるようになります。

ログシーケンス番号

ログ シーケンス番号 (LSN) は、ログ スペース内の各ログの終了ポイントであり、バイト オフセットとして表され、チェックポイントとリカバリ時に使用されます。

チェックポイント機構の原理は、ある時点ですべてのダーティページがハードディスクにリフレッシュされ、その時点より前のすべてのREDOログは再実行する必要がないことを前提としています。システムは、その時点のREDOログの終了位置をチェックポイントとして使用し、チェックポイントより前のREDOログは再実行する必要がなく、安全に削除できます。 Redo ログ領域をより有効に活用するために、InnoDb はラウンドロビン戦略を使用して Redo ログ領域を使用するため、InnoDB には少なくとも 2 つの Redo ログ ファイルがあります。チェックポイント メカニズムにより、データベースがクラッシュしたときに完了しているがまだ外部メモリに完全に書き込まれていないトランザクションを、REDO ログを通じてやり直す (元に戻す) ことができるため、データの一貫性が確保され、回復時間が短縮されます。

InnoDB redo ログ (redo) パラメータ

innodb_log_buffer_size: REDO ログ キャッシュのサイズを設定します。
innodb_log_files_in_group: ログ ファイル グループ内の REDO ログの数を設定します。
innodb_log_file_size: REDO ログ ファイルのサイズを設定します。ファイルが大きいほど、回復にかかる時間が長くなります。
innodb_mirrored_log_groups: REDO ログ ミラー ファイル グループの数。設定できるのは 1 のみです。
innodb_log_group_home_dir: ログ ファイル グループが保存されるディレクトリを設定します。デフォルトのディレクトリはデータベースのルート ディレクトリです。

InnoDB ロールバック ログ (元に戻す) パラメータ

innodb_undo_directory: ロールバック ログが保存されるディレクトリを設定します。
innodb_undo_logs: ロールバックログのロールバックセグメントサイズを設定します。デフォルトは128kです。
innodb_undo_tablespace: ロールバック ログを構成するロールバック ログ ファイルの数を設定します。デフォルト値は 0 です。
警告 特別な注意: MySQL をインストールした後、my.cnf でロールバック ログ パラメータを設定する必要があります。データベースの作成後にロールバック ログ パラメータを設定すると、MySQL はエラーを報告し、ロールバック ログの作成後は、再度変更または追加することはできません。

9. ログファイルのバックアップ

バックアップ時に、フラッシュ ログを使用して現在のログ ファイルをすべて閉じ、新しいログ ファイルを生成できます。ログ ファイルを閉じた後、物理的にバックアップすることができます。 さらに、フラッシュ ログでは特定のログ タイプを追加できます。

エラーログをフラッシュする
一般的なログをフラッシュする
バイナリログをフラッシュする
スローログをフラッシュする