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

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

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

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

肥大化したibdata1ファイル

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

/var/lib/mysql

-rw-rw---- 1 mysql mysql      16384  5月 14 09:10 aria_log.00000001
-rw-rw---- 1 mysql mysql         52  5月 14 09:10 aria_log_control
-rw-r----- 1 mysql mysql    5242880  5月 22 20:47 ib_logfile0
-rw-r----- 1 mysql mysql    5242880  5月 22 20:47 ib_logfile1
-rw-r----- 1 mysql mysql 9036627968  5月 22 20:47 ibdata1
drwx------ 2 mysql mysql       4096  3月 28 15:33 mysql
srwxrwxrwx 1 mysql mysql          0  5月 14 09:10 mysql.sock
-rw-r--r-- 1 root  root          14  8月 29  2016 mysql_upgrade_info

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

df

ファイルシス            1K-ブロック     使用  使用可 使用% マウント位置
/dev/mapper/centos-root    49746196 40510820 9235376   82% /

このまま看過しているとログファイルがたまったりデータ増加でいずれディスクフルの憂き目に遭います。自分で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

[mysqld]
innodb_data_file_path = ibdata1:100M
innodb_file_per_table=1

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

そして・・・

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

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

/var/lib/mysql

-rw-rw---- 1 mysql mysql     16384  5月 23 15:37 aria_log.00000001
-rw-rw---- 1 mysql mysql        52  5月 23 15:37 aria_log_control
-rw-rw---- 1 mysql mysql   5242880  5月 23 15:37 ib_logfile0
-rw-rw---- 1 mysql mysql   5242880  5月 23 15:37 ib_logfile1
-rw-rw---- 1 mysql mysql 104857600  5月 23 15:37 ibdata1
drwx------ 2 mysql mysql      4096  3月 28 15:33 mysql
srwxrwxrwx 1 mysql mysql         0  5月 23 15:37 mysql.sock
-rw-r--r-- 1 root  root         14  8月 29  2016 mysql_upgrade_info

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

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

df

ファイルシス            1K-ブロック     使用   使用可 使用% マウント位置
/dev/mapper/centos-root    49746196 31744444 18001752   64% /

基礎からのMySQL 第3版 (基礎からシリーズ)