ちょっと硬派なコンピュータフリークのBlogです。

カスタム検索

2009-03-11

MySQLレプリケーションを安全に利用するための10のテクニック

MySQLのレプリケーションは非常に簡単に使える割には応用の幅が広いので非常に人気のある機能の一つである。レプリケーションの応用分野は例えば、
  • バックアップ
  • 参照系の負荷分散
  • HA(高可用性)
  • ディザスタリカバリ(サイト間レプリケーション)
  • BI(レポーティングetc)
という風にとても多くのバリエーションがある。このブログを読んで頂いている皆さんの中にもレプリケーションを使っている方は多いのではないだろうか。ご覧の通りMySQLのレプリケーション機能はミッションクリティカル分野でも利用されているが、レプリケーションの使い方が適切でないとシステムの安定稼働に支障を来してしまってDBAやシステム管理者の肉体的、精神的負担が増大してしまう。逆にレプリケーションを堅牢に運用することが出来ればマクラを高くして眠れるというものだ。レプリケーションはMySQLの代表的な機能であるので、レプリケーションを使いこなしてこそ真のMySQLエンジニアであると言えよう。

というわけで、今日はMySQLのレプリケーションをトラブルから守って安全に利用するポイントを10個紹介する。

1. マルチマスターレプリケーションを利用しない

非常によくある誤解なのだが、HAにしたいからといってマルチマスター構成にしているユーザをたまに見かける。マルチマスターとは2台のMySQLサーバで構成するトポロジのことで、2つのサーバが互いに相手のマスターかつスレーブとなりレプリケーションを行う。マルチマスターは両方のホストで更新が可能なのだが、片方のサーバ行われた更新は非同期でもう一方のサーバへ適用されるため、更新を行っている方のサーバがクラッシュした場合には更新が失われる可能性がある。

また、マルチマスターは同時に同じ行を更新するとデータに不整合が生じてしまう。例えばINT型の主キーを持つテーブルにおいて主キーが10の行があるとき、片方のサーバでその行の主キーを11に、もう片方のサーバでその行の主キーを12に変更したとする。更新した直後は主キーの値は(11,12)であるが、その後非同期でレプリケーションが行われると(12,11)という状態になる。以下にマスターとスレーブにおける同一行(初期値10)の状態遷移を示す。

(10,10) ===同時更新==> (11,12) ===レプリケーション==> (12,11)

このような問題を防ぐには、アプリケーション側で対策をするしかない。マルチマスター構成は使い方が難しいので、よほどの事がない限り利用は避けた方がいいだろう。特にHA用途として利用しようと考えてはならない。HAにならないどころか更新の競合が発生する可能性があるし、ホストの台数が増えるのでハードウェア障害の確率も上昇してしまうという皮肉な結果に終わってしまう。

HA目的で利用するなら、現時点では非同期であるために最後に行ったわずかな更新が失われる可能性を考慮して、マスター・スレーブ構成のものを利用するといい。MySQL 6.0ではSemi-Synchronous Replicationによって、最後の更新を失うことなくHA化が可能である。

2. スレーブを更新しない

同じデータに対して同じクエリを実行すると同じ結果になる。これがMySQLレプリケーションの動作原理であり、マスターとスレーブで同じデータを持っていることがレプリケーションを行うための前提条件である。従って、決してスレーブ上でテーブルを更新してはならない。(スレーブだけにしか存在しないテーブルを作成して、そのテーブルを更新するのは可)MySQLはマスターとスレーブでデータに違いがある場合、それを自動的に修復するという機能はない。単にレプリケーションが止まるだけである。

スレーブを参照専用にするには、my.cnfファイルでread_onlyオプションを指定しておくと良い。read_onlyを指定しておけば、SUPER権限のないユーザは更新が出来なくなる。

3. 適切なモードを選ぶ

MySQL 5.1ではステートメントベースレプリケーション(SBR)、行ベースレプリケーション(RBR)、それらの混合モード(Mixed)という3つの種類からレプリケーションの方式を選択することが出来る。一般的にはSBRはログの容量がコンパクトで高速だとされている。が、SBR、RBRがそれぞれ利用出来ないような場合が存在する。

