Mysql必读MySQL的InnoDB扩容及ibdata1文件瘦身方案完全解析
|
《Mysql必读MySQL的InnoDB扩容及ibdata1文件瘦身方案完全解析》要点： MYSQL教程mysql的innodb扩容 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. 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 内部数据： 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 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
MYSQL教程这是一个最常见的原因,一个14天前创建的相当老的事务.这个状态是活动的,这意味着 InnoDB 已经创建了一个数据的快照,所以需要在撤销日志中维护旧页面,以保障数据库的一致性视图,直到事务开始.如果你的数据库有大量的写入任务,那就意味着存储了大量的撤销页. MYSQL教程如果你找不到任何长时间运行的事务,你也可以监控INNODB STATUS 中的其他的变量,“History list length(历史记录列表长度)”展示了一些等待清除操作.这种情况下问题经常发生,因为清除线程(或者老版本的主线程)不能像这些记录进来的速度一样快地处理撤销. MYSQL教程2. 我怎么检查什么被存储到了 ibdata1 里了? MYSQL教程很不幸,MySQL 不提供查看什么被存储到 ibdata1 共享表空间的信息,但是有两个工具将会很有帮助.第一个是马克?卡拉汉制作的一个修改版 innochecksum,它发布在这个漏洞报告里. MYSQL教程它相当易于使用： MYSQL教程
# ./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
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 -l
19272
MYSQL教程尽管这种特殊的情况下,innochedcksum 更快更容易使用,但是我推荐你使用杰里米的工具去了解更多的 InnoDB 内部的数据分布及其内部结构. MYSQL教程好,现在我们知道问题所在了. MYSQL教程3. ibdata1 瘦身方案 MYSQL教程通常不能移除 InnoDB 的数据文件.为了减小数据文件的大小,你必须使用 mysqldump 来转储(dump)所有的数据表,再重新建立一个新的数据库,并将数据导入新的数据库中.具体步骤如下： 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掉.这样省些空间和时间 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |