[MySQL] ibdata1のサイズを削減する手順 [InnoDB]

我が家では”Zabbix“や”WordPress“を利用していて、そのバックエンドのDBとして、”MySQL“を利用しています。
立ち上げに特に設計もしているわけでもなく、何となく使い始めたために、知らぬ間にいろいろと課題が出てきました。稼働に影響を与えているものとして 2つあります。

MySQLのdatadir直下にある ibdata1がディスクそのものを圧迫している
一つのサーバにいろいろと同居している上、SMPも使えず 32bitによるメモリ制限もあって、MySQLがボトルネックになっている

何も考えずに作り始めるってのは、ホント問題ですねぇ。アーキテクトとしては最低です。まぁ、作り始めたころはまだアーキテクトではなかったので、良しとしましょう ^[I]よろしくない
この二つの課題の内、一つ目「ibdata1ファイルがディスクそのものを圧迫している」問題について、先にまず対処しました ^[II]それ自体は数ヶ月前に対処してるんですけど。

全体の流れ

いくつかのサイトにも同様の対処をするための手順が載っていましたが、我が家でぴったりとあうものがなかったり、一部の操作だけだったので、複数をまとめて、一連の一つの作業手順を作ってから作業を実施しました。
流れとしては、次の通りになります。

データベースに影響するサービスの停止
全てのデータベースのバックアップ
全てのデータベースの削除
MySQLの停止
my.cnfファイルの設定
ibdata1の削除
MySQLの稼働
バックアップからのリストア
停止したサービスの再稼働

流れをご覧いただいたように、データが多いと時間もかかりますので、サービスへの影響を考慮して作業を実施ください。

1.データベースに影響するサービスの停止

まず、MySQLを利用しているサービスを全て停止しましょう。DBをロックして一時しのぎすることもできますが、後々、確実に停止する局面がやってきますので、DBがあまり大きくなければこの時点で止めてしまうことがオススメです。
今回は、サービスを停止していることを前提に次の作業へ続きます。

例えば zabbix を停止するような場合は、このようになるでしょう。

1 2	$ sudo /etc/init.d/zabbix-server stop Stopping Zabbix server: zabbix_server.

2.全てのデータベースのバックアップ

mysqldumpコマンドを使いましょう。どのTABLEやDATABASEが InnoDBを利用しているかどうか、そういったことを忘れて、全てのバックアップをとってしまいましょう。

$ sudo mysqldump \
 --all-databases \
 --add-drop-database \
 --add-drop-table \
 --lock-all-tables \
 > /tmp/dumpdata

–all-databases: 全てのデータベースを対象とします
–add-drop-database: リストア時の”CREATE DATABASE”前に、”DROP DATABASE”を実行します (保険)
–add-drop-table: リストア時の”CREATE TABLE”前に、”DROP TABLE”を実行します (保険)
–lock-all-tables: 全てのTABLEをLOCKします。一応。(保険)
-u <ユーザ名> -p [パスワード]: ※(必要に応じて)もし、メンテナンス用に別途ユーザを準備している場合は、-u と -p でユーザを指定する必要がありますよ。

ご覧いただいたように、まちがいを極力排除するために、全て削除する方向になっています。

3.全てのデータベースの削除

リストアする時に全て削除されるはずですが、InnoDBを利用している対象のDATABASEをDROPしておきましょう。

zabbixを使用している場合は、次のように mysql コマンドラインから、えいやっと削除です。思い切りが大事です。

1 2	mysql> drop database zabbix; mysql> quit

全部消してしまう人は、SHOW DATABASES; して出てくる Database を全部消しても多分平気です。

4.MySQLの停止

ここで晴れてMySQLを止めることができます。

たとえば、次のようになるでしょう。

1 2	$ sudo /etc/init.d/mysql stop Stopping MySQL database server: mysqld.

5.my.cnfファイルの設定

停止する前に作業をしても良いです。この手順では安全策をとっています。
ファイル変更の前に、一応、バックアップを取っておきましょう。どのような方法でも良いですが、例えば。

1	$ sudo cp -a /etc/mysql/my.cnf /tmp/my.cnf.20130107

