MySQLトランザクションとMySQLログの詳細な説明

取引特性

1. アトミック性: トランザクションの開始後、すべての操作が完了するか、まったく実行されないかのどちらかになります。トランザクションが途中で停止することはあり得ません。

2. 一貫性: トランザクションの開始前と終了前、および終了後にデータベースの整合性制約に違反しません。例えば、AがBに送金した場合、Aがそのお金を差し引くことは不可能ですが、Bはそれを受け取りません。

3. 分離: 同時に同じデータを要求できるトランザクションは 1 つだけであり、異なるトランザクション間で干渉は発生しません。たとえば、A が銀行カードからお金を引き出す場合、B は A が引き出しプロセスを完了するまでこのカードにお金を送金することはできません。

4. 耐久性: トランザクションが完了すると、トランザクションによってデータベースに対して行われたすべての更新がデータベースに保存され、ロールバックできなくなります。

トランザクションの同時実行の問題

1. ダーティリード: トランザクションAはトランザクションBによって更新されたデータを読み取り、その後Bは操作をロールバックするため、Aによって読み取られたデータはダーティデータになります。

2. 反復不可能な読み取り: トランザクション A は同じデータを複数回読み取ります。トランザクション A が複数回読み取りを行っている間に、トランザクション B がデータを更新してコミットするため、トランザクション A が同じデータを複数回読み取ったときに結果が不一致になります。

3. ファントム リード: システム管理者 A は、データベース内のすべての学生の成績を特定のスコアから ABCDE の成績に変更しましたが、システム管理者 B は、このとき特定のスコアのレコードを挿入しました。システム管理者 A が変更を終えると、まるで錯覚が起こったかのように、変更されていないレコードがまだ残っていることに気付きます。これをファントム リードと呼びます。

概要: 非反復読み取りとファントム読み取りは混同されやすいです。非反復読み取りは変更に重点を置いていますが、ファントム読み取りは追加または削除に重点を置いています。非反復読み取りの問題を解決するには、条件を満たす行のみをロックする必要があります。ファントム読み取りの問題を解決するには、テーブルをロックする必要があります。

トランザクション分離

MySQLはシリアル化後、デフォルトで「繰り返し読み取り」になります

#グローバルトランザクション分離レベルを確認しますSELECT @@global.tx_isolation;
#現在のセッションのトランザクション分離レベルを確認しますSELECT @@session.tx_isolation; 
#現在のトランザクション分離レベルを確認しますSELECT @@tx_isolation;
# グローバル分離レベルを設定します。set global transaction isolation level read committed;
#現在のセッション分離レベルを設定します。set session transaction isolation level read committed;

シリアル化可能性は最も高い分離レベルであり、トランザクションが互いに競合しないように強制的に順序付けすることでファントム リード問題を解決します。つまり、各データ行の読み取りに共有ロックが追加され、このレベルでは多くのタイムアウトとロック競合が発生する可能性があります。

共有ロック: 共有ロックのコード名はS

MySQL ログ ファイル システムの構成

1. MySQLログファイルシステムの構成

a. エラー ログ: mysqld の起動、実行、または停止時に発生する問題を記録します。
b. 一般ログ: 確立されたクライアント接続と実行されたステートメントを記録します。
c. 更新ログ: データを変更するステートメントを記録します。このログは MySQL 5.1 では使用されなくなりました。
d. バイナリ ログ: データを変更するすべてのステートメントを記録します。レプリケーションにも使用されます。
e. スロークエリログ: 実行に long_query_time 秒より長い時間がかかるすべてのクエリ、またはインデックスを使用しないクエリを記録します。
f. Innodb ログ: Innodb redo ログ

バイナリログ (binlog):

更新された、または更新される可能性があるすべてのデータ（どの行にも一致しない DELETE など）が含まれます。

データベースを更新する各ステートメントの実行時間に関する情報が含まれます (DML)

データを変更しないステートメントは含まれません。このオプションを有効にするには、一般的なログ機能を有効にする必要があります。

主な目的は、バイナリログにはバックアップ後に行われたすべての更新が含まれているため、データベースを可能な限りデータベース障害の時点に復元することです。

