生产环境如何缩小MySQL所占磁盘空间？

今天突然收到监控传来的报警，线上有台服务器硬盘空间不足，上去查看，发现硬盘已使用98%。由于该台服务器仅运行了MySQL应用，所以首先怀疑MySQL数据过多，导致硬盘爆满，查询了一下MySQL所占用空间，果然如此。
但是当进入数据库，发现数据库中的数据根本称不上多，确切的说，简直可以用少来形容，只有区区是十几万条数据，这时觉得应该不是本身数据大小导致的，而是其他因素导致的。

进入到数据库存储目录，发现空间几乎都被ibd文件占用，这时就牵扯到本文的主角了，ibd文件，那么ibd文件到底是什么呢？

https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html,MySQL官方文档有详细介绍，但是相信大家也没有时间仔细阅读，这里就简单的介绍一下：
表文件表空间是一个单表表空间，该表创建于自己的数据文件中。当innodb_file_per_table选项开启时，表将被创建于表文件表空间中。保存单个InnoDB表数据的每表文件表空间由.ibd数据文件表示。从历史上看，所有InnoDB表和索引都存储在系统表空间中。这种单片方法的目标是完全专用于数据库处理的机器，精心规划的数据增长，其中任何分配给MySQL的磁盘存储都不会用于其他目的。InnoDB的每表文件表空间功能提供了更灵活的替代方案，其中每个InnoDB表及其索引都存储在单独的.ibd数据文件中。每个这样的.ibd数据文件代表一个单独的表空间。此功能由innodb_file_per_table配置选项控制，该选项在MySQL 5.6.6及更高版本中默认启用。
解决思路
既然知道是由于表文件表空间导致的，那么我们先看一下有多少空间是浪费的。查看前后效果可以使用show table status命令，例如show table status from [database] like '[table_name]'，返回结果中的data_free即为空洞所占据的存储空间。也可以使用如下命令查看浪费的空间大小。
    MYSQL_USER=root
    MYSQL_PASS=rootpass
    MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
    DB=databasename
    TB=tablename
    SQL="SELECT data_length+index_length FROM information_schema.tables"
    SQL="${SQL} WHERE table_schema='${DB}' AND table_name='${TB}'"
    TBLSIZE_OPER=`ls -l var/lib/mysql/${DB}/${TB}.ibd` 
    TBLSIZE_INFO=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
    (( TBLSIZE_FRAG = TBLSIZE_OPER - TBLSIZE_INFO ))
    (( TBLSIZE_FRAG_GB = TBLSIZE_FRAG / 1073741824 ))
    echo ${TBLSIZE_FRAG} ${TBLSIZE_FRAG_GB}
    ‍执行过程如下，记得要替换自己的数据库账户和密码哦

    到最后得到的结果是traffic_log表有28G空间浪费。
    终于开始到正题了，如何缩小MySQL占用磁盘空间呢，答案就是下面的命令，执行如下命令，然后在查看硬盘空间，发现瞬间释放28G空间，同样的方法操作其它表即可。
      USE databasename
      ALTER TABLE tablename ENGINE=InnoDB;

      对了有时执行以上命令会引发Duplicate entry ... for key ...错误，可以改进使用以下命令
        OPTIMIZE TABLE `databasename`.`tablename`;
        在对数据量小的表操作时，上面的命令执行较快，但是对一张有海量数据的表进行optimze table操作时，不但执行时间会很长，而且会锁表，导致无法写入。这个时候就应该考虑使用一些运维手段避免现服务受到影响。

        免责声明：

        1、本站资源由自动抓取工具收集整理于网络。

        2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

        3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

        4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

        5、如果您喜欢本资源，请您支持作者，购买正版内容。

        6、资源失效，请下方留言，欢迎分享资源链接

        文章评论

        共0条评论