MySQL レプリケーション基礎

MySQL

今日は、MySQLのレプリケーションのお話。バージョンは5.0を前提として説明。

MySQLにおけるレプリケーションは更新情報を記録したバイナリログ(binlog)をベースとしたアーキテクチャ。マスターでの更新情報をバイナリログとしてスレーブに転送、これをSQLに変換しスレーブで実行しデータ同期を行う。Oracle Data GuardのLogicalスタンバイによく似ている。

基本概念図を以下に示す。

特徴

  • 複数のスレーブを用意することで、参照系クエリの負荷分散が可能
  • マルチマスター構成の場合には更新系クエリの負荷分散も可能
    • ただし、この場合にはアプリの扱うデータに依存し実現可否が分かれる。
      • マルチマスター扱いとするオブジェクトを限定するなど、論理設計でも考慮が必要
  • マスタのバックアップとしてスレーブを切り離し、サービス無停止のままバックアップが可能
    • この場合、スレーブを停止しコールドバックアップ、スレーブを再開し再同期という手順になる
  • レプリケーションの内部フロー
    • スレーブサーバはマスタで作成したレプリケーションユーザを使用しマスタDBにアクセス。
    • dumpされたbinlogをI/Oスレッドが転送し、独立して動作するSQLスレッドが転送されたbinlogからSQLを生成、実行する
      • なお、マスターでのbinlog dumpスレッド数はスレーブノードの数に対してリニアである。
      • スレーブがn台あるマスターではn個のbinlog dumpスレッドが生成される。
  • 同期アーキテクチャ
  • Ver5.1での機能拡張

実装上のポイント

  • マスターでのログを更新と同期出力する
    • これは、スレーブのデータ同期以外にもマスターの耐障害性にも関わる重要な設定。下記の設定は必須。



sync_binlog=1

  • データ転送パケットサイズ
    • レプリケーションが一時停止した状態からきり戻す際、一度に大きなデータの転送が行われる場合がある。
    • この場合には転送パケットサイズが小さすぎるとエラーになるため、下記のパラメータにて転送エラーにならないよう値を変更。



max_allowed_packet=XXXXM

      • 上記設定のみで回避できない場合には、MTUの設定を見直すことも必要
  • スレーブでのbinlog出力
    • レプリケーションによる更新分のログはデフォルトでは出力されない(ただし、レプリケーションではなく直接スレーブDBを操作した場合のbinlogは出力される）。耐障害性やデータの保全の観点から、スレーブでもレプリケーションの更新ログを出力するには、以下のパラメータを設定。これは多段構成(ピラミッド型)のレプリケーションを行う際、中間サーバで必須の設定となる。



log-slave-updates

  • 参照専用スレーブ設定(Read-Only属性)
    • スレーブを参照専用として構成する場合、以下のパラメータでrootユーザ以外のユーザでのデータ更新を禁止する(ただし、当然ロギングを行わない方がディスクI/Oが減るためスレーブの性能は向上する）。



read_only

  • レプリケーション対象スキーマの限定
    • レプリケーションする対象スキーマを限定する場合には、以下に示すパラメータを設定する。詳細はhttp://dev.mysql.com/doc/refman/5.1/ja/replication-rules.htmlを参照
    • 注意すべき点としては、レプリケーション対象を限定した場合の性能面での効果はスレーブが実行するSQLスレッドの部分だけである点。直感的にはIOスレッドがノード間を転送するデータ(パケット量)も軽減されるよう思いがちだが、実際にはマスターで出力される全てのbinlogを転送した後で、SQLスレッドが実行すべきSQLをフィルタしている。つまり、ノード間を流れる同期用データ通信量をおさえネットワーク負荷を下げたい場合には、スレーブ側ではなくマスター側でbinlog-do-db/binlog-ignore-dbパラメータなどを指定し、出力する時点でフィルタする必要があるので注意が必要。



replicate-do-db=XXX

replicate-do-table=XXX

replicate-ignore-db=XXX

replicate-ignore-table=XXX

    • 複数のオブジェクトを指定する場合の書式は以下の通り（例：レプリケーション対象DBをtest1とtest2に限定）



replicate-do-db=test1

replicate-do-db=test2

  • スレーブからマスターへのアクセス制御
    • マスターへの接続がうまくいかない場合のスレーブの動作設定は以下のパラメータにて設定可能



master-connect-retry=<再接続試行までのスレーブのスリープ時間(秒)>

master-retry-count=<リトライ回数>

応用構成

マルチマスター

前述の基本構成を拡張しマルチマスター構成が可能。インスタンスレベルのread_only属性なし、レプリケーション対象の適切な限定などが前提となる。データの論理破壊が発生しないようデータの性質、アプリケーションの実装には十分な考慮が必要。

この場合には、マスターAとマスターBでマスター用、スレーブ用の各スレッドが動作する。

＜ポイント＞

  • レプリケーションのLOOP回避機構
    • 両ノードが互いのbinlogを読み込むが、生成するイベントを中継するサーバはレプリケーションの機構により回避される。
    • つまり、サーバAのbinlogがサーバBに渡りサーバBのSQLスレッドにより実行されても、そのクエリは再度サーバAでは実行されない。これはレプリケーションに使用されるユニークなserver-idがレプリケーションのログにタグ付けされることによる
ピラミッドレプリケーション

いわゆる多段構成のレプリケーション。ディザスタリカバリ環境としての利用や、トップをマルチマスター構成とした構成など、OSSならではの構成が可能。

  • 構成上のTips
    • 中間サーバが「データそのもの」を保持する必要がない場合、中間サーバのストレージエンジンをBlackHoleエンジンを使用した構成とすることも可能。ストレージエンジンがマスターとスレーブで同一である必要はないため、柔軟な構成が可能。例えば、マスターではトランザクション必須のためInnoDB、スレーブではMyISAMのFULLTEXTインデックスを使用する、などの構成を取れる。ただし、MyISAMではFKが使用できないなどの制限もあるのでストレージエンジン選定時には注意が必要。