mysqlのデータベースを削除して領域を解放する

ローカルのmysqlのサイズが30GBを超えていたので、9割以上のいらないDBを消して領域を解放した話。

ローカルの開発環境を想定していて、本番環境は想定していない。
バージョンによって違うだろうし、作業の流れや、DBの特性などの参考にメモ。

環境

MacOSX 10.9.2
mysql: Server version: 5.6.16-log Homebrew

mysqlのDBのサイズを調べる

SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+------------------------------+--------------+
| Database name                | Size (MB)    |
+------------------------------+--------------+
| information_schema           |   0.00976563 |
| mysql                        |   0.61087894 |
| ...                          | 331.71875000 |
| performance_schema           |   0.00000000 |
+------------------------------+--------------+

参考 : List sizes of MySQL databases

DBをひたすら消す

DROP DATABASE my_database1;
DROP DATABASE my_database2;
DROP DATABASE my_database3;
...

予定ではこれでおしまいだったが、mysqlの占有領域が減らなかった。

MySQL(5.6)のデフォルト設定では、サイズが減らない。
参考 : How to shrink/purge ibdata1 file in MySQL

my.cnfの設定変えれば以後は、削除時サイズを小さく保てるらしい。（設定については省略）

mysqlのdataをすべて作り直す

以下の手順に従う。

MYSQL: REDUCING IBDATA1

Dump the Whole Database の部分

残されたDBをdump

mysqldump -uroot -p --extended-insert --all-databases --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > all-databases.sql

注．記事のコードを貼付けるとハイフンが違うのでエラーになる

※ローカルなのでユーザーはroot

mysqld停止

ps aux | grep mysqlからkill -9使った

データを移動または削除

mv /usr/local/var/mysql /usr/local/var/mysql_bk

my.cnfの設定変更

必要なら設定変更

mysqlのdb初期化

/usr/local/bin/mysql_install_db --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

記事の通りだとエラーになったから他を調べた。
参考 : Homebrew - mysql_install_db => FATAL ERROR

パスワード設定

/usr/local/opt/mysql/bin/mysqladmin -u root password 'new-password'

mysqld起動

別セッションなどで mysqld

DBをインポート

dumpしたディレクトリで mysql -uroot -p

SET FOREIGN_KEY_CHECKS=0;
SOURCE all-databases.sql;
SET FOREIGN_KEY_CHECKS=1;
exit

mysqld再起動

サイズ確認

$ du -sh /usr/local/var/mysql*
2.4G    /usr/local/var/mysql
 32G    /usr/local/var/mysql_bk

動作確認 -> OK
rm -rf /usr/local/var/mysql_bk

その他

MySQLは個人アプリでしか使ってなくて、最新事情は知らない。

最新事情調べる
MySQL - Wikipedia
MySQL 5.6 Release Notes

削除したデータは2年前のものとかあったけど、意外と新しいの使ってた。

どっかで brew upgrade したかな