查看mysql的ibdata1_mysql 里的 ibdata1 文件

为何 mysql 里的 ibdata1 文件不断的增加？

php

ibdata1 filemysql

咱们在 Percona 支持栏目常常收到关于 MySQL 的 ibdata1 文件的这个问题。linux

当监控服务器发送一个关于 MySQL 服务器存储的报警时，恐慌就开始了 —— 就是说磁盘快要满了。ios

一番调查后你意识到大多数地盘空间被 InnoDB 的共享表空间 ibdata1 使用。而你已经启用了 innodb_file_per_table，因此问题是：git

ibdata1存了什么？

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

数据字典，也就是 InnoDB 表的元数据

变动缓冲区

双写缓冲区

撤销日志

其中的一些在 Percona 服务器上能够被配置来避免增加过大的。例如你能够经过 innodb_ibuf_max_size 设置最大变动缓冲区，或设置 innodb_doublewrite_file 来将双写缓冲区存储到一个分离的文件。web

MySQL 5.6 版中你也能够建立外部的撤销表空间，因此它们能够放到本身的文件来替代存储到 ibdata1。能够看看这个文档。sql

什么引发 ibdata1 增加迅速？

当 MySQL 出现问题一般咱们须要执行的第一个命令是：数据库

SHOW ENGINE INNODB STATUS/G

这将展现给咱们一些颇有价值的信息。咱们从** TRANSACTION(事务)**部分开始检查，而后咱们会发现这个：

---TRANSACTION36E,ACTIVE1256288sec

MySQLthreadid42,OS thread handle0x7f8baaccc700,queryid7900290localhost root

show engine innodb status

Trxread view willnotsee trxwithid>=36F,sees<36F

这是一个最多见的缘由，一个14天前建立的至关老的事务。这个状态是活动的，这意味着 InnoDB 已经建立了一个数据的快照，因此须要在撤销日志中维护旧页面，以保障数据库的一致性视图，直到事务开始。若是你的数据库有大量的写入任务，那就意味着存储了大量的撤销页。

若是你找不到任何长时间运行的事务，你也能够监控INNODB STATUS 中的其余的变量，“History list length(历史记录列表长度)”展现了一些等待清除操做。这种状况下问题常常发生，由于清除线程(或者老版本的主线程)不能像这些记录进来的速度同样快地处理撤销。

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

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

它至关易于使用：

#./innochecksum/var/lib/mysql/ibdata1

0bad checksum

13FIL_PAGE_INDEX

19272FIL_PAGE_UNDO_LOG

230FIL_PAGE_INODE

1FIL_PAGE_IBUF_FREE_LIST

892FIL_PAGE_TYPE_ALLOCATED

2FIL_PAGE_IBUF_BITMAP

195FIL_PAGE_TYPE_SYS

1FIL_PAGE_TYPE_TRX_SYS

1FIL_PAGE_TYPE_FSP_HDR

1FIL_PAGE_TYPE_XDES

0FIL_PAGE_TYPE_BLOB

0FIL_PAGE_TYPE_ZBLOB

0other

3max index_id

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

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

#innodb_space-f/var/lib/mysql/ibdata1 space-summary|grepUNDO_LOG|wc-l

19272

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

好，如今咱们知道问题所在了。下一个问题：

我该怎么解决问题？

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

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

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

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

当你删除一些行，这个页被标为已删除稍后重用，可是这个空间从不会被回收。惟一的方法是使用新的 ibdata1 启动数据库。要作这个你应该须要使用 mysqldump 作一个逻辑全备份，而后中止 MySQL 并删除全部数据库、ib_logfile*、ibdata1* 文件。当你再启动 MySQL 的时候将会建立一个新的共享表空间。而后恢复逻辑备份。

总结

当 ibdata1 文件增加太快，一般是 MySQL 里长时间运行的被遗忘的事务引发的。尝试去解决问题越快越好(提交或者杀死事务)，由于不通过痛苦缓慢的 mysqldump 过程，你就不能回收浪费的磁盘空间。

也是很是推荐监控数据库以免这些问题。咱们的 MySQL 监控插件包括一个 Nagios 脚本，若是发现了一个太老的运行事务它能够提醒你。

