技術メモ置き場

2014-09-22

長年の議論に終止符 -- MySQL/MariaDBのバックアップとロックの関係+クラッシュセーフについて

| 18:36

よい機会なのでまとめておく。対象はMySQL5.6以下とMariaDB10.0以下。

(2014.12.3追記:以下の書籍にも記述した。)

要旨

MySQL/MariaDBバックアップについて、相変わらず「InnoDBさえ使っていれば、FLUSH TABLES WITH READ LOCKは不要。よってバックアップ中に更新不可になることはない!」との主張が繰り返されているが、少なくとも5.6/10.0まではそんなことはない。

オンラインバックアップに関するロックの正確な記述

より正確に言えば「全データベース領域をバックアップする場合には、FLUSH TABLES WITH READ LOCKは必須。特定のInnoDBだけのデータベースやテーブルをバックアップする際は、この限りではない」。

なのだが、全領域のバックアップをしたい人に対してロック不要説を吹き込む人が未だにいる。

ロックの必要な理由

システム情報を保管しているmysqlデータベース以下の多くのテーブルがMyISAMだから、ロックが必須になる。(5.7で全部InnoDBになる予定だが、あくまで予定)。

問題点

ロックをかけずにバックアップしている最中に、ALTER TABLEやCREATE TABLEなどを実行すると... バックアップしたデータの一貫性が壊れるから、リカバリできない=バックアップの意味がなくなる。

よって「このシステムはInnoDBテーブルしか作ってないからMyISAMは存在しない」と思うのは間違い。全データベース領域をバックアップするときには必ずMyISAMテーブルのバックアップも含まれるので、たとえ専門家の言説*1であっても鵜呑みせず、きちんと自分の頭で考えなければならない。痛い目をみるのは自分なので。


さらに言えば、バックアップのロックの問題は、クラッシュセーフか否かという問題にも直結している(double-write-bufferなんて機能があるのに、こっちは放置というのが勿体ないというかバランス悪いというか...)。たかがバックアップの問題と思うのは早計。ALTER TABLEやCREATE TABLE実行中にクラッシュしてmysql以下のMyISAMテーブルが壊れたら.....


「本番環境稼働中にテーブルスキーマ変えるはずない」というのはごもっともで、私自身もそれはないよなあと思いつつも、MySQL5.6/MariaDB10.0の目玉機能の一つとしてOnlineDDLがあり、ALTER TABLEでテーブルスキーマ変えるだけでなく、稼働中にlockなしでindexいじったりできることを売りにしてる以上、使う人もいるし、そういう使い方が必要とされる現場も多いということで、やはりきちんと書いておかないと

mysqldump

mysqldumpは膨大なオプションがあって、ロックについてもいろいろ選択できるが、

ここでは「データベース領域全体を安全第一」でいくためのオプションと、そのとき注意すべき点について述べる。

とはいっても、データベース全体のデータの一貫性を確保しつつデータをダンプするには、以下のオプションを選択せざるを得ない。

mysqldump --all-databases --lock-all-tables

ここで、内部的にはFLUSH TABLES WITH READ LOCKがかかるので、データダンプ中は更新不可になる。

更新不可も痛いが、他のセッションが何かしらのロックをかけていると、そのSQL(もしくはトランザクション)が終了するまで、ロック待ちになって処理に入れない。

諸々のバッチ処理バックアップは深夜にやる場合が多いが、バッチ処理で長いトランザクションが走っていたりすると、ダンプが始まらないので困る。


これが嫌なら、以下のように割り切ったオプションでやるしかない。

mysqldump --all-databases --single-transaction

この場合の条件は:

  1. MyISAMテーブルを更新しない
  2. InnoDBテーブルに対して以下を実行しない
    • CREATE TABLE, DROP TABLE, ALTER TABLE, RENAME TABLE, TRUNCATE TABLEなどテーブル自体の操作を行わない

で、テーブル自体の操作をしない理由は、「少なくとも5.6まではテーブルスキーマ情報がMyISAMで保存されているから」である。


f:id:interdb:20140922184015j:image

XtraBackup

XtraBackupはPerconaのオンラインバックアップツール。MySQLのmysqlhotcopyみたいなもので、どちらも内部的な動きは同じ。

InnoDBはロックも不要でオンラインバックアップできるが、MyISAMはロックが必要である。内部では

  1. InnoDBだけを先にバックアップ
  2. 次にFLUSH TABLES WITH READ LOCKをかけてMyISAMバックアップ

という流れになっている。

mysqldumpよりはよほど賢いが、mysql以下にMyISAMがある以上、どうしてもFLUSH TABLES WITH READ LOCKが必要で、必然的に更新不可の瞬間が発生する。

f:id:interdb:20140922184016j:image

ファイルシステムスナップショットを使う

LVMなど、ファイルシステムスナップショットを使う場合も同様で、mysql以下のMyISAMテーブルを一貫性をもってバックアップするには、一瞬であるがFLUSH TABLES WITH READ LOCKが必要。

f:id:interdb:20140922184017j:image

まとめ

現時点でMyISAMは(mysql以下以外では)ほぼ使われていないという*現実的な観点*からまとめる。

mysqldumpは酷いが、他は有用

mysqldumpでのロックはかなりひどいわけで、mysqlだけ別にダンプするとか、なんらかの工夫が必要だろう。

それと比べて、XtraBackupやファイルシステムスナップショット取得におけるロックはまあ、短時間だから運用中に大問題になることはほとんどないと思う。

ただし「ロックは必要で、ロック取得までに待たされる可能性もある」ことはきちんと周知すべき*技術情報*だろう。


いずれにせよ、バックアップ中にロックがかかることが欠点かどうかはユーザが決めること。技術者は淡々と事実を述べればいい。対処法を尋ねられたら、状況にあわせてユーザとともに考えればいい。それだけのこと。都合の悪いことを隠して万能性を吹聴する輩はエンジニアを名乗ってはならないだろう。

クラッシュ時

バックアップの話題から離れて、(冒頭で書いたように)mysql以下のMyISAM更新中にMySQL/MariaDBがクラッシュするとどうなるかについて。

具体的にはALTER TABLEやCREATE TABLEなどの実行中にクラッシュすると、データベーススキーマ情報が壊れるなんて事態に陥る可能性が、僅かながらある。


なので、mysql以下のMyISAMテーブル問題の本質はMySQL/MariaDBが厳密に言えば"まだクラッシュセーフでない"ということ*2。正確な情報があればユーザは対処できる。MySQLを使わないという選択*だけでなく*、そのリスクを引き受けることも選択できる。しかしこの情報がないのならば、ユーザはそのリスクを知らずに引き受けさせられているということになる。(5.7で全InnoDB化すれば、少なくともこの意味ではクラッシュセーフになるが)。


技術者は「技術的に正確な情報を発信」するのが基本で、正確な情報さえあればなんらかの対処方法はみつかる。しかし情報が隠蔽されたりミスリードさせられると対処しようがない。

*1MySQLをよく知るお方の"お言葉"。不思議なことに、上で指摘しているロックの問題を知らなかったらしい...

*2"ここ"で書いたように、MySQL関係者は「mysql以下のレプリケーション関係テーブルをInnoDB化したことでスレーブがクラッシュセーフになった」と主張しているのだから、全く同じロジックで「MySQL/MariaDB本体はクラッシュセーフでない」と明示的にユーザに伝えなければならないと思うのだが。

トラックバック - http://d.hatena.ne.jp/interdb/20140922/1411378571
リンク元