55
72

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Posted at

はじめに

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の修正

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

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

これはテーブルスペースをテーブル単位で作成する設定です。
そうすることでテーブルスペースが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はトランザクション機能がポイントですが、それが故に今回のような状況に陥る可能性があるので注意が必要です。

55
72
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
55
72

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?