PostgreSQLユーザのためのMySQLバイナリログ・レプリケーション講座

MySQL PostgreSQL

Original


Streaming Replication搭載のPostgreSQL9.0リリースが近づき、MySQLとのレプリケーション比較が今後ますます盛んになると思われるので、PostgreSQLユーザ向けにMySQLの説明を行う。

参考->MySQLユーザのためのPostgreSQL:WALログとレプリケーション講座


なお、なぜ最初に延々とバイナリログを説明するかといえば、MySQLのレプリケーションでマスタからスレーブに送るのは、バイナリログのデータだからである。

[レベル:1] MySQLのバイナリログ

MySQLにはWALログに直接対応するものはない。理由はMySQLがマルチストレージエンジンだからである。
マルチストレージエンジンについては後述するのでここでは簡単に説明すると、 MySQLはトランザクション機能ありのInnoDB型というテーブルや、トランザクション機能なしのMyISAM型というテーブルを混在して同時利用できる。
歴史的にはトランザクションなしのMyISAM(正確にはISAM)だけでスタートしたMySQLには、トランザクションログ(REDOログ)は不要だったのである。

トランザクションログ(REDOログ)はストレージエンジンが独自に持つ

ver3.23でマルチストレージエンジン化したMySQLは、MyISAMだけでなくInnoDB社提供のInnoDB、 bdb(バークレーdb)などに対応した。
このうち、InnoDBはトランザクション対応の本格的なRDBで、トランザクションログ(REDOログ)もある。 MySQLの世界ではInnoDBストレージエンジンのトランザクションログを"innodbログ"という。
innodbログはInnoDB型のテーブル群のためだけに存在していて、 MySQLとしてトランザクションログ(REDOログ)を持っているわけではない。

バイナリログはPseude-REDOログ

MySQLとしてREDOログを持たないということは、まともなリカバリ手段がないということで、非常にツラい。そこでマルチストレージエンジン化とほぼ同時に”"バイナリログ"がサポートされた。
ひとつ前の正式版ver5.0まで、"バイナリログ"の正体は更新系SQLコマンドの羅列であった。

バイナリログはエディタで直接覗くことはできないが、 mysqlbinlogというプログラムを使って内容を表示できる。
以下に示すのは、ver5.0のバイナリファイルmysql-bin.000001の内容。

mysql$ mysqlbinlog mysql-bin.000001

.... 略 ....

# at 719
#100818  4:28:27 server id 1  end_log_pos 754   Rand
SET @@RAND_SEED1=847758915, @@RAND_SEED2=773778930/*!*/;
# at 754
#100818  4:28:27 server id 1  end_log_pos 872   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1282073307/*!*/;
INSERT INTO tbl VALUES(rand()*10000, now())/*!*/;
# at 872
#100818  4:28:30 server id 1  end_log_pos 907   Rand
SET @@RAND_SEED1=95830206, @@RAND_SEED2=869609169/*!*/;
# at 907
#100818  4:28:30 server id 1  end_log_pos 1025  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1282073310/*!*/;
INSERT INTO tbl VALUES(rand()*10000, now())/*!*/;
DELIMITER ;
.... 略 ....

ご覧のように、INSERT文が列挙されている。ここで、乱数rand()と現在時刻now()が直接INSERT文中にあることに注目。

あえて非決定的な関数rand()や取扱いが面倒なnow()を使ったのだが、例えば乱数は、直前に"SET @@RAND_SEED1=xxx"など乱数シードを直接設定、また時刻は"SET TIMESTAMP=xxx"と時刻を直接設定、しているので、別のサーバ上でこのバイナリログを(クライアントに喰わせるなどして)実行しても同じ結果になる。

SQL文が書き込まれるタイミングは、トランザクションありのストレージエンジンの場合はCOMMIT直後、トランザクションなしの場合は実行直後になる。実行終了後でないことに注意。理由はレプリケーションのところで説明する。


ここまでが、version5.0までのバイナリログの状況。

[レベル:2] バイナリログの内部フォーマット

2008年末にリリースされたversion5.1から、バイナリログの内部フォーマットは3種類になった。

ステートメントベースロギング(Stetement-Based Logging:SBL)

バージョン5.1.4までのバイナリログは、変更分をすべてSQL文で記述していた。これをMySQLの用語ではステートメントベース ロギング(SBL:Stetement-Based Logging)という。先に示したバイナリログmysql-bin.000001はステートメントベースロギングの例である。

