如何在MySQL中缩小/清除ibdata1文件

6 浏览
0 Comments

如何在MySQL中缩小/清除ibdata1文件

我在本地主机上使用MySQL作为R语言中进行统计工作的“查询工具”，也就是每次运行一个R脚本，我都会创建一个新的数据库（A），创建一个新的表（B），将数据导入到B中，提交查询以获取所需内容，然后删除B和A。

对我来说这很好用，但我意识到ibdata文件大小在迅速增长，我没有在MySQL中存储任何东西，但ibdata1文件已经超过了100MB。

我在安装中使用了更多或更少默认的MySQL设置，是否有一种方法可以在固定时间后自动缩小/清除ibdata1文件？

admin 更改状态以发布 2023年5月21日
0

2 答案

0 Comments

补充John P的回答，

对于Linux系统，可以使用以下命令完成步骤1-6：

  1. mysqldump -u [用户名] -p[root密码] [数据库名称] > dumpfilename.sql
  2. mysqladmin -u [用户名] -p[root密码] drop [数据库名称]
  3. sudo /etc/init.d/mysqld stop
  4. sudo rm /var/lib/mysql/ibdata1
    sudo rm /var/lib/mysql/ib_logfile*
  5. sudo /etc/init.d/mysqld start
  6. mysqladmin -u [用户名] -p[root密码] create [数据库名称]
  7. mysql -u [用户名] -p[root密码] [数据库名称] < dumpfilename.sql

警告：这些指令会导致您失去其他数据库，如果您在此MySQL实例上有其他数据库，请确保修改步骤1,2和6,7以涵盖您希望保留的所有数据库。

0
0 Comments

MySQL的一个特别让人烦恼的特性是ibdata1文件无法缩小。除非你删除所有数据库、移除文件并重载一个转储文件，否则ibdata1文件就不能真正缩小。

但是，你可以配置MySQL，让每个表和其索引都作为单独的文件存储。这样ibdata1文件就不会变得如此庞大。根据Bill Karwin的评论，自MySQL 5.6.6版本起默认启用这个功能。

我已经做了一段时间了。然而，为了设置服务器使用单独的文件存储每个表，你需要更改my.cnf以启用此功能：

[mysqld]
innodb_file_per_table=1

https://dev.mysql.com/doc/refman/5.6/en/innodb-file-per-table-tablespaces.html

如果你想要从ibdata1中回收空间，你实际上需要删除该文件：

  1. 使用mysqldump备份所有数据库、存储过程、触发器等除了mysql和performance_schema数据库
  2. 删除上述2个数据库之外的所有数据库
  3. 停止mysql
  4. 删除ibdata1和ib_log文件
  5. 启动mysql
  6. 从备份文件中还原数据

当你在步骤5中启动MySQL时，ibdata1和ib_log文件将被重新创建。

现在你已经可以了。当你为分析创建新数据库时，表将位于单独的ibd*文件中，而不是ibdata1中。由于通常很快就会删除数据库，所以ibd*文件将被删除。

http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

你可能已经看到了这个：
http://bugs.mysql.com/bug.php?id=1341

通过使用命令ALTER TABLE ENGINE=innodb或者OPTIMIZE TABLE ，可以从ibdata1中提取数据和索引页面到单独的文件。然而，除非你执行以上步骤，否则ibdata1不会缩小。

关于information_schema，不需要也不可能删除。实际上，它只是一堆只读视图，不是表格。而且它们没有与之相关联的文件，甚至没有数据库目录。 information_schema使用内存数据库引擎，在mysqld停止/重新启动时被删除并重新生成。请参阅https://dev.mysql.com/doc/refman/5.7/en/information-schema.html。

0