SBRでは非決定性のクエリをうまくレプリケーションすることができない。非決定性のクエリとは、実行する度に結果が異なるクエリのことであり、例えば次のようなものである。
  • UPDATE t SET id = UUID() WHERE ...; -- UUID()の値はランダムである。
  • UPDATE t SET col = 1 LIMIT 1; -- ORDER BYがないときは順序が保証されない。
  • UPDATE t SET col = myUDF(...); -- UDF、ユーザ定義関数の挙動はMySQLからは分からない。
SBRは上記のようなステートメントそのものがスレーブへ転送されてしまうため、非決定性のクエリの特性によりスレーブでは異なる実行結果となるのでマスターとスレーブのデータに食い違いが生じてしまう。RBRなら更新結果がスレーブへ転送されるためこのような問題は起こらない。

RBRは、MyISAMなどトランザクション非対応のストレージエンジンで利用するには向かない。トランザクション対応のストレージエンジンならばCOMMITと同時にバイナリログが記録されるので問題無いが、MyISAMなどでは一度に大量にテーブルを更新するような場合でも、一行更新するごとにバイナリログが記録されてしまう。そして、そのバイナリログは直ちにスレーブへ転送され、スレーブでは更新が始まる。すると、マスター上では更新が完了していないので同じテーブルから参照を行う事は出来ないが、スレーブでは途中まで更新が完了した状態のテーブルへ参照を行う事が可能となるので、予期しない参照結果が返ってしまうことがある。

また、MySQL ClusterやFalconのようにRBRにしか対応していないストレージエンジンがある。ストレージエンジンごとに推奨されるレプリケーションのモードをまとめると次のようになる。
  • MyISAM... SBR
  • InnoDB... Mixed or RBR
  • MySQL Cluster... RBR
  • Falcon... RBR

4. テーブルにPKをつける

RBRを利用している時は、全てのテーブルに明示的なPRIMARY KEY(主キー)が必要になる。PKがなくてもレプリケーションは可能だが、PKが無い場合は更新する対象の行を特定するためにテーブルスキャンが発生する。PKがあればPKに基づいたルックアップを行うだけで良いのでそのような問題は発生しない。

5. バイナリログを同期する

適切な設定をしておかないと、サーバがクラッシュしてしまった場合などに最後にバイナリログに対して行った更新が失われてしまう場合がある。バイナリログの一部が失われてしまうと、スレーブへ更新内容を転送することが出来なくなる。そのため、マスターとスレーブでデータの食い違いが生じるので、レプリケーションは停止する。このような状況になった場合、最悪はマスターからデータのコピーをやり直す必要があるので厄介だ。そのような状況になりたくない場合には、次の設定をしておこう。
sync_binlog=1
innodb_support_xa=1
innodb_flush_logs_at_trx_commit=1
InnoDBの設定はいずれもデフォルトなので、明示的に変更している場合は注意が必要になる。また、sync_binlog=1は1回バイナリログへ更新を行うことでディスクへのフラッシュを行うことを表すオプションであるため、ディスクへの負荷が高くなる。高速なディスク、特にバッテリー付きのRAID装置などを利用していないと性能が極端に落ちてしまうので注意が必要である。

6. マスターとスレーブでハードウェアと設定を合わせる

マスターだけで更新処理を行うからといって、マスターだけを極端に豪華なハードウェアにするなどという過ちを犯してはならない。スレーブだってマスターから送られてくる更新を遅れることなく処理する必要があるし、参照系の処理だってたくさんこなさないといけないからだ。レプリケーションの遅延が発生しないようにするために、マスターとスレーブで同程度のハードウェアを利用するといいだろう。特にInnoDBバッファプールの容量などは合わせておくべきである。

