mk-mode BLOG

このブログは自作の自宅サーバに構築した Debian GNU/Linux で運用しています。
PC・サーバ構築等の話題を中心に公開しております。(クローンサイト: GitHub Pages

ブログ開設日2009-01-05
サーバ連続稼働時間
Reading...
Page View 合計
Reading...
今日
Reading...
昨日
Reading...

MySQL - InnoDB データファイルをテーブル単位に変更!

[ サーバ構築 ] [ MySQL ]

こんばんは。

MySQL でストレージエンジンに InnoDB を指定していると、データファイル・ログファイルが作成されます。
デフォルトでは、データファイル(ibdata1)はデータベースが複数あっても1つのファイルとして作成されます。

これだと、データベースが複数あったりサイズが膨大になったりすると、パフォーマンスが悪くなるだけでなく管理も煩雑になってしまいます。

設定ファイルに innodb_file_per_table を設定することで、このデータファイルをテーブル単位で管理できるようになります。
ただ、既にデータベースを InnoDB で運用している場合は、今後作成するテーブルに対してのみ適用され、既存のテーブルについては適用されません。
そこで、既存のテーブルに対しても適用させる方法を以下に記録しておきます。

0. 前提条件

  • Linux Mint 14 Nadia(64bit) での作業を想定。
  • MySQL 5.5.31 での作業を想定。
    (5.1 系や 5.6 系、MariaDB でもだいたい同じであるが、若干異なる部分もある)
  • ストレージエンジン InnoDB の仕組みについてある程度理解できている。
  • MySQL のデータディレクトリは /var/lib/mysql/ を想定。
  • 膨大データのインポート高速化については、前回の記事を参照ください。
    ( MySQL - InnoDB チューニング! )

1. データベースバックアップ

既存の InnoDB のデータベース全てを mysqldump でエクスポートする。

1
$ mysqldump -uroot -p hoge > db_hoge

ちなみに、MySQL 運用中なら --lock-all-tables オプションを指定して(テーブルをロックして)エクスポートした方がよいだろう。

2. データベース削除

MySQL サーバに root でログインして、既存の InnoDB のデータベース全てを削除する。

1
mysql> DROP DATABASE hoge;

3. MySQL 停止

MySQL サービスを停止する。

1
$ sudo service mysql stop

4. データファイル・ログファイル削除

データファイル("ibdata1")とフォルファイル("ib_logfile0", “ib_logfile1")を削除する。

1
2
$ sudo rm -f /var/lib/mysql/ibdata1
$ sudo rm -f /var/lib/mysql/ib_logfile*

5. 設定ファイル編集

MySQL 設定ファイルの [mysqld] に以下の記述を追加する。

/etc/mysql/my.cnf
1
innodb_file_per_table

6. MySQL 起動

MySQL サービスを再起動する。

1
$ sudo service mysql restart

7. 設定確認

MySQL サーバに root でログインして、設定が反映されているか(innodb_file_per_tableONになっているか)確認する。

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

8. データベース作成

MySQL サーバに root でログインして、削除したデータベースを改めて作成する。

1
mysql> CREATE DATABASE hoge;

9. データベースリストア

エクスポートしていたデータを、改めて作成したデータベースにインポートする。

1
$ sudo mysql -uroot -p hoge < db_hoge

10. 動作確認

データファイルがテーブル別に作成されていることを確認する。
(データファイルはテーブル別のディレクトリ配下に作成される)
MySQL データディレクトリに再度 “ibdata1”, “ib_logfile0”, “ib_logfile1” が作成されるが、共有データなので削除してはいけない。

1
$ sudo ls -l /var/lib/mysql/hoge/

管理は明らかにしやすくなっています。
パフォーマンスが良くなったか否かの判断は、実際に運用してみてから行うつもりです。

以上。

Comments