MySQLがクラッシュした時にやったこと

先日、当然MySQLのプロセスがダウンし、
正常に再起動しない事態となりました。

バックアップは日次のみで、最悪の場合は日次バックアップに戻すとしても、
できることなら最新のデータを保持したまま復旧したいということで、
できる限りのデータを救い上げて復旧するために行なった手順をメモしておきます。
緊急だったので、手順の是非はありますが、とりあえず以下の通りです。

 

  1. 起動スクリプト(/etc/init.d/mysqld)ではなく、mysqld_safeによる起動を試みる
  2. 強制リカバリモードでの起動(innodb_force_recoveryのレベルを1つずつあげながら、起動するまで)
  3. mysqldumpでデータを抜き出す
    (クラッシュしているテーブルに当たるとエラーで止まるので、都度除外しながら)
  4. 3で発覚した、クラッシュしているテーブルの復旧を試みる
  5. 最後の手段!同環境の別サーバを立ててデータを移行する
  6. クラッシュしていて抜き出せなかったテーブルを手動で復旧する

 

1. 起動スクリプト(/etc/init.d/mysqld)ではなく、mysqld_safeによる起動を試みる

# /usr/bin/mysqld_safe --user=root &

この時点では、原因が分からなかったので別の方法でとりあえず起動させてみようということで行いましたが、
あとで調べたら、起動スクリプト(/etc/init.d/mysqld)も中でmysqld_safeを呼んでいるらしく、この作業は時間の無駄でした・・・

起動ログ(/var/log/mysqld.log)を見ると、クラッシュリカバリに失敗して起動できていないことがわかったので、

2. 強制リカバリモードでの起動(innodb_force_recoveryのレベルを1つずつあげながら、起動するまで)

# vi /etc/my.conf
[mysqld]
 innodb_force_recovery = 1    #1~6まで、起動するまで一つずつ上げていく

1~2まで特に変わらず起動失敗。3で起動はするけどデータベースが認識できていない感じでした。
4でやっと一応ちゃんと動く形で起動しました。

 
復旧に入ろうにもとりあえず、救えるだけのデータは救ってから・・・ということでとりあえずバックアップを取ります。

mysqldumpでデータを抜き出す
(クラッシュしているテーブルに当たるとエラーで止まるので、都度除外しながら)

 # mysqldump -uroot -pパスワード DB名 > /tmp/backup.dump

クラッシュしているテーブルに当たるとエラーを吐いて止まります。
とりあえず、クラッシュしているテーブルは都度スキップしながら救えるだけ救います。

 # mysqldump -uroot -pパスワード --ignoretable=壊れているテーブル名 DB名 > /tmp/backup.dump

「–ignoretable=壊れているテーブル名」は壊れているテーブル分だけ繰り返します。

3で発覚した、クラッシュしているテーブルの復旧を試みる

 # mysqlcheck -r DB名 壊れているテーブル名 -u root -pパスワード

起動時のリカバリシーケンスで失敗しているので、期待薄な気はしていたのですがやはり駄目でした・・・
不幸中の幸いで壊れているテーブル、最悪中身は無くても良かったので、
とりあえず先に進みます。

 

最後の手段!同環境の別サーバを立ててデータを移行する

ここは、ただのMySQLサーバ構築なので割愛します。
イメージバックアップがあったので、一から作る必要はなかったのですが、
権限周りだけ再設定が必要でした。
そして、データを入れます。

 # mysql -uroot -pパスワード DB名 < /tmp/backup.dump

 

クラッシュしていて抜き出せなかったテーブルを手動で復旧する

とりあえず、最悪中身は無くても良いテーブルだったので、
DDLだけ流してテーブル構造だけ先に戻してアプリを復旧させ、
後で日次バックアップから入れられるだけのデータを入れました。

 
後でわかったのですが、
クラッシュしたのはMySQL5.0のバグとのことでした・・・(サポート公式回答済み)
古いバージョン使い続けるのは良くないということと、
費用がきつくても被害が大きくなる場合は、
最低限フェイルセーフレプリケーションだけでもやるべきだな~痛感した一件でした。。。

