MySQLのibdata1を圧縮する

Zabbixのデータを格納するためにMySQLを使っています。
しばらく運用していると/var/lib/mysql/ibdata1のサイズが約1.5GBとなりディスク容量を圧迫していました。

$ sudo ls -l /var/lib/mysql/ib*
-rw-rw---- 1 mysql mysql    5242880 2012-04-22 10:41 ib_logfile0
-rw-rw---- 1 mysql mysql    5242880 2012-04-22 10:41 ib_logfile1
-rw-rw---- 1 mysql mysql 1512046592 2012-04-22 10:41 ibdata1
$

ibdata1肥大化の対応

ibdata1は、MySQLのトランザクションセーフのストレージエンジンです。
InnoDB データおよびログファイルの追加、削除、またはサイズ変更によると、デフォルト設定ではibdata1を自動拡張し、縮小することはありません。
縮小するためには、バックアップしてデータベースとibdataファイルの削除、リストアが必要になるようです。
InnoDB テーブルのデフラグメント化の手順でテーブルごとに保守するためには、my.cnfの[mysqld]セクションにinnodb_file_per_tableを設定する必要があります。

Apache/Zabbixの停止

データベースをバックアップするためにApacheとZabbix-serverを停止します。

$ sudo /etc/init.d/apache2 stop
 * Stopping web server apache2
 ... waiting .   ...done.
$ sudo /etc/init.d/zabbix-server stop
 * Stopping Zabbix server zabbix_server
   ...done.
$

バックアップ

MySQLのバックアップは、mysqldumpコマンドを使います。
今回は、MySQLのサーバのディスク空き容量が少なくなっていたのでSSH経由で別マシンにバックアップを取りました。
常時稼動しているのは、Linuxサーバ1台、クライアント7台を1年くらい監視しているサーバですが、バックアップは2分強で終わるようです。

backup.local$ time ssh mysql.local mysqldump -u root -p<mysqlパスワード> --all-database > mysql.mysqldump

real    2m10.857s
user    0m1.928s
sys     0m6.656s
backup.local$

テーブルスペースの削除

InnoDBを使っている全テーブルスペースを削除します。
バックアップが確実にとれていないと元には戻れません。

$ mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.1.61-0ubuntu0.11.10.1 (Ubuntu)

Copyright (c) 2000, 2011, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop database zabbix;
Query OK, 88 rows affected (3.67 sec)
mysql> quit
Bye
$

MySQLサーバの停止とログファイルの削除

MySQLサーバを停止してログファイルを削除します。

$ sudo service mysql stop
mysql stop/waiting
$ sudo su -
# cd /var/lib/mysql/
# rm -rf ib_logfile0 
# rm -rf ib_logfile1 
# rm -rf ibdata1 
＃

設定変更とMySQLサーバの起動

my.cnfの[mysqld]セクションにinnodb_file_per_tableを設定します。

$ sudo bzr diff
=== modified file 'mysql/my.cnf'
--- mysql/my.cnf        2011-04-28 09:30:27 +0000
+++ mysql/my.cnf        2012-04-22 02:22:08 +0000
@@ -32,6 +32,7 @@
 nice           = 0
 
 [mysqld]
+innodb_file_per_table
 #
 # * Basic Settings
 #
$ sudo service mysql start
mysql start/running, process 28379
$

テーブルスペースの作成

削除したテーブルスペースを作成します。

mysql> create database zabbix;
Query OK, 1 row affected (0.04 sec)

mysql>

リストア

SSH経由でバックアップしたファイルをSSH経由でリストアします。
リストアには、12分弱かかりました。

$ time ssh backup.local cat /tmp/mysql-backup/mysql.mysqldump | mysql -u root -p<mysqlパスワード> zabbix

real    11m29.064s
user    0m38.986s
sys     0m5.876s
$

停止したサーバの起動

停止したサーバを起動して動作確認します。

 sudo /etc/init.d/zabbix-server start
 * Starting Zabbix server zabbix_server
   ...done.
$ sudo /etc/init.d/apache2 start
 * Starting web server apache2
   ...done.
$

サイズ

ibdata1は、1.5GBから18MBまで小さくなりました。
dfコマンドで確認したサイズで約750MB小さくなりました。

# ls -l ib*
-rw-rw---- 1 mysql mysql  5242880 2012-04-22 17:48 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 2012-04-22 17:40 ib_logfile1
-rw-rw---- 1 mysql mysql 18874368 2012-04-22 17:48 ibdata1
#

ksaitoの日記

日々試したことの覚え書き