MySQL(MariaDB)のInnoDb肥大化をmysqldump無しで解決した方法。

シェアする

久々にテック系ですが、MySQL(MariaDB)のinnodbのデータファイル肥大化を、mysqldumpしてDBクリアする、というやり方以外で解決した方法です。

MySQL(MariaDB)のInnoDb肥大化をmysqldump無しで解決した方法。

昔からmysqlを使い続けていて、どこかでライセンスの関係でmariadbに変えましたが、その過程でmysql(mariadb)のデフォルトデータベースがinnodbになっていたようで・・・知らずに使っていたらinnodbあるあるのデータファイル肥大化に陥ってました。

肥大化したibdata1ファイル

mysqlのデータディレクトリがこんな感じに。

/var/lib/mysql

innodbのデータが格納されるibdata1のファイルサイズが9,036,627,968、ということで9ギガになっていました。まぁそんなめちゃくちゃデカい、という話でもないのですが、サーバ自体もディスクそんなにあるわけじゃないので、VPSながらパーティションの使用済み容量82%までになっていました。

df

このまま看過しているとログファイルがたまったりデータ増加でいずれディスクフルの憂き目に遭います。自分でinnodbを指定したつもりはなかったのですが、こうなってしまうと仕方がないので対策を調べました。

一般的な対策としてはこちらで紹介されているような方法みたいです。

やり方としては、

  1. mysqldump –all-databasesでバックアップをとる
  2. DB止める
  3. /etc/my.cnfにinnodbのデータファイルサイズとinnodb_file_per_table、テーブル毎にデータファイルを作る設定を追加
  4. 肥大したibdata1とib_logfile*を移動
  5. dumpを入れなおして再起動

という流れ。OSでいう所のクリーンインストール的な感じですね。

けどこの「1回消す」というのが怖くて・・・1つのデータベースだけじゃなくていくつもDBが入っているのでホントに丸ごと一旦消してしまう事に。

その時にuserやらhostやら入っている接続設定系のmysqlも消したりとか、かなり大規模な感じがしました。大規模ということはダウンタイムが長い、万が一のリカバリーも時間かかる、ということで、かなりガクブルな感じです。

あとこの作業って、本質的にはトラブル解決とか問題修正とかでもなく、今機嫌よく動いているサーバをわざわざ止めて、結果「きれいになりました」というスッキリ感しかない、というのも気になるところ。将来的なディスクフルを回避しているわけではありますが。

ということで・・・なんか別の方法ないかな・・・?というのを調べまくった挙句、思いついたのが・・・。

一旦全テーブルをMyISAMに変換する

という方法。

MyISAMはmysqlの古いタイプのファイルシステムで、トランザクション無し、更新時はテーブルロックかかります、という機能的にはローレベルなファイルタイプです。昔のmysqlは低機能な分速い、というものでした。

今はトランザクションサポートのinnodbでも速度が確保できているようですが、まぁそこまでトリッキーなことはDBではしていないので、MyISAMに変換することはそんなに問題なさそう。

調べてみると最近立ち上げたサイトはinnodbですが、昔からあるWordpressのDBなんかはMyISAMで動いてました。気づいてなかった、というかこの問題が出るまであまり気にしていませんでした。

全DB、テーブルをMyISAMに変換

ということで、MyISAMでも行けそうだ、とアタリをつけたところで、innodbになっているテーブルを片っ端からMyISAMに変換します。

ALTER TABLE {tablename} ENGINE=’MyISAM’;

で変換できます。Shellで書いてもいいのですが、結局GUIクライアントでポチポチ変更していきました。一応使ってないやつ、重要度低いやつから順番に変えてみて、mariadbやphp-fpmのエラーログ見ながらですが・・・。

で、全部変換しきったところで、mysqlの設定ファイルのmy.cnfに追記します。

/etc/my.cnf

innodb_file_per_table、だけでもいいみたいですね。全部がMyISAMでもibdata1のファイルは作られるので、とりあえず100Mに上限設定しておきます。innodb_file_per_tableで、各dbのディレクトリにデータファイルも置かれるはず。

そして・・・

service mariadb stop; mv ib* /tmp/; service mariadb start

みたいなことをやってディレクトリを見てみると・・・

/var/lib/mysql

ばっちりファイルサイズが下がっていました!エラーも出てないし、あとは問題なければ古いibdata1を削除したり、各テーブルをinnodbに再変換したり・・・でクリアすることなくinnodb肥大ファイルを取り除く事ができました。

そしてディスクは・・・余裕余裕。

df

基礎からのMySQL 第3版 (基礎からシリーズ)
基礎からのMySQL 第3版 (基礎からシリーズ)
  • 著者 : 西沢 夢路
  • 価格 : ¥ 3,218
  • 単行本:546 ページ
  • 出版日 : 2017/09/22
  • 出版社 : SBクリエイティブ