(先に説明したように)乱数や時刻などはSET文で内部変数を直接操作してしまうのでリカバリやレプリケーションしても問題ないのだが、 (それでも)次の関数を含むSQL文はリカバリやレプリケーションが不正確になる欠点がある。

LOAD_FILE(), UUID(), USER(), FOUND_ROWS()
ローベースロギング(Row-Based Logging:RBL)

これに対し、バージョン5.1.5から新しい内部フォーマットとしてローベース ロギング(RBL:Row-Based Logging)がサポートされた。これは、変化のあった行の変更分を直接バイナリデータとして保存するものである。

具体例を示す。

…… 略 ……

#795 16:45:18 server id 1  end_log_pos 155      Table_map: `sampledb`.`test` mapped to number 15
#795 16:45:18 server id 1  end_log_pos 194      Write_rows: table id 15 flags: STMT_END_F

BINLOG '
jl7eRhMBAAAAMQAAAJsAAAAAAA8AAAAAAAAACHNhbXBsZWRiAAR0ZXN0AAID/AECAw==
jl7eRhcBAAAAJwAAAMIAAAAQAA8AAAAAAAEAAv/8AwAAAAMAY2Nj
'/*!*/;
# at 194
# at 243
#795 16:45:26 server id 1  end_log_pos 243      Table_map: `sampledb`.`test` mapped to number 15
#795 16:45:26 server id 1  end_log_pos 282      Write_rows: table id 15 flags: STMT_END_F

BINLOG '
ll7eRhMBAAAAMQAAAPMAAAAAAA8AAAAAAAAACHNhbXBsZWRiAAR0ZXN0AAID/AECAw==
ll7eRhcBAAAAJwAAABoBAAAQAA8AAAAAAAEAAv/8BAAAAAMAZGRk
'/*!*/;
# at 282

…… 略 ……

これまでSQL文が直接書き込まれていた部分に、"BINLOG"とバイナリデータを意味する英数字や記号が並んでいる。これが行データそのものである。

これにより、ステートメントベースのように問題を起こす関数がなくなった。ただし、更新行ごとにデータが書き込まれるので、必然的にデータ量は多くなる。

ミックスベースロギング(Mixed-Based Logging:MBL)

ステートメントベースロギングとローベースロギングはそれぞれ一長一短があるので、バージョン5.1.8からそれらをミックスしたミックスベースロギングがサポートされた。
これは、以下の条件のときにローベースロギングを行い、それ以外はステートメントベースロギングを行う方法である。

【ミックスベースロギングにおいて、ローベースロギングを行う条件】

  1. NDB型テーブルでデータ操作(INSERT,UPDATE,DELETE)を行う
  2. 関数がUUID()を含む
  3. AUTO_INCREMENTが設定された複数のテーブルを更新する
  4. INSERT DELAYED文を実効するとき
  5. ユーザ定義関数を呼び出す

ローベースロギングの"BINLOG":バイナリデータとマルチストレージエンジンの実装方法

ところでローベースロギングで"BINLOG"とただし書きされたバイナリデータは具体的にはどんなデータなのか？これを説明するには、まずマルチストレージエンジンの実装方法から説明しなければならない。

ということで、PostgreSQLユーザにとって簡潔でわかりやすい説明文がここにある。文中"プラガブルストレージエンジン"を"マルチストレージエンジン"と読みかえてほしい。


http://cydn.cybozu.co.jp/2007/07/mysql_51.htmlから引用

プラガブルストレージエンジンは、MySQL 上に任意のストレージエンジン
を読み込むことができる機構です。この機構を使えば SQL のパースや最適
化などを MySQL に任せて、データの取得処理に任意のストレージエンジン
を使うことができます。

プラガブルストレージエンジンアーキテクチャにおいて、ストレージエンジン
の実体は handler というクラスを継承した子クラスです。
( 実装は MySQL と同様に C++ です。)
この handler クラスには、MySQL が SQL を実行する際に使用するメソッド
が定義されています。これらのメソッドを実装して、データへのアクセスを
実現します。

つまりオリジナルのストレージエンジンを書くというのは、handler クラス
を継承した子クラスを定義して、その中で決められたメソッドを実装するこ
とを意味します。

どうでしょう？
以下の図を見ていただくと一目瞭然だが、各種ストレージエンジンへの操作メソッドが統一される層(図中Storage engine abstraction layer)ができるので、 MySQLはパースやプランナなどSQL操作に注力する部分と、複数のストレージエンジンの混在が可能なのである。

http://japan.internet.com/vi/developer/img/article/1539/1539_01.gif を編集

