mysqlでbinlog_formatモードと構成詳細分析
3680 ワード
mysqlレプリケーションには、SQL文ベースのレプリケーション(statement-based replication,SBR)、行ベースのレプリケーション(row-based replication,RBR)、ハイブリッドモードレプリケーション(mixed-based replication,MBR)の3つの方法があります.対応して、binlogのフォーマットも3種類あります:STATEMENT、ROW、MIXED.
①STATEMENTモード(SBR)
データを変更するsql文はbinlogに記録されます.利点は、各sql文と各行のデータ変化を記録する必要がなく、binlogログ量を削減し、IOを節約し、パフォーマンスを向上させることです.欠点は、場合によってはmaster-slaveのデータが一致しない(sleep()関数、last_insert_id()やuser-defined functions(udf)などで問題が発生します)
②ROWモード(RBR)
各sql文のコンテキスト情報は記録されず、どのデータが変更され、どのように変更されたかを記録するだけです.また、特定の場合のストレージ・プロシージャ、またはfunction、またはtriggerの呼び出しやトリガが正しくコピーされないという問題は発生しません.欠点は、大量のログが発生し、特にalter tableの場合、ログが急騰することです.
③MIXEDモード(MBR)
以上の2つのモードの混合使用は、一般的なレプリケーションではSTATEMENTモードを使用してbinlogを保存し、STATEMENTモードではコピーできない操作ではROWモードを使用してbinlogを保存し、MySQLは実行したSQL文に基づいてログ保存方式を選択します.
binlogレプリケーション構成
mysqlのプロファイルmy.cnfでは、binglog関連をオプションで構成できます.
三MIXED説明
実行されるSQL文にnow()という時間関数が含まれている場合、ログに対応するunix_が生成されます.timestamp()*1000の時間文字列、slaveは同期が完了したときにsqlEventが発生した時間を取ってデータの正確性を保証します.また、いくつかの機能関数slaveについては対応するデータの同期を完了することができ、上記で指定したUDF関数のようなものについては、Slaveが認識できない場合、これらのBinlogがROW形式で格納され、生成されたBinlogがSlaveによってデータの同期を完了することを保証します.
次に、以下のSBRとRBR 2の各モードの長所と短所を比較する.
SBRの利点:
歴史が古く,技術が成熟している.
binlogファイルが小さい
binlogにはすべてのデータベース変更情報が含まれており、これに基づいてデータベースのセキュリティなどを監査することができます.
binlogは、レプリケーションだけでなく、リアルタイムのリストアに使用できます.
プライマリ・スレーブ・バージョンは異なりますが、セカンダリ・サーバ・バージョンはプライマリ・サーバ・バージョンよりも高くなります.
SBRの欠点:
すべてのUPDATE文がコピーできるわけではありません.特に不確定な操作が含まれている場合です.
不確定な要因を持つUDFを呼び出すと、レプリケーションに問題が発生する可能性があります.
次の関数を使用する文もコピーできません.
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
*SYSDATE()(起動時に--sysdate-is-nowオプションが有効になっていない場合)
INSERT ... SELECTではRBRよりも多くの行レベルロックが発生します
全テーブルスキャン(WHERE文でインデックスが使用されていない)を必要とするUPDATEをコピーする場合は、RBR要求よりも多くの行レベルロックが必要です
AUTO_がある場合INCREMENTフィールドのInnoDBテーブルでは、INSERT文が他のINSERT文をブロックします.
いくつかの複雑な文では、サーバからのリソース消費がさらに深刻になりますが、RBRモードでは、その変化したレコードにのみ影響を与えるストレージ関数(ストレージプロセスではありません)が呼び出されると同時にNOW()関数も1回実行されます.これは悪いことと言ってもいいかもしれません.
確定したUDFもサーバから実行する必要がある
データ・テーブルは、プライマリ・サーバとほぼ一致する必要があります.そうしないと、レプリケーション・エラーが発生する可能性があります.
複雑な文の実行エラーが発生すると、より多くのリソースが消費されます.
RBRの利点:
どのような場合でもコピーできます.これはコピーにとって最も安全で信頼性が高いです.
他のほとんどのデータベース・システムのレプリケーション・テクノロジーと同様
多くの場合、サーバ上のテーブルからプライマリ・キーがあれば、レプリケーションが大幅に速くなります.
次のような文をコピーする場合、ロー・ロックは少なくなります.
* INSERT ... SELECT
*AUTOを含むINCREMENTフィールドのINSERT
*条件が付いていない、または多くのレコードを変更していないUPDATE文またはDELETE文
INSERT、UPDATE、DELETE文を実行するとロックが少なくなります
サーバからのマルチスレッドによるレプリケーションが可能
RBRの欠点:
binlogはずいぶん大きくなりました
複雑なロールバック時にbinlogに大量のデータが含まれます
プライマリ・サーバでUPDATE文を実行すると、変更されたレコードはすべてbinlogに書き込まれますが、SBRは1回しか書かれません.binlogの同時書き込みの問題が頻繁に発生します.
UDFによって生成される大きなBLOB値はレプリケーションを遅くする
binlogから見ることができなくて、すべてどんな文をコピーしました
非トランザクション・テーブル上でSQL文を積み重ねて実行する場合は、SBRモードを採用することが望ましい.そうしないと、プライマリ・スレーブ・サーバのデータが一致しない場合が発生しやすい.また、システム・ライブラリmysql内のテーブルが変化した場合の処理ルールは以下の通りである.
INSERT,UPDATE,DELETEを用いてテーブルを直接操作する場合、ログフォーマットはbinlog_formatの設定で記録
GRANT、REVOKE、SET PASWORDなどの管理文を採用しているのであれば、どうしてもSBRモードで記録します
注:RBRモードを採用すると、元のプライマリ・キーの重複問題を多く解決できます.
まとめ
以上、mysqlにおけるbinlogについてformatモードと構成の詳細分析のすべての内容は、皆さんに役立つことを望んでいます.興味のある方は、いくつかの重要なMySQL変数、MySQL prepare原理の詳細、MySQL宣言変数、ストレージプロセスの分析などを参照してください.
①STATEMENTモード(SBR)
データを変更するsql文はbinlogに記録されます.利点は、各sql文と各行のデータ変化を記録する必要がなく、binlogログ量を削減し、IOを節約し、パフォーマンスを向上させることです.欠点は、場合によってはmaster-slaveのデータが一致しない(sleep()関数、last_insert_id()やuser-defined functions(udf)などで問題が発生します)
②ROWモード(RBR)
各sql文のコンテキスト情報は記録されず、どのデータが変更され、どのように変更されたかを記録するだけです.また、特定の場合のストレージ・プロシージャ、またはfunction、またはtriggerの呼び出しやトリガが正しくコピーされないという問題は発生しません.欠点は、大量のログが発生し、特にalter tableの場合、ログが急騰することです.
③MIXEDモード(MBR)
以上の2つのモードの混合使用は、一般的なレプリケーションではSTATEMENTモードを使用してbinlogを保存し、STATEMENTモードではコピーできない操作ではROWモードを使用してbinlogを保存し、MySQLは実行したSQL文に基づいてログ保存方式を選択します.
binlogレプリケーション構成
mysqlのプロファイルmy.cnfでは、binglog関連をオプションで構成できます.
binlog_format = MIXED //binlog ，mysql statement， mixed
log-bin = /data/mysql/mysql-bin.log //binlog
expire_logs_days = 7 //binlog
max_binlog_size = 100m //binlog
binlog_cache_size = 4m //binlog
max_binlog_cache_size = 512m // binlog 三MIXED説明
実行されるSQL文にnow()という時間関数が含まれている場合、ログに対応するunix_が生成されます.timestamp()*1000の時間文字列、slaveは同期が完了したときにsqlEventが発生した時間を取ってデータの正確性を保証します.また、いくつかの機能関数slaveについては対応するデータの同期を完了することができ、上記で指定したUDF関数のようなものについては、Slaveが認識できない場合、これらのBinlogがROW形式で格納され、生成されたBinlogがSlaveによってデータの同期を完了することを保証します.
次に、以下のSBRとRBR 2の各モードの長所と短所を比較する.
SBRの利点:
歴史が古く,技術が成熟している.
binlogファイルが小さい
binlogにはすべてのデータベース変更情報が含まれており、これに基づいてデータベースのセキュリティなどを監査することができます.
binlogは、レプリケーションだけでなく、リアルタイムのリストアに使用できます.
プライマリ・スレーブ・バージョンは異なりますが、セカンダリ・サーバ・バージョンはプライマリ・サーバ・バージョンよりも高くなります.
SBRの欠点:
すべてのUPDATE文がコピーできるわけではありません.特に不確定な操作が含まれている場合です.
不確定な要因を持つUDFを呼び出すと、レプリケーションに問題が発生する可能性があります.
次の関数を使用する文もコピーできません.
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
*SYSDATE()(起動時に--sysdate-is-nowオプションが有効になっていない場合)
INSERT ... SELECTではRBRよりも多くの行レベルロックが発生します
全テーブルスキャン(WHERE文でインデックスが使用されていない)を必要とするUPDATEをコピーする場合は、RBR要求よりも多くの行レベルロックが必要です
AUTO_がある場合INCREMENTフィールドのInnoDBテーブルでは、INSERT文が他のINSERT文をブロックします.
いくつかの複雑な文では、サーバからのリソース消費がさらに深刻になりますが、RBRモードでは、その変化したレコードにのみ影響を与えるストレージ関数(ストレージプロセスではありません)が呼び出されると同時にNOW()関数も1回実行されます.これは悪いことと言ってもいいかもしれません.
確定したUDFもサーバから実行する必要がある
データ・テーブルは、プライマリ・サーバとほぼ一致する必要があります.そうしないと、レプリケーション・エラーが発生する可能性があります.
複雑な文の実行エラーが発生すると、より多くのリソースが消費されます.
RBRの利点:
どのような場合でもコピーできます.これはコピーにとって最も安全で信頼性が高いです.
他のほとんどのデータベース・システムのレプリケーション・テクノロジーと同様
多くの場合、サーバ上のテーブルからプライマリ・キーがあれば、レプリケーションが大幅に速くなります.
次のような文をコピーする場合、ロー・ロックは少なくなります.
* INSERT ... SELECT
*AUTOを含むINCREMENTフィールドのINSERT
*条件が付いていない、または多くのレコードを変更していないUPDATE文またはDELETE文
INSERT、UPDATE、DELETE文を実行するとロックが少なくなります
サーバからのマルチスレッドによるレプリケーションが可能
RBRの欠点:
binlogはずいぶん大きくなりました
複雑なロールバック時にbinlogに大量のデータが含まれます
プライマリ・サーバでUPDATE文を実行すると、変更されたレコードはすべてbinlogに書き込まれますが、SBRは1回しか書かれません.binlogの同時書き込みの問題が頻繁に発生します.
UDFによって生成される大きなBLOB値はレプリケーションを遅くする
binlogから見ることができなくて、すべてどんな文をコピーしました
非トランザクション・テーブル上でSQL文を積み重ねて実行する場合は、SBRモードを採用することが望ましい.そうしないと、プライマリ・スレーブ・サーバのデータが一致しない場合が発生しやすい.また、システム・ライブラリmysql内のテーブルが変化した場合の処理ルールは以下の通りである.
INSERT,UPDATE,DELETEを用いてテーブルを直接操作する場合、ログフォーマットはbinlog_formatの設定で記録
GRANT、REVOKE、SET PASWORDなどの管理文を採用しているのであれば、どうしてもSBRモードで記録します
注:RBRモードを採用すると、元のプライマリ・キーの重複問題を多く解決できます.
まとめ
以上、mysqlにおけるbinlogについてformatモードと構成の詳細分析のすべての内容は、皆さんに役立つことを望んでいます.興味のある方は、いくつかの重要なMySQL変数、MySQL prepare原理の詳細、MySQL宣言変数、ストレージプロセスの分析などを参照してください.