また、my.cnfに記述する各種設定も合わせる必要がある。特に文字コードやSQLモードの違いには注意が必要だ。マスターとスレーブで異なる文字コードやSQLモードを使ってしまうと、データの食い違いを生じさせる要因になるだろう。理想的にはserver_idだけが違っているのが望ましい。ただしスレーブはクラッシュしてもマスターのデータから復旧ができるので、innodb_flush_log_at_trx_commit=2としておくというのはアリだ。(こうするとクラッシュ時にInnoDBのログの一部が失われる可能性があるが、更新処理が高速化する。)

7. 一度に大量の更新をしない

MySQLレプリケーションのスレーブはI/OスレッドとSQLスレッドという2つのスレッドにより実装されていることは皆さんご存じだろう。I/Oスレッドがマスターからバイナリログの内容を受け取り、スレーブ上のリレーログにその内容を書き込む。SQLスレッドはリレーログに書いてあるSQL文(SBRの場合)やデータ(RBR)をスレーブ上のテーブルに適用する。ネットワークが高速ならI/Oスレッドは殆どタイムラグなしでリレーログへ書き込みを行う事が出来る。しかし、SQLスレッドがリレーログの内容を適用するのにどのぐらい時間がかかるかは、SQL文の内容や更新のサイズによる。

例えば一回の更新で100万行を書き換えるようなSQL文をは時間が掛かってしまう。スレーブではそのSQL文をSQLスレッドが処理しているが、マスター側ではもう既に次々と新しい更新が開始されているだろう。このように、一度に大量の更新を行うSQL文を実行すると、スレーブが遅延してしまうことになるので気をつけよう。

逆に、BIなどの処理をスレーブ上で行う場合は、処理が終わるまでレプリケーションを停止させておくというテクニックもある。BI処理中は更新を次々と適用する必要はないので、レプリケーションを停止させておいた方ががBI自体の処理が速くなるからだ。

8. 負荷分散に対応した接続方法を利用する

大量のスレーブへどうやって負荷を振り分けようか?接続しているスレーブがクラッシュした時に自動的に他のスレーブへ再接続する方法は?MySQLレプリケーションによる負荷分散の場合、アプリケーションから確実に分散を行う必要がある。

もし開発プラットフォームがJavaならば、Connector/Jがレプリケーションによる負荷分散に対応しているので悩む必要はない。例えば次のような接続設定にすれば良い。

ReplicationDriver driver = new ReplicationDriver();
Properties props = new Properties();
props.put("autoReconnect", "true");
props.put("failOverReadOnly", "true");
props.put("roundRobinLoadBalance", "true");
props.put("user", "foo");
props.put("password", "bar");
Connection conn =
driver.connect(
"jdbc:mysql://master,slave1,slave2,...,slaveN/db",
props);

マスターへ問い合わせや更新を行う場合にはConnection.setReadOnly(false)を呼び出してから処理を行い、スレーブから参照を行う場合にはConnection.setReadOnly(true)を呼び出してから処理を行う。レプリケーションによる負荷分散に対応させるのに、たったこれだけの操作で済むのである。

開発プラットフォームがJavaでないなら、MySQL Proxyを使って負荷分散することを考えよう。その場合は次のようにMySQL Proxyを起動すれば良い。

shell> mysql-proxy
--proxy-backend-addresses=master:3306
--proxy-read-only-address=slave1:3306
--proxy-read-only-address=slave2:3306
...
--proxy-read-only-address=slaveN:3306

9. 堅牢なネットワークを利用する

現時点では、MySQLレプリケーションは送信されるバイナリログのチェックサムを確認しない。そのため、不安定なネットワークを利用している場合にバイナリログが転送中に壊れてしまうケースがある。ネットワーク機器のメンテナンスをしっかりと行うのは当然であるが、いくらメンテナンスを行ってもゼッタイに壊れないということはないだろう。そんな場合はSSLを利用するといい。マスターとスレーブ間をSSLで接続すれば、データが壊れてしまった場合にはSSLが検知してくれるし、勝手に再送も行ってくれる。

MySQLでSSLの設定をするのは面倒だ!という人はSSHトンネリングを利用するといい。次のようにsshコマンドをスレーブ上で実行すると、マスターのポート3306がスレーブの3307にマッピングされる。

shell> ssh -f master-hostname -L 3307:localhost:3306 -N -4

