Mysql Innodb 使用独立表空间

2014-01-25 by dongnan

环境描述

版本: mysql 5.5 
引擎: innodb

问题描述

某个Mysql数据库共享表空间文件ibdata1大小已经达到了20GB，即使删除了数据但是ibdata1文件不会释放存储空间。

解决方法

为 Innodb 配置使用独立表空间，删除数据可以回收空间。

用mysqldump来转储数据库的数据，然后删除原有的数据文件，最后再导入数据。

操作步骤

导出数据

mysqldump -uroot -ppasswd database_name > backup.sql

停止服务

/ect/init.d/mysqld stop

删除相关文件

rm ibdata1  
rm ib_logfile*

配置独立表空间

# 编辑配置文件
vim my.cnf

# 找到的[mysqld]并添加参数
innodb_file_per_table=1

启动服务

/ect/init.d/mysqld start

导入数据

mysql -uroot -ppassword database_name < backup.sql

验证

登录Mysql 查看参数：

show variables like ‘%per_table%’;
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)

检查数据库

select * from database_name;

Innodb 独立表空间

优点：

每个表都有自已独立的表空间。
每个表的数据和索引都会存在自已的表空间中。
可以实现单表在不同的数据库中移动。
空间可以回收。
对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：

单表增加比共享空间方式更大。

其它：

共享表空间在Insert操作上有一些优势，但在其它都没独立表空间表现好。
当启用独立表空间时，请合理调整一下 innodb_open_files 参数。

参考

使用参数innodb_file_per_table支持MySQL InnoDB表数据共享空间自动收缩