さんまがおいしい季節だねー(´・ω・`)

mysqlのibdataが原因でディスクフルになったときに復旧方法

開発環境 — タグ: , — さくら @ 2011/08/27 17:32

さっきまで落ちてたsakuratan.bizです。どーもすみません。

原因は MySQL の InnoDB が使用する ibdata ファイルがディスクを食いつぶしてディスクフルになってたためでした。

ibdata はテーブルスペースを保存するファイルで、CentOS ですとデフォルトで /var/lib/mysql/ibdata1 に作成されます。MySQL のデフォルトでは ibdata が自動で拡張されていく設定になっていますので、放っておくとどんどんファイルサイズがでかくなって、さくらの VPS とかですと結構あっさりディスクフルになりました。

とりあえず復旧できたんで手順とか残しときます。同じようにトラブった方は参考程度にどうぞ。

まず ibdata1 がディスクを食いつぶしているので、復旧するにはこれを消すとかファイルを小さく必要があります。

ファイルサイズを小さくする方法は調べたらいっぱいでてきますので(13.5.7 InnoDB データとログ ファイルの追加と削除 とか ibdata1 のサイズを減らす手順 とか ibdata1のサイズを減らす方法とか)、概要だけ引用しますと、

  1. 全ての InnoDB テーブルをダンプする為に mysqldump を利用してください。
  2. サーバを停止してください。
  3. 全ての存在するテーブルスペース ファイルを削除してください。
  4. 新しいテーブルスペースを設定してください。
  5. サーバを再起動してください。
  6. ダンプ ファイルをインポートしてください。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 13.5.7 InnoDB データとログ ファイルの追加と削除

という感じだったりします。要はバックアップ取ってテーブルスペースを作り直せということのようです。

ただまあディスクフルしてるんでサーバ上に mysqldump のダンプファイルなんか置けないので、ssh 経由で mysqldump を実行することにしました。↓のように ssh を起動することで、ネットワーク越しにコマンドを実行して mysqldump の出力をローカルに直接保存できます。

ssh who@domain \
mysqldump -v -u DBUSER \
--default-character-set=binary -p DBNAME \
TABLE1 TABLE2 ...  | \
gzip > mysql.dump

それとコマンドを実行する前にサーバ上のサービスをできるだけ止めてある程度作業用のメモリの確保して、ついでに不要なファイルをできるだけ削除しておいた方が良いと思います。メモリかスワップか何が原因か調べてる暇も無かったので詳細は不明ですが、リソースが足らないと mysqldump がテーブル構造を読み込む際にエラーを出します。

今回はリストアする必要が無いテーブルが何個かありましたのでダンプする対象をテーブル単位で指定していますが、全部リストアする場合はデータベース単位で指定してもらえばよろしいかと思います。運用形態によっては mysqldump に –single-transaction オプションを指定してもらった方が良いかもしれません(とりあえずウチんとこでは不要だったので指定してませんが)。

ダンプできたら mysql を止めてから ibdata1 と ib_logfile0 と ib_logfile1 を消します。他のサイトだとリネームした方が良いとか書いてますがそんな余裕無いのでいきなりマジ削除しました。最悪データ全部豚でもいーや、の覚悟でどうぞw

cd /var/lib/mysql
rm -f ibdata1 ib_logfile[01]

/etc/my.cnf に ibdata1 のサイズ制限と innodb_file_per_table を加えてからmysqld を再起動して回復しましたよ、という感じです。

innodb_data_file_path=ibdata1:10M:autoextend:MAX:100M
innodb_file_per_table

innodb_file_per_table を指定すると、各テーブルの中身は共有テーブルスペースでは無く個別のファイルに保存されるようになるのですが、こっちのファイルも放置しておくと大きくなっていきますので、定期的に ALTER TABLE を実行してデフラグする必要があります。(詳しくは 13.5.14.3 テーブルのデフラグメント化 とか 拡張され続ける InnoDB のデータファイルのサイズを小さくする方法 とかをご覧ください。)

ALTER TABLE table1 ENGINE=InnoDB;

個別に ALTER TABLE 実行するのも面倒なので、全ての InnoDB テーブルに対して ALTER TABLE を実行するスクリプトも書いてみました。よろしければどうぞ。

#!/usr/bin/env python

import MySQLdb

def main():
    conn = MySQLdb.connect(user='root')
    cur = conn.cursor()
    cur.execute('SHOW DATABASES')
    databases = [database for database, in cur if database != 'mysql']
    cur.close()
    conn.close()

    for database in databases:
        conn = MySQLdb.connect(user='root', db=database)
        cur1 = conn.cursor()
        cur1.execute('SHOW TABLE STATUS')
        for t in cur1:
            tablename = t[0]
            engine = t[1]
            if engine and engine.lower() == 'innodb':
                print 'Defrag %s.%s' % (database, tablename)
                cur2 = conn.cursor()
                cur2.execute('ALTER TABLE %s ENGINE=INNODB' % tablename)
                cur2.close()
        cur1.close()
        conn.close()

if __name__ == '__main__':
    main()

MySQL にパスワード無しで root ログインできる前提でスクリプト書いてますので、その辺の設定変えている方は適当に改造して使ってください。

つーことで、困った時はとりあえずもちついてくだしあ。

     /\⌒ヽペタン
    /  /⌒)ノ ペタン
  ∧_∧ \ (( ∧_∧
 (; ´Д`))’ ))(・∀・ ;)
 /  ⌒ノ ( ⌒ヽ⊂⌒ヽ
.(O   ノ ) ̄ ̄ ̄()__   )
 )_)_) (;;;;;;;;;;;;;;;;;;;)(_(

6件のコメント »

  1. Widget バージョン2を利用させて頂いておりますm(_ _)m
    本、ミュージック、DVDはいまだに落ちてるお(´・ω・`)

    コメント by midori — 2011 年 8 月 27 日 @ 18:39
  2. 同じくver2をひと通り利用させてもらってますが、widgetが一切表示されない状態が続いております。

    コメント by 匿名 — 2011 年 8 月 28 日 @ 03:48
  3. twitterの方に知らせた方が早いかも知れません

    コメント by tak — 2011 年 8 月 28 日 @ 05:09
  4. あら本当ですね。直しますので少々お待ちください。
    ちなみに widget は別のサーバで動かしてますので今回のサーバ移転は関係ないです。

    コメント by さくら — 2011 年 8 月 28 日 @ 15:31
  5. 直しました。某外部ウェブサービスを使っているのですがそれのバージョンが上がって一部動かなくなっていたようです。
    表示が直るのはサーバ上のデータのキャッシュの有効期限が切れてからになりますので、もうちょっと時間かかると思います。

    コメント by さくら — 2011 年 8 月 28 日 @ 15:48
  6. キャッシュのタイムアウト確認しました。全部直ってると思います。
    ↓もどうぞ。
    http://sakuratan.biz/archives/4574

    コメント by さくら — 2011 年 8 月 28 日 @ 17:01

この投稿へのコメントの RSS フィード。 TrackBack URI

コメントする

Copyright © 2017 さくらたんどっとびーず | powered by WordPress with Barecity