その後、スレーブ側でCHANGE MASTER TOを行えば良い。

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307;
mysql> START SLAVE;

ちなみに、127.0.0.1:3307への接続が失敗する場合、"open failed: administratively prohibited: open failed"というエラーが表示さるようであれば、マスター上でフォワーディングが許可されていない可能性がある。/etc/[ssh/]sshd_configを編集して、AllowTcpForwarding=1を設定しよう。

レプリケーションのチェックサムはMySQL 6.0から搭載される予定である。それまではSSLまたはSSHトンネリングで凌ごう。

10. 監視する

スレーブが遅延したり停止したりすることも問題だが、それらの問題に気づかないことのほうがもっと重大な問題である。基本的にはSHOW SLAVE STATUSコマンドやSHOW MASTER STATUSコマンドでステータスを確認できるが、スレーブの台数が増えれば増えるほどそれは大変な作業になってしまう。商用のMySQL Enterprise Monitorを使えば、レプリケーションモニターでレプリケーションの状態を一覧できるので大変便利である。今のところ、MySQL Enterprise Monitorについているレプリケーションモニター以上に便利な機能を俺は知らない。有料ではあるが最も有効な監視方法である。

腕に自信のある人はSHOW SLAVE STATUSやSHOW MASTER STATUSを利用して監視用スクリプトを書いても良いだろう。

4 コメント:

mmt さんのコメント...

こんにちは。

何日か前にコメントしたのですが、Haloscanが闇に葬り去ってしまったようなので再送します。

単に個人的な興味からなのですが、項目「堅牢なネットワークを利用する」において、バイナリログが転送中に壊れたケースというのを具体的に教えていただけませんでしょうか。

というのもIP層もTCP層もチェックサムによる誤り検出機構を持っており、誤り検出時にIP層では破棄、TCP層では再送します。
SSLのチェックサムで可能なのは改竄検出であり、換算検出された場合基本的にセッションを終了すると記憶しています。
というわけで、SSL使用、不使用でバイナリログ破損が防止できるとは考えづらいのです。

もしバイナリログが転送中に壊れるのだとすれば、OSのTCP/IPスタックもしくはMySQLのSocket周りにバグがあってデータの受け渡しで破損するか、転送中にセッションが切れてバイナリログが尻切れトンボになったかのどちらかだと思います。(前者とは思えませんが・・・)

どちらにせよSSLでは救えませんので、たまたまSSLを併用したら障害が再発しなかっただけではないかと思った次第です。

Mikiya Okuno さんのコメント...

> mmtさん
こんばんは :)

コメントの再送ありがとうございます。実はHaloscanで前のコメントが見られないことに気がつき、Haloscan削除に踏み切りました。実にスッキリです。

> というのもIP層もTCP層もチェックサムによる誤り検出機構を持っており、誤り検出時にIP層では破棄、TCP層では再送します。

チェックサムではマルチビットエラーに対応出来ない場合があります。ifconfigなどでパケットのエラーが多い場合には用心しておいた方がイイでしょう。

> SSLのチェックサムで可能なのは改竄検出であり、換算検出された場合基本的にセッションを終了すると記憶しています。

その改竄検出がまさに重要で、ビット化けなどをより確実に(少なくともチェックサムよりはずっと高精度で)検出することが可能なのです。セッションが終了すると転送中だったイベント(バイナリログの記録単位)はいったん破棄され、スレーブはmaster-connect-retry秒後にマスターへ再接続します。再接続が成功すればレプリケーションは正常に再開します。再接続はmaster-retry-count回だけ実行されます。

> 転送中にセッションが切れてバイナリログが尻切れトンボになったかのどちらかだと思います。(前者とは思えませんが・・・)

バイナリログはイベントごとに転送されます。イベントの途中で接続が切れてしまった場合にはリレーログへの書き込みは行われません。(1トランザクション=1イベントで、イベントにはトランザクション以外の情報も含まれています。)

もちろん、リレーログへの書き込み中にスレーブがクラッシュした場合には中途半端なリレーログが出来上がってしまいますが、通信の切断などによってそのような状況にはならないわけです。

