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

MySQLのレプリケーションは非常に簡単に使える割には応用の幅が広いので非常に人気のある機能の一つである。レプリケーションの応用分野は例えば、

• バックアップ
• 参照系の負荷分散
• HA（高可用性）
• ディザスタリカバリ（サイト間レプリケーション）
• BI（レポーティングetc）

という風にとても多くのバリエーションがある。このブログを読んで頂いている皆さんの中にもレプリケーションを使っている方は多いのではないだろうか。ご覧の通りMySQLのレプリケーション機能はミッションクリティカル分野でも利用されているが、レプリケーションの使い方が適切でないとシステムの安定稼働に支障を来してしまってDBAやシステム管理者の肉体的、精神的負担が増大してしまう。逆にレプリケーションを堅牢に運用することが出来ればマクラを高くして眠れるというものだ。レプリケーションはMySQLの代表的な機能であるので、レプリケーションを使いこなしてこそ真のMySQLエンジニアであると言えよう。

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

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

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

また、マルチマスターは同時に同じ行を更新するとデータに不整合が生じてしまう。例えば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）という３つの種類からレプリケーションの方式を選択することが出来る。一般的には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を利用して監視用スクリプトを書いても良いだろう。