こんな感じでファイルが残っているだけでも安心でしょう。/tmp/部分はお好きな場所を選択ください。

次に、[mysqld]セクション内に、次の2行を追加しましょう。

1 2	innodb_data_file_path = ibdata1:1G innodb_file_per_table

innodb_data_file_path = ibdata1:1G: innodb で使用する ibdata1 のサイズ制限です。今回は 1GBとしました。この制限サイズまで達すると、TABLEの更新ができなくなります。
innodb_file_per_table: ibdata1に全てのデータを放り込まずに、それぞれのテーブルへ分散させます。 “cl.pocari.org – 拡張され続ける InnoDB のデータファイルのサイズを小さくする方法“

詳しくはどういうことかというと、”漢(オトコ)のコンピュータ道: InnoDBのファイルサイズ管理” こちらが詳しいです。

6.ibdata1の削除

えいやっと削除しましょう。

ですが、いきなり削除してうまく行かなかった時に残念な思いをするので、あまり意味は無いかも知れませんが、/tmp/ へ移動する程度にしておきましょう。

1
2
3

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

^[III]/var/lib/mysql/ は my.cnf 内の datadir を変更している場合は、適宜読み替えること

7.MySQLの稼働

次に改めて起動です。多分、時間がかかります。failed する場合もありますが、logを見てみましょう。時間がかかっているだけの場合もあります。

まず、起動した場合

1 2	$ sudo /etc/init.d/mysql start Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

我が家はめでたくfailしました。

そこで、ログファイルを確認してみましょう。
起動時のログなので、大抵の syslogでは /var/log/daemon.logになると思います。次のようになっているのではないでしょうか。

Jan  8 xx:xx:xx host01 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Jan  8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx [Note] Plugin 'FEDERATED' is disabled.
Jan  8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx  InnoDB: Initializing buffer pool, size = 8.0M
Jan  8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx  InnoDB: Completed initialization of buffer pool
Jan  8 xx:xx:xx host01 mysqld: InnoDB: The first specified data file ./ibdata1 did not exist:
Jan  8 xx:xx:xx host01 mysqld: InnoDB: a new database to be created!
Jan  8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx  InnoDB: Setting file ./ibdata1 size to 1024 MB
Jan  8 xx:xx:xx host01 mysqld: InnoDB: Database physically writes the file full: wait...
Jan  8 xx:xx:xx host01 mysqld: InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000
Jan  8 xx:xx:xx host01 /etc/init.d/mysql[22517]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Jan  8 xx:xx:xx host01 /etc/init.d/mysql[22517]: #007/usr/bin/mysqladmin: connect to server at 'localhost' failed
Jan  8 xx:xx:xx host01 /etc/init.d/mysql[22517]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Jan  8 xx:xx:xx host01 /etc/init.d/mysql[22517]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Jan  8 xx:xx:xx host01 /etc/init.d/mysql[22517]:
Jan  8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
Jan  8 xx:xx:xx host01 mysqld: InnoDB: Setting log file ./ib_logfile0 size to 5 MB
Jan  8 xx:xx:xx host01 mysqld: InnoDB: Database physically writes the file full: wait...
Jan  8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
Jan  8 xx:xx:xx host01 mysqld: InnoDB: Setting log file ./ib_logfile1 size to 5 MB
Jan  8 xx:xx:xx host01 mysqld: InnoDB: Database physically writes the file full: wait...
Jan  8 xx:xx:xx host01 mysqld: InnoDB: Doublewrite buffer not found: creating new
Jan  8 xx:xx:xx host01 mysqld: InnoDB: Doublewrite buffer created
Jan  8 xx:xx:xx host01 mysqld: InnoDB: Creating foreign key constraint system tables
Jan  8 xx:xx:xx host01 mysqld: InnoDB: Foreign key constraint system tables created
Jan  8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx  InnoDB: Started; log sequence number 0 0
Jan  8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx [Note] Event Scheduler: Loaded 0 events
Jan  8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx [Note] /usr/sbin/mysqld: ready for connections.
Jan  8 xx:xx:xx host01 mysqld: Version: '5.1.66-0+squeeze1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)

