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掉。这样省些空间和时间