スレーブサーバーに送信されるマスターレプリケーションサーバー上のすべてのステートメントを記録するために使用されます。

このオプションを有効にすると、データベースのパフォーマンスが 1% 低下しますが、データベースの整合性は保証されます。重要なデータベースの場合、整合性と引き換えにパフォーマンスを優先する価値があります。これは、Oracle でアーカイブ モードを有効にすることに多少似ています。

'%version%' のような変数を表示します。 
'%log_bin%' のような変数を表示します。 //binlog を有効にするかどうか
'%binlog%' のような変数を表示します。 //binlog 関連のパラメータ '%datadir%' のような変数を表示します。 //データ ファイル ディレクトリ。ログがデフォルトで保存される場所です。 #my.cnf を編集してバイナリ ログの場所を設定します (注: バイナリ ログのパスとファイル名を構成すると、システム変数 log_bin が自動的にオンに設定されます) 

log_bin=/var/lib/mysql/binarylog/binlog 

#my.cnf で log_bin のみを設定し、file_name を指定していない場合は、データベースを再起動します。バイナリ ログ ファイル名は、${hostname}-bin #Switch log show master status; の形式であることがわかります。
ログをフラッシュする。
マスターステータスを表示します。

MySQL サービスが再起動されるたびに、新しいバイナリ ログ ファイルが生成されます。これは、バイナリ ログの切り替えに相当します。バイナリ ログに切り替えると、これらの数値が増加し続けることがわかります。これらのバイナリ ログ ファイルに加えて、DB-Server-bin.index ファイルも生成されます。このファイルには、すべてのバイナリ ログ ファイルのリスト (バイナリ ファイルのインデックスとも呼ばれます) が格納されます。

バイナリ ログは、コマンドを使用して手動で削除することも、自動的にクリーンアップされるように設定することもできます。

バイナリログを表示します。
mysql> バイナリ ログを 'DB-Server-bin.000002' に消去します。

バイナリ ログを xxx に消去します。これは、特定のログより前のすべてのバイナリ ログ ファイルを削除することを意味します。このコマンドは、インデックス内の関連データを変更します。つまり、「2017-03-10 10:10:00」より前のバイナリ ログを消去し、特定の時点より前のバイナリ ログ ファイルをクリアします。
date_sub( now() )、interval 7 日前にマスター ログを消去します。7 日前にバイナリ ログ ファイルをクリアします。マスターをリセットします。すべてのバイナリ ログ ファイルをクリアします (現在、マスターとスレーブのレプリケーション関係はありません)。

'expire_logs_days' のような変数を表示します。expire_logs_days パラメータを設定して自動クリーンアップを設定することもできます。デフォルト値は 0 で、自動削除機能が有効になっていないことを意味します。自動クリーンアップ機能が有効になっている場合、この日数を超えたバイナリ ログ ファイルは自動的に削除されます。自動削除は通常、MySQL の起動時またはログのフラッシュ時に行われます。
expire_logs_days=7 を設定します。

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

1. システム変数 log_bin_trust_function_creators は、デフォルトでは OFF になっています。このパラメータを有効にすると、ストアド プロシージャ、関数、トリガーの作成が制限されます。

2: システム変数 sql_log_bin は、セッション レベルのバイナリ ログ機能のオン/オフを制御するために使用されます。デフォルト値は ON で、バイナリ ログ機能が有効であることを意味します。

3. システム変数 binlog_cache_size は、各クライアントに binlog_cache_size サイズのキャッシュが割り当てられていることを示します。デフォルト値は 32768 です。バイナリログキャッシュを使用するための前提条件は、サーバーがトランザクションをサポートするエンジンを使用し、バイナリログ機能が有効になっていることです。これは、バイナリログの効率を向上させるために、MySQL によって設計された、短期間のバイナリログデータを一時的にキャッシュするメモリ領域です。一般的に、データベースに大きなトランザクションがなく、書き込みが特に頻繁でない場合は、2MB ～ 4MB が適切な選択です。ただし、データベースに多数の大規模なトランザクションやマルチトランザクション ステートメントがあり、書き込みボリュームが比較的大きい場合は、binlog_cache_size を適切に増やすことができます。同時に、binlog_cache_use と binlog_cache_disk_use を使用して、設定された binlog_cache_size が十分かどうか、メモリ サイズが不足しているためにキャッシュに一時ファイル (binlog_cache_disk_use) を使用している binlog_cache が大量にあるかどうかを分析できます。