Jan 8 xx:xx:xx host01 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql Jan 8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx [Note] Plugin 'FEDERATED' is disabled. Jan 8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx InnoDB: Initializing buffer pool, size = 8.0M Jan 8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx InnoDB: Completed initialization of buffer pool Jan 8 xx:xx:xx host01 mysqld: InnoDB: The first specified data file ./ibdata1 did not exist: Jan 8 xx:xx:xx host01 mysqld: InnoDB: a new database to be created! Jan 8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx InnoDB: Setting file ./ibdata1 size to 1024 MB Jan 8 xx:xx:xx host01 mysqld: InnoDB: Database physically writes the file full: wait... Jan 8 xx:xx:xx host01 mysqld: InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 Jan 8 xx:xx:xx host01 /etc/init.d/mysql[22517]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in Jan 8 xx:xx:xx host01 /etc/init.d/mysql[22517]: #007/usr/bin/mysqladmin: connect to server at 'localhost' failed Jan 8 xx:xx:xx host01 /etc/init.d/mysql[22517]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Jan 8 xx:xx:xx host01 /etc/init.d/mysql[22517]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Jan 8 xx:xx:xx host01 /etc/init.d/mysql[22517]: Jan 8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx InnoDB: Log file ./ib_logfile0 did not exist: new to be created Jan 8 xx:xx:xx host01 mysqld: InnoDB: Setting log file ./ib_logfile0 size to 5 MB Jan 8 xx:xx:xx host01 mysqld: InnoDB: Database physically writes the file full: wait... Jan 8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx InnoDB: Log file ./ib_logfile1 did not exist: new to be created Jan 8 xx:xx:xx host01 mysqld: InnoDB: Setting log file ./ib_logfile1 size to 5 MB Jan 8 xx:xx:xx host01 mysqld: InnoDB: Database physically writes the file full: wait... Jan 8 xx:xx:xx host01 mysqld: InnoDB: Doublewrite buffer not found: creating new Jan 8 xx:xx:xx host01 mysqld: InnoDB: Doublewrite buffer created Jan 8 xx:xx:xx host01 mysqld: InnoDB: Creating foreign key constraint system tables Jan 8 xx:xx:xx host01 mysqld: InnoDB: Foreign key constraint system tables created Jan 8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx InnoDB: Started; log sequence number 0 0 Jan 8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx [Note] Event Scheduler: Loaded 0 events Jan 8 xx:xx:xx host01 mysqld: 130107 xx:xx:xx [Note] /usr/sbin/mysqld: ready for connections. Jan 8 xx:xx:xx host01 mysqld: Version: '5.1.66-0+squeeze1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian)

ログを見る限り、正常に起動しているようです。
なので、稼働状態を見てみると、正常に起動しています。焦らないでねヽ(´ー｀)ノ ^[IV]Debian Squeeze での結果です

$ sudo /etc/init.d/mysql status
/usr/bin/mysqladmin  Ver 8.42 Distrib 5.1.66, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Server version          5.1.66-0+squeeze1
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 1 min 14 sec
 
Threads: 1  Questions: 2  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.27.

8.バックアップからのリストア

では、先ほどのバックアップから、リストアしてみましょう。

1 2	mysql -u root-p < /tmp/dumpdata Enter password:

バックアップを取得した時よりも時間がかかります。気長におまちください。

9.停止したサービスの再稼働

最後に、停止していたサービスを開始して、正しくサービスが提供されているかどうかを確認しましょう。

先ほど停止した zabbixを開始すると次のようになるはずです。

1 2	$ sudo /etc/init.d/zabbix-server start Starting Zabbix server: zabbix_server.

以上です、zabbix にアクセスしてみましょうヽ(´ー｀)ノお疲れ様でした

[tmkm-amazon]4798124230[/tmkm-amazon]

References[+]

References
↑I	よろしくない
↑II	それ自体は数ヶ月前に対処してるんですけど
↑III	/var/lib/mysql/ は my.cnf 内の datadir を変更している場合は、適宜読み替えること
↑IV	Debian Squeeze での結果です