How to shrink/purge ibdata1 file in MySQL
database, innodb, mysql
Solution
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.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
https://dev.mysql.com/doc/refman/5.6/en/innodb-file-per-table-tablespaces.html
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(`mysql` and `performance_schema`)
- 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.
http://dev.mysql.com/doc/refman/5.1/en/drop-database.html
You have probably seen this: http://bugs.mysql.com/bug.php?id=1341
By using the command `ALTER TABLE <tablename> ENGINE=innodb` or `OPTIMIZE TABLE <tablename>` one can extract data and index pages from ibdata1 to separate files. However, ibdata1 will not shrink unless you do the steps above.
Regarding the `information_schema`, that is not necessary nor possible to drop. It is in fact just a bunch of read-only views, not tables. And there are no files associated with the them, not even a database directory. The `informations_schema` is using the memory db-engine and is dropped and regenerated upon stop/restart of mysqld. See https://dev.mysql.com/doc/refman/5.7/en/information-schema.html.
Problem
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?