MySQL のデータを整理してサーバストレージの空き容量を確保する

旧い MySQL サーバを移行するために、まずは空き容量を確保しないと。
ibdata1 ファイルが肥大化しているのでどうにかしたい。

環境

• Ubuntu 14.04.1 LTS \n \l
• mysqld 5.5.38-0ubuntu0.14.04.1

最初

# ls -alh /var/lib/mysql
total 2.8G
drwx------  7 mysql mysql 4.0K Mar 20 18:07 .
drwxr-xr-x 48 root  root  4.0K Jul 27  2016 ..
drwx------  2 mysql mysql 4.0K Apr 29  2015 database1
drwx------  2 mysql mysql 4.0K Apr 20  2016 database2
-rw-r--r--  1 root  root     0 Oct  3  2014 debian-5.5.flag
drwx------  2 mysql mysql 4.0K Oct  3  2014 database3
-rw-rw----  1 mysql mysql 2.8G Jun 25 08:26 ibdata1
-rw-rw----  1 mysql mysql 5.0M Jun 25 08:26 ib_logfile0
-rw-rw----  1 mysql mysql 5.0M Jun 25 08:26 ib_logfile1
drwx------  2 mysql root  4.0K Oct  3  2014 mysql
-rw-rw----  1 root  root     6 Oct  3  2014 mysql_upgrade_info
drwx------  2 mysql mysql 4.0K Oct  3  2014 performance_schema

まずは ibdata1 をスリムにしようかな。

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1      7.8G  7.3G   31M 100% /
none            4.0K     0  4.0K   0% /sys/fs/cgroup
udev            492M   12K  492M   1% /dev
tmpfs           100M  348K   99M   1% /run
none            5.0M     0  5.0M   0% /run/lock
none            497M     0  497M   0% /run/shm
none            100M     0  100M   0% /run/user

おっと、これはこれは。31M しかないのでMySQLとは関係ないゴミファイルを整理して作業場所を開けました。

# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1      7.8G  6.6G  748M  91% /
none            4.0K     0  4.0K   0% /sys/fs/cgroup
udev            492M   12K  492M   1% /dev
tmpfs           100M  348K   99M   1% /run
none            5.0M     0  5.0M   0% /run/lock
none            497M     0  497M   0% /run/shm
none            100M     0  100M   0% /run/user

これで、データベースのダンプを取ってから ibdata1 を削除することにします。

# mysqldump -u root -p -A > 20230625.sql 
Enter password: 
mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'wp\_comments'': Got error 28 from storage engine (1030)

あれれ。ストレージが一杯?

# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1      7.8G  7.4G     0 100% /
none            4.0K     0  4.0K   0% /sys/fs/cgroup
udev            492M   12K  492M   1% /dev
tmpfs           100M  348K   99M   1% /run
none            5.0M     0  5.0M   0% /run/lock
none            497M     0  497M   0% /run/shm
none            100M     0  100M   0% /run/user

おっとー、ダンプファイルが大きすぎて容量がいっぱいになってしまいました。どこかでデータベース使っているところがありますね。ダンプファイルを一旦消してからデータベースにログオンします。

+--------------------+
| Database           |
+--------------------+
| information_schema |
| database1          |
| database2          |
| database3          |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)

この中で database3 が怪しかったので、テーブルが何があるか調べてみます。

Database changed
mysql> show tables;
+-----------------------+
| Tables_in_fablib      |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
11 rows in set (0.00 sec)

それぞれ、レコード数がどれだけあるか調べてみます。

mysql> select table_name, table_rows from information_schema.TABLES where table_schema = 'database3';
+-----------------------+------------+
| table_name            | table_rows |
+-----------------------+------------+
| wp_commentmeta        |         20 |
| wp_comments           |     501502 |
| wp_links              |          0 |
| wp_options            |        112 |
| wp_postmeta           |         70 |
| wp_posts              |         37 |
| wp_term_relationships |          8 |
| wp_term_taxonomy      |          1 |
| wp_terms              |          1 |
| wp_usermeta           |         17 |
| wp_users              |          1 |
+-----------------------+------------+
11 rows in set (0.04 sec)

なるほどー、wp_comments はいらないデータので消してしまいます。

mysql> delete from wp_comments;
Query OK, 344955 rows affected (1 min 35.19 sec)

mysql> 

このうえで、データベースを dump します。

# mysqldump -u root -p -A > 20230625.sql 

データベースを全部 drop します。

mysql> drop database information_schema;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

information_schema だけは消せませんでしたが、そのまま進めます。

mysqld を停止します。

# service mysql stop
mysql stop/waiting

消すものを確認

# ls -alh
total 3.1G
drwx------  3 mysql mysql 4.0K Jun 25 09:04 .
drwxr-xr-x 48 root  root  4.0K Jul 27  2016 ..
-rw-r--r--  1 root  root   72M Jun 25 08:59 20230625.sql
-rw-r--r--  1 root  root     0 Oct  3  2014 debian-5.5.flag
-rw-rw----  1 mysql mysql 2.9G Jun 25 09:05 ibdata1
-rw-rw----  1 mysql mysql 5.0M Jun 25 09:05 ib_logfile0
-rw-rw----  1 mysql mysql 5.0M Jun 25 09:04 ib_logfile1
drwx------  2 mysql root  4.0K Oct  3  2014 mysql
-rw-rw----  1 root  root     6 Oct  3  2014 mysql_upgrade_info

ibdata1,ib_logfile を削除します

# rm ibdata1 ib_logfile*

mysqldを起動して

# service mysql start
mysql start/running, process 8031

dump したデータを復旧します

# mysql -p -u root < 20230625.sql

結果です

# ls -alh /var/lib/mysql
total 260M
drwx------  6 mysql mysql 4.0K Jun 25 09:06 .
drwxr-xr-x 48 root  root  4.0K Jul 27  2016 ..
-rw-r--r--  1 root  root   72M Jun 25 08:59 20230625.sql
drwx------  2 mysql mysql 4.0K Jun 25 09:06 database1
drwx------  2 mysql mysql 4.0K Jun 25 09:06 database2
-rw-r--r--  1 root  root     0 Oct  3  2014 debian-5.5.flag
drwx------  2 mysql mysql 4.0K Jun 25 09:06 database3
-rw-rw----  1 mysql mysql 106M Jun 25 09:11 ibdata1
-rw-rw----  1 mysql mysql 5.0M Jun 25 09:11 ib_logfile0
-rw-rw----  1 mysql mysql 5.0M Jun 25 09:11 ib_logfile1
drwx------  2 mysql root  4.0K Jun 25 09:06 mysql
-rw-rw----  1 root  root     6 Oct  3  2014 mysql_upgrade_info
# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1      7.8G  4.1G  3.3G  56% /
none            4.0K     0  4.0K   0% /sys/fs/cgroup
udev            492M   12K  492M   1% /dev
tmpfs           100M  348K   99M   1% /run
none            5.0M     0  5.0M   0% /run/lock
none            497M     0  497M   0% /run/shm
none            100M     0  100M   0% /run/user

やれやれ。