はじめに
MySQLでibdata1のデータサイズが肥大化していた際の対処法。
今回、とある案件でサーバの調査をしていた際にibdata1のサイズがかなり大きくなっているのに気づきました。100GBを余裕で越すほどの。
これはInnoDBのデータ領域(テーブルスペース)です。
こうなると稼働したままデータサイズを小さくするのは難しく、リストアが必要となります。
事前準備
リストア作業を行う前にデータベースを利用するサービスを停止またはメンテ表示にしてトランザクションがmysqlへ来ないようにしておきましょう。
作業
1. mysqldumpの取得
サービスを停止したら対象となるデータベースのdumpを取得します。
だいたいこういう時はデータサイズが大きいので&
をつけて実行しておくと便利です。
$ mysqldump -p****** -u root -B hogehoge > hogehoge.sql &
3. MySQLの停止
dumpが取得できたらMySQLを停止します。
$ sudo service mysqld stop
Shutting down MySQL.. SUCCESS!
4. my.cnfの修正
同じことを繰り返さないためにも設定ファイルを修正します。
まずは原因となる箇所を見てみます。
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
ここにautoextend
が記載されていると、テーブルスペースに空き領域がなくなると自動拡張します。そのため今回は肥大化に繋がりました。
これはデフォルト設定なのでInnoDBを利用する場合は注意が必要です。
なのでこれを回避するためにautoextend
をやめて以下のようにinnodb_file_per_table
を追加しました。
[mysqld]
innodb_data_file_path = ibdata1:1G
innodb_file_per_table
これはテーブルスペースをテーブル単位で作成する設定です。
そうすることでテーブルスペースが1つにまとまってしまうのを防ぐことができます。分散されるのでパフォーマンスも良くなるみたいです。
5. ibdataとib_logfileの削除
それでは肥大化したibdataを削除します。その際、ib_logfileもあれば削除しておきます。
$ rm -f ibdata1 ib_logfile*
6. MySQLを起動する
MySQLを起動します。その際に削除されたibdataも再度作成されるので起動に多少時間がかかります。
$ sudo service mysqld start
Starting MySQL................. SUCCESS!
7. dumpデータの流し込み
ここまできたらあとは、はじめにdumpしたデータを流し込むだけです。
$ mysql -p****** -uroot < hogehoge.sql &
フルダンプでない場合、dumpデータの流し込みには対象となるデータベースを用意しておく必要があります。ない場合は作成しておくのをお忘れなく。
完了したら最後に、事前準備で停止していた関連サービスを起動またはメンテ解除すれば完了です。
おわりに
InnoDBはトランザクション機能がポイントですが、それが故に今回のような状況に陥る可能性があるので注意が必要です。