详解MySQL表空间以及ibdata1文件过大问题
详解MySQL表空间以及ibdata1文件过大问题
ibdata1文件过大
原因分析
ibdata1是一个用来构建innodb系统表空间的文件，关于系统表空间详细介绍参考MySQL官网文档
上面是一个数据库的ibdata1文件，达到了780多G，而且还在不断增长。
这个文件包含了innodb数据字典、修改buffer和双写buffer、撤销日志，还包含在用户在系统表空间创建的表信息和索引数据。
显然，由于所有表的数据索引和缓存都存在这个文件中，随着数据库的不断增大，这个文件肯定会越来越大的。
解决方法
和系统表空间（也称作共享表空间）对应，MySQL提供了另外一种存储文件的方式：独立表空间。
独立表空间模式下，每个innodb表都有自己独立的表空间文件（.ibd文件），存储各种表的索引和数据。
通过配置项：innodb_file_per_table指定MySQL使用独立表空间，MySQL5.6.6以后的版本默认值是ON。MySQL5.6.5以前的版本默认值是OFF。
解决ibdata1文件过大具体操作步骤
如果当前MySQL使用系统表空间的模式，是无法在开启数据库的情况下进行切换到共享表空间的。必须关闭MySQL重建数据结构。步骤如下：
备份数据库
使用mysqldump备份所有InnoDB数据表，包括MySQL的系统表。
使用下面的命令可以参考当前系统表：
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
MySQL5.6中包含五张系统表：
- innodb_index_stats
- innodb_table_stats
- slave_master_info
- slave_relay_log_info
- slave_worker_info
如果数据库只用了InnoDB表，可以直接备份所有数据库。
mysqldump -h localhost -uroot -pxxxxx –all-databases > dump.sql
关闭MySQL服务
使用mysqld_safe关闭MySQL服务。
mysqladmin -uroot -pxxxxx shutdown
删除现有数据库文件
首先需要删除当前存在的所有表空间文件（.idb），包括ibdata1和ib_log文件，以及数据库自带的.idb文件。
然后需要删除所有的表结构描述文件：*.frm。位于数据库名称相应的文件夹下。
修改配置文件
在数据库配置文件my.cnf中的[mysqld]下添加innodb_file_per_table=1配置。
[mysqld]
innodb_file_per_table=1
重新启动服务器并导入数据
最后使用新的配置文件重新启动mysql服务。
mysqld_safe --defaults-file=/your/config/path/my.cnf &
然后导入mysaldump备份的数据
# 登录进入mysql
mysql -hlocalhost -uroot -pxxxxx database_name
# 导入数据
source /your/backup/file/path/dump.sql
独立表空间的优点
刚开始建立数据库时，就推荐使用独立表空间，MySQL5.6.6以后的版本默认是独立表空间。
使用独立表空间很显然能够提高存储效率，拆分表和表之间的耦合，将对数据库的操作粒度降低到表级别。
独立表空间对于存储优化，迁移，备份，恢复和监控来说，都更加灵活和强大。下面列举一些代表性的好处：
- truncate和drop表时会释放掉磁盘空间，共享表空间并不会释放而是在ibdata1中开辟新的空间
- truncate table时速度更快
- 可以将表放在不同的磁盘上（用于I/O优化等），共享表空间必须所有表都反正ibdata1中
- 可以对每个表使用
OPTIMIZE TABLE命令进行优化和重建，回收未使用的空间 - 可以移动单个表，或者将单个表从一个实例复制到另外一个实例
- 使用
Barracuda文件格式，至此压缩和动态行等功能 - 使用动态行(dynamic row format）可以使得存储大型BLOB和TEXT格式数据更高效
- 当文件损坏时，提高成功恢复机会，节省服务器重启或备份的时间
当然独立表空间也有一些潜在的缺点
- 由于每个表都存在为使用的空间，这些空间只能同一个表使用，可能会造成空间浪费
- fsync操作必须在每个打开的表上运行
- mysqld必须为每个表保留一个打开的文件句柄，如果表过多，可能会影响性能
- 在删除表空间的文件时会扫描缓冲池，如果缓冲池达到几十G，则需要几秒的时间，而扫描会造成锁，可能会延迟其他操作
- 如果许多表正在增长，可能会存在更多的碎片，这回妨碍删除表和扫描表的性能。