Binlog_cache_disk_use と Binlog_cache_use をチェックして、binlog_cache_size を調整する必要があるかどうかを判断できます。

4. システム変数 max_binlog_cache_size バイナリ ログで使用できる最大キャッシュ メモリ サイズ。マルチステートメント トランザクションを実行するときに、max_binlog_cache_size が十分な大きさでない場合、システムは「マルチステートメント トランザクションには 'max_binlog_cache_size' バイトを超えるストレージが必要です」というエラーを報告することがあります。

5. システム変数 max_binlog_stmt_cache_size

max_binlog_cache_size はトランザクション ステートメント用であり、max_binlog_stmt_cache_size は非トランザクション ステートメント用です。Binlog_cache_disk_use または Binlog_stmt_cache_disk_use が比較的大きいことがわかった場合は、キャッシュ サイズを増やすことを検討する必要があります。

6. システム変数 max_binlog_size はバイナリ ログの最大サイズを示します。これは通常 512M または 1GB に設定されますが、1GB を超えることはできません。この設定では、バイナリ ログのサイズを厳密に制御することはできません。特に、バイナリ ログが大規模なトランザクションに近い場合はそうです。トランザクションの整合性を確保するために、ログを切り替えることはできません。トランザクションのすべての SQL ステートメントは、トランザクションが終了するまで現在のログにのみ記録されます。

7. システム変数 binlog_checksum は、レプリケーションのマスター/スレーブ検証に使用されます。 NONE はチェックサムが生成されないことを意味し、CRC-32 はチェックにこのアルゴリズムを使用することを意味します。

8. システム変数 sync_binlog。このパラメータは、MySQL システムにとって非常に重要です。MySQL へのバイナリ ログ ファイルのパフォーマンス低下に影響するだけでなく、MySQL 内のデータの整合性にも影響します。

sync_binlog=0 の場合、トランザクションがコミットされると、Mysql は binlog_cache 内のデータを binlog ファイルに書き込むだけで、ファイル システムにキャッシュをディスクに更新するように通知する fsync などのディスク同期命令は実行せず、ファイル システムがいつ同期するかを決定できるようにします。 MySQL のデフォルト設定は sync_binlog=0 です。これは、必須のディスク更新指示が行われないことを意味します。この設定ではパフォーマンスは最高ですが、リスクも最大になります。システムがクラッシュすると、ファイル システム キャッシュ内のすべてのバイナリ ログ情報が失われます。これにより、データが不完全になるという問題が発生します。

sync_binlog=n の場合、n 個のトランザクションがコミットされた後、Mysql は fsync などのディスク同期命令を実行し、ファイル システムは Binlog ファイル キャッシュをディスクに更新します。

sync_binlog を適切に調整すると、ある程度の一貫性を犠牲にして、より高い同時実行性とパフォーマンスを実現できます。

9. システム変数 binlog_format はバイナリ ログの種類を指定します。値は、STATEMENT、ROW、MIXED の 3 つです。 MySQL 5.7.6 より前のデフォルト モードは STATEMENT です。 MySQL 5.7.7 以降のデフォルト モードは ROW モードです。このパラメータは主にマスター-スレーブレプリケーションに影響します。

SQL文ベースのレプリケーション（SBR）

行ベースのレプリケーション（RBR）

混合ベースレプリケーション (MBR)。

バイナリログの内容を表示する

方法 1: show binlog events メソッドを使用して、現在の binlog と指定された binlog のログを取得します。大量のログを抽出するのには適していません。

