为什么mysql的ibdata1文件越来越多？
php
ibdata 1文件MySQL
我们经常在Percona支持栏中收到关于MySQL ibdata 1文件的这个问题。 linux
当仲裁服务器发送有关MySQL服务器存储的警报时，——磁盘即将满。 国际操作系统
调查结果显示，大多数地面空间都被InnoDB的共享表空间ibdata1使用。 问题是git，因为您已经启用了innodb_file_per_table
ibdata1中保存了什么？
qjdjr启用InnoDB_file_per_table，表存储在自己的表空间中，但共享表空间中仍存储其他innodb内部数据： github
数据词典，即InnoDB表的元数据
可变缓冲器
双重写入缓冲区
取消日志
其中一些可以配置为在Percona服务中避免过度增加。 例如，可以通过innodb_ibuf_max_size设置最大变化缓冲区，也可以通过设置innodb_doublewrite_file将双写缓冲区保存到单独的文件中。 网络服务
在MySQL 5.6版中，还可以创建外部撤销表空间，因此可以将其放在自己的文件中，而不是存储在ibdata1中。 可以看到这个文档。 sql
ibdata1快速增长的原因是什么？
如果MySQL有问题，我们必须执行的第一个命令是数据库
SHOW ENGINE INNODB STATUS/G
这向我们展示了非常有价值的信息。 从** TRANSACTION (事务) *的部分开始检查吧。 然后找到这个。
---TRANSACTION36E，ACTIVE1256288sec
MySQLthreadid42，OS thread handle0x7f8baaccc700，queryid 7900290本地主机根
show engine innodb status
trxreadviewwillnotseetrxwithid=36f，sees36F
这是最常见的理由，是14天前成立的非常古老的事务。 此状态处于活动状态。 这意味着InnoDB已经创建了数据的快照，因此必须在撤消日志中保留旧页面，以确保数据库的一致视图，直到事务启动。 如果数据库中有大量的写入任务，则表示保存了大量的撤消页。
如果找不到要长时间运行的事务，也可以监视INNODB STATUS中的其他变量。 “历史列表长度”表示等待清除操作。 在这种情况下，由于清除线程(或旧版本的主线程)无法像处理这些记录进入的速度一样快速处理撤销，因此经常出现问题。
你是怎么检查ibdata1中保存了什么的？
遗憾的是，MySQL没有提供显示存储在ibdata1共享表空间中的内容的信息，但这两个工具非常有用。 第一个是由马克tzdty创建的修订版innochecksum，该漏洞报告中包含。
很容易使用。
#./innochecksum/var/lib/MySQL/ibdata 1
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
0以太网
3max index_id
的20608有19272个撤销日志页面。 这占表空间的93%。
第二个核对表中的空间内容是用wndpd创建的InnoDB Ruby工具。 是检查InnoDB内部结构的更高级的工具。 例如，可以使用space-summary参数获取每个页面及其数据类型的列表。 尝试使用标准的Unix工具来计数撤销日志页数。
# innodb _ space-f/var/lib/MySQL/ibdata1space-summary|grep undo _ log|WC-l
19272
在这些特殊情况下，innochedcksum更快、更容易使用，但我们建议您使用混乱的羊具了解更多InnoDB内部的数据
分布及其内部结构。好，如今咱们知道问题所在了。下一个问题：
我该怎么解决问题？
这个问题的答案很简单。若是你还能提交语句，就作吧。若是不能的话，你必需要杀掉线程开始回滚过程。那将中止 ibdata1 的增加，可是很显然，你的软件会出现漏洞，有些人会遇到错误。如今你知道如何去鉴定问题所在，你须要使用你本身的调试工具或普通的查询日志来找出谁或者什么引发的问题。
若是问题发生在清除线程，解决方法一般是升级到新版本，新版中使用一个独立的清除线程替代主线程。更多信息查看该文档
有什么方法回收已使用的空间么？
没有，目前尚未一个容易而且快速的方法。InnoDB 表空间从不收缩...参见10 年之久的漏洞报告，最新更新自dqdch(谢谢)：
qjdjr删除一些行，这个页被标为已删除稍后重用，可是这个空间从不会被回收。惟一的方法是使用新的 ibdata1 启动数据库。要作这个你应该须要使用 mysqldump 作一个逻辑全备份，而后中止 MySQL 并删除全部数据库、ib_logfile*、ibdata1* 文件。qjdjr再启动 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.txtxhdjzg查找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