mysql innodb引擎 长时间使用后，数据文件远大于实际数据量，导致空间不足。

近期我碰到了一个令人头疼的事情。就是我的mysql服务器使用了很久之后，发现/data  目录的空间占满了我系统的整个空间，马上就要满了。下面是我的分析。

在网上查看了这2个方法，但是执行后发现没有解决。系统空间没有变小。

 

1.optimize table table.name                                                    #后面发现这种方式只对MyISAM引擎的表格有用。

2.ALTER TABLE table.name ENGINE='InnoDB';                 #这种方式从原理上，感觉可以，但是我的测试并没有成功。

 

所以继续在网上查资料，并按整理下分析思路，最终解决：

ps -ef|grep mysql

root      1297     1  0 12:02 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data --pid-file=/var/run/mysqld/mysql5.pid
mysql     1412  1297  0 12:02 ?        00:00:01 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/data --user=mysql --log-error=/var/log/mysqld/mysql5-error.log --pid-file=/var/run/mysqld/mysql5.pid --socket=/var/run/mysqld/mysql5.socket --port=3306 

备份命令：

time=`date +%Y%m%d`
mysqldump -u root -p123456 -e --max_allowed_packet=16777216 --net_buffer_length=16384 my_data >/backup/my_data$time.sql

全库备份大小：du -sh /backup/my_data$time.sql                      看到大小为150G

数据库的大小：du -sh /data/my_data                                             看到457G

 

我经过多番研究，发现是innodb 为引擎的数据表，他的文件大小是只会增加而不会减小的，

例如你有1个表格里面有约10000000条，大概10G的数据，但是你手动删除了5000000条数据，即约5G的数据，但是删除后，你会发现系统的空间还是占用了10G，

如果需要减小他的大小只有1种方式，就是重建数据库或是数据表。

 

例如你执行下面语句可以查看表格是否多占用了空间： show table status like  ‘table_1%';

就会看到里面有个 Data_free字段，而他对应的值就是他多占用的物理空间（61827186688），如果我们重建 或是重新导入即可节省出空间（61827186688）。

mysql> show table status like 'table_1%';
+--------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+---------+
| Name         | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free   | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options     | Comment |
+--------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+---------+
| table_1      | InnoDB |      10 | Compact    | 43626792 |            478 | 20884504576 |               0 |   3810557952 | 61827186688 |      527273346 | 2013-01-21 18:58:58 | NULL        | NULL       | utf8_unicode_ci |     NULL | row_format=COMPACT |         |
+--------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+---------+
2 rows in set (1.41 sec)

 

因为我的系统对mysql 做了独立表空间，所以我可以看到各个表占用的系统空间。

[root@SJZQ76 ~]# du -sh /data/my_data/* |grep G
66G     /data/my_data/table_1.ibd
64G    /data/my_data/table_2.ibd
106G     /data/my_data/table_3.ibd
84G    /data/my_data/table_4.ibd

经过我的仔细查看发现，原来我的数据库里面有4个表格是大于60G的  还有1个是100G的，通过上面这个方式查看，data_free下面的值非常大，所以我在测试环境测试后，发现没有问题。

如果你的系统是默认的，没做独立表空间，即在/etc/my.conf 这个文件中没有添加这一条innodb_file_per_table=1；那么你就只能用show table status like 'table%'; 一个一个表格去查看 Data_free字段,找出可以恢复空间的表格。

然后进行下面操作：

#到处可以恢复系统空间的表格。

mysqldump -uroot -p123456  my_data table_1 >/mnt/table_1.sql 

#登录数据库

mysql -uroot -p123456   

#选择数据库                                                                       

use my_data;              

#删除可恢复空间的表格，即data_free值很大的                                                                        

drop table table_1.sql;      

  #恢复表格                                                            

source /mnt/table_1.sql;           

#对比恢复前后的状态                                                     

show table status like 'table_1%';                                                    

 

对比和之前状态一直，然后查看系统，发现系统的空间恢复了几十G，如此对几个大的表格都做了如上操作，磁盘不能回收的问题解决。

此处之所以进行多次对表的备份和恢复，是因为我的业务不允许停机，所以只能进行单表操作。

如果你的系统的业务允许你在一段时间都停止服务的话，建议备份整个数据库，然后清空，重建/data  目录，然后恢复整个数据库。

如果最后你发现自己的没做独立表空间，那么你就只能对整个数据库做个全库备份，然后重建/data目录，然后恢复整个数据库。

20140327 今天空间又不足了，经过多次试验得出结论。

原来对于InnoDB 通过该命令还是有用的，”OPTIMIZE TABLE  my_table1“。

使用方式如下：

1.首先查看可以释放空间的表格有哪些，其中data_free项有值的表是可以释放空间的。

#select table_name,data_free,engine from information_schema.tables where table_schema='house_radar';

+----------------------------+-------------+--------+
| table_name                 | data_free   | engine |
+----------------------------+-------------+--------+
| xixi_test2                 | 10108272640 | InnoDB |
| xixi_test1                 | 12522094592 | InnoDB |
| haha_back             |    17825792    | InnoDB |
| haha_list                 |           0 | InnoDB |
| haha_list_2            |           0 | InnoDB |
| haha_rest               |           0 | InnoDB |
| haha_type_list       |           0 | InnoDB |
| wawa_list               |           0 | InnoDB |
| borker_haha          |           0 | InnoDB |
| broker_data           |           0 | MyISAM |
| jiajia_test2             | 18768461824 | InnoDB |
| jiajia_test1             |     6291456 | InnoDB |

此处data_free 字段显示的单位是字节，如  xixi_test2,10108272640/1024/1024/1024=9.4G,所以理论上释放后是可以恢复9.4G的空间的。

2.查看下单个表的data_free大小

#show table status like 'xixi_test2';

3.执行该命令，执行后查看磁盘空间，发现下空间果然变小了。 

#OPTIMIZE TABLE xixi_test2;

其次，该命令执行完毕后，返回命令,虽然提示不支持，optimize，但是已经进行重建和分析，空间已经回收。

+-------------+----------+----------+-------------------------------------------------------------------+
| Table        | Op        | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| xixi_test2 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| xixi_test2 | optimize | status  | OK                                                               |
+-------------+----------+----------+-------------------------------------------------------------------+

执行前后：

/dev/sdb1               503960    455150     23212  96% /home/mysql
/dev/sdb1               503960    452495     25866  95% /home/mysql

注意：该操作执行的时候会把该表格先写入一个tmp临时表，所以如果你要释放的那个表格是10G，那么你的磁盘必须还有10G的空余空间，否则会执行失败。