mysql数据库文件瘦身_mysql数据库ibdata1文件瘦身

遇到InnoDB的共享表空间文件ibdata1文件大小暴增时，应该如何处理？

1、问题背景

用MySQL/InnoDB的童鞋可能也会有过烦恼，不知道为什么原因，ibdata1文件莫名其妙的增大，不知道该如何让它缩回去，就跟30岁之后男人的肚腩一样，汗啊，可喜可贺的是我的肚腩还没长出来，hoho~

正式开始之前，我们要先知道ibdata1文件是干什么用的。

ibdata1文件是InnoDB存储引擎的共享表空间文件，该文件中主要存储着下面这些数据：

data dictionary

double write buffer

insert buffer/change buffer

rollback segments

undo space

Foreign key constraint system tables

另外，当选项 innodb_file_per_table = 0 时，在ibdata1文件中还需要存储 InnoDB 表数据&索引。ibdata1文件从5.6.7版本开始，默认大小是12MB，而在这之前默认大小是10MB，其相关选项是innodb_data_file_path，比如我一般是这么设置的：

innodb_data_file_path = ibdata1:1G:autoextend

当然了，无论是否启用了 innodb_file_per_table = 1，ibdata1文件都必须存在，因为它必须存储上述 InnoDB 引擎所依赖&必须的数据，尤其是上面加粗标识的 rollback segments 和 undo space，它俩是引起 ibdata1 文件大小增加的最大原因，我们下面会详细说。

2、原因分析

我们知道，InnoDB是支持MVCC的，它和Oracle类似，采用 undo log、redo log来实现MVCC特性的。在事务中对一行数据进行修改时，InnoDB 会把这行数据的旧版本数据存储一份在undo log中，如果这时候有另一个事务又要修改这行数据，就又会把该事物最新可见的数据版本存储一份在undo log中，以此类推，如果该数据当前有N个事务要对其进行修改，就需要存储N份历史版本(和ORACLE略有不同的是，InnoDB的undo log不完全是物理block，主要是逻辑日志，这个可以查看 InnoDB 源码或其他相关资料)。这些 undo log 需要等待该事务结束后，并再次根据事务隔离级别所决定的对其他事务而言的可见性进行判断，确认是否可以将这些 undo log 删除掉，这个工作称为 purge(purge 工作不仅仅是删除过期不用的 undo log，还有其他，以后有机会再说)。

那么问题来了，如果当前有个事务中需要读取到大量数据的历史版本，而该事务因为某些原因无法今早提交或回滚，而该事务发起之后又有大量事务需要对这些数据进行修改，这些新事务产生的 undo log 就一直无法被删除掉，形成了堆积，这就是导致 ibdata1 文件大小增大最主要的原因之一。这种情况最经典的场景就是大量数据备份，因此我们建议把备份工作放在专用的 slave server 上，不要放在 master server 上。

另一种情况是，InnoDB的 purge 工作因为本次 file i/o 性能是在太差或其他的原因，一直无法及时把可以删除的 undo log 进行purge 从而形成堆积，这是导致 ibdata1 文件大小增大另一个最主要的原因。这种场景发生在服务器硬件配置比较弱，没有及时跟上业务发展而升级的情况。

比较少见的一种是在早期运行在32位系统的MySQL版本中存在bug，当发现待 purge 的 undo log 总量超过某个值时，purge 线程直接放弃抵抗，再也不进行 purge 了，这个问题在我们早期使用32位MySQL 5.0版本时遇到的比较多，我们曾经遇到这个文件涨到100多G的情况。后来我们费了很大功夫把这些实例都迁移到64位系统下，终于解决了这个问题。

最后一个是，选项 innodb_data_file_path 值一开始就没调整或者设置很小，这就必不可免导致 ibdata1 文件增大了。Percona官方提供的 my.cnf 参考文件中也一直没把这个值加大，让我百思不得其解，难道是为了像那个经常被我吐槽的xx那样，故意留个暗门，好方便后续帮客户进行优化吗？(我心理太阴暗了，不好不好~~)

稍微总结下，导致ibdata1文件大小暴涨的原因有下面几个：

有大量并发事务，产生大量的undo log；

有旧事务长时间未提交，产生大量旧undo log；

file i/o性能差，purge进度慢；

初始化设置太小不够用；

32-bit系统下有bug。

稍微题外话补充下，另一个热门数据库 PostgreSQL 的做法是把各个历史版本的数据 和 原数据表空间 存储在一起，所以不存在本案例的问题，也因此 PostgreSQL 的事务回滚会非常快，并且还需要定期做 vaccum 工作(具体可参见PostgreSQL的MVCC实现机制，我可能说的不是完全正确哈)

3、解决方法建议

看到上面的这些问题原因描述，有些同学可能觉得这个好办啊，对 ibdata1 文件大小进行收缩，回收表空间不就结了吗。悲剧的是，截止目前，InnoDB 还没有办法对 ibdata1 文件表空间进行回收/收缩，一旦 ibdata1 文件的肚子被搞大了，只能把数据先备份后恢复再次重新初始化实例才能恢复原先的大小，或者把依次把各个独立表空间文件备份恢复到一个新实例中，具体做法如下。

1、备份整个mysql数据库：

mysqldump -uroot -p123 --extended-insert --all-databases --add-drop-database --disable-keys --flush-privileges --routines --events > all-databases.sql

2、备份完以后停止数据库服务：

Service mysqld stop

3、修改数据库配置：/etc/mysql/my.cnf

[mysqld]下增加下面配置

innodb_file_per_table=1

验证配置是否生效：重启mysql服务，查看innodb_file_per_table值是否为ON。

#service mysqld restart

#mysql -uroot -ppassword

mysql> show variables like '%per_table%';

4、变量为ON之后，再停止数据库服务，然后删除原来的数据文件：数据库文件一般存放路径/var/lib/mysql，删除原来的ibdata1文件、日志文件ib_logfile*和原来的应用数据库文件夹(mysql文件夹不要删)。

5、删完之后启动数据库服务，从之前备份中还原数据库。

service mysqld start

mysql -uroot -p123 < all-databases.sql

还原以后发现/var/lib/mysql下，新的ibdata1文件就只有几十M了，数据及索引都变成了针对单个表的小ibd文件，分别在对应的数据库文件夹下面。

当然了，这个问题也并不是不能防范，根据上面提到的原因，相应的建议对策是：

升级到5.6及以上(64-bit)，采用独立undo表空间，5.6版本开始就支持独立的undo表空间了，再也不用担心会把 ibdata1 文件搞大；

初始化设置时，把 ibdata1 文件至少设置为1GB以上；

增加purge线程数，比如设置 innodb_purge_threads = 8；

提高file i/o能力，该上SSD的赶紧上；

事务及时提交，不要积压；

默认打开autocommit = 1，避免忘了某个事务长时间未提交；

检查开发框架，确认是否设置了 autocommit=0，记得在事务结束后都有显式提交或回滚。