mmt さんのコメント...

>チェックサムではマルチビットエラーに対応出来ない場合があります。ifconfigなどでパケットのエラーが多い場合には用心しておいた方がイイでしょう。

確かに確率としては0ではありませんが、今までさほど気にしてはいませんでしたので軽く調べてみました。

http://www.analogzone.com/nett1003.pdf
上記の資料を読むとGigabit Ethernetの要求Bit Error Rateは1/(10^12)、フルレートでデータを流し続けた場合で13分に1bit化ける確率だと書いてあります。

TCPのチェックサムをすり抜けるには2オクテットの中で偶数個のビットエラーが起きればよいので、確率的には1/(10^24)以下(近接する確率は考慮していない)。
根拠は無いですが、仮に状態の悪いEthernet環境というのが要求スペックより1万倍悪い状態だと仮定すると、1/(10^16)で750日に一回発生する確率ですか。常に最大帯域を流し続けた場合だと約2年に1回起きる可能性があるわけで、確かに何か対策した方がよいですね。

しかしケーブルや機材を取り替えて推奨値以上の環境を用意すれば2000万年に1回。10GbEthernetでも200万年に1回の確率ですね・・・それでも対策しないよりも対策した方がよいですが・・・

Okunoさんはバイナリログがネットワークによって化けた経験がおありなんですよね?
いくら確率が低くても今この瞬間に現象が起こる可能性はありますから起きても不思議ではないですが、複数回起こっているのであればネットワークの信頼性以外が原因な可能性の方が高い気もするのですがどうでしょう?

>その改竄検出がまさに重要で、ビット化けなどをより確実に(少なくともチェックサムよりはずっと高精度で)検出することが可能なのです。セッションが終了すると転送中だったイベント(バイナリログの記録単位)はいったん破棄され、スレーブはmaster-connect-retry秒後にマスターへ再接続します。再接続が成功すればレプリケーションは正常に再開します。再接続はmaster-retry-count回だけ実行されます。

MySQLのretryとの合わせ技ですか。私自身はDBAではないので細かい挙動は知りませんでした。

>もちろん、リレーログへの書き込み中にスレーブがクラッシュした場合には中途半端なリレーログが出来上がってしまいますが、通信の切断などによってそのような状況にはならないわけです。

なるほど。

MySQL6が採用するレプリケーションのチェックサムというのは具体的に何が採用されるのでしょうのか?

SSLのハッシュダイジェストよりMySQL6の新機能の誤り検出精度が低ければ、リリースされてもそれを使わずにSSLを併用した方が良いということになりますので、それよりは確実な実装になると思われますが、参考までに教えていただければありがたいです。

Mikiya Okuno さんのコメント...

こんにちは。

> http://www.analogzone.com/nett1003.pdf

これはいい資料ですね。ありがとうございます。

> 常に最大帯域を流し続けた場合だと約2年に1回起きる可能性があるわけで、確かに何か対策した方がよいですね。

Ethernet環境以外にもNICの故障などもエラー率上昇の原因になりますね。(個人的にはNIC故障が最大の原因だと思います。)また、この資料で扱ってるBERはエンド・ツー・エンドのものではなく機器同士の接続における話なので、スイッチなどの中継点が入った場合にもエラー率は向上しますね。スイッチが一つ入るだけでも2年に1回が1年に一回に。

たくさんのスレーブを用いて負荷分散するような構成の場合には、スレーブの数が多ければ多いほど接続数も増えるので、システム全体で見た時のBERも増大してしまいます。

> Okunoさんはバイナリログがネットワークによって化けた経験がおありなんですよね?

私自身が直接経験したわけではありませんが、報告例はあります。詳細は下記のページなどをご覧下さい。
http://forge.mysql.com/worklog/task.php?id=2540

前述のように、レプリケーションを数多く運用しているところではこの問題が多いらしく、Mark Callaghan氏曰くGoogleでも事例があるようです。
http://bugs.mysql.com/25737

MySQL 6.0における実装についても前述のページに記載されていますのでご参考まで。

コメントを投稿