BINLOG イベントを表示 [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

'mysql-bin.000005' の BINLOG イベントを表示 \G

方法 2: mysqlbinlog コマンドラインを使用してログの内容を表示します (バッチ ログ抽出に適しています)。

システム mysqlbinlog /var/lib/mysql/DB-Server-bin.000013;
mysqlbinlog /var/lib/mysql/DB-Server-bin.000013 > test.sql;

バイナリログの種類

セグメントベースのログ形式

binlog_format=ステートメント

操作のSQL文が記録されます。

アドバンテージ：

ログ レコードの量は比較的少ないため、ディスクとネットワークの I/O が節約されます。ROW 形式のレコードを 1 つだけ変更または挿入することによって生成されるログの量は、セグメントによって生成されるログの量よりも少なくなります。

欠点:

スレーブ サーバー上のステートメントの実行結果がマスター サーバー上のステートメントの実行結果と同じであることを確認するには、コンテキスト情報を記録する必要があります。

UUID や USER() などの特定の非決定論的関数は複製できません。

これにより、MySQL レプリケーションのプライマリ サーバーとセカンダリ サーバーの間でデータの不整合が発生し、レプリケーション リンクが中断される可能性があります。

バイナリログ形式を表示

'binlog_format' のような変数を表示します。

セッションbinlog_format=ステートメントを設定します。

行ベースのログ形式

my.ini のバイナリ形式を binlog_format=ROW に変更します。

行の利点: 行形式は、MYSQL レプリケーションにおけるマスターとスレーブの不整合の問題を回避できます。この形式は公式に推奨されています。同じ SQL ステートメントで 10,000 個のデータが変更されます。セグメントベースのログ記録では、この SQL ステートメントのみが記録されます。行ベースのログには 10,000 件のレコードが含まれ、各データ行の変更が記録されます。

1. MySQL マスタースレーブレプリケーションはより安全です。

2. 各データ行を変更する方が、セグメントベースのレプリケーションよりも効率的です。データベース内のデータがエラー操作によって変更され、復元するバックアップがない場合は、バイナリ ログを分析し、ログに記録されたデータ変更操作を元に戻すことで、データを回復できます。

行の欠点: ログレコードの量が多い

binlog_row_image=[フル、最小、noblob]

full: 列に対するすべての変更を記録します。minimal: 変更された列のみを記録します。 noblob: フィールドがテキスト型または CLOB の場合、これらのログは記録されません。

詳細なログを表示するには、mysqlbinlog -vv ../data/mysql-bin.000005 を使用します。

セッションbinlog_row_image=minimalを設定する

ハイブリッドログ形式:

binlog_format=混合

機能: システムは、SQL ステートメントに基づいて、セグメントベースと行ベースのログ形式の選択を決定します。データの量は実行される SQL によって決まります。

バイナリ形式の選択方法

binlog_format = mixed または binlog_format = row; binlog_row_image = minimal; を使用することをお勧めします。

コピー方法:

1. SQL ステートメントベースのレプリケーション (SBR)

利点: 生成されるログが少なくなり、ネットワーク転送用の ID が節約されます。マスター データベースとスレーブ データベースのテーブル定義がまったく同じである必要はありません。

行ベースのレプリケーションよりも柔軟性があります。

デメリット: 非決定論的なイベントの場合、マスターとスレーブ間のレプリケーション データの一貫性は保証されません。ストアドプロシージャ、トリガーの場合

2. 行ベースのレプリケーション (RBR)

利点: 非決定論的関数、ストアド プロシージャなどを含むあらゆる SQL レプリケーションに適用できます。データベース ロックの使用を削減できます。

デメリット: マスター データベースとスレーブ データベースのテーブル構造は同じである必要があります。そうでない場合、レプリケーションが中断されます。

3. 作業方法をコピーする

1. マスター サーバーは変更をバイナリ ログに書き込みます。

2. スレーブはマスターのバイナリ ログの変更を読み取り、それを relay_log に書き込みます。

ログポイントベースのレプリケーション、GTID ベースのレプリケーション。

3. スレーブ上の relay_log 内のログを再生します。

SQL セグメントベースのログ記録は、スレーブ データベースで記録された SQL を再実行します。

行ベースのログ記録では、スレーブ上のデータ行への変更が直接適用されます。

以上がMySQLトランザクションとMySQLログの詳細な説明です。MySQLトランザクションとMySQLログの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。