转 mysql里的ibdata1文件

##感谢  mikeguan

 

https://www.cnblogs.com/mikeguan/p/8018531.html

 

mysql里的ibdata1文件

 

 我们对数据库很多知识都不太了解，而像这种恢复数据的工作也没有做过，因此首先百度了一下.frm和.ibd文件是做什么的，见下：

  -> MySQL中.frm文件：保存了每个表的元数据，包括表结构的定义等，该文件与数据库引擎无关。

_>  MySQL中.ibd文件：InnoDB引擎开启了独立表空间(my.ini中配置innodb_file_per_table = 1)产生的存放该表的数据和索引的文件。

 

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（事务）**部分开始检查，然后我们会发现这个：

---TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx read view will not see trx with id >= 36F, sees < 36F

这是一个最常见的原因，一个14天前创建的相当老的事务。这个状态是活动的，这意味着 InnoDB 已经创建了一个数据的快照，所以需要在撤销日志中维护旧页面，以保障数据库的一致性视图，直到事务开始。如果你的数据库有大量的写入任务，那就意味着存储了大量的撤销页。

如果你找不到任何长时间运行的事务，你也可以监控INNODB STATUS 中的其他的变量，“History list length（历史记录列表长度）”展示了一些等待清除操作。这种情况下问题经常发生，因为清除线程（或者老版本的主线程）不能像这些记录进来的速度一样快地处理撤销。

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

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

它相当易于使用：

# ./innochecksum /var/lib/mysql/ibdata1
0 bad checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG
230 FIL_PAGE_INODE
1 FIL_PAGE_IBUF_FREE_LIST
892 FIL_PAGE_TYPE_ALLOCATED
2 FIL_PAGE_IBUF_BITMAP
195 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
1 FIL_PAGE_TYPE_FSP_HDR
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
3 max index_id

全部的 20608 中有 19272 个撤销日志页。这占用了表空间的 93%。

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

# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l
19272

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

好，现在我们知道问题所在了。下一个问题：

我该怎么解决问题？

这个问题的答案很简单。如果你还能提交语句，就做吧。如果不能的话，你必须要杀掉线程开始回滚过程。那将停止 ibdata1 的增长，但是很显然，你的软件会出现漏洞，有些人会遇到错误。现在你知道如何去鉴定问题所在，你需要使用你自己的调试工具或普通的查询日志来找出谁或者什么引起的问题。

如果问题发生在清除线程，解决方法通常是升级到新版本，新版中使用一个独立的清除线程替代主线程。更多信息查看该文档

有什么方法回收已使用的空间么？

没有，目前还没有一个容易并且快速的方法。InnoDB 表空间从不收缩...参见10 年之久的漏洞报告，最新更新自詹姆斯·戴（谢谢）：

当你删除一些行，这个页被标为已删除稍后重用，但是这个空间从不会被回收。唯一的方法是使用新的 ibdata1 启动数据库。要做这个你应该需要使用 mysqldump 做一个逻辑全备份，然后停止 MySQL 并删除所有数据库、ib_logfile、ibdata1 文件。当你再启动 MySQL 的时候将会创建一个新的共享表空间。然后恢复逻辑备份。

总结

当 ibdata1 文件增长太快，通常是 MySQL 里长时间运行的被遗忘的事务引起的。尝试去解决问题越快越好（提交或者杀死事务），因为不经过痛苦缓慢的 mysqldump 过程，你就不能回收浪费的磁盘空间。

也是非常推荐监控数据库以避免这些问题。我们的 MySQL 监控插件包括一个 Nagios 脚本，如果发现了一个太老的运行事务它可以提醒你。

 

 

###

感谢hi_study

https://blog.csdn.net/hi__study/article/details/53489672

