主夫ときどきプログラマ

データベース、Webエンジニアリング、コミュニティ、etc

ibdata1のサイズを減らす方法

mysqlのデータフォルダが大きくなっている。どうもibdata1というファイルが肥大化している。調べてみるとデフォルト設定でInnoDbを長期間使っていると、そのデータファイルであるibdata1が実際のデータよりも大きいファイルになってしまう。しかもこのibdata1は全てのInnoDbが共通で使うデータファイルのようだ。
これはいかん、ということでibdata1のサイズを減らし、tableごとにデータファイルを作成する方法のまとめ。

1.databaseをすべてダンプ

DBに登録されているデータをすべてダンプしてバックアップをつくります。--single-transaction のオプションをつけることで、ダンプ中のデータ変更を禁止します。

# mysqldump -u root -p --all-database --single-transaction > /tmp/all-database.dump

2.InnoDbを使っているテーブルを全て削除する。

TRUNCATEではなくDropする。面倒なのでschemaごとDropしてしまえ。

3.mysqldを停止

# /sbin/service mysqld stop

4.データディレクトリのibdata1, ib_logfile0, ib_logfile1を削除

# rm -f ibdata1
# rm -f ib_logfile0
# rm -f ib_logfile1

念のためにリネームしておくほうがベター

5.my.cnfを編集

innodb_data_home_dir = /var/lib/mysql	(default comment out)
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend	(default comment out)
innodb_file_per_table
innodb_buffer_pool_size = 512M

詳細はマニュアルを見るべし→http://dev.mysql.com/doc/refman/5.1/ja/innodb-configuration.html

6.mysqldを起動

# /sbin/service/mysqld start

※ログをチェックしてみよう

〜(略)〜
Starting MySQL................................... [Failed]

となることがあるけれど、ibdata1 の作成に時間がかかりtimeoutしてfailedとなるがmysqldは正常に起動する場合もあるらしい。
ログの参照と、プロセスの参照をして判断しよう。

# tail -n 20 /var/log/mysqld.log
# ps aux | grep mysqld

など

7.ダンプデータをインポート

# mysql -u root -p < /tmp/all-database.dump

時間がたくさんかかります。気をつけよう。

補足

データテーブルに mysql-bin.000xxxというファイルが大量にある場合は必要に応じて削除する。
直接ファイルを削除せず、mysqlのコンソールから削除すること。