近期我碰到了一个令人头疼的事情。就是我的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的空余空间,否则会执行失败。