モロモロ工事中です

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 ログインできる前提でスクリプト書いてますので、その辺の設定変えている方は適当に改造して使ってください。

12件のコメント

  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
  7. Can yoս tell uѕ more aƅout this? Ι’ⅾ like to find out some additional іnformation.

    Feel free to surf to᧐ my bloog post :: https://www.youtube.com/watch?v=3n6qU2luteg

    コメント by https://www.youtube.com/watch?v=3n6qU2luteg — 2018 年 8 月 11 日 @ 02:31
  8. 座椅子のなるほど案。記録文書をたてる。座椅子の当て嵌めるおもいのほかとは。直後にな感じで行きます。

    コメント by 座椅子|裏側を解説します — 2019 年 3 月 15 日 @ 13:27
  9. Everything posted made a bunch of sense. However,
    think on this, what if you added a little content? I ain’t suggesting your information is not solid., however what if you added a
    headline that makes people desire more? I mean mysqlのibdataが原因でディスクフルになったときに復旧方法 |
    さくらたんどっとびーず is a little plain. You might peek
    at Yahoo’s home page and note how they write article titles to
    get people interested. You might add a video or a picture or two to get people excited about what you’ve
    written. Just my opinion, it could make your website a little
    livelier.

    コメント by Shizuka Kudo — 2020 年 3 月 3 日 @ 07:14
  10. Kumpulan situs daftar mpo slot gacor terbaru dan terpercaya deposit pulsa tanpa potongan 24jam

    コメント by mpo slot terbaru — 2022 年 10 月 29 日 @ 03:16
  11. This site was… how do I say it? Relevant!! Finally I have found something that helped me.
    Thank you!

    コメント by slot deposit pulsa tanpa potongan — 2023 年 5 月 4 日 @ 12:17
  12. エルメス ユニクロ zara

    非常にいい書いてあったけどそうは思えない。
    自社基準なので仕方ないと思うけど、もうここで買わない。

    トラックバック by エルメス ユニクロ zara — 2023 年 9 月 15 日 @ 16:08

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

現在、コメントフォームは閉鎖中です。

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