How to shrink/purge ibdata1 file in 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?

 Answers

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. 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

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

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

1. Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
2. Drop all databases except the above 2 databases
3. Stop mysql
4. Delete ibdata1 and ib_log files
5. Start mysql
6. 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.

---

Adding to John P's answer,

For a linux system, steps 1-6 can be accomplished with these commands:

1. mysqldump -u [username] -p[root_password] [database_name] > dumpfilename.sql
2. DROP DATABASE database_name
3. sudo /etc/init.d/mysqld stop
4. sudo rm /var/lib/mysql/ibdata1 
sudo rm /var/lib/mysql/ib_logfile (and delete any other ib_logfile's that may be named ib_logfile0, ib_logfile1 etc...)
5. sudo /etc/init.d/mysqld start
6. create database [database_name]
7. mysql -u [username]-p[root_password] [database_name] < dumpfilename.sql

Warning: these instructions will cause you to lose other databases if you have other databases on this mysql instance. Make sure that steps 1,2 and 6,7 are modified to cover all databases you wish to keep.

---

If your goal is to monitor MySQL free space and you can't stop MySQL to shrink your ibdata file, then get it through table status commands. Example:

MySQL > 5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print $20}'

MySQL < 5.1.24:

mysqlshow --status myInnodbDatabase myTable | awk '{print $35}'

Then compare this value to your ibdata file:

du -b ibdata1

Source: http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

---

As already noted you can't shrink ibdata1 (to do so you need to dump and rebuild), but there's also often no real need to.

Using autoextend (probably the most common size setting) ibdata1 preallocates storage, growing each time it is nearly full. That makes writes faster as space is already allocated.

When you delete data it doesn't shrink but the space inside the file is marked as unused. Now when you insert new data it'll reuse empty space in the file before growing the file any further.

So it'll only continue to grow if you're actually needing that data. Unless you actually need the space for another application there's probably no reason to shrink it.