[MySQL]ログ形式binlog_format
MySQL 5.5でバイナリ・ログ(binlog)には、Mixed、Statement、Rowの3つの異なるフォーマットがあります.デフォルトのフォーマットはStatementです.この3つのフォーマット・ログのメリットとデメリットをまとめます.
MySQL Replicationレプリケーションは、1つの文(Statement Level)に基づいてもよいし、1つのレコード(Row Level)に基づいてもよい.MySQLの構成パラメータでこのレプリケーションレベルを設定することができ、異なるレプリケーションレベルの設定はMaster側のbin-logログフォーマットに影響を与える.
1.Rowログには、各ローのデータが変更された形で記録され、slave側で同じデータが変更されます.
利点:rowモードではbin-logに実行されるSQL文のコンテキストに関する情報を記録せずに、その記録が変更されたことを記録するだけで、どのように変更されたかを記録することができます.したがってrowのログ内容は、各行のデータ修正の詳細を明確に記録し、理解しやすい.また、特定の場合のストレージ・プロシージャやfunction、triggerの呼び出しやトリガが正しくコピーされないという問題は発生しません.
欠点:rowモードでは、実行されたすべての文がログに記録されると、各行のレコードの変更で記録されます.これにより、update文のような大量のログ内容が発生する可能性があります.
実行後、ログに記録されるのは、このupdate文に対応するイベント(MySQLはbin-logログをイベント形式で記録する)ではなく、この文が更新した各レコードの変化状況であり、多くのレコードが更新された多くのイベントとして記録される.もちろんbin-logログの量は大きくなります.特にalter tableのような文を実行する場合、発生するログの量は驚くべきものです.MySQLはalter tableのようなテーブル構造変更文の扱い方がテーブル全体のレコードごとに変動するため、実際にはテーブル全体を再構築しています.テーブルの各レコードがログに記録されます.
2.Statementデータを変更するSQLは、masterのbin-logに記録されます.slaveはレプリケーション時にSQLプロセスを元のマスター側が実行したのと同じSQLに解析して再実行します.
利点:statementモードでは、まずrowモードの欠点を解決し、各行のデータの変化を記録する必要がなく、bin-logログ量を減らし、I/Oとストレージリソースを節約し、性能を高める.マスターで実行された文の詳細と、文を実行するときのコンテキストの情報だけを記録する必要があるからです.
欠点:statementモードでは、記録された実行文であるため、slave側でも正しく実行できるように、各文の実行時の関連情報、すなわちコンテキスト情報を記録する必要があります.すべての文がslaveエンドカップで実行されたときにmasterエンドで実行されたときと同じ結果が得られることを保証します.また、MySQLは現在の発展が比較的速いため、多くの新機能が絶えず加入し、MySQLのレプリケーションに小さな挑戦に直面し、自然レプリケーションの時に複雑な内容に触れるほど、バグも現れやすい.statementでは、現在発見されているMySQLのレプリケーションに問題が発生することが少なくありません.主にデータを変更する際に特定の関数や機能を使用した場合に発生します.例えば、sleep()関数はバージョンによっては正しく復元されず、ストレージ中にlast_を使用しています.insert_id()関数は、slaveとmasterで一致しないidなどを得る可能性があります.rowは行ごとに記録される変化であるため,類似の問題は生じない.
3.Mixedは5.1.8バージョンから始まり、MySQLはStatementとRowを除く3番目のレプリケーションモードを提供しています.Mixedは、実際には前の2つのモードの結合です.Mixedモードでは、MySQLは、実行される各SQL文に基づいてレコードに対するログ形式を区別します.つまり、statementとrowの間で1つを選択します.新しいバージョンのstatmentは、以前と同様に実行された文のみを記録します.新バージョンのMySQLではrowモードも最適化されており、すべての変更がrowモードで記録されるわけではありません.例えば、テーブル構造の変更に遭遇したときにstatementモードで記録され、SQL文がupdateやdeleteなどのデータを変更した文であれば、すべての行の変更が記録されます.
その他の参照情報
以下の場合を除き、binlogのフォーマットを実行時に動的に変更できます.ストレージプロセスまたはトリガの中間; はNDBを有効にした. 現在のセッションではrowモードが使用され、テンポラリ・テーブルが開かれています.
binlogがMixedモードを使用している場合、binlogのモードはstatementモードからrowモードに自動的に変わります. DML文がNDBテーブルを更新するとき. 関数にUUID()が含まれている場合. 個以上AUTOを含むINCREMENTフィールドのテーブルが更新された場合 INSERT DELAYED文を実行する場合. UDF使用時; ビューでrowの運用が要求されなければならない場合、例えばビューの確立時にUUID()関数が使用される.
プロファイルmy.iniマスターコピーモードを設定する:
binlogのフォーマットは、実行時に動的に変更することもできます.例:
**二つのモードの対比:Statementの長所**歴史が長く、技術が成熟している;生成されたbinlogファイルは小さい.binlogにはすべてのデータベース修正情報が含まれており、これに基づいてデータベースのセキュリティなどを審査することができる.binlogは、レプリケーションだけでなく、リアルタイムのリストアに使用できます.プライマリ・スレーブ・バージョンは異なり、セカンダリ・サーバ・バージョンはプライマリ・サーバ・バージョンよりも高くなります.
Statementの欠点:すべてのUPDATE文がコピーできるわけではありません.特に不確定な操作が含まれている場合.不確定な要因を持つUDFを呼び出すと、レプリケーションに問題が発生する可能性があります.次の関数を使用する文もコピーできません. LOAD_FILE() UUID() USER() FOUND_ROWS() SYSDATE()(起動時に-sysdate-is-nowオプションが有効になっていない限り)INSERT...SELECTはRBRよりも多くの行レベルロックを生成します.全テーブルスキャン(WHERE文でインデックスが使用されていない)を実行する必要があるUPDATEをコピーする場合は、rowリクエストよりも多くの行レベルロックが必要です.AUTO_がある場合INCREMENTフィールドのInnoDBテーブルでは、INSERT文が他のINSERT文をブロックします.いくつかの複雑な文では、サーバからのリソース消費がさらに深刻になりますが、rowモードでは、その変化したレコードにのみ影響を与えます.ストレージ関数(ストレージフローではない)は呼び出されると同時にNOW()関数も1回実行されるので、これは悪いことと言っても良いことかもしれません.確定したUDFもサーバーから実行する必要がある.データ・テーブルは、プライマリ・サーバとほぼ一致する必要があります.そうしないと、レプリケーション・エラーが発生する可能性があります.複雑な文を実行してエラーが発生すると、より多くのリソースが消費されます.
Rowの利点は、どのような場合でもコピーできます.これはコピーにとって最も安全で信頼性があります.他のほとんどのデータベースシステムのレプリケーションスキルと同じです.多くの場合、サーバ上のテーブルからプライマリ・キーがあれば、レプリケーションが大幅に速くなります.次のような文をコピーする場合、ロー・ロックは少なくなります. INSERT … SELECT AUTOを含むINCREMENTフィールドのINSERT 条件が付いていないか、多くのレコードを変更していないUPDATE文またはDELETE文はINSERTを実行し、UPDATE文はDELETE文のロックが少ない.サーバからマルチスレッドを用いてレプリケーションを実行することが可能になる.
Rowの欠点はbinlogログの体積が大きくなった.複雑なロールバック時にbinlogには大量のデータが含まれます.プライマリ・サーバでUPDATE文を実行すると、変更されたすべてのレコードがbinlogに書き込まれ、statementは1回しか書かれず、binlogの書き込み同時要求が頻繁に発生します.UDFによって生成された大きなBLOB値はレプリケーションを遅くする.binlogからどんな文がコピーされているか(暗号化されている)を見ることはできません.非トランザクション・テーブルで積み上げられたSQL文を実行する場合は、statementモードを採用するとよい.そうしないと、プライマリ・スレーブ・サーバのデータの不一致が発生しやすい.また、システムライブラリMySQL内のテーブルが変化した場合の処理基準は、INSERT,UPDATE,DELETEを用いてテーブルを直接操作した場合、binlog_formatの設定で記録する.GRANT、REVOKE、SET PASWORDなどの管理文を採用しているのであれば、どうしてもstatementモードで記録しなければなりません.statementモードを使用すると、元のプライマリ・キーの重複問題を多く処理できます.
原文リンク:MySQLログ形式binlog_format
MySQL Replicationレプリケーションは、1つの文(Statement Level)に基づいてもよいし、1つのレコード(Row Level)に基づいてもよい.MySQLの構成パラメータでこのレプリケーションレベルを設定することができ、異なるレプリケーションレベルの設定はMaster側のbin-logログフォーマットに影響を与える.
1.Rowログには、各ローのデータが変更された形で記録され、slave側で同じデータが変更されます.
利点:rowモードではbin-logに実行されるSQL文のコンテキストに関する情報を記録せずに、その記録が変更されたことを記録するだけで、どのように変更されたかを記録することができます.したがってrowのログ内容は、各行のデータ修正の詳細を明確に記録し、理解しやすい.また、特定の場合のストレージ・プロシージャやfunction、triggerの呼び出しやトリガが正しくコピーされないという問題は発生しません.
欠点:rowモードでは、実行されたすべての文がログに記録されると、各行のレコードの変更で記録されます.これにより、update文のような大量のログ内容が発生する可能性があります.
UPDATE product SET owner_member_id = 'b' WHERE owner_member_id = 'a' 実行後、ログに記録されるのは、このupdate文に対応するイベント(MySQLはbin-logログをイベント形式で記録する)ではなく、この文が更新した各レコードの変化状況であり、多くのレコードが更新された多くのイベントとして記録される.もちろんbin-logログの量は大きくなります.特にalter tableのような文を実行する場合、発生するログの量は驚くべきものです.MySQLはalter tableのようなテーブル構造変更文の扱い方がテーブル全体のレコードごとに変動するため、実際にはテーブル全体を再構築しています.テーブルの各レコードがログに記録されます.
2.Statementデータを変更するSQLは、masterのbin-logに記録されます.slaveはレプリケーション時にSQLプロセスを元のマスター側が実行したのと同じSQLに解析して再実行します.
利点:statementモードでは、まずrowモードの欠点を解決し、各行のデータの変化を記録する必要がなく、bin-logログ量を減らし、I/Oとストレージリソースを節約し、性能を高める.マスターで実行された文の詳細と、文を実行するときのコンテキストの情報だけを記録する必要があるからです.
欠点:statementモードでは、記録された実行文であるため、slave側でも正しく実行できるように、各文の実行時の関連情報、すなわちコンテキスト情報を記録する必要があります.すべての文がslaveエンドカップで実行されたときにmasterエンドで実行されたときと同じ結果が得られることを保証します.また、MySQLは現在の発展が比較的速いため、多くの新機能が絶えず加入し、MySQLのレプリケーションに小さな挑戦に直面し、自然レプリケーションの時に複雑な内容に触れるほど、バグも現れやすい.statementでは、現在発見されているMySQLのレプリケーションに問題が発生することが少なくありません.主にデータを変更する際に特定の関数や機能を使用した場合に発生します.例えば、sleep()関数はバージョンによっては正しく復元されず、ストレージ中にlast_を使用しています.insert_id()関数は、slaveとmasterで一致しないidなどを得る可能性があります.rowは行ごとに記録される変化であるため,類似の問題は生じない.
3.Mixedは5.1.8バージョンから始まり、MySQLはStatementとRowを除く3番目のレプリケーションモードを提供しています.Mixedは、実際には前の2つのモードの結合です.Mixedモードでは、MySQLは、実行される各SQL文に基づいてレコードに対するログ形式を区別します.つまり、statementとrowの間で1つを選択します.新しいバージョンのstatmentは、以前と同様に実行された文のみを記録します.新バージョンのMySQLではrowモードも最適化されており、すべての変更がrowモードで記録されるわけではありません.例えば、テーブル構造の変更に遭遇したときにstatementモードで記録され、SQL文がupdateやdeleteなどのデータを変更した文であれば、すべての行の変更が記録されます.
その他の参照情報
以下の場合を除き、binlogのフォーマットを実行時に動的に変更できます.
binlogがMixedモードを使用している場合、binlogのモードはstatementモードからrowモードに自動的に変わります.
プロファイルmy.iniマスターコピーモードを設定する:
log-bin=mysql-bin
#binlog_format=STATEMENT
#binlog_format=ROW
binlog_format=MIXEDbinlogのフォーマットは、実行時に動的に変更することもできます.例:
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED'; **二つのモードの対比:Statementの長所**歴史が長く、技術が成熟している;生成されたbinlogファイルは小さい.binlogにはすべてのデータベース修正情報が含まれており、これに基づいてデータベースのセキュリティなどを審査することができる.binlogは、レプリケーションだけでなく、リアルタイムのリストアに使用できます.プライマリ・スレーブ・バージョンは異なり、セカンダリ・サーバ・バージョンはプライマリ・サーバ・バージョンよりも高くなります.
Statementの欠点:すべてのUPDATE文がコピーできるわけではありません.特に不確定な操作が含まれている場合.不確定な要因を持つUDFを呼び出すと、レプリケーションに問題が発生する可能性があります.次の関数を使用する文もコピーできません.
Rowの利点は、どのような場合でもコピーできます.これはコピーにとって最も安全で信頼性があります.他のほとんどのデータベースシステムのレプリケーションスキルと同じです.多くの場合、サーバ上のテーブルからプライマリ・キーがあれば、レプリケーションが大幅に速くなります.次のような文をコピーする場合、ロー・ロックは少なくなります.
Rowの欠点はbinlogログの体積が大きくなった.複雑なロールバック時にbinlogには大量のデータが含まれます.プライマリ・サーバでUPDATE文を実行すると、変更されたすべてのレコードがbinlogに書き込まれ、statementは1回しか書かれず、binlogの書き込み同時要求が頻繁に発生します.UDFによって生成された大きなBLOB値はレプリケーションを遅くする.binlogからどんな文がコピーされているか(暗号化されている)を見ることはできません.非トランザクション・テーブルで積み上げられたSQL文を実行する場合は、statementモードを採用するとよい.そうしないと、プライマリ・スレーブ・サーバのデータの不一致が発生しやすい.また、システムライブラリMySQL内のテーブルが変化した場合の処理基準は、INSERT,UPDATE,DELETEを用いてテーブルを直接操作した場合、binlog_formatの設定で記録する.GRANT、REVOKE、SET PASWORDなどの管理文を採用しているのであれば、どうしてもstatementモードで記録しなければなりません.statementモードを使用すると、元のプライマリ・キーの重複問題を多く処理できます.
原文リンク:MySQLログ形式binlog_format