MySQL的InnoDB扩容及ibdata1文件瘦身方案完全解析
mysql的innodb扩容
为了添加一个数据文件到表空间中,首先要关闭MySQL数据库,编辑my.cnf文件,确认innodbibdata文件的实际情况和my.cnf的配置是否一致,这里有两种情况:
1.my.cnf的配置
innodb_data_file_path=ibdata1:10G;ibdata2:10G:autoextend
如果当前数据库正在使用ibdata1,或者使用ibdata2,但ibdata2没有超过10G,则对my.cnf配置直接改成:
innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G:autoextend
2.如果设置了最后一个ibdata自动扩展时,有可能最后一个ibdata的占用空间大于my.cnf的配置空间。例如:
mysql@test:/data1/mysqldata/innodb/data>ls-lh
-rw-rw----1mysqlmysql107374182402010-01-2616:34ibdata1 -rw-rw----1mysqlmysql161061273602010-01-2616:34ibdata2
这时,需要精确的计算ibdata2的大小15360M,修改:
innodb_data_file_path=ibdata1:10G;ibdata2:15360M;ibdata3:10G:autoextend
重启mysql。
注意:
1、扩容前注意磁盘空间是否足够。
2、restart后关注是否生成了新的ibdata。
更多说明:
如果,最后一个文件以关键字autoextend来描述,那么编辑my.cnf的过程中,必须检查最后一个文件的尺寸,并使它向下接近于1024*1024bytes(=1MB)的倍数(比方说现在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,这个文件接近于988MB。下面是添加了另一个auto-extending数据文件后的可能示例。
innodb_data_home_dir= innodb_data_file_path=/ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
ibdata1瘦身
0.ibdata1里存了什么
当你启用了innodb_file_per_table,表被存储在他们自己的表空间里,但是共享表空间仍然在存储其它的InnoDB内部数据:
(1)数据字典,也就是InnoDB表的元数据
(2)变更缓冲区
(3)双写缓冲区
(4)撤销日志
其中的一些在Percona服务器上可以被配置来避免增长过大的。例如你可以通过innodb_ibuf_max_size设置最大变更缓冲区,或设置innodb_doublewrite_file来将双写缓冲区存储到一个分离的文件。
MySQL5.6版中你也可以创建外部的撤销表空间,所以它们可以放到自己的文件来替代存储到ibdata1。
1.什么引起ibdata1增长迅速?
当MySQL出现问题通常我们需要执行的第一个命令是:
SHOWENGINEINNODBSTATUS/G
这将展示给我们一些很有价值的信息。我们从**TRANSACTION(事务)**部分开始检查,然后我们会发现这个:
---TRANSACTION36E,ACTIVE1256288sec MySQLthreadid42,OSthreadhandle0x7f8baaccc700,queryid7900290localhostroot showengineinnodbstatus Trxreadviewwillnotseetrxwithid>=36F,sees<36F
这是一个最常见的原因,一个14天前创建的相当老的事务。这个状态是活动的,这意味着InnoDB已经创建了一个数据的快照,所以需要在撤销日志中维护旧页面,以保障数据库的一致性视图,直到事务开始。如果你的数据库有大量的写入任务,那就意味着存储了大量的撤销页。
如果你找不到任何长时间运行的事务,你也可以监控INNODBSTATUS中的其他的变量,“Historylistlength(历史记录列表长度)”展示了一些等待清除操作。这种情况下问题经常发生,因为清除线程(或者老版本的主线程)不能像这些记录进来的速度一样快地处理撤销。
2.我怎么检查什么被存储到了ibdata1里了?
很不幸,MySQL不提供查看什么被存储到ibdata1共享表空间的信息,但是有两个工具将会很有帮助。第一个是马克·卡拉汉制作的一个修改版innochecksum,它发布在这个漏洞报告里。
它相当易于使用:
#./innochecksum/var/lib/mysql/ibdata1 0badchecksum 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 3maxindex_id
全部的20608中有19272个撤销日志页。这占用了表空间的93%。
第二个检查表空间内容的方式是杰里米·科尔制作的InnoDBRuby工具。它是个检查InnoDB的内部结构的更先进的工具。例如我们可以使用space-summary参数来得到每个页面及其数据类型的列表。我们可以使用标准的Unix工具来统计撤销日志页的数量:
#innodb_space-f/var/lib/mysql/ibdata1space-summary|grepUNDO_LOG|wc-l 19272
尽管这种特殊的情况下,innochedcksum更快更容易使用,但是我推荐你使用杰里米的工具去了解更多的InnoDB内部的数据分布及其内部结构。
好,现在我们知道问题所在了。
3.ibdata1瘦身方案
其中的一些在Percona服务器上可以被配置来避免增长过大的。例如你可以通过innodb_ibuf_max_size设置最大变更缓冲区,或设置innodb_doublewrite_file来将双写缓冲区存储到一个分离的文件。
MySQL5.6版中你也可以创建外部的撤销表空间,所以它们可以放到自己的文件来替代存储到ibdata1。
通常不能移除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)停止数据库
servicemysqldstop
(3)删除相关文件
ibdata1 ib_logfile* mysql-bin.index
(4)手动删除除Mysql之外所有数据库文件夹,然后启动数据库
servicemysqldstart
(5)还原数据
/usr/local/mysql/bin/mysql-uroot-phigkoo</data/bkup/mysqldump.sql
主要是使用Mysqldump时的一些参数,建议在使用前看一个说明再操作。另外备份前可以先查看一下当前数据库里哪些表占用空间大,把一些不必要的给truncatetable掉。这样省些空间和时间