详解MySQL表空间以及ibdata1文件过大问题

时间:2021-12-02 20:16:46

ibdata1文件过大

原因分析

ibdata1是一个用来构建innodb系统表空间的文件,关于系统表空间详细介绍参考MySQL官网文档
详解MySQL表空间以及ibdata1文件过大问题

上面是一个数据库的ibdata1文件,达到了780多G,而且还在不断增长。

这个文件包含了innodb数据字典、修改buffer和双写buffer、撤销日志,还包含在用户在系统表空间创建的表信息和索引数据。

显然,由于所有表的数据索引和缓存都存在这个文件中,随着数据库的不断增大,这个文件肯定会越来越大的。

解决方法

和系统表空间(也称作共享表空间)对应,MySQL提供了另外一种存储文件的方式:独立表空间。

独立表空间模式下,每个innodb表都有自己独立的表空间文件(.ibd文件),存储各种表的索引和数据。

通过配置项:innodb_file_per_table指定MySQL使用独立表空间,MySQL5.6.6以后的版本默认值是ONMySQL5.6.5以前的版本默认值是OFF

解决ibdata1文件过大具体操作步骤

如果当前MySQL使用系统表空间的模式,是无法在开启数据库的情况下进行切换到共享表空间的。必须关闭MySQL重建数据结构。步骤如下:

备份数据库

使用mysqldump备份所有InnoDB数据表,包括MySQL的系统表。

使用下面的命令可以参考当前系统表:

SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';

MySQL5.6中包含五张系统表:

  • innodb_index_stats
  • innodb_table_stats
  • slave_master_info
  • slave_relay_log_info
  • slave_worker_info

如果数据库只用了InnoDB表,可以直接备份所有数据库。

mysqldump -h localhost -uroot -pxxxxx –all-databases > dump.sql

关闭MySQL服务

使用mysqld_safe关闭MySQL服务。

mysqladmin -uroot -pxxxxx shutdown

删除现有数据库文件

首先需要删除当前存在的所有表空间文件(.idb),包括ibdata1和ib_log文件,以及数据库自带的.idb文件。

然后需要删除所有的表结构描述文件:*.frm。位于数据库名称相应的文件夹下。

修改配置文件

在数据库配置文件my.cnf中的[mysqld]下添加innodb_file_per_table=1配置。

[mysqld]
innodb_file_per_table=1

重新启动服务器并导入数据

最后使用新的配置文件重新启动mysql服务。

mysqld_safe --defaults-file=/your/config/path/my.cnf &

然后导入mysaldump备份的数据

# 登录进入mysql
mysql -hlocalhost -uroot -pxxxxx database_name

# 导入数据
source /your/backup/file/path/dump.sql

独立表空间的优点

刚开始建立数据库时,就推荐使用独立表空间,MySQL5.6.6以后的版本默认是独立表空间。

使用独立表空间很显然能够提高存储效率,拆分表和表之间的耦合,将对数据库的操作粒度降低到表级别。

独立表空间对于存储优化,迁移,备份,恢复和监控来说,都更加灵活和强大。下面列举一些代表性的好处:

  • truncate和drop表时会释放掉磁盘空间,共享表空间并不会释放而是在ibdata1中开辟新的空间
  • truncate table时速度更快
  • 可以将表放在不同的磁盘上(用于I/O优化等),共享表空间必须所有表都反正ibdata1中
  • 可以对每个表使用OPTIMIZE TABLE命令进行优化和重建,回收未使用的空间
  • 可以移动单个表,或者将单个表从一个实例复制到另外一个实例
  • 使用Barracuda文件格式,至此压缩和动态行等功能
  • 使用动态行(dynamic row format)可以使得存储大型BLOB和TEXT格式数据更高效
  • 当文件损坏时,提高成功恢复机会,节省服务器重启或备份的时间

当然独立表空间也有一些潜在的缺点:

  • 由于每个表都存在为使用的空间,这些空间只能同一个表使用,可能会造成空间浪费
  • fsync操作必须在每个打开的表上运行
  • mysqld必须为每个表保留一个打开的文件句柄,如果表过多,可能会影响性能
  • 在删除表空间的文件时会扫描缓冲池,如果缓冲池达到几十G,则需要几秒的时间,而扫描会造成锁,可能会延迟其他操作
  • 如果许多表正在增长,可能会存在更多的碎片,这回妨碍删除表和扫描表的性能。