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

mysql的innodb扩容
为了添加一个数据文件到表空间中，首先要关闭 MySQL 数据库，编辑 my.cnf 文件，确认innodb ibdata文件的实际情况和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---- 1 mysql mysql 10737418240 2010-01-26 16:34 ibdata1 
-rw-rw---- 1 mysql mysql 16106127360 2010-01-26 16:34 ibdata2 

这时，需要精确的计算ibdata2的大小 15360M，修改：

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

重启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 数据文件后的可能示例 。

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 来将双写缓冲区存储到一个分离的文件。

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

1. 什么引起 ibdata1 增长迅速？

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

SHOW ENGINE INNODB STATUS/G

这将展示给我们一些很有价值的信息。我们从** TRANSACTION（事务）**部分开始检查，然后我们会发现这个：

---TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx read view will not see trx with id >= 36F, sees < 36F

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

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

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

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

它相当易于使用：

# ./innochecksum /var/lib/mysql/ibdata1
0 bad checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG
230 FIL_PAGE_INODE
1 FIL_PAGE_IBUF_FREE_LIST
892 FIL_PAGE_TYPE_ALLOCATED
2 FIL_PAGE_IBUF_BITMAP
195 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
1 FIL_PAGE_TYPE_FSP_HDR
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
3 max index_id

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

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

# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l
19272

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

好，现在我们知道问题所在了。

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

MySQL 5.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  <br> （2）停止数据库

service mysqld stop 

（3）删除相关文件

ibdata1 
ib_logfile* 
mysql-bin.index 

（4）手动删除除Mysql之外所有数据库文件夹，然后启动数据库 

service mysqld start 

（5）还原数据

 /usr/local/mysql/bin/mysql -uroot -phigkoo < /data/bkup/mysqldump.sql 

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