MySql innodb若是是共享表空间，ibdata1文件愈来愈大，达到了30多个G，对一些没用的表进行清空：truncate table xxx;而后optimize table xxx; 没有效果由于对共享表空间不起做用。mysql ibdata1存放数据，索引等，是MYSQL的最主要的数据。若是不把数据分开存放的话，这个文件的大小很容易就上了G，甚至几十G。对于某些应用来讲，并非太合适。所以要把此文件缩小。没法自动收缩，必须数据导出，删除ibdata1，而后数据导入，比较麻烦，所以须要改成每一个表单独的文件。解决方法：数据文件单独存放(共享表空间如何改成每一个表独立的表空间文件)。步骤以下：1)备份数据库备份所有数据库，执行命令#mysqldump -q -uroot -ppassword --add-drop-table --all-databases >/home/backup/all.sql作完此步后，中止数据库服务。#service mysqld stop2)找到my.ini或my.cnf文件linux下执行# /usr/libexec/mysqld --verbose --help | grep -A 1 'Default options'Default options are read from the following files in the given order:/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnfwindows环境下能够：mysqld --verbose --help > mysqlhelp.txtnotepad mysqlhelp.txt在里面查找Default options，能够看到查找my.ini的顺序，以找到真实目录3)修改mysql配置文件打开my.ini或my.cnf文件[mysqld]下增长下面配置innodb_file_per_table=1验证配置是否生效，能够重启mysql后,执行#service mysqld restart#mysql -uroot -ppasswordmysql> show variables like '%per_table%';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_file_per_table | ON    |+-----------------------+-------+1 row in set (0.00 sec)mysql>看看innodb_file_per_table变量是否为ON4)删除原数据文件删除原来的ibdata1文件及日志文件ib_logfile*，删除/var/lib/mysql目录下的应用数据库文件夹(mysql文件夹不要删)5)还原数据库启动数据库服务从命令行进入MySQL Server还原所有数据库，执行命令#service mysqld start#mysql -uroot -pocs < /home/backup/all.sql通过以上几步后，能够看到新的ibdata1文件就只有几十M了，数据及索引都变成了针对单个表的小ibd文件了，它们在相应数据库的文件夹下面。# lltotal 295028drwx------  2 mysql mysql     36864 Apr 22 14:16 glpidrwx------  2 mysql mysql     36864 Feb 15 13:45 glpi-1-rw-rw----  1 mysql mysql  10485760 Apr 22 14:27 ibdata1-rw-rw----. 1 mysql mysql 270532608 Apr 22 14:14 ibdata1-1-rw-rw----  1 mysql mysql   5242880 Apr 22 14:27 ib_logfile0-rw-rw----. 1 mysql mysql   5242880 Apr 22 14:14 ib_logfile0_bak-rw-rw----  1 mysql mysql   5242880 Apr 22 14:28 ib_logfile1-rw-rw----. 1 mysql mysql   5242880 Apr 21 22:50 ib_logfile1_bakdrwx------  2 mysql mysql      4096 Apr 22 14:16 mrbsdrwx------  2 mysql mysql      4096 Apr 14 12:05 mrbs-1drwx------. 2 mysql mysql      4096 Apr 22 14:16 mysqlsrwxrwxrwx  1 mysql mysql         0 Apr 22 14:16 mysql.sockdrwx------  2 mysql mysql     12288 Apr 22 14:16 ocswebdrwx------  2 mysql mysql     12288 Nov 16  2011 ocsweb-1# ll mrbstotal 808-rw-rw---- 1 mysql mysql     61 Apr 22 14:16 db.opt-rw-rw---- 1 mysql mysql  10492 Apr 22 14:16 mrbs_area.frm-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_area.ibd-rw-rw---- 1 mysql mysql   9264 Apr 22 14:16 mrbs_entry.frm-rw-rw---- 1 mysql mysql 131072 Apr 22 14:16 mrbs_entry.ibd-rw-rw---- 1 mysql mysql   9442 Apr 22 14:16 mrbs_repeat.frm-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_repeat.ibd-rw-rw---- 1 mysql mysql   8888 Apr 22 14:16 mrbs_room.frm-rw-rw---- 1 mysql mysql 114688 Apr 22 14:16 mrbs_room.ibd-rw-rw---- 1 mysql mysql   8688 Apr 22 14:16 mrbs_users.frm-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_users.ibd-rw-rw---- 1 mysql mysql   8658 Apr 22 14:16 mrbs_variables.frm-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_variables.ibd-rw-rw---- 1 mysql mysql   8738 Apr 22 14:16 mrbs_zoneinfo.frm-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_zoneinfo.ibd# ll mrbs-1total 88-rw-rw---- 1 mysql mysql    61 Apr 14 12:05 db.opt-rw-rw---- 1 mysql mysql 10492 Apr 14 12:05 mrbs_area.frm-rw-rw---- 1 mysql mysql  9264 Apr 14 12:05 mrbs_entry.frm-rw-rw---- 1 mysql mysql  9442 Apr 14 12:05 mrbs_repeat.frm-rw-rw---- 1 mysql mysql  8888 Apr 14 12:05 mrbs_room.frm-rw-rw---- 1 mysql mysql  8688 Apr 14 12:05 mrbs_users.frm-rw-rw---- 1 mysql mysql  8658 Apr 14 12:05 mrbs_variables.frm-rw-rw---- 1 mysql mysql  8738 Apr 14 12:05 mrbs_zoneinfo.frm