一次恢复数据库表结构和数据的实战，仅以此贴作为记录。

  由于，前几天我们使用的数据库被入侵(顺便鄙视一下安全管理人员)，数据库中的表都显示不存在(仅剩一个黑客自建的----qq_xxxxx的表，但物理文件都在，例如：.frm、.ibd文件等)。因此，产生了这次恢复表结构和表数据的实战。以下开始记述：

  我们对数据库很多知识都不太了解，而像这种恢复数据的工作也没有做过，因此首先百度了一下.frm和.ibd文件是做什么的，见下：

  -> MySQL中.frm文件：保存了每个表的元数据，包括表结构的定义等，该文件与数据库引擎无关。

_>  MySQL中.ibd文件：InnoDB引擎开启了独立表空间(my.ini中配置innodb_file_per_table = 1)产生的存放该表的数据和索引的文件。

 

  接着，百度了一下如何用.frm和.ibd文件恢复数据库的方法。

  1、首先，恢复数据库表结构，以我本地数据库中t_user表为例，其结构如下：

CREATE TABLE `t_user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `create_date` DATETIME NULL DEFAULT NULL,
    `email` VARCHAR(255) NULL DEFAULT NULL,
    `nickname` VARCHAR(255) NULL DEFAULT NULL,
    `password` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(255) NULL DEFAULT NULL,
    `status` INT(11) NOT NULL,
    `username` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2;

  ①、创建一个新数据库，然后创建一个和需要恢复的表的引擎相同的同名表(即引擎为InnoDB的t_user表，其结构无所谓，可以只有一个字段)，如下：

 

 

  以上，我在新建的mytest数据库中创建了一个引擎为InnoDB的只有一个字段a的t_user表。

  ②、使用net stop mysql关闭数据库服务，然后用需要恢复的t_user.frm覆盖这个新建的mytest数据库的t_user表的物理文件t_user.frm，接着对配置文件(my.ini)设置innodb_force_recovery = 6，然后使用net start mysql启动服务，并在控制台进入mytest库中，使用desc t_user查看该表结构，如下：表不存在

 

  ③，此时，我们查看../mysql/data下的log文件，可以看到如下：

 

  以上，应该是说mytest下t_user只定义了一列，但MySQL中有8列。

  ④，使用net stop mysql停掉服务，然后在配置文件my.ini中将innodb_force_recovery = 6注释掉(#或;均可)，再net start mysql启动服务。接着，将mytest下t_user表删掉(drop掉)，再新建一个8列字段的t_user表，如下：字段随意

 

 

  ⑤、继续重复②中的操作，使用desc t_user即可获取到t_user的表结构(使用mysqldump导出即可)，如下：

 

 

  2、用t_user.ibd文件恢复t_user表的数据

  ①、停掉数据库服务，将innodb_force_recovery = 6注释掉，然后启动数据库服务(若启动中出现1067错误，可以尝试删除data文件夹下ibdata1、ib_logfile0、ib_logfile1即可)并删除mytest下t_user表，并使用新导出的表结构创建t_user表，如下：

 

 

  ②、由于.ibd文件含有tablespace id，因此我们将需要恢复数据的t_user.ibd的tablespace id改为新创建的t_user.ibd的tablespace id，如下：使用了winHex

需要恢复数据的t_user.id的tablespace id：

 

新创建的t_user.ibd的tablespace id：

 

修改后的带数据的t_user.ibd如下：

 

  ③、停掉数据库服务，使用修改tablespace id的t_user.ibd文件覆盖mytest库下的t_user.ibd文件，并且将配置文件中innodb_force_recovery = 6去掉注释，进入mytest库内，使用select * from t_user查看数据，获取到数据如下：

 

④使用mysqldump导出表结构和数据即可。

 

  至此，一次使用.frm和.ibd文件恢复数据库数据的演示结束。

 

注意：最好重新配置一个空的MySQL来进行恢复操作，这样不会影响到MySQL下别的数据库，本例中使用的是MySQL 5.6.24版。

————————————————
版权声明：本文为CSDN博主「hi__study」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/hi__study/java/article/details/53489672