※古い記事ですのでご注意ください※
こんにちは。小宮です。

社内のMyISAMテーブルが混在しているサービスにて、
mysqldumpによるバックアップに失敗しコールドバックアップを取ったが不整合出たという事件について相談うけまして、
その顛末を記録しがてら注意事項をまとめておきたいと思います。
　（昔軽く伝えた記憶があり皆知ってると思ってたんですが、わかりやすさや伝える力と、
　私と他の人のmysqlに対する愛のレベルが少し異なるという認識が足らなかったようです。
　トラブルは起きるもんだし失敗は挑戦の証で成功の元ですが同じことは繰り返さないようにしないと。）

何が大切かって、マスタとスレーブのデータの整合性です。
　つまり、
　⇒バックアップ対象が絶対に更新されてないこと
　⇒バックアップ取得時点のポジションとバイナリログファイル名が明確であること
　が超重要です。
　MyISAM混在環境では気をつけないと不整合でます。
　不整合でると購入したはずのコンテンツが買われてないように見えるなど割と大変なことになりますね。
　InnoDBだけなら–single-transactionつけとけば不整合は気にしなくて大丈夫です。

・mysqldumpに失敗した原因

mysqldump: Error 1317: Query execution was interrupted when dumping table

このエラーは、
mysqldumpでデータを読んでる間にクエリーが中断されると出るやつです。Ctrl + Cとかkillでスレッドが殺されたとか。。
（とTwitterで教えていただきました。ありがとうございます。

それから、整合性のためにマスタからmysqldumpでデータをとることになったのですが、
その際にslaveでreplicationを止めて更新がない状態でとっている方法をそのまま用いたのも不整合が起きる原因になりました。
（MyISAMが混在する環境なのにFLUSH TABLES WITH READ LOCK;をしてなかった）

MyISAMが混在する環境の場合、トランザクションは使えないので、
replicaion停止または共有ロックで更新停止が必要になります。

ex)
FLUSH TABLES WITH READ LOCK;
sleepやsyncでロックが終わる(確実に更新停止される)のを待つ
ポジションとファイル名をとりログに記録する
mysqldumpする
UNLOCK TABLES;

特にクエリを中断したりしてない(中断と整合性がごっちゃ)という指摘があったりしてそれは私もよくわからないところですが、
更新されないようにしてれば中断も起きるはずがないので、対処方法は更新を確実に止める一択かと思います。

・コールドバックアップ後にマスタとスレーブで不整合が出た原因

以下のエラーが出てreplication開始できなかった

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

元のマスタで起動直後からreset master;を打つまでの間に更新が入った（これ以外に考えられないので推測です）
repはreset master後の初期ポジションから開始したところ、
　とあるテーブルの件数がマスタとスレーブで異なるという結果がでていたそうです。
reset master;しちゃうとマスタのバイナリログがデータファイルにフラッシュされて消えるので、
バイナリログを見ているスレーブがまだ読んでないところが消されて読めなくなるので、
スレーブを持ってるマスタで実行する時は注意が必要です。

・対策

MyISAMのテーブルが更新されててmysqldumpに失敗することがないようにするために、以下の対策を推奨します。

