Mysql表空间以及ibdata1文件过大问题

原因分析

ibdata1是一个用来构建innodb系统表空间的文件

上面是一个数据库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，则需要几秒的时间，而扫描会造成锁，可能会延迟其他操作
• 如果许多表正在增长，可能会存在更多的碎片，这回妨碍删除表和扫描表的性能。

MySQL的ibdata1详解

关于 MySQL 的 ibdata1 文件的这个问题：
当监控服务器发送一个关于 MySQL 服务器存储的报警时，恐慌就开始了 —— 就是说磁盘快要满了。
一番调查后你意识到大多数地盘空间被 InnoDB 的共享表空间 ibdata1 使用。而你已经启用了innodb_file_per_table

ibdata1存了什么

当你启用了 innodb_file_per_table，表被存储在他们自己的表空间里，但是共享表空间仍然在存储其它的 InnoDB 内部数据：

• 数据字典，也就是 InnoDB 表的元数据
• 变更缓冲区
• 双写缓冲区
• 撤销日志

其中的一些在 Percona 服务器上可以被配置来避免增长过大的。例如你可以通过 innodb_ibuf_max_size 设置最大变更缓冲区，或设置 innodb_doublewrite_file 来将双写缓冲区存储到一个分离的文件。
MySQL 5.6 版中你也可以创建外部的撤销表空间，所以它们可以放到自己的文件来替代存储到 ibdata1。可以看看这个文档。

引起 ibdata1 增长迅速的原因

当 MySQL 出现问题通常我们需要执行的第一个命令是：

SHOW ENGINE INNODB STATUS/G

这将展示给我们一些很有价值的信息。我们从** TRANSACTION（事务）**部分开始检查，然后我们会发现这个：
:::tips

1. —TRANSACTION 36E, ACTIVE 1256288 sec
2. MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
3. show engine innodb status
4. Trx read view will not see trx with id >= 36F, sees < 36F
   :::
   这是一个最常见的原因，一个14天前创建的相当老的事务。这个状态是活动的，这意味着 InnoDB 已经创建了一个数据的快照，所以需要在撤销日志中维护旧页面，以保障数据库的一致性视图，直到事务开始。如果你的数据库有大量的写入任务，那就意味着存储了大量的撤销页。
   如果你找不到任何长时间运行的事务，你也可以监控INNODB STATUS 中的其他的变量，“History list length（历史记录列表长度）”展示了一些等待清除操作。这种情况下问题经常发生，因为清除线程（或者老版本的主线程）不能像这些记录进来的速度一样快地处理撤销。

我怎么检查什么被存储到了 ibdata1 里了

很不幸，MySQL 不提供查看什么被存储到 ibdata1 共享表空间的信息，但是有两个工具将会很有帮助。第一个是马克·卡拉汉制作的一个修改版 innochecksum ，它发布在这个漏洞报告里。
它相当易于使用：
:::tips

1. ./innochecksum /var/lib/mysql/ibdata1

2. 0 bad checksum

3. 13 FIL_PAGE_INDEX

4. 19272 FIL_PAGE_UNDO_LOG

5. 230 FIL_PAGE_INODE

6. 1 FIL_PAGE_IBUF_FREE_LIST

7. 892 FIL_PAGE_TYPE_ALLOCATED

8. 2 FIL_PAGE_IBUF_BITMAP

9. 195 FIL_PAGE_TYPE_SYS

10. 1 FIL_PAGE_TYPE_TRX_SYS

11. 1 FIL_PAGE_TYPE_FSP_HDR

12. 1 FIL_PAGE_TYPE_XDES

13. 0 FIL_PAGE_TYPE_BLOB

14. 0 FIL_PAGE_TYPE_ZBLOB

15. 0 other

16. 3 max index_id

17. 全部的 20608 中有 19272 个撤销日志页。这占用了表空间的 93%。
    :::
    第二个检查表空间内容的方式是杰里米·科尔制作的 InnoDB Ruby 工具。它是个检查 InnoDB 的内部结构的更先进的工具。例如我们可以使用 space-summary 参数来得到每个页面及其数据类型的列表。我们可以使用标准的 Unix 工具来统计撤销日志页的数量：
    :::tips

18. innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l

19. 19272
    :::
    尽管这种特殊的情况下，innochedcksum 更快更容易使用，但是我推荐你使用杰里米的工具去了解更多的 InnoDB 内部的数据分布及其内部结构。

解决问题的办法

这个问题的答案很简单。如果你还能提交语句，就做吧。如果不能的话，你必须要杀掉线程开始回滚过程。那将停止 ibdata1 的增长，但是很显然，你的软件会出现漏洞，有些人会遇到错误。现在你知道如何去鉴定问题所在，你需要使用你自己的调试工具或普通的查询日志来找出谁或者什么引起的问题。
如果问题发生在清除线程，解决方法通常是升级到新版本，新版中使用一个独立的清除线程替代主线程。更多信息查看该文档

回收已使用的空间的方法

没有，目前还没有一个容易并且快速的方法。InnoDB 表空间从不收缩…参见10 年之久的漏洞报告，最新更新自詹姆斯·戴（谢谢）：
当你删除一些行，这个页被标为已删除稍后重用，但是这个空间从不会被回收。唯一的方法是使用新的 ibdata1 启动数据库。要做这个你应该需要使用 mysqldump 做一个逻辑全备份，然后停止 MySQL 并删除所有数据库、ib_logfile*、ibdata1* 文件。当你再启动 MySQL 的时候将会创建一个新的共享表空间。然后恢复逻辑备份。

总结

当 ibdata1 文件增长太快，通常是 MySQL 里长时间运行的被遗忘的事务引起的。尝试去解决问题越快越好（提交或者杀死事务），因为不经过痛苦缓慢的 mysqldump 过程，你就不能回收浪费的磁盘空间。
也是非常推荐监控数据库以避免这些问题。我们的 MySQL 监控插件包括一个 Nagios 脚本，如果发现了一个太老的运行事务它可以提醒你。