そもそも、無駄に高い国内DCなんて使わずに、
Amazon RDSを使えば、こんな心配もせずに済むんですけどねw


主キーの変更でGROUP BYが劇的に速くなった件を、現実的な方法で置き換えられないか考えてみた。

第5弾! Tuningathon(チューニンガソン)優勝の決め手となった主キーの変更ですが、
これによってGROUP BYが劇的に速くなった件を、

現実的な方法で置き換えられないか考えてみました。

GROUP BYで一時テーブルが作られる時にボトルネックになるのは、
「一時テーブルの作成」だけではなく「一時テーブルに載せるデータの取得」もあるということ。

MySQLにおいてのインデックスと主キーの違いは、
やったことにも書きましたが、
「実データを取りに行く必要がある(インデックス)かない(主キー)か」という部分。

(厳密にはそれだけでは無いでしょうが・・・)

・・・ということは、

結合キー→GROUP BYキー=SELECT項目→集計項目(今回は*だったので最小カラム?)

という感じで、全てが一つのインデックス内部で片付くようなインデックスを貼ったら、

実データを見に行く必要が無くなるから同じくらい速いんじゃないだろうか?

 

今度、時間があったら検証してみたいです。


第5弾! Tuningathon(チューニンガソン)振り返り

所感

  • まさか優勝できるとは思っていなかったので、
    ものすごく嬉しいのですが、それ以上にどう振る舞うべきか戸惑っていますw
  • 優勝できたのは運が本当に運が良かった。
    主キーの変更に気がつかず、インデックスのみではどこまでいけたものか…
  • 主キーの変更は、実際の現場ではほぼ使えないテクニックなので、
    若干反則かなと思ったりもしていますw
    実際の現場で途中で主キーを変えるとしたら、それは完全に設計の誤りなわけで…
    SQL書き直すそうが現実的な対策と言える気がします。
  • 今回の内容ってインフラっていうよりアプリに近くないですか?
    テーブルとインデックスの構成考えるのってアプリエンジニアの仕事ですよね?
    完全にインフラ寄りの内容だったら、僕みたいなどっちも齧ってる中途半端エンジニアが優勝なんてできなかったでしょうが・・・w

勉強になったこと

    • やったことにも書いていますが、サイズの小さい列にインデックスを貼ると、集計関数が少し速くなること
    • 後半二つのSQLのようなGROUP BYを使ったSQLが遅いのはよく悩まされる所なのですが、
      主キーの変更によって劇的に高速化された理由を自分なりに考えてみたら、
      同じ手はそうそう使えないけど、今後はちょっと違ったアプローチができそうな気がしてきた。
      考えてみた内容はちょっと長くなりそうなので今度別に書きます。→書きました。

その他

  • 主催のゼロスタート様、運営スタッフの方々、スポンサーの皆様、素敵なイベントと経験をありがとうございました!
    たぶん、また次回も参加します。目指せ連覇!w
  • 恐縮ですが、一つだけ残念な点があります。
    今回はサーバがかなりオーバースペックだったので、メモリの設定は適当で済んでしまったのが少し残念です。
    各バッファの使用量やパフォーマンス統計を見ながらシビアに割り当て量を調整するような感じの方が、
    チューニンガソンらしかったかなと思います。
    スペックを落とすと集計が終わらない懸念があったようなので難しいでしょが…
  • 優勝のご褒美に、JAWS DAYS 2013に社費で連れて行ってくれるとのこと。
    それは嬉しいけど、その代わり大LT大会に「優勝者です」って言って出ろ…ってそれ本当にご褒美ですか?w
    嫁がその二週間前に出産予定なので、その兼ね合いもあって悩み中。