More than 5 years have passed since last update.

MySQL ibdataのデータサイズが肥大化した場合の対処方法。

Posted at 2016-10-28

はじめに

MySQLでibdata1のデータサイズが肥大化していた際の対処法。

今回、とある案件でサーバの調査をしていた際にibdata1のサイズがかなり大きくなっているのに気づきました。100GBを余裕で越すほどの。
これはInnoDBのデータ領域(テーブルスペース)です。
こうなると稼働したままデータサイズを小さくするのは難しく、リストアが必要となります。

リストア作業を行う前にデータベースを利用するサービスを停止またはメンテ表示にしてトランザクションがmysqlへ来ないようにしておきましょう。

サービスを停止したら対象となるデータベースのdumpを取得します。
だいたいこういう時はデータサイズが大きいので&をつけて実行しておくと便利です。

コマンド

$ mysqldump -p****** -u root -B hogehoge > hogehoge.sql &

dumpが取得できたらMySQLを停止します。

コマンド

$ sudo service mysqld stop
Shutting down MySQL.. SUCCESS!

同じことを繰り返さないためにも設定ファイルを修正します。
まずは原因となる箇所を見てみます。

my.cnf(デフォルト)

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

ここにautoextendが記載されていると、テーブルスペースに空き領域がなくなると自動拡張します。そのため今回は肥大化に繋がりました。
これはデフォルト設定なのでInnoDBを利用する場合は注意が必要です。

なのでこれを回避するためにautoextendをやめて以下のようにinnodb_file_per_tableを追加しました。

my.cnf（変更後）

[mysqld]
innodb_data_file_path = ibdata1:1G
innodb_file_per_table

これはテーブルスペースをテーブル単位で作成する設定です。
そうすることでテーブルスペースが１つにまとまってしまうのを防ぐことができます。分散されるのでパフォーマンスも良くなるみたいです。

それでは肥大化したibdataを削除します。その際、ib_logfileもあれば削除しておきます。

コマンド

$ rm -f ibdata1 ib_logfile*

MySQLを起動します。その際に削除されたibdataも再度作成されるので起動に多少時間がかかります。

コマンド

$ sudo service mysqld start
Starting MySQL................. SUCCESS!

ここまできたらあとは、はじめにdumpしたデータを流し込むだけです。

コマンド

$ mysql -p****** -uroot < hogehoge.sql &

フルダンプでない場合、dumpデータの流し込みには対象となるデータベースを用意しておく必要があります。ない場合は作成しておくのをお忘れなく。

完了したら最後に、事前準備で停止していた関連サービスを起動またはメンテ解除すれば完了です。

InnoDBはトランザクション機能がポイントですが、それが故に今回のような状況に陥る可能性があるので注意が必要です。