mysql ibdata1を小さくする。テーブル全て作り直したというお話

fluentd経由でmysqlにログつっこんで、レンジパーティション切ってパーティション毎にdropで楽な運用にと思ってたのに、デフォルトだとinnodbのデータはibdata1にすべて格納されると知らず、それが肥大化しすぎて圧縮不可と知ったので、物理ファイルをテーブル毎に分けるため作り直した話

やった作業の備忘録（自分の為に）

$ sudo vim /etc/my.cnf

innodbのテーブル毎ファイル保管をオンにする
※新規にテーブル作成時に適用され圧縮等できるようになる
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:1G:autoextend

自作スクリプトにて対象全テーブルのdump取得
$ sudo sh winevt_dump.sh

各テーブルの件数を確認しとく

対象テーブルを削除
mysql> drop database **db;
mysql> drop database **db;
mysql> drop database **db;

サービス停止
$sudo service mysqld stop

$sudo mv /var/lib/mysql/ibdata1 /tmp/
$sudo mv /var/lib/mysql/ib_logfile0 /tmp/
$sudo mv /var/lib/mysql/ib_logfile1 /tmp/

DB作成
mysql> create database **db;
mysql> create database **db;
mysql> create database **db;

dump取得時に一番小さいテーブルをまずリストアしてみる
解凍
$ gunzip tbl_**_db.sql.2014-05-13.gz

$ mysql -uroot -p -d **db --default-character-set=utf8
<tbl_**_db.sql.2014-05-13.gz

リストアして*.idbが作成されたことを確認する

ll /var/lib/mysql

drwx------ 2 mysql mysql       4096  5月 13 10:40 2014 **db

DB毎にディレクトリが切られてその下に.idbが出来てる

# ll **db
合計 135188
-rw-rw---- 1 mysql mysql        61  5月 13 10:40 2014 db.opt
-rw-rw---- 1 mysql mysql      8722  5月 13 10:40 2014 tbl_**.frm
-rw-rw---- 1 mysql mysql 138412032  5月 13 10:40 2014 tbl_**.ibd

ここで直近のデータ引いてデータ入っていることと件数を確認

ここまでくれば後は、同じ手順を繰り返す

退避したibdata1とログを消すの忘れずに。

一番感動したのは、パーティショニングしているテーブルはこんな感じでパーティション毎に物理ファイル分かれる。
これぞ求めていた形。

合計 506649600
-rw-rw---- 1 mysql mysql           61  5月 13 11:17 2014 db.opt
-rw-rw---- 1 mysql mysql 209660674048  5月 14 01:11 2014 tbl_**#P#p201403.ibd
-rw-rw---- 1 mysql mysql 202505191424  5月 14 09:23 2014 tbl_**#P#p201404.ibd
-rw-rw---- 1 mysql mysql 106640179200  5月 14 17:15 2014 tbl_**#P#p201405.ibd
-rw-rw---- 1 mysql mysql        98304  5月 13 16:44 2014 tbl_**#P#p201406.ibd
-rw-rw---- 1 mysql mysql        98304  5月 13 16:44 2014 tbl_**#P#p201407.ibd
-rw-rw---- 1 mysql mysql        98304  5月 13 16:44 2014 tbl_**#P#p201408.ibd
-rw-rw---- 1 mysql mysql        98304  5月 13 16:44 2014 tbl_**#P#p201409.ibd

自分の無知さでリカバリにだいぶ時間かかったがいい経験になったかな。。