１．共有ロック(FLUSH TABLES WITH READ LOCK;)をかけたうえで、
　(MyISAMが混在する場合は–optでのロックだけでは全体の整合性がとれません)
　(slave機でreplicationを止めてdumpとる場合は更新される可能性がありませんので共有ロックしなくても大丈夫）
２．show master status;を何回も打って更新されてないことを確認し
　（共有ロックは実行中のクエリを待ったりして数秒待たないとダメな場合があるようです
　http://d.hatena.ne.jp/jitsu102/20110423/1303553133）、
３．iptablesで3306を遮断（iptables -A INPUT -p tcp –dport 3306 -j DROP）し、
　(もしくは再起動できるならskip-networkingをmy.cnfに書いて反映）
４．show master status;を何回も打って更新されてないことを確認して、
５．mysqldumpでbkupとる。すると確実に更新されなくて不整合を防げそうです。

もしくはコールドバックアップで。

・replication用途にデータを用いる場合のポジションの確認方法
　replication用途があるならバイナリログのファイルとポジションをどうにかして確認する必要がありますが、

　mysqldump(マスタから取得)であれば、
　　–master-dataをかいておけばdumpfile内の最初のほうにCHANGE MASTER TO構文が書かれるのでそこからrep開始すればOK,

　mysqldump(スレーブから取得)の場合、
　　5.5からは以下のような便利なオプションを用いるといいでしょう。
　　–dump-slave: スレーブからダンプをとった場合、スレーブが参照しているマスターの情報をCHANGE MASTERとしてダンプに含める。
　　–apply-slave-statements: STOP SLAVEおよびSTART SLAVEコマンドを、CHANGE MASTERの前後に追加する。
　　–include-master-host-port: CHANGE MASTERコマンドにマスターのホスト名とポートを含める。
　　それより前のバージョンではreplicationを止めた際のポジションを記録する(エラーログにも出てるはず)といいでしょう。

　コールドバックアップ(マスタから)の場合は、
　　解凍したデータディレクトリ内のバイナリログをmysqlbinlogでtailでみればバイナリログファイル名とポジション書いてあります。
　スレーブからコールドバックアップする時は、たぶんエラーログにポジションが書いてあるはずです。(確認してください)

※マスタでreset master;を打ってしまうとスレーブでreplicationしたいデータが失われるので、
　事前にバックアップとった時点から絶対に更新されていないことを確認必須です。

・整合性の確認のためにオススメしたいこと
　いつ更新されるかわからなくて確実に止めることが難しいか把握しきれてない環境等であるならば、
　show master status;はデータの整合性のために何度も打って確認するといいのではないでしょうか。
　（マスタからならmysqldumpした際のサービス停止時間が無駄になってしまいます。）
　あとはネットワーク的に遮断するのが手っ取り早そうです。
　set global read_only=1すればという意見もありそうですが、SUPER権限のユーザは更新できてしまうので注意が必要です。

・InnoDBのみとMyISAM混在の場合で使ってるmysqldumpオプション詳細
InnoDB：
--all-databases –quote-names –opt –single-transaction –master-data=2 –hex-blob –flush-logs -R –order-by-primary
MyISAM：
--all-databases –quote-names –lock-all-tables –hex-blob –flush-logs -R –master-data=2 –order-by-primary

InnoDBは–single-transaction、MyISAMは–lock-all-tables！と覚えるとよさそうです。

マスタからとる場合：–master-data=2　を指定するとshow master status;のポジションがCHANGE MASTER TO構文としてdumpファイルの頭に記録されます。
スレーブからとる場合：–dump-slave=2（※MySQL5.5以降）を指定するとマスタのポジションがdumpファイルの頭に記録されます。

mysqldump参考：
　MySQL :: MySQL 5.5 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
　mysqldump –dump-slave について - Studio3104::BLOG.new
　mysqldump –single-transaction に –flush-logs をつけてはいけない - @tmtms のメモ

余談ですが、今回マスタからdumpデータとらなくてはならなくなったそもそもの原因が、
スレーブがマスタの半分程度の低スペックであるところに重い処理(大量のDelete)が重なり遅延が著しくなってしまったためだったようです。
マスタですぐ終わった処理がスレーブでは遅延するというのは良くあることのようですね。
節約は利益のために必要ですが、サービスの機会損失を招かない程度にするなどの余裕やバランスも大切そうということと、
MySQLにおけるdeleteはとても重い部類らしいので、こまめに実施するかテーブルをパーティショニングしてパーティションをDropすると軽い処理になると思います。

パーティショニング参考：
　高速処理化！MySQLのパーティショニング機能を使ってみよう | 株式会社LIG
　今さらだけどMySQLのパーティショニング機能を試してみた - (ﾟ∀ﾟ)o彡 sasata299’s blog
　ソーシャルゲームのためのMySQL入門 - Technology of DeNA
　漢(オトコ)のコンピュータ道: パーティショニングの使用例 - http session情報

以上、ご覧いただいてありがとうございました。