本題に戻ると、"BINLOG"のバイナリデータは、ストレージエンジンのメソッドに渡す行データそのものである。「どのテーブルの何行目をどのデータ(OLD_DATA)からどのデータ(NEW_DATA)へ更新」という情報をバイナリで記述しているわけだ。

図中、SBL(ステートメントベースロギング)とRBL(ローベースロギング)を追加したが、 SBLはSQL文そのもの、RBLはパースなどが終わってストレージエンジンに更新を依頼する直前のデータがそれぞれ書き込まれていることが分かっていただけると幸い。
また、無理矢理だがWALに相当するレイヤ?も念のため書き添えてある。

[レベル:3] レプリケーション

MySQLのレプリケーションは、バイナリログを使ったマスタースレーブ型非同期レプリケーションである。これはPostgreSQL9.0のSR+HotStandbyと完全にカブる機能で、比較したくもなるはずである。

動作シーケンス

スレーブは2つのスレッド(I/OスレッドとSQLスレッド)、マスターは接続してくるスレーブ毎に1つのスレッド(Binlog_dumpスレッド)を生成し、レプリケーション機能を実現している。

以下にマスターとスレーブのスレッドと、それらのスレッドの動作シーケンスを示す。

  1. スレーブ側でSTART SLAVE文を実行すると、
  2. I/Oスレッドが起動され、
  3. マスターに接続要求を送信する
  4. マスターはBinlog_dumpスレッドを起動し、
  5. ログをスレーブに送信する
  6. ログを受信したスレーブはリレーログに書き込み、
  7. SQLスレッドを起動する
  8. SQLスレッドはリレーログを読み込み、データベースにデータを反映(クエリを実行)する
  9. 以降、マスターのデータ更新の度にログが送られ、随時データベースに反映される

ここまでかなり長い道のりだったので忘れてしまったかもしれないが、ここでマスタからスレーブに送っているデータはバイナリログそのものである。

ここまでくれば、RBLはスレーブ側でパースなどの処理が不要なので、データ反映が効率的にできることも自明である。

機構

バイナリログの書き込みタイミングとレプリケーション

前述したが、マスタ側でバイナリログを書き込むタイミングは、 InnoDBなどトランザクションありのストレージエンジンの場合はCOMMIT直後、 MyISAMなどトランザクションなしの場合は実行直後である。


トランザクションありの場合、COMMIT後に変更分(ステートメントベースならSQL文、ローベースならBINLOGバイナリデータ) がスレーブに送信される。 COMMIT後なので、トランザクション分離レベルなどデータ競合の面倒な問題はマスタ側が解決済み。よって、マスタとスレーブでデータの不一致は起きない。


トランザクションなしの場合、SQLの実行直後にバイナリログに記録する。実行終了後ではない。(修正：2013/12/22)これ、少なくともMyISAMでは間違い　m(_ _)m。書き込まれるのは実行終了後。
これは2つのSQLが実行されることを考えればわかるだろう。 SQLの実行順序が正確にバイナリログに反映されなければならない。更新A->更新Bの順に実行されたのに、更新Bが早く終了したからといって、バイナリログに「更新B->更新A」の順序で記録されてはまずい。(追記：2013/12/22)なぜ実行終了後の書込みで実行順序が保存されるかというと、MyISAMはテーブルロックなので、先に更新したほうが先に終るため（後から更新したほうは、前のが終るまで待たされる）。


ということで、RBLを使っていればマスタ-スレーブ間でデータの不一致は起きない。「バイナリログは論理ログだから…」と心配しなくてよい*1。

スレーブ側の2つのスレッドの意味

スレーブ側の機構について一言。
リレーログがバッファの役割を果たしているので、 SQLスレッドの処理が間に合わなくともIOスレッドはログを受信しつづけることができる。データの取りこぼしはない*2。
なお、SQLスレッドによって反映されたリレーログのデータは随時削除されていく。

ユーザからみたレプリケーションの機能

PostgreSQL9.0のStreaming Replicationと比較して、ユーザからみて羨ましい部分を列挙する。

* カスケード接続可すでに対応
スレーブの接続はわりと自由。

*テーブル単位、DB単位でレプリケーション可
レプリケーションするテーブルやDBを指定できる。


これら以外は、たとえばマスタもスレーブも運用中に停止、起動できるなど、SR+Hotstandbyと同期能を有している。

*1:SBLでも先に書いた不一致を起こす関数を使わない限り問題ない

*2:この機構はver4.0から。最初のレプリケーション3.23ではスレーブ側のスレッドも1つだった