mysql ibdata1 压缩,如何压缩/清除ibdata1文件在MySQL

I am using MySQL in localhost as a "query tool" for performing statistics in R, that is, everytime I run a R script, I create a new database (A), create a new table (B), import the data into B, submit a query to get what I need, and then I drop B and drop A.

It's working fine for me, but I realize that the ibdata file size is increasing rapidly, I stored nothing in MySQL, but the ibdata1 file already exceeded 100 MB.

I am using more or less default MySQL setting for the setup, is there a way for I can automatically shrink/purge the ibdata1 file after a fixed period of time?

解决方案

That ibdata1 isn't shrinking is a particularly annoying feature of MySQL. The ibdata1 file can't actually be shrunk unless you delete all databases, remove the files and reload a dump.

But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1 will not grow as large. According to Bill Karwin's comment this is enabled by default as of version 5.6 of MySQL.

It was a while ago I did this. However, to setup your server to use separate files for each table you need to change my.cnf in order to enable this:

[mysqld]

innodb_file_per_table=1

As you want to reclaim the space from ibdata1 you actually have to delete the file:

Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases

Drop all databases except the above 2 databases

Stop mysql

Delete ibdata1 and ib_log files

Start mysql

Restore from dump

When you start MySQL in step 5 the ibdata1 and ib_log files will be recreated.

Now you're fit to go. When you create a new database for analysis, the tables will be located in separate ibd* files, not in ibdata1. As you usually drop the database soon after, the ibd* files will be deleted.

By using the command ALTER TABLE ENGINE=innodb or OPTIMIZE TABLE one can extract data and index pages from ibdata1 to separate files. However, ibdata1 will not shrink unless you do the steps above.