Mysql必读MySQL的InnoDB扩容及ibdata1文件瘦身方案完全解析

概述介绍《Mysql必读MySQL的InnoDB扩容及ibdata1文件瘦身方案完全解析》开发教程，希望对您有用。

《MysqL必读MysqL的InnoDB扩容及ibdata1文件瘦身方案完全解析》要点：
本文介绍了MysqL必读MysqL的InnoDB扩容及ibdata1文件瘦身方案完全解析，希望对您有用。如果有疑问，可以联系我们。

MysqL教程MysqL的innodb扩容
为了添加一个数据文件到表空间中,首先要关闭 MysqL 数据库,编辑 my.cnf 文件,确认innodb ibdata文件的实际情况和my.cnf的配置是否一致,这里有两种情况：
1.my.cnf的配置

MysqL教程innodb_data_file_path=ibdata1:10G;ibdata2:10G:autoextend 

MysqL教程如果当前数据库正在使用ibdata1,或者使用ibdata2,但ibdata2没有超过10G,则对my.cnf配置直接改成：

MysqL教程innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G:autoextend 

MysqL教程2.如果设置了最后一个ibdata自动扩展时,有可能最后一个ibdata的占用空间大于my.cnf的配置空间.例如：

MysqL教程 MysqL@test:/data1/MysqLdata/innodb/data> ls -lh 

MysqL教程-rw-rw---- 1 MysqL MysqL 10737418240 2010-01-26 16:34 ibdata1 -rw-rw---- 1 MysqL MysqL 16106127360 2010-01-26 16:34 ibdata2 

MysqL教程这时,需要精确的计算ibdata2的大小 15360M,修改：

MysqL教程innodb_data_file_path=ibdata1:10G;ibdata2:15360M;ibdata3:10G:autoextend 

MysqL教程重启MysqL.
注意：
1、扩容前注意磁盘空间是否足够.
2、restart后关注是否生成了新的ibdata.
更多说明：
如果,最后一个文件以关键字 autoextend 来描述,那么编辑 my.cnf 的过程中,必须检查最后一个文件的尺寸,并使它向下接近于 1024 * 1024 bytes (= 1 MB) 的倍数(比方说现在autoextend 的/ibdata/ibdata1为18.5M,而在旧的my.ini中为10M,则需要修改为innodb_data_file_path = /ibdata/ibdata1:19M; 且必须是19M,如果指定20M,就会报错.),并在 innodb_data_file_path 中明确指定它的尺寸.然后你可以添加另一个数据文件.记住只有 innodb_data_file_path 中的最后一个文件可以被指定为 auto-extending.
一个例子：假设起先仅仅只有一个 auto-extending 数据文件 ibdata1,这个文件接近于 988 MB.下面是添加了另一个 auto-extending 数据文件后的可能示例 .

MysqL教程innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 

MysqL教程ibdata1 瘦身

MysqL教程0. ibdata1里存了什么

MysqL教程当你启用了 innodb_file_per_table,表被存储在他们自己的表空间里,但是共享表空间仍然在存储其它的 InnoDB 内部数据：
(1)数据字典,也就是 InnoDB 表的元数据
(2)变更缓冲区
(3)双写缓冲区
(4)撤销日志

MysqL教程其中的一些在 Percona 服务器上可以被配置来避免增长过大的.例如你可以通过 innodb_ibuf_max_size 设置最大变更缓冲区,或设置 innodb_doublewrite_file 来将双写缓冲区存储到一个分离的文件.

MysqL教程MysqL 5.6 版中你也可以创建外部的撤销表空间,所以它们可以放到自己的文件来替代存储到 ibdata1.

MysqL教程1. 什么引起 ibdata1 增长迅速?

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

MysqL教程SHOW ENGINE INNODB STATUS/G

MysqL教程这将展示给我们一些很有价值的信息.我们从** TRANSACTION(事务)**部分开始检查,然后我们会发现这个：

MysqL教程---TRANSACTION 36E,ACTIVE 1256288 secMysqL thread ID 42,OS thread handle 0x7f8baaccc700,query ID 7900290 localhost rootshow engine innodb statusTrx read vIEw will not see trx with ID >= 36F,sees < 36F

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

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

MysqL教程2. 我怎么检查什么被存储到了 ibdata1 里了?

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

MysqL教程它相当易于使用：

MysqL教程# ./innochecksum /var/lib/MysqL/ibdata10 bad checksum13 FIL_PAGE_INDEX19272 FIL_PAGE_UNDO_LOG230 FIL_PAGE_INODE1 FIL_PAGE_IBUF_FREE_List892 FIL_PAGE_TYPE_ALLOCATED2 FIL_PAGE_IBUF_BITMAP195 FIL_PAGE_TYPE_SYS1 FIL_PAGE_TYPE_TRX_SYS1 FIL_PAGE_TYPE_FSP_HDR1 FIL_PAGE_TYPE_XDES0 FIL_PAGE_TYPE_BLOB0 FIL_PAGE_TYPE_ZBLOB0 other3 max index_ID

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

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

MysqL教程# innodb_space -f /var/lib/MysqL/ibdata1 space-summary | grep UNDO_LOG | wc -l19272

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

MysqL教程好,现在我们知道问题所在了.

MysqL教程3. ibdata1 瘦身方案
其中的一些在 Percona 服务器上可以被配置来避免增长过大的.例如你可以通过 innodb_ibuf_max_size 设置最大变更缓冲区,所以它们可以放到自己的文件来替代存储到 ibdata1.

MysqL教程通常不能移除 InnoDB 的数据文件.为了减小数据文件的大小,你必须使用 MysqLdump 来转储(dump)所有的数据表,再重新建立一个新的数据库,并将数据导入新的数据库中.具体步骤如下：
(1)备份数据库
MysqLdump -uroot -p123456 --default-character-set=utf8 --opt --extended-insert=true --triggers -R --hex-blob --single-transaction --no-autocommit  test > db_name.sql 
(2)停止数据库

MysqL教程service MysqLd stop 

MysqL教程(3)删除相关文件

MysqL教程ibdata1 ib_logfile* MysqL-bin.index 

MysqL教程(4)手动删除除MysqL之外所有数据库文件夹,然后启动数据库 

MysqL教程service MysqLd start 

MysqL教程(5)还原数据

MysqL教程 /usr/local/MysqL/bin/MysqL -uroot -phigkoo < /data/bkup/MysqLdump.sql 

MysqL教程主要是使用MysqLdump时的一些参数,建议在使用前看一个说明再 *** 作.另外备份前可以先查看一下当前数据库里哪些表占用空间大,把一些不必要的给truncate table掉.这样省些空间和时间

总结

以上是内存溢出为你收集整理的Mysql必读MySQL的InnoDB扩容及ibdata1文件瘦身方案完全解析全部内容，希望文章能够帮你解决Mysql必读MySQL的InnoDB扩容及ibdata1文件瘦身方案完全解析所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。